Skip Headers
Oracle® Database SQL Tuning Guide
12c Release 1 (12.1)

E15858-16
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
PDF · Mobi · ePub

Index

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

A

access paths, 3.1.4
execution plans, 8.1
full table scan, 10.2.3.1
full table scans, 11.1
adaptive plans, 4.4.1, 9.2.1, 9.2.1, 9.3.1, 14.2.1, 17.2.2
cardinality misestimates, 4.4.1
join methods, 4.4.1.2
optimizer statistics collector, 4.4.1.1
parallel distribution methods, 4.4.1.3
reporting mode, 14.2.1
subplans, 4.4.1.1
adaptive query optimization, 4.4
adaptive plans, 4.4.1, 9.2.1, 9.3.1, 14.2.1, 17.2.2
controlling, 14.2.4
dynamic statistics, 10.3.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, 13.3.1
ADDM, 1.4.2.1.1
ALTER INDEX statement, A.1.7
ALTER SESSION statement
examples, 18.4.2
antijoins, 7.1.3
applications
implementing, 2.2
automatic reoptimization, 4.4.2.2, 9.2.1, 10.4.1.2
cardinality misestimates, 4.4.2.2.1
performance feedback, 4.4.2.2.2
statistics feedback, 4.4.2.2.1
automatic statistics collection, 12.2
Automatic Tuning Optimizer, 1.4.2.1.2
Automatic Workload Repository (AWR), 1.4.2.1.1

B

big bang rollout strategy, 2.2.2
bind variables, 15.1.2
bitmap indexes
inlist iterator, 9.2.5.5.3
on joins, A.6
when to use, A.5
BOOLEAN data type, 6.3.3.1
broadcast
distribution value, 9.2.6, 9.3.2
BYTES column
PLAN_TABLE table, 9.2.6, 9.3.2

C

cardinality, 1.4.2.1.2, 4.2.2, 4.4.1, 4.4.1.2, 4.4.2.2.1, 4.4.2.3, 6.4.1.1, 8.2.1, 10.2.1, 10.4.1.1, 11.1, 11.1, 11.3
CARDINALITY column
PLAN_TABLE table, 9.2.6, 9.3.2
cartesian joins, 7.2.4
clusters, A.8, A.8
sorted hash, A.9
column group statistics, 10.4.1.1
column groups, 13.3.1, 13.3.1.2
columns
cardinality, 4.2.2
to index, A.1.3
compilation, SQL, 10.4, 10.4.1.1, 10.4.3
composite indexes, A.1.4
composite partitioning
examples of, 9.2.5.2
concurrent statistics gathering, 12.4.7.1, 12.4.7.3, Glossary
consistent mode
TKPROF, 18.4.4.1
constraints, A.1.10
COST column
PLAN_TABLE table, 9.2.6, 9.3.2
create_extended_statistics, 13.3.2.2, 13.3.2.2
current mode
TKPROF, 18.4.4.1
CURSOR_NUM column
TKPROF_TABLE table, 18.4.5.3
CURSOR_SHARING initialization parameter, 15.2
cursors, SQL, 3.1.1

D

