Index

A B C D E F G H I J L M N O P Q R S T U V W

A

accepted plans 4.6
access paths 3.1.4, 8
- bitmap index 8.4.1
- bitmap indexes 8.4, 8.4.1.2, 8.4.5
- bitmap index range scans 8.4.4
- B-tree indexes 8.3, 8.3.1
- execution plans 6.1
- full table scans 8.2.2, 8.2.2.1, 10.2.3.2, 11.1
- heap-organized tables 8.2.1
- index fast full 8.3.5
- index full scans 8.3.4
- index join scans 8.3.7
- index range scans 8.3.3
- index skip scans 8.3.6
- index unique scans 8.3.2
- In-Memory table scans 8.2.5, 8.2.5.3
- optimizing with SQL Access Advisor 26.2
- sample table scans 8.2.4, 8.2.4.2
- table 8.2
- table access by rowid 8.2.3
- table cluster 8.5
adaptive query optimization 4.4
- adaptive plans 22.2.2
- adaptive query plans 4.4.1, 7.2.1, 7.3.1
- controlling 19.2.4
- dynamic statistics 10.3.2
adaptive query plans 4.4.1, 7.2.1, 7.3.1, 22.2.2
- about 4.4.1.1
- bitmap index pruning 4.4.1.3.3
- cardinality misestimates 4.4.1.2
- enabling 4.4.1.4
- join methods 4.4.1.3.1
- optimizer statistics collector 4.4.1.3
- parallel distribution methods 4.4.1.3.2
- subplans 4.4.1.3
adaptive sampling 13.2.8.2.2
adaptive statistics 4.4.2
- automatic reoptimization 4.4.2.2
- dynamic statistics 4.4.2.1
- SQL plan directives 4.4.2.3, 14.1
- when enabled 4.4.2.4
ADDM 1.4.2.1.1
advisors
- Optimizer Statistics Advisor 18.1, 18.1.1, 18.1.1.1
- SPM Evolve Advisor 29.2, 29.2.2
- SQL Access Advisor 26.1, 26.1.3
- SQL Tuning Advisor 25, 25.1
ALTER SESSION statement 23.4.2
ANALYZE command 16.1.3
antijoins 9.1.3, 9.3.4
- handling nulls 9.3.4.3
- how they work 9.3.4.2
- when the optimizer considers 9.3.4.1
APPEND hint 10.3.3
applications
- implementing 2.2
approximate query processing 4.5
automatic plan capture
- creating automatic capture filters 29.2.1.2
- enabling 29.2.1.1
automatic reoptimization 4.4, 4.4.2.2, 7.2.1, 10.4.2.4
- cardinality misestimates 4.4.2.2.1
- performance feedback 4.4.2.2.2
- statistics feedback 4.4.2.2.1
automatic statistics collection 13.1.1
Automatic Tuning Optimizer 1.4.2.1.2, 4.3
- SQL profiles 25.1.2.3.2
Automatic Workload Repository (AWR) 1.4.2.1.1

B

band joins 9.3.1.3
big bang rollout strategy 2.2.2
bind-aware cursors 20.3.4
bind-sensitive cursors 20.3.3
bind variables 20.1.1, 20.1.1.1
- bind peeking 20.1.3.3
- cursors 20.1.3.2
- substitution 20.2
bitmap indexes
- access paths 8.4.1
- bitmap merge 8.4.5
- conversion to rowid 8.4.2
- inlist iterator 7.2.5.5.3
- pruning 4.4.1.3.3
- purpose 8.4.1.2
- range scans 8.4.4
- single value 8.4.3
Bloom filters 9.4.1
- controls 9.4.1.3
- how they work 9.4.1.2
- metadata 9.4.1.4
- purpose 9.4.1.1
BOOLEAN data type 8.3.3.1
B-tree indexes 8.3, 8.3.1
bulk loads
- creating histograms 10.3.3.3
- gathering index statistics 10.3.3.3
- restrictions for online statistics gathering 10.3.3.4
bushy join trees 9.1.1
BYTES column
- PLAN_TABLE table 7.2.6, 7.3.2

C

