Index

Title and Copyright Information
Preface
Changes in This Release for Oracle Database SQL Tuning Guide
- Changes in Oracle Database Release 19c, Version 19.1
  - New Features
  - Other Changes
- Changes in Oracle Database Release 18c, Version 18.1
  - New Features
Part I SQL Performance Fundamentals
- 1 Introduction to SQL Tuning
- 2 SQL Performance Methodology
  - 2.1 Guidelines for Designing Your Application
    - 2.1.1 Guideline for Data Modeling
    - 2.1.2 Guideline for Writing Efficient Applications
  - 2.2 Guidelines for Deploying Your Application
    - 2.2.1 Guideline for Deploying in a Test Environment
    - 2.2.2 Guidelines for Application Rollout
Part II Query Optimizer Fundamentals
Part III Query Execution Plans
- 6 Explaining and Displaying Execution Plans
- 7 PLAN_TABLE Reference
Part IV SQL Operators: Access Paths and Joins
- 8 Optimizer Access Paths
- 9 Joins
Part V Optimizer Statistics
Part VI Optimizer Controls
- 19 Influencing the Optimizer
- 20 Improving Real-World Performance Through Cursor Sharing
Part VII Monitoring and Tracing SQL
- 21 Monitoring Database Operations
- 22 Performing Application Tracing
Part VIII Automatic SQL Tuning
Part IX SQL Management Objects
Glossary
Index

Search

Print

Download

PDF for offline and print

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
- 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 query plans 4.4.1, 6.3.1.2, 6.3.3
- controlling 19.2.4
- dynamic statistics 10.3.2, 13.3.8.2.2
adaptive query plans 4.4.1, 6.3.1.2, 6.3.3
- 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.3.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
ADMINISTER ANY SQL TUNING SET system privilege 23.1.3.2
advisors
- Optimizer Statistics Advisor 18.1, 18.1.1, 18.1.1.1
- SPM Evolve Advisor 28.2, 28.2.2
- SQL Access Advisor 25.1, 25.1.3, 25.2
- SQL Tuning Advisor 24, 24.1
aggregate functions 4.5
ALTER SESSION statement 22.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.1
applications
- implementing 2.2
APPROX_COUNT_DISTINCT_AGG function 4.5.2
APPROX_COUNT_DISTINCT_DETAIL function 4.5.2
APPROX_COUNT_DISTINCT function 4.5.2
APPROX_COUNT function 4.5.2
APPROX_FOR_AGGREGATION initialization parameter 4.5.1
APPROX_FOR_COUNT_DISTINCT initialization parameter 4.5.1
APPROX_FOR_PERCENTILE initialization parameter 4.5.1
APPROX_MEDIAN function 4.5.2
APPROX_PERCENTILE function 4.5.2
APPROX_RANK function 4.5.2
APPROX_SUM function 4.5.2
approximate query processing 4.5
- initialization parameters 4.5.1
- SQL functions 4.5.2
automatic indexing 1.4.2.1.4
automatic plan capture
- creating automatic capture filters 28.2.1.2
- enabling 28.2.1.1
automatic reoptimization 4.4, 4.4.2.2, 6.3.3, 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 24.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 6.3.4.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.1.3
- gathering index statistics 10.3.3.1.3
- restrictions for online statistics gathering 10.3.3.1.4
bushy join trees 9.1.1
BYTES column
- PLAN_TABLE table 7.1

C