data
modeling, 2.1.1
data blocks, 3.2.1
data dictionary cache, 3.1.1.3
Data Pump
Export utility
statistics on system-generated columns names, 13.7.2
Import utility
copying statistics, 13.7.2
data skew, 11.1
data types
BOOLEAN, 6.3.3.1
database operations
composite, 1.4.2.2.2, 16.1
definition, 1.4.2.2.2, 16.1
simple, 1.4.2.2.2, 16.1
database operations, monitoring, 1.4.2.2.2, 16.1
composite, 16.1.2.2
composite operations, 16.1
creating database operations, 16.3
enabling with hints, 16.2.2
enabling with initialization parameters, 16.2.1
Enterprise Manager interface, 16.1.3.1
generating a report, 16.4
PL/SQL interface, 16.1.3.2
purpose, 16.1.1
real-time SQL, 16.1
simple operations, 16.1
DATE_OF_INSERT column
TKPROF_TABLE table, 18.4.5.3
DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 6.2.2.2, 6.2.2.2
DBMS_ADVISOR package, 21.1.1
DBMS_MONITOR package
end-to-end application tracing, 18.1.1.2
DBMS_SQLTUNE package
SQL Tuning Advisor, 19.1.3.1, 20.3.1.2.1
dbms_stats functions
create_extended_statistics, 13.3.2.2, 13.3.2.2
drop_extended_stats, 12.5.5, 13.3.1.6, 13.3.2.4
gather_table_stats, 13.3.2.2, 13.3.2.2
show_extended_stats_name, 13.3.1.5
DBMS_STATS package, 21.1.2.3
DBMS_XPLAN package
displaying plan table output, 8.4
DDL (data definition language)
processing of, 3.3
deadlocks, 3.1.1
debugging designs, 2.2.1
dedicated server, 3.1.1.3
density, histogram, 11.3.2
DEPTH column
TKPROF_TABLE table, 18.4.5.3
designs
debugging, 2.2.1
testing, 2.2.1
validating, 2.2.1
development environments, 2.2
DIAGNOSTIC_DEST initialization parameter, 18.4.1
disabled constraints, A.1.10
DISTRIBUTION column
PLAN_TABLE table, 9.2.6, 9.3.2
domain indexes
and EXPLAIN PLAN, 9.2.5.6
using, A.7
drop_extended_stats, 12.5.5, 13.3.1.6, 13.3.2.4
dynamic statistics, 4.4.2.1, 10.3.2, 10.4, 10.4.2, 12.4.6, 17.2.2
controlling, 13.1
process, 13.1.1
sampling levels, 13.1.1
when to use, 13.1.3

E

enabled constraints, A.1.10
endpoint repeat counts, in histograms, 11.6
end-to-end application tracing, 1.4.2.2.3
action and module names, 18.1.1.2
creating a service, 18.1.1.2
DBMS_APPLICATION_INFO package, 18.1.1.2
DBMS_MONITOR package, 18.1.1.2
enforced constraints, A.1.10
examples
ALTER SESSION statement, 18.4.2
EXPLAIN PLAN output, 18.4.4.10
SQL trace facility output, 18.4.4.10
EXECUTE_TASK procedure, 21.2.4
execution plans, 3.1.1.3
adaptive, 4.4.1, 9.2.1, 9.2.1, 9.3.1
examples, 18.4.3.1
overview of, 8.1
TKPROF, 18.4.3.1, 18.4.3.2
V$ views, 9.3.1
viewing with the utlxpls.sql script, 8.3
execution trees, 3.1.4
EXPLAIN PLAN statement
access paths, 6.2.4.2
and domain indexes, 9.2.5.6
and full partition-wise joins, 9.2.5.4
and partial partition-wise joins, 9.2.5.3
and partitioned objects, 9.2.5
basic steps, 8.3
examples of output, 18.4.4.10
execution order of steps in output, 8.3
invoking with the TKPROF program, 18.4.3.2
PLAN_TABLE table, 8.2.6
restrictions, 8.2.5
scripts for viewing output, 8.3
viewing the output, 8.3
extended statistics, 10.2.2
extensions, 10.4.1

F

fixed objects
gathering statistics for, 12.1, 12.4.5
frequency histograms, 11.4
FULL hint, A.1.6
full outer joins, 7.3.2.4
full partition-wise joins, 9.2.5.4
full table scans, 10.2.3.1, 11.1
function-based indexes, A.2

G

gather_table_stats, 13.3.2.2, 13.3.2.2
global temporary tables, 10.2.4

H