cardinality 1.4.2.1.2, 4.2.2, 4.4.1.2, 4.4.1.3.1, 4.4.2.2.1, 4.4.2.3, 8.4.1.2, 10.2.1, 10.4.2.2, 11.1, 11.4, 27.1.2
CARDINALITY column
- PLAN_TABLE table 7.2.6, 7.3.2
Cartesian joins 9.3.5, 9.3.5.1, 9.3.5.2, 9.3.5.3
child cursors 20.1.1.2
column groups 14.1, 14.1.2
- about 14.1.1
- creating 14.1.3, 14.1.4
- displaying information 14.1.5
- dropping 14.1.6
- optimizer statistics 14.1.1
- why needed 14.1.1.1
column group statistics 10.4.2.2
- automatic and manual 14.1.1.2
columns
- cardinality 4.2.2
compilation, SQL 10.4.1, 10.4.2.2, 10.4.4
composite partitioning
- examples of 7.2.5.2
concurrent statistics gathering 13.2.7, 13.2.7.1, 13.2.7.1.2,
CONFIGURE procedure 28.2.1.1
consistent mode
- TKPROF 23.6.1.2.5.2
COST column
- PLAN_TABLE table 7.2.6, 7.3.2
CPU
- system statistics 13.3.1
current mode
- TKPROF 23.6.1.2.5.2
CURSOR_NUM column
- TKPROF_TABLE table 23.4.4.3
CURSOR_SHARING initialization parameter 20.1.1.1, 20.2
cursor-duration temporary tables 5.8, 5.8.1, 5.8.2, 5.8.3
cursors 3.1.1
- about 20.1.1
- adaptive sharing 20.3, 20.3.1, 20.3.6
- bind-aware 20.3.4
- bind-sensitive 20.3.3
- bind variable peeking 20.1.3.3
- bind variables 20.1.3.2
- child 20.1.1.2
- invalidating 20.1.4.1
- literals 20.1.3.1
- marked DEFERRED INVALIDATION 20.1.4.2
- marked rolling invalid 20.1.4.2
- merging 20.3.5
- parent 20.1.1.2, 20.1.1.2.1
- parsing and 20.1.2
- Real-World Performance guidelines 20.4
- sharing 20, 20.1, 20.1.1.1
- temporary tables and 5.8.1, 5.8.2, 5.8.3
cursor sharing
- adaptive 20.3, 20.3.6
- forcing 20.2.2
- Real-World Performance guidelines 20.4

D

database operations
- attributes 21.1.2.3
- composite 1.4.2.2.2, 21.1
- concepts 21.1.2
- definition 1.4.2.2.2, 21.1
- monitoring 21
- simple 1.4.2.2.2, 21.1, 21.1.1.1
database operations, monitoring 1.4.2.2.2, 21.1
- composite 21.1.2.2.2
- composite operations 21.1, 21.1.1.2
- creating database operations 21.3
- enabling with hints 21.2.2
- enabling with initialization parameters 21.2.1
- Enterprise Manager interface 21.1.3.1
- PL/SQL interface 21.1.3.2
- purpose 21.1.1
- real-time SQL 21.1
- simple operations 21.1
- user interfaces 21.1.3
- using Cloud Control 21.4
Database Resource Manager 13.2.7.1.2
data blocks 3.2.2
data dictionary cache 3.1.1.3
data modeling 2.1.1
Data Pump
- Export utility
  - statistics on system-generated columns names 17.2
- Import utility
  - copying statistics 17.2
data skew 11.1
data types
- BOOLEAN 8.3.3.1
DATE_OF_INSERT column
- TKPROF_TABLE table 23.4.4.3
DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter 8.2.2.2
DBA_ADVISOR_ACTIONS view 26.6.1
DBA_ADVISOR_EXECUTIONS view 18.2.2
DBA_IND_STATISTICS view 13.2.3
DBA_OPTSTAT_OPERATION_TASKS view 13.2.7.3
DBA_OPTSTAT_OPERATIONS view 13.2.7.3
DBA_OUTLINES view 30.2
DBA_SQL_MANAGEMENT_CONFIG view 28.2.1.1, 29.7.1
DBA_SQL_PROFILES view 27.1.2
DBA_STAT_EXTENSIONS view 14.2.3
DBA_TAB_COL_STATISTICS view 11.4.2, 14.1.1.1
DBA_TAB_MODIFICATIONS view 13.2.3
DBA_TAB_PENDING_STATS view 15.2.2
DBA_TAB_STATISTICS view 13.2.3
DBMS_ADVISOR package 26.1.1, 26.4.2, 26.4.4.1, 26.4.4.2
DBMS_AUTO_SQLTUNE package 25.2.3.2
DBMS_AUTO_TASK_ADMIN package 12.1.2.2
DBMS_MONITOR package VII, 23.3.2, 23.4.2
- end-to-end application tracing 23.1.3
DBMS_SESSION package 23.4.2
DBMS_SPM package 29, 29.1.1, 29.4, 29.5.2, 29.7.2, 30.2
DBMS_SQL_MONITOR package 21.1.3
DBMS_SQLDIAG package 22.3.2
DBMS_SQLTUNE package 25.2.4.1, 25.3.1.2.2, 25.3.5, 25.3.6, 27.1.3
- SQL Tuning Advisor 25.3.1.2.1
DBMS_STATS package 12.1.2.2, 13.2.2.3, 13.2.7.1.2, 13.2.8.2, 13.3.3.1, 13.3.3.3, 13.3.4, 13.4, 16.2.2, 14, 14.1.3, 14.1.5, 14.2.3, 15.1, 15.1.1, 15.2.2, 15.2.3, 16.1.3, 16.1.4, 16.2.3, 17.2
DBMS_XPLAN 7.3.3
DBMS_XPLAN package 6.4
DDL (data definition language)
- processing of 3.3
deadlocks 3.1.1
debugging designs 2.2.1
dedicated server 3.1.1.3
deferred invalidation 20.1.4.2
density, histogram 11.4.2
DEPTH column
- TKPROF_TABLE table 23.4.4.3
designs
- debugging 2.2.1
- testing 2.2.1
- validating 2.2.1
development environments 2.2
DIAGNOSTIC_DEST initialization parameter 23.4.1
DISTRIBUTION column
- PLAN_TABLE table 7.2.6, 7.3.2
domain indexes
- and EXPLAIN PLAN 7.2.5.6
DROP_SQL_PLAN_DIRECTIVE procedure 12.4
dynamic statistics 4.4.2.1, 10.3.2, 10.4.1, 10.4.3, 13.2.6, 22.2.2
- controlling 12.3
- process 12.3.1
- sampling levels 12.3.1
- setting statistics level 12.3.2
- when to use 12.3.3