cardinality 1.4.2.1.2, 4.2.2, 4.4.2.3, 4.4.1.2, 4.4.1.3.1, 4.4.2.2.1, 8.4.1.2, 10.2.1.1, 10.4.2.2, 11.1, 11.4, 26.1.2
CARDINALITY column
- PLAN_TABLE table 7.1
Cartesian joins 9.3.5, 9.3.5.1, 9.3.5.2, 9.3.5.3
child cursors 20.1.1.2
- V$SQL 20.1.1.2.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
COMPARE_PLANS function 6.4.2
compilation, SQL 10.4.1, 10.4.2.2, 10.4.4
composite partitioning
- examples of 6.3.4.5.2
concurrent statistics gathering 13.3.7, 13.3.7.1, 13.3.7.1.2,
CONFIGURE procedure 27.3.1.1
consistent mode
- TKPROF 22.6.1.2.5.2
COST column
- PLAN_TABLE table 7.1
CPU
- system statistics 13.4.1
current mode
- TKPROF 22.6.1.2.5.2
CURSOR_NUM column
- TKPROF_TABLE table 22.4.4.3
CURSOR_SHARING initialization parameter 20.1.1.1, 20.2, 20.2.1
cursor-duration temporary tables 5.9, 5.9.1, 5.9.2, 5.9.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, 20.1.1.2.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.9.1, 5.9.2, 5.9.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.4.3
- composite 1.4.2.2.2
- definition 1.4.2.2.2, 21.1.1
- monitoring 21
- simple 1.4.2.2.2, 21.1.1
database operations, monitoring 1.4.2.2.2, 21.1
- creating database operations 21.3
- enabling with hints 21.2.2
- enabling with initialization parameters 21.2.1
- Enterprise Manager interface 21.1.4.1
- PL/SQL interface 21.1.4.2
- purpose 21.1.2
- real-time SQL 21.1
- user interfaces 21.1.4
- using Cloud Control 21.4
Database Resource Manager 13.3.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 22.4.4.3
DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter 8.2.2.2
DBA_ADVISOR_ACTIONS view 25.6.1
DBA_ADVISOR_EXECUTIONS view 18.2.2
DBA_IND_STATISTICS view 13.3.3
DBA_OPTSTAT_OPERATION_TASKS view 13.3.7.3
DBA_OPTSTAT_OPERATIONS view 13.3.7.3
DBA_OUTLINES view 29.2
DBA_SQL_MANAGEMENT_CONFIG view 27.3.1.1, 28.7.1
DBA_SQL_PROFILES view 26.1.2
DBA_STAT_EXTENSIONS view 14.2.3
DBA_TAB_COL_STATISTICS view 10.3.3.3.3, 11.4.2, 14.1.1.1
DBA_TAB_MODIFICATIONS view 13.3.3
DBA_TAB_PENDING_STATS view 15.2.2
DBA_TAB_STATISTICS view 10.3.3.3.3, 13.3.3
DBMS_ADVISOR package 25.1.1, 25.4.2, 25.4.4.1, 25.4.4.2
DBMS_AUTO_SQLTUNE package 24.2.3.2
DBMS_AUTO_TASK_ADMIN package 12.1.2.2
DBMS_MONITOR package , 22.3.2, 22.4.2
- end-to-end application tracing 22.1.3
DBMS_SESSION package 22.4.2
DBMS_SPM package 28, 28.1.1, 28.4, 28.5.2, 28.7.2, 29.2
DBMS_SQL_MONITOR package 21.1.4
DBMS_SQLSET package 23.1.3.2
DBMS_SQLTUNE package 23.1.3.2, 24.2.4.1, 24.3.1.2.2, 24.3.5, 24.3.6, 26.1.3
- SQL Tuning Advisor 24.3.1.2.1
DBMS_STATS package 12.1.2.2, 13.3.2.3, 13.3.7.1.2, 13.3.8.2, 13.4.3.1, 13.4.3.3, 13.4.4, 13.5, 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 6.3.1.1
DBMS_XPLAN package 6.3.1, 6.4.2
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 22.4.4.3
designs
- debugging 2.2.1
- testing 2.2.1
- validating 2.2.1
development environments 2.2
DIAGNOSTIC_DEST initialization parameter 22.4.1
DISTRIBUTION column
- PLAN_TABLE table 7.1, 7.3
domain indexes
- and EXPLAIN PLAN 6.3.4.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.3.6
- 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,
- action and module names 22.1.3
- creating a service 22.1.3
- DBMS_APPLICATION_INFO package 22.1.3
- DBMS_MONITOR package 22.1.3
- enabling 22.3, 22.3.1, 22.3.2
- gathering statistics 22.2, 22.2.1
- multitenant environment 22.1.2
- overview 22.1
- performing 22
- purpose 22.1.1
ESS
- See: Expression Statistics Store
examples
- EXPLAIN PLAN output 22.5.3
EXECUTE_TASK procedure 25.2.4
execution plans 1.4.2.2.1, 3.1.1.3
- about 6.2.1
- adaptive 4.4.1, 6.3.1.2, 6.3.3
- comparing 6.4, 6.4.1, 6.4.2, 6.4.4
- contents 6.1.1
- displaying 6
- generating 6
- generation 4.2.3
- overview of 6.1
- parallel execution 6.3.4.2
- reading 6.3
- reference 7
- TKPROF 22.4.3, 22.6.1.2.4
- V$ views 6.3.1.2
- viewing with the utlxpls.sql script 6.2
execution trees 3.1.4
EXPLAIN PLAN statement
- access paths 8.2.4.2, 8.2.5.3
- and full partition-wise joins 6.3.4.5.4
- and partial partition-wise joins 6.3.4.5.3
- and partitioned objects 6.3.4.5
- basic steps 6.2.2
- examples of output 22.5.3
- invoking with the TKPROF program 22.6.1.2.4
- PLAN_TABLE table 6.2.1.1
- restrictions 6.2.1.2
- viewing the output 6.2
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.8
extended statistics 10.2.2
extensions, optimizer 10.4.2, 14
- displaying 14.2.3
external tables
- guideline for gathering optimizer statistics 13.3.2.5