hard parsing, 2.1.2, 3.1.1.3
hash
distribution value, 9.2.6, 9.3.2
hash clusters
sorted, A.9
hash joins, 7.2.2
cost-based optimization, 7.1.3
hash partitions, 9.2.5
examples of, 9.2.5.1
hashing, A.9
height-balanced histograms, 11.5
high-load SQL
tuning, 12.1.2.1.2, 20.3.1.2.1
hints, optimizer, 1.4.2.2.4
FULL, A.1.6
NO_INDEX, A.1.6
NO_MONITOR, 16.2.2
histograms, 11
cardinality algorithms, 11.3
data skew, 11.1
definition, 11
density, 11.3.2
endpoint numbers, 11.3.1
endpoint repeat counts, 11.6
endpoint values, 11.3.1
frequency, 11.4
height-balanced, 11.5
hybrid, 11.6
NDV, 11
nonpopular values, 11.3.2
popular values, 11.3.2
purpose, 11.1
top frequency, 11.4
hybrid histograms, 11.6

I

ID column
PLAN_TABLE table, 9.2.6, 9.3.2
incremental statistics, 12.4.8.4, 12.4.8.5
index clustering factor, 10.2.3.1
INDEX hint, A.1.6
index statistics, 10.2.3
index clustering factor, 10.2.3.1
INDEX_COMBINE hint, A.1.6
indexes
avoiding the use of, A.1.6
bitmap, A.5
choosing columns for, A.1.3
composite, A.1.4
domain, A.7
dropping, A.1.1
enforcing uniqueness, A.1.9
ensuring the use of, A.1.5
function-based, A.2
improving selectivity, A.1.4
low selectivity, A.1.6
modifying values of, A.1.3
non-unique, A.1.9
rebuilding, A.1.7
re-creating, A.1.7
scans, 6.3.3
selectivity of, A.1.3
initialization parameters
DIAGNOSTIC_DEST, 18.4.1
INLIST ITERATOR operation, 9.2.5.5
inlists, 9.2.5.5
I/O
reducing, A.1.4

J

joins
antijoins, 7.1.3
cartesian, 7.2.4
full outer, 7.3.2.4
hash, 7.2.2
nested loops, 3.1.4, 7.2.1.1
nested loops and cost-based optimization, 7.1.3
order, 14.3.2
outer, 7.3.2
partition-wise
examples of full, 9.2.5.4
examples of partial, 9.2.5.3
full, 9.2.5.4
semijoins, 7.1.3
sort-merge and cost-based optimization, 7.1.3, 7.1.3

L

latches
parsing and, 3.1.1.3
library cache, 3.1.1.3
library cache miss, 3.1.1.3
locks
deadlocks, 3.1.1

M

manual plan capture, 23.1.2.2
MAX_DUMP_FILE_SIZE initialization parameter
SQL Trace, 18.4.1
modeling
data, 2.1.1
multiversion read consistency, 3.2.1

N

NDV, 11
nested loops joins, 7.2.1.1
cost-based optimization, 7.1.3
NO_INDEX hint, A.1.6
nonpopular values, in histograms, 11.3.2
NOT IN subquery, 7.1.3

O