E

endpoint repeat counts, in histograms 11.8
end-to-end application tracing 1.4.2.2.3, VII
- action and module names 23.1.3
- creating a service 23.1.3
- DBMS_APPLICATION_INFO package 23.1.3
- DBMS_MONITOR package 23.1.3
- enabling 23.3, 23.3.1, 23.3.2
- gathering statistics 23.2, 23.2.1
- multitenant environment 23.1.2
- overview 23.1
- performing 23
- purpose 23.1.1
ESS
- See: Expression Statistics Store
examples
- EXPLAIN PLAN output 23.5.3
EXECUTE_TASK procedure 26.2.4
execution plans 1.4.2.2.1, 3.1.1.3
- about 6.2, 6.2.1
- adaptive 4.4.1, 7.2.1, 7.3.1
- displaying 6
- generating 6
- generation 4.2.3
- guidelines 6.2.4
- overview of 6.1
- parallel execution 7.2.2
- reading 7
- reference 7.3
- TKPROF 23.4.3, 23.6.1.2.4
- V$ views 7.3.1
- viewing with the utlxpls.sql script 6.3
execution trees 3.1.4
EXPLAIN PLAN statement
- access paths 8.2.4.2, 8.2.5.3
- and full partition-wise joins 7.2.5.4
- and partial partition-wise joins 7.2.5.3
- and partitioned objects 7.2.5
- basic steps 6.3.1
- examples of output 23.5.3
- invoking with the TKPROF program 23.6.1.2.4
- PLAN_TABLE table 6.2.7
- restrictions 6.2.6
- viewing the output 6.3
expressions, optimizer
- statistics 14.2, 14.2.4
expression statistics
- creating 14.2.2
- dropping 14.2.4
- managing 14.2
Expression Statistics Store (ESS) 4.7
extended statistics 10.2.2
extensions, optimizer 10.4.2, 14
- displaying 14.2.3
external tables
- guideline for gathering optimizer statistics 13.2.2.5

F

fixed objects
- gathering statistics for 12.1, 13.2.5
FLUSH_DATABASE_MONITORING_INFO procedure 13.2.3
FLUSH_SQL_PLAN_DIRECTIVE procedure 12.4
frequency histograms 11.5, 11.5.1, 11.6
full outer joins 9.3.2.4
full partition-wise joins 7.2.5.4
full table scans 8.2.2, 8.2.2.1, 10.2.3.2, 11.1

G

global temporary tables 10.2.4
- statistics 10.2.4.1

H

hard parsing 2.1.2, 3.1.1.3, 3.1.2, 20.1.2
hash clusters 8.5.2
hash joins 9.2.2
- cost-based optimization 9.1.3
- how they work 9.2.2.2
- outer joins 9.3.2.2
hash partitions 7.2.5
- examples of 7.2.5.1
height-balanced histograms 11.7, 11.7.1
high-load SQL
- tuning 12.1.2.1.2, 25.3.1.2.1
hints, optimizer 1.4.2.2.4, 19.3
- about 19.3.1
- APPEND 10.3.3
- GATHER_OPTIMIZER_STATISTICS 10.3.3.5
- guidelines 19.3.1.3
- INDEX_JOIN 8.3.7.3
- multiple 19.3.1.3
- NO_GATHER_OPTIMIZER_STATISTICS 10.3.3.5
- NO_MONITOR 21.2.2
- NO_PX_JOIN_FILTER 9.4.1.3
- OPTIMIZER_FEATURES_ENABLE 19.2.2
- ORDERED 9.3.5.3
- PQ_DISTRIBUTE 7.2.5.3
- PX_JOIN_FILTER 9.4.1.3
- types 19.3.1.1
- USE_NL 9.2.1.6, 9.3.2.1
histograms 11
- bucket compression 11.4.3
- cardinality algorithms 11.4
- creation during bulk loads 10.3.3.3
- criteria for creation 11.3
- data skew 11.1
- definition 11
- density 11.4.2
- endpoint numbers 11.4.1
- endpoint repeat counts 11.8
- endpoint values 11.4.1
- frequency 11.5, 11.5.1, 11.6
- height-balanced 11.7, 11.7.1
- hybrid 11.8, 11.8.2
- NDV 11
- nonpopular values 11.4.2
- popular values 11.4.2
- purpose 11.1
- top frequency 11.6, 11.6.1
hybrid histograms 11.8, 11.8.2
HyperLogLog algorithm 13.2.8.2.2

I

