Index
A B C D E F G H I J K L M N O P Q R S T U V W
A
- access paths
-
- cluster scans, 13.5.4
- defined, 13.4.2.2
- execution plans, 13.4.2
- hash scans, 13.5.5
- index scans, 13.5.3
- Active Session History, 5.1.1.3
- addmrpt.sql
-
- Automatic Database Diagnostic Monitor, 6.2.5.1
- advisors
-
- accessing with Oracle Enterprise Manager, 1.2.1
- ALL_OUTLINE_HINTS view
-
- stored outline hints, 18.1.7
- ALL_OUTLINES view
-
- stored outlines, 18.1.7
- ALL_ROWS hint, 13.2.2
- allocation
-
- of memory, 7.1
- ALTER INDEX statement, 15.1.7
- ALTER SESSION statement
-
- examples, 20.4.2
- SET SESSION_CACHED_CURSORS clause, 7.3.7
- ANALYZE statement, 14.3.1
- antijoins, 13.6.2
- applications
-
- deploying, 2.7
- design principles, 2.5
- development trends, 2.5.7
- implementing, 2.5.6
- Automatic Database Diagnostic Monitor, 6.1
-
- accessing with Oracle Enterprise Manager, 6.2.4
- actions and rationales of recommendations, 6.2.1
- addmrpt.sql report, 6.2.5.1
- analysis results example, 6.2.2
- and DB time, 6.2
- DBIO_EXPECTED, 6.2.3
- DBMS_ADVISOR package, 6.2.5.2
- example report, 6.2.2
- findings, 6.2.1
- overview, 6.2
- results, 6.2.1
- running with APIs, 6.2.5.2
- setups, 6.2.3
- STATISTICS_LEVEL parameter, 6.2.3
- types of problems considered, 6.2
- types of recommendations, 6.2
- automatic database diagnostic monitoring, 1.2.1, 11.4
- automatic segment-space management, 4.1.4.1, 8.2.6.2, 10.3.2.2.1
- Automatic Shared Memory Management, 7.1.2
- automatic SQL tuning, 1.2.1, 11.4
-
- analysis, 12.1.2
- features, 12
- overview, 12.1
- Automatic Tuning Optimizer, 12.1.1.2
- automatic undo management, 4.1.2
-
- mode, 4.1.2
- Automatic Workload Repository, 1.2.1
-
- accessing with Oracle Enterprise Manager, 5.2.1
- data gathering, 5.1
- DBMS_WORKLOAD_REPOSITORY package, 5.2.2
- default settings, 5.2
- factors affecting space usage, 5.2
- managing with APIs, 5.2.2
- minimizing space usage, 5.2
- overview, 5.2
- recommendations for retention period, 5.2
- reports, 5.2.4
- retention period, 5.2
- settings in DBA_HIST_WR_CONTROL view, 5.2.2.3
- space usage, 5.2
- statistics collected, 5.2
- turning off automatic snapshot collection, 5.2
- unusual percentages in reports, 5.2.4
- views for accessing data, 5.2.3
- awrrpt.sql
-
- Automatic Workload Repository report, 5.2.4
B
- baselines, 1.1.2.2
-
- performance, 5.1
- preserved snapshot sets, 5.2
- benchmarking workloads, 2.6.2.2
- big bang rollout strategy, 2.7.1
- bind variables, 7.3.1.3
-
- peeking, 13.4.1.2
- bitmap indexes, 2.5.3.2.2
-
- inlist iterator, 19.8.5.3
- on joins, 15.6
- when to use, 15.5
- block cleanout, 10.2.4.2
- block size
-
- choosing, 8.2.6
- optimal, 8.2.6
- bottlenecks
-
- elimination, 1.1.2.4.2
- fixing, 3.1
- identifying, 3.1
- memory, 7.1
- resource, 10.3.1.1.2
- broadcast
-
- distribution value, 19.9
- B-tree indexes, 2.5.3.2.1
- buffer busy wait events, 10.2.3, 10.3.2
-
- actions, 10.3.2.2
- buffer cache
-
- contention, 10.3.3, 10.3.4, 10.3.10.2
- hit ratio, 7.2.2.3
- reducing buffers, 7.2.3.2, 7.3.4.2
- buffer pools
-
- default cache, 7.2.4.1
- hit ratio, 7.2.6
- KEEP, 7.2.8
- KEEP cache, 7.2.4.1
- multiple, 7.2.4
- RECYCLE cache, 7.2.4.1
- business logic, 2.4.1.2.2, 2.5.6
- BYTES column
-
- PLAN_TABLE table, 19.9
C
- CARDINALITY column
-
- PLAN_TABLE table, 19.9
- cartesian joins, 13.6.6
- chained rows, 10.2.4.3
- classes
-
- wait events, 5.1.1.1, 10.1.3.2
- client/server applications, 9.4.2
- clusters, 15.8, 15.8
-
- hash and scans of, 13.5.5
- scans of, 13.5.4
- sorted hash, 15.9
- column order
-
- indexes, 2.5.3.5
- columns
-
- to index, 15.1.3
- COMPATIBLE initialization parameter, 4.1.1
- components
-
- hardware, 2.4.1.1
- software, 2.4.1.2
- composite indexes, 15.1.4
- composite partitioning
-
- examples of, 19.8.2
- conceptual modeling, 3.1.2
- consistency
-
- read, 10.2.4.2
- consistent gets from cache statistic, 7.2.2.3
- consistent mode
-
- TKPROF, 20.4.4.1
- constraints, 15.1.10
- contention
-
- library cache latch, 10.3.10.3
- memory, 7.1, 10
- shared pool, 10.3.10.3
- tuning, 10
- wait events, 10.3.10
- context switches, 9.4.4.2
- CONTROL_FILES initialization parameter, 4.1.1
- cost
-
- optimizer calculation, 13.4
- COST column
-
- PLAN_TABLE table, 19.9
- cost-based optimizations, 13.4
-
- procedures for plan stability, 18.2
- upgrading to, 18.2.2
- cpu statistics, 10.1.2.1.3
- CPUs, 2.4.1.1.1
-
- statistics, 5.1.2.1
- utilization, 9.4
- CREATE INDEX statement
-
- PARALLEL clause, 4.2.3
- CREATE OUTLINE statement, 18.1.5
- CREATE_STORED_OUTLINES initialization parameter, 18.1.5, 18.1.5.1
- CREATE_STORED_OUTLINES parameter, 18.1.5
- current mode
-
- TKPROF, 20.4.4.1
- CURSOR_NUM column
-
- TKPROF_TABLE table, 20.4.5.3
- CURSOR_SHARING initialization parameter, 7.3.2.1, 7.3.10.3, 13.3.2
- CURSOR_SPACE_FOR_TIME initialization parameter
-
- setting, 7.3.6
- cursors
-
- accessing, 7.3.2.6
- sharing, 7.3.2.6
D
- data
-
- and transactions, 2.4.1.2.4
- cache, 9.1.1
- gathering, 5.1
- modeling, 2.5.2
- queries, 2.4.2
- searches, 2.4.2
- data dictionary, 7.3.4.1.2
-
- statistics in, 14.6.1
- views used in optimization, 14.6.1
- database monitoring, 1.2.1, 11.4
-
- diagnostic, 6.1
- Database Resource Manager, 9.1.3, 9.1.3, 9.3, 10.1.2.1.3
- databases
-
- buffers, 7.2.3.2, 7.3.4.2
- diagnosing and monitoring, 6.1
- size, 2.4.2
- statistics, 5.1.1
- DATE_OF_INSERT column
-
- TKPROF_TABLE table, 20.4.5.3
- db block gets from cache statistic, 7.2.2.3
- db file scattered read wait events, 10.2.3, 10.3.3, 10.3.3
-
- actions, 10.3.3.1, 10.3.4.1
- db file sequential read wait events, 10.2.3, 10.3.3, 10.3.4
-
- actions, 10.3.4.1
- DB time
-
- metric, 6.2
- statistic, 5.1.1.2
- DB_BLOCK_SIZE initialization parameter, 4.1.1, 8.2.1.2
- DB_CACHE_ADVICE parameter, 7.2.3.1
- DB_CACHE_SIZE initialization parameter, 7.2.3.2, 7.2.4
- DB_DOMAIN initialization parameter, 4.1.1
- DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 8.2.1.1, 8.2.1.2, 8.2.1.3, 10.3.3, 13.3.2, 13.5.1
-
- cost-based optimization, 13.6.2
- DB_KEEP_CACHE_SIZE
-
- initialization parameter, 7.2.8
- DB_NAME initialization parameter, 4.1.1
- DB_nK_CACHE_SIZE initialization parameter, 7.2.3.1
- DB_RECYCLE_CACHE_SIZE
-
- initialization parameter, 7.2.9
- DB_WRITER_PROCESSES initialization parameter, 10.3.9.3.1
- DBA_HIST views, 5.2.3
- DBA_HIST_WR_CONTROL view
-
- Automatic Workload Repository settings, 5.2.2.3
- DBA_OBJECTS view, 7.2.7
- DBA_OUTLINE_HINTS view
-
- stored outline hints, 18.1.7
- DBA_OUTLINES view
-
- stored outlines, 18.1.7
- DBIO_EXPECTED parameter, 6.2.3
- DBMS_ADVISOR package, 17.1
-
- Automatic Database Diagnostic Monitor, 6.2.5, 6.2.5.2
- setting DBIO_EXPECTED, 6.2.3
- setups for ADDM, 6.2.3, 6.2.3
- DBMS_MONITOR package
-
- End to End Application Tracing, 20.1.2
- DBMS_OUTLN package
-
- procedures for managing outlines, 18.1.4
- DBMS_OUTLN_EDIT package
-
- procedures for managing outlines, 18.1.4
- DBMS_SHARED_POOL package
-
- managing the shared pool, 7.3.9
- DBMS_SQLTUNE package
-
- SQL Profiles, 12.4
- SQL Tuning Advisor, 12.2.5
- SQL Tuning Sets, 12.3.2
- DBMS_STATS package, 14.3.1, 17.1
-
- managing query optimizer statistics, 13.2.3, 14.1
- manually determining sample size for gathering procedures, 14.3.1.1
- DBMS_WORKLOAD_REPOSITORY package
-
- managing the Automatic Workload Repository, 5.2.2
- DBMS_XPLAN package
-
- displaying plan table output, 19.4
- debugging designs, 2.6.4
- default cache, 7.2.4.1
- deploying applications, 2.7
- DEPTH column
-
- TKPROF_TABLE table, 20.4.5.3
- design principles, 2.5
- designs
-
- debugging, 2.6.4
- testing, 2.6.4
- validating, 2.6.4
- development environments, 2.5.6
- diagnostic monitoring, 1.2.1, 6.1, 11.4
-
- introduction, 6.1
- direct path
-
- read events, 10.3.5
- read events actions, 10.3.5.2
- read events causes, 10.3.5.1
- wait events, 10.3.6
- write events actions, 10.3.6.2
- write events causes, 10.3.6.1
- disabled constraints, 15.1.10
- disks
-
- monitoring operating system file activity, 10.1.2.2
- statistics, 5.1.2.3
- DISTRIBUTION column
-
- PLAN_TABLE table, 19.9
- domain indexes
-
- and EXPLAIN PLAN, 19.8.6
- using, 15.7
- dynamic sampling
-
- improving performance, 14.5.6.3
- level settings, 14.5.6.4, 14.5.6.4
- process, 14.5.6.1
- purpose, 14.5.6
- when to use, 14.5.6.2
E
- emergencies
-
- performance, 3.2
- Emergency Performance Method, 3.2.1
- enabled constraints, 15.1.10
- End to End Application Tracing, 20, 20.1
-
- accessing with Oracle Enterprise Manager, 20.1.1
- action and module names, 2.5.6, 20.1
- creating a service, 20.1
- DBMS_APPLICATION_INFO package, 20.1
- DBMS_MONITOR package, 20.1.2
- enforced constraints, 15.1.10
- enqueue wait events, 10.2.3, 10.3.7
-
- actions, 10.3.7.2
- statistics, 10.1.3.4.5
- equijoins, 11.5.3.1
- error message documentation, Preface
- estimating workloads, 2.6.2
-
- benchmarking, 2.6.2.2
- extrapolating, 2.6.2.1
- examples
-
- ALTER SESSION statement, 20.4.2
- EXPLAIN PLAN output, 20.4.4.10
- SQL trace facility output, 20.4.4.10
- EXECUTE_TASK procedure, 17.2.5.3.1
- execution plans
-
- examples, 20.4.3.1
- joins, 13.6.1
- overview of, 13.4.2
- plan stability, 18.1
- preserving with plan stability, 18.1
- TKPROF, 20.4.3.1, 20.4.3.2
- viewing with the utlxpls.sql script, 13.4.2.1
- EXPLAIN PLAN statement
-
- access paths, 13.5.6
- and domain indexes, 19.8.6
- and full partition-wise joins, 19.8.4
- and partial partition-wise joins, 19.8.3
- and partitioned objects, 19.8
- basic steps, 13.4.2.1
- examples of output, 20.4.4.10
- execution order of steps in output, 13.4.2.1
- invoking with the TKPROF program, 20.4.3.2
- PLAN_TABLE table, 19.2
- restrictions, 19.1.4
- scripts for viewing output, 13.4.2.1
- viewing the output, 13.4.2.1
- EXPLAIN_MVIEW procedure, 17.3
- Export utility
-
- statistics on system-generated columns names, 14.5.2
- expression
-
- mixed-type, 11.5.3.2
- extended syntax
-
- for specifying tables in hints, 16.2.3
- global hints, 16.2.3
- EXTENT MANAGEMENT LOCAL
-
- creating temporary tablespaces, 4.1.4.2
- extrapolating workloads, 2.6.2.1
F
- features, new, Preface
- FILESYSTEMIO_OPTIONS initialization parameter, 9.1.1.2
- FIRST_ROWS(n) hint, 13.2.2
- free buffer wait events, 10.2.3, 10.3.9
- free lists, 10.3.2.2.1
- FULL hint, 15.1.6
- full outer joins, 13.6.7.4
- full partition-wise joins, 19.8.4
- full table scans, 10.3.5.2.2
- function-based indexes, 2.5.3.2.3, 15.2
G
- GATHER_ INDEX_STATS procedure
-
- in DBMS_STATS package, 14.3.1
- GATHER_DATABASE_STATS procedure
-
- in DBMS_STATS package, 14.3.1
- GATHER_DATABASE_STATS_JOB_PROC procedure
-
- and GATHER_STATS_JOB in Maintenance Window, 14.2.1
- automatically gathering optimizer statistics, 14.2.1
- GATHER_DICTIONARY_STATS procedure
-
- in DBMS_STATS package, 14.3.1
- GATHER_SCHEMA_STATS procedure
-
- in DBMS_STATS package, 14.3.1
- GATHER_STATS_JOB
-
- automatically gathering optimizer statistics, 14.2.1
- GATHER_TABLE_STATS procedure
-
- in DBMS_STATS package, 14.3.1
- GETMISSES column
-
- in V$ROWCACHE table, 7.3.4.1.2
- GETS column
-
- in V$ROWCACHE view, 7.3.4.1.2
- global hints, 16.2.3
- GV$BUFFER_POOL_STATISTICS view, 7.2.6
H
- hard parsing, 2.5.5
- hardware
-
- components, 2.4.1.1
- limitations of components, 2.3.3
- sizing of components, 2.3.3
- hash
-
- distribution value, 19.9
- hash clusters
-
- scans of, 13.5.5
- sorted, 15.9
- hash joins, 13.6.4
-
- cost-based optimization, 13.6.2
- index join, 13.5.3.8.1
- hash partitions, 19.8
-
- examples of, 19.8.1
- hashing, 15.9
- high water mark, 13.5.1
- hints
-
- access paths, 11.5.4, 16.1.2.2, 16.1.2.3
- as used in outlines, 18.1.1.1
- cannot override sample access path, 13.5.7
- degree of parallelism, 16.1.2.6
- FULL, 15.1.6
- global, 16.2.3
- global compared to local, 16.2.3
- how to use, 16.1
- INDEX_FFS, 13.5.3.7
- INDEX_JOIN, 13.5.3.8
- indexspec syntax, 16.2.4
- join operations, 16.1.2.5
- location syntax, 16.2.2
- NO_INDEX, 15.1.6
- optimization approach and goal, 16.1.2.1
- optimizer, 16.1
- ORDERED hint, 13.6.2
- overriding optimizer choice, 13.5.7
- overriding OPTIMIZER_MODE, 13.2.2
- parallel query option, 16.1.2.6
- specifying a query block, 16.2.2
- specifying indexes, 16.2.4
- tablespec syntax, 16.2.3
- using extended syntax, 16.2.3
- histograms
-
- frequency, 14.6.2.2
- height-balanced, 14.6.2.1
- viewing, 14.6.2
- HOLD_CURSOR clause, 7.3.2.6.2
- hours of service, 2.4.2
- HW enqueue
-
- contention, 10.3.7.2.2
I
- ID column
-
- PLAN_TABLE table, 19.9
- idle wait events, 10.4
-
- SQL*Net message from client, 10.3.1.1
- implementing business logic, 2.4.1.2.2
- Import utility
-
- copying statistics, 14.5.2
- INDEX hint, 15.1.6
- INDEX_COMBINE hint, 15.1.6
- INDEX_FFS hint, 13.5.3.7, 13.5.3.7.1
- INDEX_JOIN hint, 13.5.3.8
- indexes
-
- adding columns, 2.5.3.1
- appending columns, 2.5.3.1
- avoiding the use of, 15.1.6
- bitmap, 2.5.3.2.2, 15.5
- B-tree, 2.5.3.2.1
- choosing columns for, 15.1.3
- column order, 2.5.3.5
- composite, 15.1.4
- costs, 2.5.3.3
- creating, 4.2.3
- design, 2.5.3
- domain, 15.7
- dropping, 15.1.1
- enforcing uniqueness, 15.1.9
- ensuring the use of, 15.1.5
- function-based, 2.5.3.2.3, 15.2
- improving selectivity, 15.1.4
- index joins, 13.5.3.8.1
- joins, 13.5.3.8.1
- low selectivity, 15.1.6
- modifying values of, 15.1.3
- non-unique, 15.1.9
- partitioned, 2.5.3.2.4
- placement on disk, 8.2.2
- rebuilding, 15.1.7
- re-creating, 15.1.7
- reducing I/O, 2.5.3.5
- reverse key, 2.5.3.2.5
- scans of, 13.5.3
- selectivity, 2.5.3.5
- selectivity of, 15.1.3
- sequences in, 2.5.3.4
- serializing in, 2.5.3.4
- specifying in hints, 16.2.4
- statistics gathering, 14.5
- index-organized tables, 2.5.3.1
- indexspec
-
- hint syntax, 16.2.4
- initialization parameters
-
- CONTROL_FILES, 4.1.1
- DB_BLOCK_SIZE, 4.1.1
- DB_DOMAIN, 4.1.1
- DB_FILE_MULTIBLOCK_READ_COUNT, 13.6.2
- DB_NAME, 4.1.1
- OPEN_CURSORS, 4.1.1
- OPTIMIZER_DYNAMIC_SAMPLING, 14.5.6.1, 14.5.6.3, 14.5.6.4
- OPTIMIZER_FEATURES_ENABLE, 13.5.3.7, 13.5.3.8
- OPTIMIZER_MODE, 13.2.1, 16.1.2.1
- PGA_AGGREGATE_TARGET, 4.2.3.1
- PROCESSES, 4.1.1
- SESSION_CACHED_CURSORS, 7.3.7
- SESSIONS, 4.1.1
- SQL_TRACE, 20.4.2, 20.4.2
- STREAMS_POOL_SIZE, 4.1.1, 7.1.2
- USER_DUMP_DEST, 20.4.1
- INLIST ITERATOR operation, 19.8.5
- inlists, 19.8.5
- instance configuration
-
- initialization files, 4.1.1
- performance considerations, 4.1
- Internet scalability, 2.3.2
- I/O
-
- and SQL statements, 10.3.3.4
- contention, 5.1.1.1, 10.1.2.2, 10.1.3.2, 10.3.3.2, 10.3.14
- excessive I/O waits, 10.3.3.2
- monitoring, 10.1.2.2
- objects causing I/O waits, 10.3.3.5
- reducing, 15.1.4
- IOT (index-organized table), 2.5.3.1
J
- joins
-
- antijoins, 13.6.2
- cartesian, 13.6.6
- execution plans and, 13.6.1
- full outer, 13.6.7.4
- hash, 13.6.4
- index joins, 13.5.3.8.1
- join order and execution plans, 13.4.2
- nested loop, 13.6.3
- nested loops and cost-based optimization, 13.6.2
- order, 11.5.4, 11.5.4
- outer, 13.6.7
- partition-wise
-
- examples of full, 19.8.4
- examples of partial, 19.8.3
- full, 19.8.4
- semijoins, 13.6.2
- sort merge, 13.6.5
- sort-merge and cost-based optimization, 13.6.2, 13.6.2
K
- KEEP buffer pool, 7.2.8
- KEEP cache, 7.2.4.1
L
- LARGE_POOL_SIZE initialization parameter, 7.3.5.1
- latch contention
-
- library cache latches, 10.2.1.2
- shared pool latches, 10.2.1.2
- latch free wait events, 10.2.3
-
- actions, 10.3.10.1
- latch wait events, 10.3.10
- latches
-
- tuning, 1.1.2.3, 10.3.10.3
- library cache
-
- latch contention, 10.3.10.3
- latch wait events, 10.3.10.1
- lock, 10.3.13
- memory allocation, 7.3.4.1.1
- pin, 10.3.12
- linear scalability, 2.3.3
- locks and lock holders
-
- finding, 10.3.7.1
- log buffer
-
- space wait events, 10.2.3, 10.3.14
- tuning, 7.4.1
- log file
-
- parallel write wait events, 10.3.11
- switch wait events, 10.3.15
- sync wait events, 10.2.3, 10.3.16
- log writer processes
-
- tuning, 8.2.3.2
- LOG_BUFFER initialization parameter, 7.4
-
- setting, 7.4.2
- LRU
-
- aging policy, 7.2.4
- latch contention, 10.3.10.3.4
M
- managing the user interface, 2.4.1.2.1
- materialized views
-
- tuning, 17.3
- max session memory statistic, 7.3.5.1.2
- MAX_DISPATCHERS initialization parameter, 4.3.1.1
- MAX_DUMP_FILE_SIZE initialization parameter
-
- SQL Trace, 20.4.1
- MAXOPENCURSORS clause, 7.3.2.6.2
- memory
-
- hardware component, 2.4.1.1.2
- Memory Advisor
-
- accessing with Oracle Enterprise Manager, 7.1
- memory allocation
-
- importance, 7.1
- library cache, 7.3.4.1.1
- shared SQL areas, 7.3.4.1.1
- tuning, 7.1.6
- metrics, 5.1
- migrated rows, 10.2.4.3
- mirroring
-
- redo logs, 8.2.3.3
- modeling
-
- conceptual, 3.1.2
- data, 2.5.2
- workloads, 2.6.3
- monitoring
-
- diagnostic, 1.2.1, 11.4
- multiple buffer pools, 7.2.4
N
- NAMESPACE column
-
- V$LIBRARYCACHE view, 7.3.3.2
- nested loop joins, 13.6.3
-
- cost-based optimization, 13.6.2
- network
-
- hardware component, 2.4.1.1.4
- speed, 2.4.2
- statistics, 5.1.2.4
- network communication wait events, 10.3.1
-
- db file scattered read wait events, 10.3.3
- db file sequential read wait events, 10.3.3, 10.3.4
- SQL*Net message from Dblink, 10.3.1.2
- SQL*Net more data to client, 10.3.1.3
- new features, Preface
- NO_INDEX hint, 15.1.6
- NOT IN subquery, 13.6.2
O
- OBJECT_INSTANCE column
-
- PLAN_TABLE table, 19.9
- OBJECT_NAME column
-
- PLAN_TABLE table, 19.9
- OBJECT_NODE column
-
- PLAN_TABLE table, 19.9
- OBJECT_OWNER column
-
- PLAN_TABLE table, 19.9
- OBJECT_TYPE column
-
- PLAN_TABLE table, 19.9
- object-orientation, 2.5.7
- OLAP_PAGE_POOL_SIZE initialization parameter, 7.5.2
- OPEN_CURSORS initialization parameter, 4.1.1
-
- increasing cursors for each session, 7.3.4.1.1
- operating system
-
- data cache, 9.1.1
- monitoring disk I/O, 10.1.2.2
- statistics, 5.1.2
- OPERATION column
-
- PLAN_TABLE table, 19.9, 19.9
- optimization
-
- and dynamic sampling, 13.2.3
- choosing the approach, 13.2.1
- cost calculation, 13.4
- cost-based, 13.4
- cost-based and choosing an access path, 13.5.7
- described, 13.1
- hints, 13.2.2, 13.5.3.7, 13.5.3.8
- manual, 13.2.2
- operations performed, 13.1
- optimizer
-
- cost calculation, 13.4
- goals, 13.2, 13.2
- introduction, 1.1.3.1, 13.1
- modes, 12.1.1
- moving to from RBO, 18.2.1
- operations, 13.1
- parameters for setting mode, 13.2.1
- plan stability, 18.1
- query, 1.1.3.1
- response time, 13.2
- statistics, 14.1
- throughput, 13.2
- upgrading, 18.2.2
- OPTIMIZER column
-
- PLAN_TABLE, 19.9
- OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, 14.5.6.1, 14.5.6.3, 14.5.6.4
- OPTIMIZER_FEATURES_ENABLE initialization parameter, 13.3.1, 13.5.3.7, 13.5.3.8
- OPTIMIZER_INDEX_CACHING initialization parameter, 13.3.2
- OPTIMIZER_INDEX_COST_ADJ initialization parameter, 13.3.2
- OPTIMIZER_MODE initialization parameter, 13.2.1, 13.2.1, 13.3.2, 13.3.2, 16.1.2.1
-
- hints affecting, 13.2.2
- OPTIONS column
-
- PLAN_TABLE table, 19.9
- OPTMIZER_DYNAMIC_SAMPLING initialization parameter, 13.2.3
- Oracle CPU statistics, 10.1.2.1.3
- Oracle Enterprise Manager
-
- accessing advisors, 1.2.1
- accessing SQL Tuning Sets, 12.3.1
- accessing the SQLAccess Advisor, 11.4
- advisors, 1.2.1
- Outline Editor, 18.1.6
- Performance page, 1.2.1
- Oracle Forms, 20.4.2
-
- control of parsing and private SQL areas, 7.3.2.6.5
- Oracle performance improvement method, 3.1
-
- steps, 3.1.1
- Oracle-managed files, 8.2.5
-
- tuning, 8.2.5
- order
-
- joins, 11.5.4
- ORDERED hint, 13.6.2
- OTHER column
-
- PLAN_TABLE table, 19.9
- OTHER_TAG column
-
- PLAN_TABLE table, 19.9
- outer joins, 11.5.4.1, 13.6.7
- Outline Editor, 18.1.6
- outlines
-
- CREATE OUTLINE statement, 18.1.5
- creating and using, 18.1.5
- description, 18.1
- execution plans and plan stability, 18.1
- hints, 18.1.1.1
- moving tables, 18.1.8
- moving to the cost-based optimizer, 18.2.1
- storage requirements, 18.1.2
- using, 18.1.6
- viewing data for, 18.1.7
P
- packages
-
- DBMS_ADVISOR, 17.1
- DBMS_STATS, 17.1
- page table, 9.4.1.2
- paging, 9.4.2
-
- reducing, 7.1.5.1
- PARALLEL clause
-
- CREATE INDEX statement, 4.2.3
- PARENT_ID column
-
- PLAN_TABLE table, 19.9
- parsing
-
- hard, 2.5.5
- Oracle Forms, 7.3.2.6.5
- Oracle precompilers, 7.3.2.6.2
- reducing unnecessary calls, 7.3.2.6
- soft, 2.5.5
- PARTITION_ID column
-
- PLAN_TABLE table, 19.9
- PARTITION_START column
-
- PLAN_TABLE table, 19.9
- PARTITION_STOP column
-
- PLAN_TABLE table, 19.9
- partitioned indexes, 2.5.3.2.4
- partitioned objects
-
- and EXPLAIN PLAN statement, 19.8
- partitioning
-
- distribution value, 19.9
- examples of, 19.8.1
- examples of composite, 19.8.2
- hash, 19.8
- range, 19.8
- start and stop columns, 19.8.1
- partition-wise joins
-
- full, 19.8.4
- full, and EXPLAIN PLAN output, 19.8.4
- partial, and EXPLAIN PLAN output, 19.8.3
- PCTFREE parameter, 4.2, 10.2.4.3
- PCTUSED parameter, 10.2.4.3, 10.2.4.3
- peeking
-
- bind variables, 13.4.1.2
- performance
-
- emergencies, 3.2
- improvement method, 3.1
- improvement method steps, 3.1.1
- mainframe, 9.2.3
- monitoring memory on Windows, 9.4.1.1
- tools for diagnosing and tuning, 1.2
- UNIX-based systems, 9.2.1
- viewing execution plans, 13.4.2.1
- Windows, 9.2.2
- PGA_AGGREGATE_TARGET initialization parameter, 4.1.1, 4.2.3.1, 7.5.1, 9.1.2.2, 13.3.2
- physical reads from cache statistic, 7.2.2.3
- plan stability, 18.1
-
- limitations of, 18.1.1
- preserving execution plans, 18.1
- procedures for the cost-based optimizer, 18.2
- use of hints, 18.1.1
- PLAN_TABLE table
-
- BYTES column, 19.9
- CARDINALITY column, 19.9
- COST column, 19.9
- creating, 19.2, 19.2
- displaying, 19.4
- DISTRIBUTION column, 19.9
- ID column, 19.9
- OBJECT_INSTANCE column, 19.9
- OBJECT_NAME column, 19.9
- OBJECT_NODE column, 19.9
- OBJECT_OWNER column, 19.9
- OBJECT_TYPE column, 19.9
- OPERATION column, 19.9
- OPTIMIZER column, 19.9
- OPTIONS column, 19.9
- OTHER column, 19.9
- OTHER_TAG column, 19.9
- PARENT_ID column, 19.9
- PARTITION_ID column, 19.9
- PARTITION_START column, 19.9
- PARTITION_STOP column, 19.9
- POSITION column, 19.9
- REMARKS column, 19.9
- SEARCH_COLUMNS column, 19.9
- STATEMENT_ID column, 19.9
- TIMESTAMP column, 19.9
- PL/SQL procedures
-
- EXPLAIN_MVIEW, 17.3
- TUNE_MVIEW, 17.3
- POSITION column
-
- PLAN_TABLE table, 19.9
- precompilers
-
- control of parsing and private SQL areas, 7.3.2.6.2
- preserved snapshots, 5.2
- PRIMARY KEY constraint, 15.1.9
- PRIVATE_SGA variable, 7.3.5.1.3
- privileges
-
- SQL Access Advisor, 17.2.2
- proactive monitoring, 1.1.2.4.1
- processes
-
- scheduling, 9.4.4.1
- PROCESSES initialization parameter, 4.1.1
- program global area (PGA)
-
- direct path read, 10.3.5
- direct path write, 10.3.6
- shared servers, 7.3.5.1.1
- programming languages, 2.5.6
Q
- queries
-
- avoiding the use of indexes, 15.1.6
- data, 2.4.2
- ensuring the use of indexes, 15.1.5
- query optimizer, 1.1.3.1
-
- See optimizer
R
- range
-
- distribution value, 19.9
- examples of partitions, 19.8.1
- partitions, 19.8
- rdbms ipc reply wait events, 10.3.17
- read consistency, 10.2.4.2
- read wait events
-
- direct path, 10.3.5
- scattered, 10.3.3
- REBUILD clause, 15.1.7
- recursive calls, 20.4.4.5
- RECYCLE cache, 7.2.4.1
- REDO BUFFER ALLOCATION RETRIES statistic, 7.4.2
- redo logs, 4.1.3
-
- buffer size, 10.3.14
- mirroring, 8.2.3.3
- placement on disk, 8.2.3.2
- sizing, 4.1.3
- space requests, 10.2.4.1
- reducing
-
- contention with dispatchers, 4.3.1.1
- contention with shared servers, 4.3.2
- data dictionary cache misses, 7.3.4.1.2
- paging and swapping, 7.1.5.1
- unnecessary parse calls, 7.3.2.6
- RELEASE_CURSOR clause, 7.3.2.6.2
- REMARKS column
-
- PLAN_TABLE table, 19.9
- resources
-
- allocation, 2.4.1.2.3, 2.5.6
- bottlenecks, 10.3.1.1.2
- wait events, 10.3.4
- response time, 2.4.2
-
- cost-based approach, 13.2.1
- optimizer goal, 13.2
- optimizing, 13.2
- reverse key indexes, 2.5.3.2.5
- rollout strategies
-
- big bang approach, 2.7.1
- trickle approach, 2.7.1
- round-robin
-
- distribution value, 19.9
- row cache objects, 10.3.10.3.6
- row sources, 13.4.2.2
- rowids
-
- table access by, 13.5.2
- rows
-
- row sources, 13.4.2.2
- rowids used to locate, 13.5.2
S
- SAMPLE BLOCK clause, 13.5.6
-
- access path and hints, 13.5.7
- SAMPLE clause, 13.5.6
-
- access path and hints cannot override, 13.5.7
- sample table scans, 13.5.6
-
- hints cannot override, 13.5.7
- sar UNIX command, 9.4.1.1
- scalability, 2.3.1
-
- factors preventing, 2.3.3, 2.3.3
- Internet, 2.3.2
- linear, 2.3.3
- scans
-
- index, 13.5.3
- index joins, 13.5.3.8.1
- index of type bitmap, 13.5.3.9
- sample table, 13.5.6
- sample table and hints cannot override, 13.5.7
- scattered read wait events, 10.3.3
-
- actions, 10.3.3.1
- SEARCH_COLUMNS column
-
- PLAN_TABLE table, 19.9
- segment-level statistics, 10.1.3.5
- SELECT statement
-
- SAMPLE clause, 13.5.6
- selectivity
-
- creating indexes, 15.1.3
- improving for an index, 15.1.4
- indexes, 15.1.6
- ordering columns in an index, 2.5.3.5
- semijoins, 13.6.2
- sequential read wait events
-
- actions, 10.3.4.1
- service hours, 2.4.2
- session memory statistic, 7.3.5.1.2
- SESSION_CACHED_CURSORS initialization parameter, 7.3.7
- SESSIONS initialization parameter, 4.1.1
- SGA size, 7.4.1
- SGA_TARGET initialization parameter, 4.1.1
-
- and Automatic Shared Memory Management, 7.1.2
- automatic memory management, 7.1.2
- shared pool contention, 10.3.10.3
- shared server
-
- performance issues, 4.3
- reducing contention, 4.3
- tuning, 4.3
- tuning memory, 7.3.5.1
- shared SQL areas
-
- memory allocation, 7.3.4.1.1
- SHARED_POOL_RESERVED_SIZE initialization parameter, 7.3.8.2
- SHARED_POOL_SIZE initialization parameter, 7.3.4.1.2, 7.3.4.2, 7.3.8.4
-
- allocating library cache, 7.3.4.1.1
- tuning the shared pool, 7.3.5.1.4
- SHOW SGA statement, 7.1.5.2
- sizing redo logs, 4.1.3
- snapshots
-
- preserved set, 5.2
- soft parsing, 2.5.5
- software
-
- components, 2.4.1.2
- sort areas
-
- tuning, 7.5
- sort merge joins, 13.6.5
-
- cost-based optimization, 13.6.2
- SQL Access Advisor, 17.1, 17.1, 17.2.3.2
-
- constants, 17.2.8
- creating a task, 17.1.1
- defining the workload, 17.1.1
- EXECUTE_TASK procedure, 17.2.5.3.1
- generating the recommendations, 17.1.1
- implementing the recommendations, 17.1.1
- maintaining workloads, 17.2.4.8
- privileges, 17.2.2
- quick tune, 17.2.6
- recommendation process, 17.2.5.6
- steps in using, 17.1.1
- workload objects, 17.2.4.1
- SQL Access Advisor workloads
-
- maintaining, 17.2.4.8
- SQL Profiles
-
- description, 12.1.2.2
- managing with APIs, 12.4
- SQL statements
-
- avoiding the use of indexes, 15.1.6
- ensuring the use of indexes, 15.1.5
- execution plans of, 13.4.2
- modifying indexed data, 15.1.3
- waiting for I/O, 10.3.3.4
- SQL trace facility, 20.3.1, 20.4.3
-
- example of output, 20.4.4.10
- output, 20.4.4.1
- statement truncation, 20.4.4.7
- steps to follow, 20.4
- trace files, 20.4.1
- SQL Tuning Advisor, 1.2.1, 11.4
-
- administering with APIs, 12.2.5
- input sources, 12.2.1
- overview, 12.2
- tuning options, 12.2.2
- SQL Tuning Sets
-
- accessing with Oracle Enterprise Manager, 12.3.1
- description, 11.4, 12.2, 12.2.1, 12.2.5
- managing with APIs, 12.3, 12.3.2
- SQL Workload Journal, 17.2.5.5
- SQL*Net
-
- message from client idle events, 10.3.1.1
- message from dblink wait events, 10.3.1.2
- more data to client wait events, 10.3.1.3
- SQL_STATEMENT column
-
- TKPROF_TABLE, 20.4.5.3
- SQL_TRACE
-
- initialization parameter, 20.4.2
- SQLAccess Advisor, 1.2.1, 11.4
-
- accessing with Oracle Enterprise Manager, 11.4
- SQLTUNE_CATEGORY initialization parameter
-
- determining the SQL Profile category, 12.1.2.2
- ST enqueue
-
- contention, 10.3.7.2.1
- STAR_TRANSFORMATION_ENABLED initialization parameter, 13.3.2
- start columns
-
- in partitioning and EXPLAIN PLAN statement, 19.8.1
- STATEMENT_ID column
-
- PLAN_TABLE table, 19.9
- statistics
-
- and STATISTICS_LEVEL initialization parameter, 1.2
- automatic gathering, 14.2
- baselines, 5.1
- collecting on external tables, 14.2.3.1
- consistent gets from cache, 7.2.2.3
- databases, 5.1.1
- db block gets from cache, 7.2.2.3
- displaying in views, 14.6.1
- enabling automatic gathering, 14.2.2
- exporting and importing, 14.5.2
- GATHER_STATS_JOB, 14.2.1
- gathering, 5.1
- gathering stale, 14.3.1.5
- gathering using sampling, 14.3.1.1
- gathering with DBMS_STATS package, 14.3.1
- gathering with DBMS_STATS procedures, 14.3.1
- generating for query optimization, 14.2
- histograms, 14.6.2
- limitations on restoring previous versions, 14.5.1
- locking, 14.5.4
- manually gathering, 14.3
- max session memory, 7.3.5.1.2
- missing, 14.5.7
- operating systems, 5.1.2
-
- CPU statistics, 5.1.2.1
- disk statistics, 5.1.2.3
- network statistics, 5.1.2.4
- virtual memory statistics, 5.1.2.2
- optimizer, 14.1
- optimizer mode, 13.2.1
- optimizer use of, 13.4
- physical reads from cache, 7.2.2.3
- restoring previous versions, 14.5.1
- segment-level, 10.1.3.5
- session memory, 7.3.5.1.2
- shared server processes, 4.3.2
- stale, 14.3.1.5
- system, 14.4
- time model, 5.1.1.2
- user-defined, 14.3.1.6
- when to gather, 14.3.2
- STATISTICS_LEVEL initialization parameter, 5.1.3, 10.1.3.1
-
- and Automatic Workload Repository, 5.2
- enabling automatic database diagnostic monitoring, 6.2.3
- settings for statistic gathering, 1.2
- stop columns
-
- in partitioning and EXPLAIN PLAN statement, 19.8.1
- stored outlines
-
- creating and using, 18.1.5
- execution plans and plan stability, 18.1
- hints, 18.1.1.1
- moving tables, 18.1.8
- storage requirements, 18.1.2
- using, 18.1.6
- viewing data for, 18.1.7
- STREAMS_POOL_SIZE initialization parameter, 4.1.1, 7.1.2
- striping
-
- manual, 8.2.2
- subqueries
-
- NOT IN, 13.6.2
- unnesting, 11.5.4.1.3
- swapping, 9.4.1.1, 9.4.2
-
- reducing, 7.1.5.1
- switching processes, 9.4.4.1
- system architecture, 2.4
-
- configuration, 2.4.2
- hardware components, 2.4.1.1
-
- CPUs, 2.4.1.1.1
- I/O subsystems, 2.4.1.1.3
- memory, 2.4.1.1.2
- networks, 2.4.1.1.4
- software components, 2.4.1.2
-
- data and transactions, 2.4.1.2.4
- implementing business logic, 2.4.1.2.2
- managing the user interface, 2.4.1.2.1
- user requests and resource allocation, 2.4.1.2.3
- System Global Area tuning, 7.1.5.2
T
- tables
-
- creating, 4.2
- design, 2.5.3
- full scans, 10.3.5.2.2
- placement on disk, 8.2.2
- setting storage options, 4.2
- tablespaces, 4.1.4
-
- creating, 4.1.4
- creating temporary, 4.1.4.2
- temporary, 4.1.4, 4.1.4.2
- tablespec
-
- hint syntax, 16.2.3
- templates
-
- SQL Access Advisor, 17.2.3.2
- temporary tablespaces, 4.1.4
-
- creating, 4.1.4.2
- testing designs, 2.6.4
- thrashing, 9.4.2
- throughput
-
- optimizer goal, 13.2
- optimizing, 13.2
- time model statistics, 5.1.1.2
- TIMED_STATISTICS initialization parameter
-
- SQL Trace, 20.4.1
- TIMESTAMP column
-
- PLAN_TABLE table, 19.9
- TKPROF program, 20.3.2, 20.4.3
-
- editing the output SQL script, 20.4.5.2
- example of output, 20.4.4.10
- generating the output SQL script, 20.4.5.1
- row source operations, 20.4.4.2
- syntax, 20.4.3.2
- using the EXPLAIN PLAN statement, 20.4.3.2
- wait event information, 20.4.4.3
- TKPROF_TABLE, 20.4.5.3
-
- querying, 20.4.5.3
- TM enqueue
-
- contention, 10.3.7.2.3
- tools
-
- for performance tuning, 1.2
- TRACEFILE_IDENTIFIER initialization parameter
-
- identifying trace files, 20.4.1
- tracing
-
- consolidating with trcsess, 20.2
- identifying files, 20.4.1
- transactions and data, 2.4.1.2.4
- trcsess utility, 20.2
- trickle rollout strategy, 2.7.1
- TUNE_MVIEW procedure, 17.3
- tuning
-
- and bottleneck elimination, 1.1.2.4.2
- and proactive monitoring, 1.1.2.4.1
- latches, 1.1.2.3, 10.3.10.3
- logical structure, 15.1.1
- memory allocation, 7.1.6
- resource contention, 10
- shared server, 4.3
- sorts, 7.5
- SQL Tuning Advisor, 12.2
- System Global Area (SGA), 7.1.5.2
- TX enqueue
-
- contention, 10.3.7.2.4
- type conversion, 11.5.3.2
U
- undo management
-
- automatic mode, 4.1.2
- UNDO TABLESPACE clause, 4.1.2
- UNDO_MANAGEMENT initialization parameter, 4.1.1, 4.1.2
- UNDO_TABLESPACE initialization parameter, 4.1.1
- UNIQUE constraint, 15.1.9
- uniqueness, 15.1.9
- UNIX system performance, 9.2.1
- untransformed column values, 11.5.3.2
- upgrade
-
- to the cost-based optimizer, 18.2.2
- USE_STORED_OUTLINES parameter, 18.1.6, 18.1.6
- user global area (UGA)
-
- shared servers, 4.3, 7.3.5.1
- V$SESSTAT, 7.3.5.1.2
- user requests, 2.4.1.2.3
- USER_DUMP_DEST initialization parameter, 20.4.1
-
- SQL Trace, 20.4.1
- USER_ID column
-
- TKPROF_TABLE, 20.4.5.3
- USER_OUTLINE_HINTS view
-
- stored outline hints, 18.1.7
- USER_OUTLINES view
-
- stored outlines, 18.1.7
- user-defined bind variables, 13.4.1.2
- users
-
- interaction method, 2.4.2
- interfaces, 2.5.6
- location, 2.4.2
- network speed, 2.4.2
- number of, 2.4.2
- requests, 2.5.6
- response time, 2.4.2
- UTLCHN1.SQL script, 10.2.4.3
- UTLXPLP.SQL script
-
- displaying plan table output, 19.4
- for viewing EXPLAIN PLANs, 13.4.2.1
- UTLXPLS.SQL script
-
- displaying plan table output, 19.4
- for viewing EXPLAIN PLANs, 13.4.2.1
- used for displaying EXPLAIN PLANs, 13.4.2.1
V
- V$ACTIVE_SESSION_HISTORY view, 5.1.1.3, 10.1.3.3
- V$ADVISOR_PROGRESS view, 12.2.5.4, 12.5
- V$BH view, 7.2.7
- V$BUFFER_POOL_STATISTICS view, 7.2.6, 7.2.6
- V$DB_CACHE_ADVICE view, 7.2.2.1, 7.2.2.3, 7.2.3, 7.2.3.1, 7.2.3.2, 7.2.5
- V$EVENT_HISTOGRAM view, 10.1.3.3
- V$FILE_HISTOGRAM view, 10.1.3.3
- V$JAVA_LIBRARY_CACHE_MEMORY view, 7.3.3.3.3
- V$JAVA_POOL_ADVICE view, 7.3.3.3.3
- V$LIBRARY_CACHE_MEMORY view, 7.3.3.3.2
- V$LIBRARYCACHE view
-
- NAMESPACE column, 7.3.3.2
- V$OSSTAT view, 5.1.2.1
- V$PGASTAT view, 7.5.1.2.1
- V$PROCESS view, 7.5.1.2.2
- V$PROCESS_MEMORY view, 7.5.1.2.3
- V$QUEUE view, 4.3.2
- V$ROWCACHE view
-
- GETMISSES column, 7.3.4.1.2
- GETS column, 7.3.4.1.2
- performance statistics, 7.3.3.4
- V$RSRC_CONSUMER_GROUP view, 10.1.2.1.3
- V$SESS_TIME_MODEL view, 5.1.1.2, 10.1.3.3
- V$SESSION view, 10.1.3.3, 10.1.3.4, 10.3
- V$SESSION_EVENT view, 10.1.3.3, 10.3
- V$SESSION_WAIT view, 10.1.3.3, 10.3
- V$SESSION_WAIT_CLASS view, 10.1.3.3
- V$SESSION_WAIT_HISTORY view, 10.1.3.3, 10.1.3.3
- V$SESSTAT view, 10.1.2.1.3
-
- using, 7.3.5.1.2
- V$SHARED_POOL_ADVICE view, 7.3.3.3.1
- V$SHARED_POOL_RESERVED view, 7.3.8.4
- V$SQL_PLAN view
-
- using to display execution plan, 19.1.3.1
- V$SQL_PLAN_STATISTICS view
-
- using to display execution plan statistics, 19.1.3.1
- V$SQL_PLAN_STATISTICS_ALL view
-
- using to display execution plan information, 19.1.3.1
- V$SQL_WORKAREA view, 7.5.1.2.6
- V$SQL_WORKAREA_ACTIVE view, 7.5.1.2.5
- V$SQL_WORKAREA_HISTOGRAM view, 7.5.1.2.4
- V$SYS_TIME_MODEL view, 5.1.1.2, 5.1.2.1, 10.1.3.3
- V$SYSMETRIC_HISTORY view, 5.1.2.1
- V$SYSSTAT view
-
- redo buffer allocation, 7.4.2
- using, 7.2.2.3
- V$SYSTEM_EVENT view, 10.1.3.3, 10.3
- V$SYSTEM_WAIT_CLASS view, 10.1.3.3
- V$TEMP_HISTOGRAM view, 10.1.3.3
- V$UNDOSTAT view, 4.1.2
- V$WAITSTAT view, 10.1.3.4
- validating designs, 2.6.4
- views, 2.5.4
-
- DBA_HIST, 5.2.3
- statistics, 14.6.1
- virtual memory statistics, 5.1.2.2
- vmstat UNIX command, 9.4.1.1
W
- wait events, 5.1.1.1
-
- buffer busy waits, 10.3.2
- classes, 5.1.1.1, 10.1.3.2
- contention wait events, 10.3.10
- direct path, 10.3.6
- enqueue, 10.3.7
- free buffer waits, 10.3.9
- idle wait events, 10.4
- latch, 10.3.10
- library cache latch, 10.3.10.1
- log buffer space, 10.3.14
- log file parallel write, 10.3.11
- log file switch, 10.3.15
- log file sync, 10.3.16
- network communication wait events, 10.3.1
- rdbms ipc reply, 10.3.17
- resource wait events, 10.3.4
- Windows performance, 9.2.2
- workload objects, 17.2.4.1
- workloads
-
- deleting, 17.2.4.4
- estimating, 2.6.2
-
- benchmarking, 2.6.2.2
- extrapolating, 2.6.2.1
- modeling, 2.6.3
- testing, 2.6.4