OBJECT_INSTANCE column
PLAN_TABLE table, 9.2.6, 9.3.2
OBJECT_NAME column
PLAN_TABLE table, 9.2.6, 9.3.2
OBJECT_NODE column
PLAN_TABLE table, 9.2.6, 9.3.2
OBJECT_OWNER column
PLAN_TABLE table, 9.2.6, 9.3.2
OBJECT_TYPE column
PLAN_TABLE table, 9.2.6, 9.3.2
OPERATION column
PLAN_TABLE table, 9.2.6, 9.2.6, 9.3.2, 9.3.2
optimization, SQL, 4.1.2
optimizer
adaptive, 9.2.1
definition, 4.1
environment, 3.1.1.3
estimator, 4.2.2
execution, 3.1.4
goals, 14.2.3
purpose of, 4.1.1
row sources, 3.1.3, 3.1.3
statistics, 14.3
throughput, 14.2.3
OPTIMIZER column
PLAN_TABLE, 9.2.6, 9.3.2
optimizer environment, 3.1.1.3
optimizer hints, 1.4.2.2.4
FULL, A.1.6
MONITOR, 16.2.2
NO_INDEX, A.1.6
optimizer statistics
adaptive statistics, 4.4.2
automatic collection, 12.2
bulk loads, 10.3.3
cardinality, 11.1
collection, 12.1
column group, 10.4.1.1
column groups, 13.3.1
dynamic, 10.3.2, 10.4, 12.4.6, 13.1, 17.2.2
extended, 10.2.2
gathering concurrently, 12.4.7, 12.4.7.1, Glossary
gathering in parallel, 12.4.7.3
histograms, 11
incremental, 12.4.8.4, 12.4.8.5
index, 10.2.3
pluggable databases and, 12.2
preferences, 12.3.1
SQL plan directives, 10.4.1, 13.3.1
system, 12.5.1
temporary, 10.2.4
optimizer statistics collection, 12.1
optimizer statistics collectors, 4.4.1.1
OPTIONS column
PLAN_TABLE table, 9.2.6, 9.3.2
OTHER column
PLAN_TABLE table, 9.2.6, 9.3.2
OTHER_TAG column
PLAN_TABLE table, 9.2.6, 9.3.2
outer joins, 7.3.2

P

packages
DBMS_ADVISOR, 21.1.1
DBMS_STATS, 21.1.2.3
parallel execution
gathering statistics, 12.4.7.3
PARENT_ID column
PLAN_TABLE table, 9.2.6, 9.3.2
parse calls, 3.1.1
parsing, SQL, 3.1.1
hard, 2.1.2
hard parse, 3.1.1.3
parse trees, 3.1.4
soft, 2.1.2
soft parse, 3.1.1.3
partition maintenance operations, 12.4.8.4
PARTITION_ID column
PLAN_TABLE table, 9.2.6, 9.3.2
PARTITION_START column
PLAN_TABLE table, 9.2.6, 9.3.2
PARTITION_STOP column
PLAN_TABLE table, 9.2.6, 9.3.2
partitioned objects
and EXPLAIN PLAN statement, 9.2.5
partitioning
distribution value, 9.2.6, 9.3.2
examples of, 9.2.5.1
examples of composite, 9.2.5.2
hash, 9.2.5
range, 9.2.5
start and stop columns, 9.2.5.1
partition-wise joins
full, 9.2.5.4
full, and EXPLAIN PLAN output, 9.2.5.4
partial, and EXPLAIN PLAN output, 9.2.5.3
performance
viewing execution plans, 8.3
PLAN_TABLE table
BYTES column, 9.2.6, 9.3.2
CARDINALITY column, 9.2.6, 9.3.2
COST column, 9.2.6, 9.3.2
creating, 8.2.6, 8.2.6
displaying, 8.4
DISTRIBUTION column, 9.2.6, 9.3.2
ID column, 9.2.6, 9.3.2
OBJECT_INSTANCE column, 9.2.6, 9.3.2
OBJECT_NAME column, 9.2.6, 9.3.2
OBJECT_NODE column, 9.2.6, 9.3.2
OBJECT_OWNER column, 9.2.6, 9.3.2
OBJECT_TYPE column, 9.2.6, 9.3.2
OPERATION column, 9.2.6, 9.3.2
OPTIMIZER column, 9.2.6, 9.3.2
OPTIONS column, 9.2.6, 9.3.2
OTHER column, 9.2.6, 9.3.2
OTHER_TAG column, 9.2.6, 9.3.2
PARENT_ID column, 9.2.6, 9.3.2
PARTITION_ID column, 9.2.6, 9.3.2
PARTITION_START column, 9.2.6, 9.3.2
PARTITION_STOP column, 9.2.6, 9.3.2
POSITION column, 9.2.6, 9.3.2
REMARKS column, 9.2.6, 9.3.2
SEARCH_COLUMNS column, 9.2.6, 9.3.2
STATEMENT_ID column, 9.2.6, 9.3.2
TIMESTAMP column, 9.2.6, 9.3.2
pluggable databases
automatic optimizer statistics collection, 12.2
manageability features, 12.2
SQL management base, 23.1.5.1
SQL Tuning Advisor, 20.1.1, 21.1
SQL tuning sets, 19.1
popular values, in histograms, 11.3.2
POSITION column
PLAN_TABLE table, 9.2.6, 9.3.2
PRIMARY KEY constraint, A.1.9
private SQL areas
parsing and, 3.1.1
processes
dedicated server, 3.1.1.3
programming languages, 2.2