F

fixed objects
- gathering statistics for 12.1, 13.3.5
FLUSH_DATABASE_MONITORING_INFO procedure 13.3.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 6.3.4.5.4
full table scans 8.2.2, 8.2.2.1, 10.2.3.2, 11.1

G

global temporary tables 10.2.1.2

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 6.3.4.5
- examples of 6.3.4.5.1
height-balanced histograms 11.7, 11.7.1, 11.7.2
high-frequency automatic optimizer statistics collection
- about 13.2.1
- configuring 13.2
- enabling 13.2.2
high-load SQL
- tuning 12.1.2.1.2, 24.3.1.2.1
hints, optimizer 1.4.2.2.4, 19.3
- about 19.3.1
- APPEND 10.3.3.1
- conflicting 19.3.3.4
- examples 19.3.3.4
- GATHER_OPTIMIZER_STATISTICS 10.3.3.1.5
- guidelines 19.3.3.1
- INDEX_JOIN 8.3.7.3
- MONITOR 21.1.4.4
- multiple 19.3.3.1
- NO_GATHER_OPTIMIZER_STATISTICS 10.3.3.1.5, 10.3.3.3.3
- NO_MONITOR 21.1.4.4, 21.2.2
- NO_PX_JOIN_FILTER 9.4.1.3
- OPTIMIZER_FEATURES_ENABLE 19.2.2
- ORDERED 9.3.5.3
- PQ_DISTRIBUTE 6.3.4.5.3
- purpose 19.3.1.1
- PX_JOIN_FILTER 9.4.1.3
- reporting 19.3.3, 19.3.3.3
- statement-level 19.3.3.4
- types 19.3.1.2
- USE_NL 9.2.1.6, 9.3.2.1
- user interface for hint reports 19.3.3.2
histograms 11
- bucket compression 11.4.3
- cardinality algorithms 11.4
- creation during bulk loads 10.3.3.1.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, 11.7.2
- 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.3.8.2.2

I

I/O
- system statistics 13.4.1
ID column
- PLAN_TABLE table 7.1
incremental statistics 13.3.8.4, 13.3.8.5
- considerations 13.3.8.3.1
- maintenance 13.3.8, 13.3.8.1
- synopses 13.3.8.2.1
INDEX_JOIN hint 8.3.7.3
index clustering factor 10.2.3.2
indexes
- automatic 1.4.2.1.4
- bitmap 4.4.1.3.3, 8.4, 8.4.1.2, 8.4.3
- B-tree 8.3, 8.3.1
- domain 6.3.4.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.8
In-Memory Expressions (IMEs)
- Expression Statistics Store (ESS) 4.8
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.11.4, 9.3.2
- partition-wise 9.4.2, 9.4.2.1, 9.4.2.2
  - examples of full 6.3.4.5.4
  - examples of partial 6.3.4.5.3
  - full 6.3.4.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.11.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