I/O
- system statistics 13.3.1
ID column
- PLAN_TABLE table 7.2.6, 7.3.2
Incident Manager, accessing 22.3.1.1
incidents, SQL 22.2.1
incremental statistics 13.2.8.4, 13.2.8.5
- maintenance 13.2.8, 13.2.8.1
- synopses 13.2.8.2.1
INDEX_JOIN hint 8.3.7.3
index clustering factor 10.2.3.2
indexes
- bitmap 4.4.1.3.3, 8.4, 8.4.1.2, 8.4.3
- B-tree 8.3, 8.3.1
- domain 7.2.5.6
- fast full scans 8.3.5
- full scans 8.3.4
- join scans 8.3.7
- range scans 8.3.3
- skip scans 8.3.6
- unique scans 8.3.2
index statistics 10.2.3
- index clustering factor 10.2.3.2
In-Memory Aggregation 5.7
In-Memory Expressions (IMEs)
- Expression Statistics Store (ESS) 4.7
In-Memory table scans 8.2.5
- controls 8.2.5.2
- example 8.2.5.3
- when chosen 8.2.5.1
inner loop, in nested loops joins 9.2.1.2
invalidation, deferred 20.1.4.2

J

join conditions 9.1
join methods
- about 9.2
- hash joins 9.2.2.2
- nested loops 9.2.1, 9.2.1.3, 9.2.1.4
- sort merge joins 9.2.3
join optimizations 9.4
- Bloom filters 9.4.1
- partition-wise 9.4.2, 9.4.2.2
joins 9
- about 9.1
- antijoins 9.1.3, 9.3.4.2
- band 9.3.1.3
- Bloom filters 9.4.1
- Cartesian 9.1, 9.3.5, 9.3.5.1, 9.3.5.2, 9.3.5.3
- conditions 9.1
- full outer 9.3.2.4
- hash 9.2.2
- how the optimizer executes 9.1.2
- index join scans 8.3.7
- inner 9.3.1
- join types 9.3
- nested loops 3.1.4, 9.2.1, 9.2.1.1, 9.2.1.2
- nested loops and cost-based optimization 9.1.3
- order 19.3.2
- outer 5.10.4, 9.3.2
- partition-wise 9.4.2, 9.4.2.1, 9.4.2.2
  - examples of full 7.2.5.4
  - examples of partial 7.2.5.3
  - full 7.2.5.4
- semijoins 9.1.3, 9.3.3, 9.3.3.1, 9.3.3.2
- sort merge 9.2.3.2
- sort-merge and cost-based optimization 9.1.3
- trees 9.1.1
join types 9.3
- antijoins 9.3.4
- inner joins 9.3.1
- outer joins 5.10.4, 9.3.2, 9.3.2.1, 9.3.2.2
- semijoins 9.3.3

L

latches
- parsing and 3.1.1.3
left deep join trees 9.1.1
library cache
- misses 3.1.1.3
literals, in cursors 20.1.3.1

M

management objects, SQL IX
manual plan capture 28.2.2
materialized views
- query rewrite 5.5
modeling, data 2.1.1
multiversion read consistency 3.2.2

N

NDV 11
nested loops joins 9.2.1, 9.2.1.1
- controls 9.2.1.6
- cost-based optimization 9.1.3
- current implementation 9.2.1.4
- how they work 9.2.1.2
- nested nested loops 9.2.1.3
- original implementation 9.2.1.5
- outer joins 9.3.2.1
- USE_NL hint 9.2.1.6
NO_PX_JOIN_FILTER hint 9.4.1.3
nonpopular values, in histograms 11.4.2
NOT IN subquery 9.1.3
nulls
- in antijoins 9.3.4.3

O