Q

queries
avoiding the use of indexes, A.1.6
ensuring the use of indexes, A.1.5

R

range
distribution value, 9.2.6, 9.3.2
examples of partitions, 9.2.5.1
partitions, 9.2.5
Real-Time Database Operations, 1.4.2.2.2
Real-Time SQL Monitoring, 1.4.2.2.2, 16.1
REBUILD clause, A.1.7
recursive calls, 18.4.4.5
recursive SQL, 3.3, 10.3.2, 10.4.3
REMARKS column
PLAN_TABLE table, 9.2.6, 9.3.2
reoptimization, automatic, 4.4.2.2, 9.2.1, 10.4.1.2
cardinality misestimates, 4.4.2.2.1
performance feedback, 4.4.2.2.2
statistics feedback, 4.4.2.2.1
result sets, SQL, 3.1.3, 3.2
rollout strategies
big bang approach, 2.2.2
trickle approach, 2.2.2
round-robin
distribution value, 9.2.6, 9.3.2
row source generation, 3.1.3
rowids
table access by, 6.2.3
rows
row set, 3.1.3
row source, 3.1.3
rowids used to locate, 6.2.3

S

SAMPLE BLOCK clause, 6.2.4
SAMPLE clause, 6.2.4
sample table scans, 6.2.4
scans
sample table, 6.2.4
SEARCH_COLUMNS column
PLAN_TABLE table, 9.2.6, 9.3.2
SELECT statement
SAMPLE clause, 6.2.4
selectivity
creating indexes, A.1.3
improving for an index, A.1.4
indexes, A.1.6
semijoins, 7.1.3
shared pool, 10.4.1.1
parsing check, 3.1.1.3
shared SQL areas, 3.1.1.3
show_extended_stats_name, 13.3.1.5
soft parsing, 2.1.2, 3.1.1.3
sort merge joins
cost-based optimization, 7.1.3
SQL
execution, 3.1.4
optimization, 4.1.2
parsing, 3.1.1
recursive, 3.3
result sets, 3.1.3, 3.2
stages of processing, 6.2, 6.3
SQL Access Advisor, 1.4.2.1.3, 21.1.1, 21.1.1
constants, 21.6.3
EXECUTE_TASK procedure, 21.2.4
SQL compilation, 10.4, 10.4.1.1, 10.4.3
SQL management base
pluggable databases and, 23.1.5.1
SQL parsing
parse calls, 3.1.1
SQL Performance Analyzer, 1.4.2.1.5
SQL plan baselines, 1.4.2.1.4, 23.1
displaying, 23.3
SQL plan capture, 23.1.2
SQL plan directives, 4.4.2.3, 10.4.1, 13.3.1
cardinality misestimates, 10.4.1.1
managing, 13.10
SQL plan management, 1.4.2.1.4
automatic plan capture, 23.1.2.1
introduction, 23.1
manual plan capture, 23.1.2.2
plan capture, 23.1
plan evolution, 23.1, 23.1.4
plan selection, 23.1, 23.1.3
plan verification, 23.1.4
purpose, 23.1.1
SQL plan baselines, 23.1
SQL plan capture, 23.1.2
SQL processing
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
and SQL plan baselines, 23.1.1.2
SQL statements
avoiding the use of indexes, A.1.6
ensuring the use of indexes, A.1.5
execution plans of, 8.1
modifying indexed data, A.1.3
SQL Test Case Builder, 17.1
SQL test cases, 17
SQL trace facility, 1.4.2.2.3, 18.3.1, 18.4.3
example of output, 18.4.4.10
output, 18.4.4.1
statement truncation, 18.4.4.7
trace files, 18.4.1
SQL trace files, 1.4.2.2.3
SQL tuning
definition, 1.1
introduction, 1
tools overview, 1.4.2
SQL Tuning Advisor, 1.4.2.1.2
administering with APIs, 19.1.3.1, 20.3.1.2.1
input sources, 20.1.2.2, 21.1.2.1
pluggable databases and, 20.1.1, 21.1
using, 12.1.2.1.2, 20.3.1.2.1
SQL tuning sets
pluggable databases and, 19.1
SQL Tuning Sets
managing with APIs, 19.1
SQL, recursive, 10.4.3, 13.1
SQL_STATEMENT column
TKPROF_TABLE, 18.4.5.3
SQLTUNE_CATEGORY initialization parameter
determining the SQL Profile category, 22.4
start columns
in partitioning and EXPLAIN PLAN statement, 9.2.5.1
STATEMENT_ID column
PLAN_TABLE table, 9.2.6, 9.3.2
statistics, optimizer, 4.1.2, 14.3
adaptive statistics, 4.4.2
automatic collection, 12.2
bulk loads, 10.3.3
cardinality, 11.1
collection, 12.1
column group, 10.4.1.1
column groups, 13.3.1, 13.3.1.2
dynamic, 10.3.2, 10.4, 12.4.6, 13.1, 17.2.2
dynamic statistics, 10.4.2
exporting and importing, 13.7
extended, 10.2.2
gathering concurrently, 12.4.7
incremental, 12.4.8.4, 12.4.8.5
index, 10.2.3
limitations on restoring previous versions, 13.5.2
preferences, 12.3.1
system, 10.2.5, 12.5, 12.5.1
user-defined, 10.2.6
stop columns
in partitioning and EXPLAIN PLAN statement, 9.2.5.1
subqueries
NOT IN, 7.1.3
system statistics, 12.5.1

