40/40

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, 3.1.4

execution plans, 6.1

full table scan, 10.2.3.1

full table scans, 11.1

adaptive plans, 4.4.1, 7.2.1, 7.2.1, 7.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, 7.2.1, 7.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, 9.1.3

applications

implementing, 2.2

automatic reoptimization, 4.4.2.2, 7.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, 7.2.5.5.3

on joins, A.6

when to use, A.5

BOOLEAN data type, 8.3.3.1

broadcast

distribution value, 7.2.6, 7.3.2

BYTES column

PLAN_TABLE table, 7.2.6, 7.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.2.1, 8.4.1.1, 10.2.1, 10.4.1.1, 11.1, 11.1, 11.3

CARDINALITY column

PLAN_TABLE table, 7.2.6, 7.3.2

cartesian joins, 9.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, 7.2.5.2

concurrent statistics gathering, 12.4.7.1, 12.4.7.3, Glossary

consistent mode

TKPROF, 18.5.4.4

constraints, A.1.10

COST column

PLAN_TABLE table, 7.2.6, 7.3.2

create_extended_statistics, 13.3.2.2, 13.3.2.2

current mode

TKPROF, 18.5.4.4

CURSOR_NUM column

TKPROF_TABLE table, 18.4.4.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 flow operator (DFO), 5.7.2

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, 8.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.4.3

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 8.2.2.2, 8.2.2.2

DBMS_ADVISOR package, 21.1.1

DBMS_MONITOR package

end-to-end application tracing, 18.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, 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

dense keys, 5.7.2.1

dense grouping keys, 5.7.2.1

dense join keys, 5.7.2.1

density, histogram, 11.3.2

DEPTH column

TKPROF_TABLE table, 18.4.4.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, 7.2.6, 7.3.2

domain indexes

and EXPLAIN PLAN, 7.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.2

creating a service, 18.1.2

DBMS_APPLICATION_INFO package, 18.1.2

DBMS_MONITOR package, 18.1.2

enforced constraints, A.1.10

examples

ALTER SESSION statement, 18.4.2

EXPLAIN PLAN output, 18.5.3

SQL trace facility output, 18.5.3

EXECUTE_TASK procedure, 21.2.4

execution plans, 3.1.1.3

adaptive, 4.4.1, 7.2.1, 7.2.1, 7.3.1

examples, 18.4.3

overview of, 6.1

TKPROF, 18.4.3, 18.5.4.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 domain indexes, 7.2.5.6

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

examples of output, 18.5.3

execution order of steps in output, 6.3

invoking with the TKPROF program, 18.5.4.4

PLAN_TABLE table, 6.2.6

restrictions, 6.2.5

scripts for viewing output, 6.3

viewing the output, 6.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, 9.3.2.4

full partition-wise joins, 7.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, 7.2.6, 7.3.2

hash clusters

sorted, A.9

hash joins, 9.2.2

cost-based optimization, 9.1.3

hash partitions, 7.2.5

examples of, 7.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, 7.2.6, 7.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, 8.3.3

selectivity of, A.1.3

initialization parameters

DIAGNOSTIC_DEST, 18.4.1

INLIST ITERATOR operation, 7.2.5.5

inlists, 7.2.5.5

in-memory aggregation, 5.7

controls, 5.7.3

how it works, 5.7.2

purpose, 5.7.1

in-memory table scans, 8.2.5

controls, 8.2.5.2

example, 8.2.5.3

when chosen, 8.2.5.1

I/O

reducing, A.1.4

---

J

joins

antijoins, 9.1.3

cartesian, 9.2.4

full outer, 9.3.2.4

hash, 9.2.2

nested loops, 3.1.4, 9.2.1.1

nested loops and cost-based optimization, 9.1.3

order, 14.3.2

outer, 9.3.2

partition-wise

examples of full, 7.2.5.4

examples of partial, 7.2.5.3

full, 7.2.5.4

semijoins, 9.1.3

sort-merge and cost-based optimization, 9.1.3, 9.1.3

---

K

key vectors, 5.7.2.1

---

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, 9.2.1.1

cost-based optimization, 9.1.3

NO_INDEX hint, A.1.6

nonpopular values, in histograms, 11.3.2

NOT IN subquery, 9.1.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.2.6, 7.3.2, 7.3.2

optimization, SQL, 4.1.2

optimizer

adaptive, 7.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, 7.2.6, 7.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, 7.2.6, 7.3.2

OTHER column

PLAN_TABLE table, 7.2.6, 7.3.2

OTHER_TAG column

PLAN_TABLE table, 7.2.6, 7.3.2

outer joins, 9.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, 7.2.6, 7.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, 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

partitioning

distribution value, 7.2.6, 7.3.2

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-wise joins

full, 7.2.5.4

full, and EXPLAIN PLAN output, 7.2.5.4

partial, and EXPLAIN PLAN output, 7.2.5.3

performance

viewing execution plans, 6.3

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.6, 6.2.6

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 column, 7.2.6, 7.3.2

OTHER_TAG 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

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, 7.2.6, 7.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, 7.2.6, 7.3.2

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, 16.1

REBUILD clause, A.1.7

recursive calls, 18.5.2

recursive SQL, 3.3, 10.3.2, 10.4.3

REMARKS column

PLAN_TABLE table, 7.2.6, 7.3.2

reoptimization, automatic, 4.4.2.2, 7.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, 7.2.6, 7.3.2

row source generation, 3.1.3

rowids

table access by, 8.2.3

rows

row set, 3.1.3

row source, 3.1.3

rowids used to locate, 8.2.3

---

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

selectivity

creating indexes, A.1.3

improving for an index, A.1.4

indexes, A.1.6

semijoins, 9.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, 9.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, 8.2, 8.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, 6.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.1.2.1, 18.4.3

example of output, 18.5.3

output, 18.5.4.4

statement truncation, 18.4.3

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.4.3

SQLTUNE_CATEGORY initialization parameter

determining the SQL Profile category, 22.4

start columns

in partitioning and EXPLAIN PLAN statement, 7.2.5.1

STATEMENT_ID column

PLAN_TABLE table, 7.2.6, 7.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, 7.2.5.1

subqueries

NOT IN, 9.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, 7.2.6, 7.3.2

TKPROF program, 18.1.2.2, 18.4.3, 18.5.4.4

editing the output SQL script, 18.4.4.2

example of output, 18.5.3

generating the output SQL script, 18.4.4.1

row source operations, 18.5.4.4

using the EXPLAIN PLAN statement, 18.5.4.4

wait event information, 18.5.4.4

TKPROF_TABLE, 18.4.4.3, 18.4.4.3

top frequency histograms, 11.4

TRACEFILE_IDENTIFIER initialization parameter

identifying trace files, 18.4.1

tracing

consolidating with trcsess, 18.5.4.4

identifying files, 18.4.1

trcsess utility, 18.5.4.4

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.4.3

user_stat_extensions, 13.3.1.5

UTLXPLP.SQL script

displaying plan table output, 6.4

for viewing EXPLAIN PLANs, 6.3

UTLXPLS.SQL script

displaying plan table output, 6.4

for viewing EXPLAIN PLANs, 6.3

used for displaying EXPLAIN PLANs, 6.3

---

V

V$SQL_PLAN view

using to display execution plan, 6.2.4.1

V$SQL_PLAN_STATISTICS view

using to display execution plan statistics, 6.2.4.1

V$SQL_PLAN_STATISTICS_ALL view

using to display execution plan information, 6.2.4.1

validating designs, 2.2.1

---

W

workloads, 2.2.1