OBJECT_INSTANCE column
- PLAN_TABLE table 7.2.6, 7.3.2
OBJECT_NAME column
- PLAN_TABLE table 7.2.6, 7.3.2
OBJECT_NODE column
- PLAN_TABLE table 7.2.6, 7.3.2
OBJECT_OWNER column
- PLAN_TABLE table 7.2.6, 7.3.2
OBJECT_TYPE column
- PLAN_TABLE table 7.2.6, 7.3.2
OPERATION column
- PLAN_TABLE table 7.2.6, 7.3.2
optimizer
- about 3.1.2
- access paths 8
- adaptive 7.2.1
- concepts 4
- cost-based 4.1.2
- definition 4.1
- environment 3.1.1.3
- estimator 4.2.2
- execution 3.1.4
- Expression Statistics Store (ESS) 4.7
- extensions 10.4.2, 14.2.3
- goals 19.2.3
- plan generation 4.2.3
- purpose of 4.1.1
- query transformer 4.2.1
- row sources 3.1.3, 9.2
- SQL plan directives 10.4.2
- SQL processing 3, 3.1
- throughput 19.2.3
OPTIMIZER_ADAPTIVE_FEATURES initialization parameter
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter 28.2.1, 29.2.1
OPTIMIZER_FEATURES_ENABLE initialization parameter 19.2.2
OPTIMIZER_USE_PENDING_STATISTICS initialization parameter 15.2.2
OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter 28.2.1, 29.2.1
OPTIMIZER column
- PLAN_TABLE 7.2.6, 7.3.2
optimizer hints 1.4.2.2.4, 19.3
- about 19.3.1
- APPEND 10.3.3
- guidelines 19.3.1.3
- INDEX_JOIN 8.3.7.3
- MONITOR 21.2.2
- multiple 19.3.1.3
- OPTIMIZER_FEATURES_ENABLE 19.2.2
- ORDERED 9.3.5.3
- PQ_DISTRIBUTE 7.2.5.3
- types 19.3.1.1
- USE_NL 9.3.2.1
optimizer statistics V, 10
- adaptive statistics 4.4.2
- automatic collection 13.1, 13.1.1
- bulk loads 10.3.3
- cardinality 11.1
- collection 12.1
- column groups 10.4.2.2, 14.1, 14.1.1, 14.1.1.1, 14.1.1.2, 14.1.3, 14.1.4
- controlling 15
- determining staleness 13.2.3
- dynamic 10.3.2, 10.4.1, 12.3, 13.2.6, 22.2.2
- exporting and importing 17
- expressions 14.2, 14.2.4
- extended 10.2.2, 14
- gathering 13
- gathering concurrently 13.2.7, 13.2.7.1, 13.2.7.1.2,
- gathering during bulk loads 10.3.3.4
- gathering manually 13.2, 13.2.2
- global statistics 13.2.8.2
- guideline for gathering external table 13.2.2.5
- histograms 11
- history 16.2.1
- how gathered 10.3
- incremental 13.2.8, 13.2.8.1, 13.2.8.4, 13.2.8.5
- index 10.2.3
- locking 15.1, 15.1.1
- managing 16
- manual collection 13.2.1
- monitoring collection 13.2.7.3
- noworkload 13.3.3.3
- Optimizer Statistics Advisor 18, 18.1, 18.1.1, 18.1.1.1
- options for gathering 12
- partitioned tables 13.2.2.3, 13.2.8.3
- pending 15.2, 15.2.3
- pluggable databases and 13.1.1
- preferences 12.2, 12.2.1, 12.2.1.1, 12.2.1.2, 12.2.1.3
- publishing 15.2.2
- purging 16.2.3
- reporting mode 13.4
- restoring 16.1.2, 16.1.3, 16.1.4
- retention 16.2
- retention period 16.2.2
- setting 15.3, 15.3.2
- SQL plan directives 10.4.2, 14.1
- system 13.3.2, 13.3.3.1, 13.3.3.3, 13.3.4
- temporary 10.2.4
- transporting 17.2
- types 10.2
- unlocking 15.1.2
- when gathered 10.4
- workload 13.3.3.2
Optimizer Statistics Advisor 18, 18.1
- about 18.1.2
- actions 18.1.2.1.4
- basic tasks 18.2
- benefits 18.1.1.2
- components 18.1.2.1
- creating an object filter 18.2.3.2
- creating a rule filter 18.2.3.3, 18.2.3.4
- creating a task 18.2.1, 18.2.5
- DBA_ADVISOR_EXECUTIONS view 18.2.2
- DBMS_STATS PL/SQL package 18.1.3
- executing a task 18.2.4
- filters 18.2.3.1
- findings 18.1.2.1.2
- generating a script 18.2.6.2
- implementing actions automatically 18.2.6.1
- listing advisor tasks 18.2.2
- modes of operation 18.1.2.2
- problems with script-based approach 18.1.1.1
- purpose 18.1.1
- recommendations 18.1.2.1.3
- rules 18.1.2.1.1
optimizer statistics collection 12.1
optimizer statistics collectors 4.4.1.3
OPTIONS column
- PLAN_TABLE table 7.2.6, 7.3.2
ORA$AUTOTASK consumer group 13.2.7.1.2
ORDERED hint 9.3.5.3
OR expansion 5.1
OTHER_TAG column
- PLAN_TABLE table 7.2.6, 7.3.2
OTHER column
- PLAN_TABLE table 7.2.6, 7.3.2
outer joins 9.3.2, 9.3.2.1
- hash joins 9.3.2.2
- multiple left tables 9.3.2.5
outer loops, in nested loops join 9.2.1.2

P