T

table statistics, 10.2.1
temporary tables, global, 10.2.4
testing designs, 2.2.1
throughput
optimizer goal, 14.2.3
TIMED_STATISTICS initialization parameter
SQL Trace, 18.4.1
TIMESTAMP column
PLAN_TABLE table, 9.2.6, 9.3.2
TKPROF program, 18.3.2, 18.4.3
editing the output SQL script, 18.4.5.2
example of output, 18.4.4.10
generating the output SQL script, 18.4.5.1
row source operations, 18.4.4.2
syntax, 18.4.3.2
using the EXPLAIN PLAN statement, 18.4.3.2
wait event information, 18.4.4.3
TKPROF_TABLE, 18.4.5.3, 18.4.5.3
top frequency histograms, 11.4
TRACEFILE_IDENTIFIER initialization parameter
identifying trace files, 18.4.1
tracing
consolidating with trcsess, 18.2
identifying files, 18.4.1
trcsess utility, 18.2
trickle rollout strategy, 2.2.2
tuning
logical structure, A.1.1

U

UNIQUE constraint, A.1.9
uniqueness, A.1.9
USER_DUMP_DEST initialization parameter
SQL Trace, 18.4.1
USER_ID column, TKPROF_TABLE, 18.4.5.3
user_stat_extensions, 13.3.1.5
UTLXPLP.SQL script
displaying plan table output, 8.4
for viewing EXPLAIN PLANs, 8.3
UTLXPLS.SQL script
displaying plan table output, 8.4
for viewing EXPLAIN PLANs, 8.3
used for displaying EXPLAIN PLANs, 8.3

V

V$SQL_PLAN view
using to display execution plan, 8.2.4.1
V$SQL_PLAN_STATISTICS view
using to display execution plan statistics, 8.2.4.1
V$SQL_PLAN_STATISTICS_ALL view
using to display execution plan information, 8.2.4.1
validating designs, 2.2.1

W

workloads, 2.2.1