manual plan capture 27.3.2
materialized views
- query rewrite 5.5
  - parameters 5.5.2
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_GATHER_OPTIMIZER_STATISTICS hint 10.3.3.3.3
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.1
OBJECT_NAME column
- PLAN_TABLE table 7.1
OBJECT_NODE column
- PLAN_TABLE table 7.1
OBJECT_OWNER column
- PLAN_TABLE table 7.1
OBJECT_TYPE column
- PLAN_TABLE table 7.1
OPERATION column
- PLAN_TABLE table 7.1
optimizer
- about 3.1.2
- access paths 8
- adaptive 6.3.3
- 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.8
- 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
- with rewrite 5.5.1
OPTIMIZER_ADAPTIVE_STATISTICS initialization parameter 4.4.2.4
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter 27.3.1, 28.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 27.3.1, 28.2.1
OPTIMIZER column
- PLAN_TABLE 7.1
optimizer hints 1.4.2.2.4, 19.3
- about 19.3.1
- APPEND 10.3.3.1
- examples 19.3.3.4
- guidelines 19.3.3.1
- INDEX_JOIN 8.3.7.3
- MONITOR 21.2.2
- multiple 19.3.3.1
- OPTIMIZER_FEATURES_ENABLE 19.2.2
- ORDERED 9.3.5.3
- PQ_DISTRIBUTE 6.3.4.5.3
- purpose 19.3.1.1
- reporting 19.3.3, 19.3.3.3
- types 19.3.1.2
- USE_NL 9.3.2.1
- user interface for reports 19.3.3.2
optimizer statistics , 10
- adaptive statistics 4.4.2
- automatic collection 13.1, 13.1.1
- bulk loads 10.3.3.1, 10.3.3.1.1
- 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.3.3
- dynamic 10.3.2, 10.4.1, 12.3, 13.3.6
- exporting and importing 17
- expressions 14.2, 14.2.4
- extended 10.2.2, 14
- gathering 13
- gathering concurrently 13.3.7, 13.3.7.1, 13.3.7.1.2,
- gathering during bulk loads 10.3.3.1.4
- gathering manually 13.3, 13.3.2
- global statistics 13.3.8.2
- guideline for gathering external table 13.3.2.5
- high-frequency collection 13.2, 13.2.1, 13.2.2
- histograms 11, 11.7.2
- history 16.2.1
- how gathered 10.3
- incremental 13.3.8, 13.3.8.1, 13.3.8.3.1, 13.3.8.4, 13.3.8.5
- index 10.2.3
- locking 15.1, 15.1.1
- managing 16
- manual collection 13.3.1
- monitoring collection 13.3.7.3
- noworkload 13.4.3.3
- Optimizer Statistics Advisor 18, 18.1, 18.1.1, 18.1.1.1
- options for gathering 12
- partitioned tables 13.3.2.3, 13.3.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.3, 12.2.1.4
- publishing 15.2.2
- purging 16.2.3
- reporting mode 13.5
- 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.4.2, 13.4.3.1, 13.4.3.3, 13.4.4, 24.1.2.3.1
- table 10.2.1
- temporary 10.2.1.2
- transporting 17.2
- types 10.2
- unlocking 15.1.2
- when gathered 10.4
- workload 13.4.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, 13.2, 13.2.1, 13.2.2
optimizer statistics collectors 4.4.1.3
OPTIONS column
- PLAN_TABLE table 7.1
ORA$AUTOTASK consumer group 13.3.7.1.2
Oracle Exadata Database Machine 24.1.2.3.1, 26.1.2.1
ORDERED hint 9.3.5.3
OR expansion 5.1
OTHER_TAG column
- PLAN_TABLE table 7.1
OTHER column
- PLAN_TABLE table 7.1
outer joins 9.3.2, 9.3.2.1
- hash joins 9.3.2.2
outer loops, in nested loops join 9.2.1.2

P