PARENT_ID column
- PLAN_TABLE table 7.2.6, 7.3.2
parent cursors 20.1.1.2, 20.1.1.2.1
parsing, SQL 3.1.1
- hard 2.1.2, 20.1.2
- hard parse 3.1.1.3
- parse trees 3.1.4
- soft 2.1.2
- soft parse 3.1.1.3
PARTITION_ID column
- PLAN_TABLE table 7.2.6, 7.3.2
PARTITION_START column
- PLAN_TABLE table 7.2.6, 7.3.2
PARTITION_STOP column
- PLAN_TABLE table 7.2.6, 7.3.2
partitioned objects
- and EXPLAIN PLAN statement 7.2.5
- gathering incremental statistics 13.2.8, 13.2.8.1
- gathering statistics 13.2.8.3
- guideline for gathering statistics 13.2.2.3
partitioning
- examples of 7.2.5.1
- examples of composite 7.2.5.2
- hash 7.2.5
- range 7.2.5
- start and stop columns 7.2.5.1
partition maintenance operations 13.2.8.4
partition-wise joins 9.4.2
- full 7.2.5.4
- full, and EXPLAIN PLAN output 7.2.5.4
- how they work 9.4.2.2
- partial, and EXPLAIN PLAN output 7.2.5.3
- purpose 9.4.2.1
PLAN_TABLE table
- BYTES column 7.2.6, 7.3.2
- CARDINALITY column 7.2.6, 7.3.2
- COST column 7.2.6, 7.3.2
- creating 6.2.7
- displaying 6.4
- DISTRIBUTION column 7.2.6, 7.3.2
- ID column 7.2.6, 7.3.2
- OBJECT_INSTANCE column 7.2.6, 7.3.2
- OBJECT_NAME column 7.2.6, 7.3.2
- OBJECT_NODE column 7.2.6, 7.3.2
- OBJECT_OWNER column 7.2.6, 7.3.2
- OBJECT_TYPE column 7.2.6, 7.3.2
- OPERATION column 7.2.6, 7.3.2
- OPTIMIZER column 7.2.6, 7.3.2
- OPTIONS column 7.2.6, 7.3.2
- OTHER_TAG column 7.2.6, 7.3.2
- OTHER column 7.2.6, 7.3.2
- PARENT_ID column 7.2.6, 7.3.2
- PARTITION_ID column 7.2.6, 7.3.2
- PARTITION_START column 7.2.6, 7.3.2
- PARTITION_STOP column 7.2.6, 7.3.2
- POSITION column 7.2.6, 7.3.2
- REMARKS column 7.2.6, 7.3.2
- SEARCH_COLUMNS column 7.2.6, 7.3.2
- STATEMENT_ID column 7.2.6, 7.3.2
- TIMESTAMP column 7.2.6, 7.3.2
plan evolution
- purpose 28.4.1
pluggable databases
- automatic optimizer statistics collection 13.1.1
- manageability features 13.1.1
- SQL management base 28.5.1
- SQL Tuning Advisor 25.1.1
- SQL tuning sets 24.1
popular values, in histograms 11.4.2
POSITION column
- PLAN_TABLE table 7.2.6, 7.3.2
PQ_DISTRIBUTE hint 7.2.5.3
preferences, optimizer statistics 12.2.1.1, 12.2.1.3
private SQL areas 20.1.1, 20.1.1.1
- parsing and 3.1.1
processes
- dedicated server 3.1.1.3
profiles, SQL
- about 27.1
- concepts 27.1.2
- implementing 27.2
- managing 27
programming languages 2.2
PX_JOIN_FILTER hint 9.4.1.3

Q

query transformations 5
- In-Memory Aggregation 5.7
- join factorization 5.10, 5.10.1
- OR expansion 5.1
- predicate pushing 5.3
- purpose of table expansions 5.9.1
- query rewrite with materialized views 5.5
- star transformation 5.6.5
- star transformations 5.6, 8.4.1.2
- subquery unnesting 5.4
- table expansion 5.9, 5.9.2, 5.9.3
- view merging 5.2
query transformer 4.2.1

R

range
- examples of partitions 7.2.5.1
- partitions 7.2.5
Real-Time Database Operations 1.4.2.2.2
Real-Time SQL Monitoring 1.4.2.2.2, 21.1
- architecture 21.1.2.1
- use cases 21.1.1.1
Real-World Performance
- cursor sharing 20
recursive SQL 3.3, 10.3.2, 10.4.4, 23.5.2
REMARKS column
- PLAN_TABLE table 7.2.6, 7.3.2
reoptimization, automatic 4.4.2.2, 7.2.1, 10.4.2.4
- cardinality misestimates 4.4.2.2.1
- performance feedback 4.4.2.2.2
- statistics feedback 4.4.2.2.1
restoring optimizer statistics 16.1.2, 16.1.3
result sets, SQL 3.1.3, 3.2
right deep join trees 9.1.1
rolling invalid cursors 20.1.4.2
rollout strategies
- big bang approach 2.2.2
- trickle approach 2.2.2
rowids
- table access by 8.2.3
rows
- rowids used to locate 8.2.3
- row sets 3.1.3
- row sources 3.1.3, IV, 9.2

S