PARENT_ID column
- PLAN_TABLE table 7.1
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.1
PARTITION_START column
- PLAN_TABLE table 7.1
PARTITION_STOP column
- PLAN_TABLE table 7.1
partitioned objects
- and EXPLAIN PLAN statement 6.3.4.5
- gathering incremental statistics 13.3.8, 13.3.8.1, 13.3.8.3.1
- gathering statistics 13.3.8.3
- guideline for gathering statistics 13.3.2.3
partitioning
- examples of 6.3.4.5.1
- examples of composite 6.3.4.5.2
- hash 6.3.4.5
- maintenance operations 13.3.8.4
- range 6.3.4.5
- start and stop columns 6.3.4.5.1
partition-wise joins 9.4.2
- full 6.3.4.5.4
- full, and EXPLAIN PLAN output 6.3.4.5.4
- how they work 9.4.2.2
- partial, and EXPLAIN PLAN output 6.3.4.5.3
- purpose 9.4.2.1
PLAN_TABLE table
- BYTES column 7.1
- CARDINALITY column 7.1
- COST column 7.1
- creating 6.2.1.1
- displaying 6.3.1
- DISTRIBUTION column 7.1
- ID column 7.1
- OBJECT_INSTANCE column 7.1
- OBJECT_NAME column 7.1
- OBJECT_NODE column 7.1
- OBJECT_OWNER column 7.1
- OBJECT_TYPE column 7.1
- OPERATION column 7.1
- OPTIMIZER column 7.1
- OPTIONS column 7.1
- OTHER_TAG column 7.1
- OTHER column 7.1
- PARENT_ID column 7.1
- PARTITION_ID column 7.1
- PARTITION_START column 7.1
- PARTITION_STOP column 7.1
- POSITION column 7.1
- REMARKS column 7.1
- SEARCH_COLUMNS column 7.1
- STATEMENT_ID column 7.1
- TIMESTAMP column 7.1
plan evolution
- purpose 27.5.1
pluggable databases
- automatic optimizer statistics collection 13.1.1
- manageability features 13.1.1
- SQL management base 27.6.1
- SQL Tuning Advisor 24.1.1
- SQL tuning sets 23.1
popular values, in histograms 11.4.2
POSITION column
- PLAN_TABLE table 7.1
PQ_DISTRIBUTE hint 6.3.4.5.3
preferences, optimizer statistics 12.2.1.1, 12.2.1.4
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 26.1
- concepts 26.1.2
- implementing 26.2
- managing 26
programming languages 2.2
PX_JOIN_FILTER hint 9.4.1.3

Q

quarantined SQL 4.7
query rewrite
- correctness 5.5.3
- parameters 5.5.2
query transformations 5
- In-Memory Aggregation 5.8
- join factorization 5.11, 5.11.1
- OR expansion 5.1
- predicate pushing 5.3
- purpose of table expansions 5.10.1
- query rewrite with materialized views 5.5
- star transformation 5.7.5
- star transformations 5.7, 8.4.1.2
- statistics-based query transformation 5.6
- subquery unnesting 5.4
- table expansion 5.10, 5.10.2, 5.10.3
- view merging 5.2
query transformer 4.2.1

R

range
- examples of partitions 6.3.4.5.1
- partitions 6.3.4.5
Real-Time Database Operations 1.4.2.2.2
Real-Time SQL Monitoring 1.4.2.2.2, 21.1.1
- architecture 21.1.3
Real-World Performance
- cursor sharing 20
recursive SQL 3.3, 10.3.2, 10.4.4, 22.5.2
REMARKS column
- PLAN_TABLE table 7.1
reoptimization, automatic 4.4.2.2, 6.3.3, 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
rewrites
- parameters 5.5.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, , 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.1
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 28.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
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 28.2, 28.2.2
SPM Evolve Advisor task
- about 28.2.2.1
- managing 28.2.2
- scheduling 28.2.2.2
SQL
- execution 3.1.4
- management objects
- operators
- optimization 4.1.2
- parsing 3.1.1
- processing 3.1
- quarantined 4.7
- recursive 3.3, 10.4.4, 12.3
- result sets 3.1.3, 3.2
- stages of processing 8.2, 8.3
SQL_STATEMENT column
- TKPROF_TABLE 22.4.4.3
SQL Access Advisor 1.4.2.1.3, 25.1.1
- about 25.1
- actions 25.1.2.4
- advanced tasks 25.4
- basic tasks 25.2
- constants 25.6.3
- creating task templates 25.4.3
- deleting tasks 25.4.5
- demo script 25.5
- evaluation mode 25.4.1
- EXECUTE_TASK procedure 25.2.4
- filter options 25.1.2.2
- input sources 25.1.2.1
- marking recommendations 25.4.6
- modifying recommendations 25.4.7
- populating SQL tuning sets 25.2.2
- quick tune 25.3
- recommendations 25.1.2.4.2
- reference 25.6
- repository 25.1.2.5
- task categories 25.6.2
- terminating tasks 25.4.4
- updating task attributes 25.4.2
- user interfaces 25.1.3
SQL compilation 10.4.1, 10.4.2.2, 10.4.4
SQL management base
- changing disk space limits 28.7.2
- managing 28.7, 28.7.1
- plan retention policy 28.7.3
- pluggable databases and 27.6.1
SQL Performance Analyzer 1.4.2.1.6
SQL plan baselines 1.4.2.1.5, 4.6, 27
- automatic plan capture 27.3.1.1
- configuring initial capture 28.2.1
- displaying 28.3
- dropping 28.6
- loading 28.4.1
- loading from AWR 28.4.2
- loading from shared SQL area 28.4.3
- loading using DBMS_SPM 28.4
- managing 28, 28.1
- plan history 27.6.3
- plan matching 27.3.1.2
- plan retention 28.7.3
SQL plan capture 27.3
- configuring filters of automatic plan capture 28.2.1.2
- enabling automatic 28.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 28.5.2
SQL plan history 27.6.3
SQL plan management 1.4.2.1.5
- accepted plans 4.6
- automatic plan capture 27.3.1, 28.2.1.1
- basic tasks 28.1.2
- benefits 27.2.1
- configuring 28.2
- DBMS_SPM 28.1.1.2
- DBMS_SPM package 28.1.1
- filters for automatic plan capture 28.2.1.2
- introduction 27
- loading plans from a staging table 28.4.5
- loading plans from SQL tuning sets 28.4.4
- manual plan capture 27.3.2
- plan capture 27
- plan evolution 27, 27.5, 28.5.2
- plan retention 28.7.3
- plan selection 27, 27.4
- plan verification 27.5
- purpose 27.2
- SPM Evolve Advisor 28.2.2
- SQL management base 28.7, 28.7.2
- SQL plan baselines 4.6, 27, 28, 28.6
- SQL plan capture 27.3
- storage architecture 27.6
- user interfaces 28.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, 24.1.2.3.2
- about 26.1
- and SQL plan baselines 27.2.2
- concepts 26.1.2
- Exadata-aware 26.1.2.1
- implementing 26.2
- managing 26
- statistics 26.1.2.1
- user interface 26.1.3
SQL statement log 27.6.2
SQL statements
- execution plans of 6.1
SQL trace facility , 22.1.3.1, 22.4
- enabling 22.4.2
- generating output 22.4.3
- output 22.6.1.2.5.2
- statement truncation 22.4.3
- trace files 1.4.2.2.3, 22.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, 24
- about 24.1
- administering with APIs 24.3.1.2.1
- analyses 24.1.2.3
- architecture 24.1.2
- automatic 24.2.2, 24.2.4.1
- automatic tuning task 24.2.1, 24.2.3.2
- command-line interface 24.3.1.2.2
- configuring a SQL tuning task 24.3.3
- controls 24.1.3.1
- creating a task 24.3.2
- DBMS_SQLTUNE 24.3.1.2.2
- displaying tuning results 24.3.6
- executing a task 24.3.4
- input sources 24.1.2.1
- monitoring tasks 24.3.5
- pluggable databases and 24.1.1
- reports 24.2.4
- running on-demand 24.3, 24.3.1, 24.3.1.1
- statistical analysis 24.1.2.3.1
- using 12.1.2.1.2, 24.3.1.2.1
- using remotely 24.1.3.2
SQL tuning sets
- command-line interface 23.1.3.2
- loading in SQL plan baselines 28.4.4
- managing with APIs 23.1
- pluggable databases and 23.1
- populating with user-defined workload 25.2.2
start columns
- in partitioning and EXPLAIN PLAN statement 6.3.4.5.1
star transformations 5.7, 5.7.5, 8.4.1.2
STATEMENT_ID column
- PLAN_TABLE table 7.1
STATISTICS_LEVEL initialization parameter 16.2.3
statistics, optimizer 4.1.2,
- adaptive statistics 4.4.2, 13.3.8.2.2
- automatic collection 13.1, 13.1.1
- bulk loads 10.3.3.1, 10.3.3.1.1
- 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.4.4
- determining staleness 13.3.3
- dynamic 10.3.2, 10.4.1, 12.3, 13.3.6
- dynamic statistics 10.4.3
- exporting and importing 17
- expressions 14.2
- extended 10.2.2, 14
- gathering concurrently 13.3.7, 13.3.7.1
- gathering manually 13.3, 13.3.2
- global statistics 13.3.8.2
- guideline for external tables 13.3.2.5
- history 16.2.1
- how gathered 10.3
- incremental 13.3.8, 13.3.8.1, 13.3.8.3.1, 13.3.8.4, 13.3.8.5
- index 10.2.3
- locking 15.1, 15.1.1
- managing 16
- manual collection 13.3.1
- monitoring collection 13.3.7.3
- noworkload 13.4.3.3
- Optimizer Statistics Advisor 18.1, 18.1.1, 18.1.1.1
- options for gathering 12
- partitioned tables 13.3.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.5
- restoring 16.1.2, 16.1.3, 16.1.4
- retention 16.2
- retention period 16.2.2
- setting 15.3
- system 10.2.4, 13.4, 13.4.1, 13.4.2, 13.4.3.1
- table 10.2.1
- transporting 17.2
- types 10.2
- unlocking 15.1.2
- user-defined 10.2.5
- when gathered 10.4
- workload 13.4.3.2
stop columns
- in partitioning and EXPLAIN PLAN statement 6.3.4.5.1
stored outlines
- about 29.1
- categories 29.1.2.2
- how they work 29.1.2
- purpose of migrating 29.1.1
subqueries
- NOT IN 9.1.3
synopses 13.3.8.2.1
SYS_AUTO_SQL_TUNING_TASK 24.3.4
system statistics 13.4.2, 13.4.3.1, 24.1.2.3.1
- about 13.4.1
- deleting 13.4.4
- gathering 13.4.3.2.2
- noworkload 13.4.3.3