SAMPLE BLOCK clause 8.2.4
SAMPLE clause 8.2.4
sample table scans 8.2.4
scans
- In-Memory 8.2.5
- sample table 8.2.4
SEARCH_COLUMNS column
- PLAN_TABLE table 7.2.6, 7.3.2
SELECT statement
- SAMPLE clause 8.2.4
semijoins 9.1.3, 9.3.3
- how they work 9.3.3.2
- when the optimizer consider 9.3.3.1
SET_EVOLVE_TASK_PARAMETER procedure 29.2.2.1
shared cursors
- life cycle 20.1.4
shared pool 10.4.2.2, 20.1
- parsing check 3.1.1.3
shared SQL areas 3.1.1.3, 20.1.1.1
simple database operations 21.1.1.1
SMB
- See: SQL management base
soft parsing 2.1.2, 3.1.1.3
sort merge joins 9.2.3
- cost-based optimization 9.1.3
- how they work 9.2.3.2
SPM Evolve Advisor 29.2, 29.2.2
SPM Evolve Advisor task
- about 29.2.2.1
- managing 29.2.2
- scheduling 29.2.2.2
SQL
- execution 3.1.4
- management objects IX
- operators IV
- optimization 4.1.2
- parsing 3.1.1
- processing 3.1
- recursive 3.3, 10.4.4, 12.3
- result sets 3.1.3, 3.2
- stages of processing 8.2, 8.3
- test cases 22
SQL_STATEMENT column
- TKPROF_TABLE 23.4.4.3
SQL Access Advisor 1.4.2.1.3, 26.1.1
- about 26.1
- actions 26.1.2.4
- advanced tasks 26.4
- basic tasks 26.2
- constants 26.6.3
- creating SQL tuning set 26.2.1
- creating task templates 26.4.3
- deleting tasks 26.4.5
- demo script 26.5
- evaluation mode 26.4.1
- EXECUTE_TASK procedure 26.2.4
- filter options 26.1.2.2
- input sources 26.1.2.1
- marking recommendations 26.4.6
- modifying recommendations 26.4.7
- quick tune 26.3
- recommendations 26.1.2.4.2
- reference 26.6
- repository 26.1.2.5
- task categories 26.6.2
- terminating tasks 26.4.4
- updating task attributes 26.4.2
- user interfaces 26.1.3
SQL compilation 10.4.1, 10.4.2.2, 10.4.4
SQL incidents 22.2.1
SQL management base
- changing disk space limits 29.7.2
- managing 29.7, 29.7.1
- plan retention policy 29.7.3
- pluggable databases and 28.5.1
SQL Performance Analyzer 1.4.2.1.5
SQL plan baselines 1.4.2.1.4, 4.6, 28
- automatic plan capture 28.2.1.1
- configuring initial capture 29.2.1
- displaying 29.3
- dropping 29.6
- loading 29.4.1
- loading from AWR 29.4.2
- loading from shared SQL area 29.4.3
- loading using DBMS_SPM 29.4
- managing 29, 29.1
- migrating stored outlines to 30
- plan matching 28.2.1.2
- plan retention 29.7.3
SQL plan capture 28.2
- configuring filters of automatic plan capture 29.2.1.2
- enabling automatic 29.2.1.1
SQL plan directives 4.4.2.3, 10.4.2, 14.1
- cardinality misestimates 10.4.2.2
- creation 10.4.2.1
- managing 12.4
SQL plan evolution
- managing an evolve task 29.5.2
SQL plan history 28.5.3
SQL plan management 1.4.2.1.4
- accepted plans 4.6
- automatic plan capture 28.2.1, 29.2.1.1
- basic tasks 29.1.2
- benefits 28.1.1
- configuring 29.2
- DBMS_SPM 29.1.1.2
- DBMS_SPM package 29.1.1
- filters for automatic plan capture 29.2.1.2
- introduction 28
- loading plans from a staging table 29.4.5
- loading plans from SQL tuning sets 29.4.4
- manual plan capture 28.2.2
- plan capture 28
- plan evolution 28, 28.4, 29.5.2
- plan history 28.5.3
- plan retention 29.7.3
- plan selection 28, 28.3
- plan verification 28.4
- purpose 28.1
- SPM Evolve Advisor 29.2.2
- SQL management base 29.7, 29.7.2
- SQL plan baselines 4.6, 28, 29, 29.6
- SQL plan capture 28.2
- storage architecture 28.5
- user interfaces 29.1.1
SQL processing 3
- semantic check 3.1.1.2
- shared pool check 3.1.1.3
- stages 3.1
- syntax check 3.1.1.1
SQL profiles 1.4.2.1.2, 25.1.2.3.2
- about 27.1
- and SQL plan baselines 28.1.2
- concepts 27.1.2
- implementing 27.2
- managing 27
- user interface 27.1.3
SQL statement log 28.5.2
SQL statements
- execution plans of 6.1
SQL Test Case Builder 22.1
- accessing the Incident Manager 22.3.1.1
- accessing the Support Workbench 22.3.1.2
- command-line interface 22.3.2
- gathering diagnostic data 22
- graphical interface 22.3.1
- key concepts 22.2
- output 22.2.3
- running 22.4
- SQL incidents 22.2.1
- user interfaces 22.3
- what it captures 22.2.2
SQL trace facility VII, 23.1.3.1, 23.4
- enabling 23.4.2
- generating output 23.4.3
- output 23.6.1.2.5.2
- statement truncation 23.4.3
- trace files 1.4.2.2.3, 23.4.1
SQL tuning
- automation 1.4.2.1
- definition 1.1
- introduction 1
- manual tools 1.4.2.2
- proactive 1.4.1
- purpose 1.2
- reactive 1.4.1
- tools overview 1.4, 1.4.2
SQL Tuning Advisor 1.4.2.1.2, 25
- about 25.1
- administering with APIs 25.3.1.2.1
- analyses 25.1.2.3
- architecture 25.1.2
- automatic 25.2.2, 25.2.4.1
- automatic tuning task 25.2.1, 25.2.3.2
- command-line interface 25.3.1.2.2
- configuring a SQL tuning task 25.3.3
- controls 25.1.3.1
- creating a task 25.3.2
- DBMS_SQLTUNE 25.3.1.2.2
- displaying tuning results 25.3.6
- executing a task 25.3.4
- input sources 25.1.2.1
- monitoring tasks 25.3.5
- pluggable databases and 25.1.1
- reports 25.2.4
- running on-demand 25.3, 25.3.1, 25.3.1.1
- using 12.1.2.1.2, 25.3.1.2.1
- using remotely 25.1.3.2
SQL tuning sets
- creating as input for SQL Access Advisor 26.2.1
- loading in SQL plan baselines 29.4.4
- managing with APIs 24.1
- pluggable databases and 24.1
start columns
- in partitioning and EXPLAIN PLAN statement 7.2.5.1
star transformations 5.6, 5.6.5, 8.4.1.2
STATEMENT_ID column
- PLAN_TABLE table 7.2.6, 7.3.2
STATISTICS_LEVEL initialization parameter 16.2.3
statistics, optimizer 4.1.2, V
- adaptive statistics 4.4.2
- automatic collection 13.1, 13.1.1
- bulk loads 10.3.3
- cardinality 11.1
- collection 12.1
- column group 10.4.2.2
- column groups 14.1, 14.1.1.1, 14.1.2, 14.1.3, 14.1.4
- controlling 15
- deleting 13.3.4
- determining staleness 13.2.3
- dynamic 10.3.2, 10.4.1, 12.3, 13.2.6, 22.2.2
- dynamic statistics 10.4.3
- exporting and importing 17
- expressions 14.2
- extended 10.2.2, 14
- gathering concurrently 13.2.7, 13.2.7.1
- gathering manually 13.2, 13.2.2
- global statistics 13.2.8.2
- global temporary table 10.2.4.1
- guideline for external tables 13.2.2.5
- history 16.2.1
- how gathered 10.3
- incremental 13.2.8, 13.2.8.1, 13.2.8.4, 13.2.8.5
- index 10.2.3
- locking 15.1, 15.1.1
- managing 16
- manual collection 13.2.1
- monitoring collection 13.2.7.3
- noworkload 13.3.3.3
- Optimizer Statistics Advisor 18.1, 18.1.1, 18.1.1.1
- options for gathering 12
- partitioned tables 13.2.8.3
- pending 15.2, 15.2.3
- preferences 12.2, 12.2.1
- publishing 15.2.2
- purging 16.2.3
- reporting mode 13.4
- restoring 16.1.2, 16.1.3, 16.1.4
- retention 16.2
- retention period 16.2.2
- session-specific 10.2.4.1
- setting 15.3
- shared 10.2.4.1
- system 10.2.5, 13.3, 13.3.1, 13.3.2, 13.3.3.1
- transporting 17.2
- types 10.2
- unlocking 15.1.2
- user-defined 10.2.6
- when gathered 10.4
- workload 13.3.3.2
stop columns
- in partitioning and EXPLAIN PLAN statement 7.2.5.1
stored outlines
- about 30.1
- categories 30.1.2.2
- how they work 30.1.2
- migrating 30
- purpose of migrating 30.1.1
subqueries
- NOT IN 9.1.3
Support Workbench, accessing 22.3.1.2
synopses 13.2.8.2.1
SYS_AUTO_SQL_TUNING_TASK 25.3.4
system statistics 13.3.2, 13.3.3.1
- about 13.3.1
- deleting 13.3.4
- gathering 13.3.3.2.2
- noworkload 13.3.3.3