T

table clusters
- access paths 8.5
table expansion
- about 5.10
- how it works 5.10.2
- purpose 5.10.1
- scenario 5.10.3
table statistics 10.2.1, 10.2.1.1
task templates, SQL Access Advisor 25.4.3
temporary tables
- cursor-duration 5.9, 5.9.1, 5.9.2, 5.9.3
- global 10.2.1.2
- types 10.2.1.2.1
testing designs 2.2.1
throughput
- optimizer goal 19.2.3
TIMED_STATISTICS initialization parameter 22.6.1.2.5.2
TIMESTAMP column
- PLAN_TABLE table 7.1
TKPROF_TABLE 22.4.4.3
TKPROF program 22.1.3.2, 22.4, 22.6.1, 22.6.1.2
- editing the output SQL script 22.4.4.2
- example of output 22.5.3
- generating the output SQL script 22.4.4.1
- row source operations 22.6.1.2.5.4
- timing statistics 22.5.1
- using the EXPLAIN PLAN statement 22.6.1.2.4
- wait event information 22.6.1.2.5.5
top frequency histograms 11.6, 11.6.1
tracing , 22
- consolidating with TRCSESS 22.6.1.1
- enabling 22.3.1, 22.3.2
- enabling statistics gathering 22.2
- generating output files 22.4
- identifying files 22.4.1
transformations, query
- In-Memory Aggregation 5.8
- join factorization 5.11, 5.11.1
- predicate pushing 5.3
- purpose of table expansions 5.10.1
- star 5.7
- statistics-based query transformation 5.6
- subquery unnesting 5.4
- table expansion 5.10, 5.10.2, 5.10.3
- view merging 5.2
transformer, query 4.2.1
TRCSESS program 22.6.1, 22.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 22.4.4.3
utlxplp.sql script 6.3.1
utlxpls.sql script 6.3.1

V

V$SESSION view 22.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$SQLAREA view 20.1.1.2.1
V$SQL view 20.1.1.2.2, 20.3.6
validating designs 2.2.1
view merging 5.2

W

workloads 2.2.1