T

table clusters
- access paths 8.5
table expansion
- about 5.9
- how it works 5.9.2
- purpose 5.9.1
- scenario 5.9.3
table statistics 10.2.1
task templates, SQL Access Advisor 26.4.3
temporary tables
- cursor-duration 5.8, 5.8.1, 5.8.2, 5.8.3
- global 10.2.4
- statistics 10.2.4.1
- transaction-specific 10.2.4.2
testing designs 2.2.1
throughput
- optimizer goal 19.2.3
TIMED_STATISTICS initialization parameter 23.6.1.2.5.2
TIMESTAMP column
- PLAN_TABLE table 7.2.6, 7.3.2
TKPROF_TABLE 23.4.4.3
TKPROF program 23.1.3.2, 23.4, 23.6.1, 23.6.1.2
- editing the output SQL script 23.4.4.2
- example of output 23.5.3
- generating the output SQL script 23.4.4.1
- row source operations 23.6.1.2.5.4
- timing statistics 23.5.1
- using the EXPLAIN PLAN statement 23.6.1.2.4
- wait event information 23.6.1.2.5.5
top frequency histograms 11.6, 11.6.1
tracing VII, 23
- consolidating with TRCSESS 23.6.1.1
- enabling 23.3.1, 23.3.2
- enabling statistics gathering 23.2
- generating output files 23.4
- identifying files 23.4.1
transaction-specific temporary tables 10.2.4.2
transformations, query
- In-Memory Aggregation 5.7
- join factorization 5.10, 5.10.1
- predicate pushing 5.3
- purpose of table expansions 5.9.1
- star 5.6
- subquery unnesting 5.4
- table expansion 5.9, 5.9.2, 5.9.3
- view merging 5.2
transformer, query 4.2.1
TRCSESS program 23.6.1, 23.6.1.1
trees, join 9.1.1
trickle rollout strategy 2.2.2
TRUNCATE command 16.1.3

U

unique index scans 8.3.2
USE_NL hint 9.2.1.6, 9.3.2.1
USER_ID column, TKPROF_TABLE 23.4.4.3
utlxplp.sql script 6.4
utlxpls.sql script 6.4

V

V$SESSION view 23.2.1
V$SQL_CS_HISTOGRAM view 20.3.6
V$SQL_CS_SELECTIVITY view 20.3.6
V$SQL_CS_STATISTICS view 20.3.6
V$SQL_PLAN_STATISTICS_ALL view
- using to display execution plan information 6.2.5
V$SQL_PLAN_STATISTICS view
- using to display execution plan statistics 6.2.5
V$SQL_PLAN view
- using to display execution plan 6.2.5
V$SQLAREA view 20.1.1.2.1
V$SQL view 20.3.6
validating designs 2.2.1
view merging 5.2

W

workloads 2.2.1