Index

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

A

access paths
cluster scans, 11.2.4
defined, 11.4.2
execution plans, 11.4
hash scans, 11.2.5
index scans, 11.2.3
Active Session History, 5.1.1.3
report
activity over time, 5.3.9.8
load profile, 5.3.9.2
top events, 5.3.9.1
top files, 5.3.9.7
top Java, 5.3.9.5
top latches, 5.3.9.7
top objects, 5.3.9.7
top PL/SQL, 5.3.9.4
top sessions, 5.3.9.6
Top SQL, 5.3.9.3
using, 5.3.9
adaptive thresholds, 5.2.3
ALL_OUTLINE_HINTS view
stored outline hints, 20.1.7
ALL_OUTLINES view
stored outlines, 20.1.7
ALL_ROWS hint, 11.5.2.2
allocation of memory, 7.1
ALTER INDEX statement, 14.1.7
ALTER SESSION statement
examples, 21.4.2
SET SESSION_CACHED_CURSORS clause, 7.3.7.2
ANALYZE statement, 13.3.1
antijoins, 11.3.2
applications
deploying, 2.7
design principles, 2.5
development trends, 2.5.7
implementing, 2.5.6
Automatic Database Diagnostic Monitor
actions and rationales of recommendations, 6.1.3
analysis results example, 6.1.4
and DB time, 6.1.1
CONTROL_MANAGEMENT_PACK_ACCESS parameter, 6.2
DBIO_EXPECTED, 6.2
example report, 6.1.4
findings, 6.1.3
overview, 6.1
results, 6.1.3
setups, 6.2
STATISTICS_LEVEL parameter, 6.2
types of problems considered, 6.1.1
types of recommendations, 6.1.3
automatic database diagnostic monitoring, 1.2.1, 16.4.1
automatic segment-space management, 4.1.4.1, 8.2.6.2, 10.3.1.2.1
Automatic Shared Memory Management, 7.1.3
automatic SQL tuning, 1.2.1, 16.4.2
analysis, 17.1
overview, 17
Automatic Tuning Optimizer, 17.1
automatic undo management, 4.1.2, 4.1.2
Automatic Workload Repository, 1.2.1
configuring, 5.2
data gathering, 5.1
DBMS_WORKLOAD_REPOSITORY package, 5.3.1, 5.3.2, 5.3.3
default settings, 5.2.4
factors affecting space usage, 5.2.4
managing with APIs, 5.3.1, 5.3.2, 5.3.3
minimizing space usage, 5.2.4
overview, 5.2
recommendations for retention period, 5.2.4
reports, 5.3.6, 5.3.7, 5.3.8
retention period, 5.2.4
settings in DBA_HIST_WR_CONTROL view, 5.3.1.3
space usage, 5.2.4
statistics collected, 5.2
turning off automatic snapshot collection, 5.2.4
unusual percentages in reports, 5.3.6
views for accessing data, 5.3.5
awrrpt.sql
Automatic Workload Repository report, 5.3.6, 5.3.7, 5.3.8

B

baselines, 1.1.2.2, 5.2.2
performance, 5.1
benchmarking workloads, 2.6.2.2
big bang rollout strategy, 2.7.1
bind variables, 7.3.1.3
peeking, 11.1.3
bitmap indexes, 2.5.3.2.2
inlist iterator, 12.9.5.3
on joins, 14.6
when to use, 14.5
block cleanout, 10.2.4.2
block size
choosing, 8.2.6
optimal, 8.2.6
bottlenecks
elimination, 1.1.2.4.2
fixing, 3.1
identifying, 3.1
memory, 7.1
resource, 10.3.18.1.2
broadcast
distribution value, 12.10
B-tree indexes, 2.5.3.2.1
buffer busy wait events, 10.2.3, 10.3.1
actions, 10.3.1.2
buffer cache
contention, 10.3.2, 10.3.3, 10.3.10.2
hit ratio, 7.2.2.3
reducing buffers, 7.2.3.2, 7.3.4.2
buffer pools
default cache, 7.2.4.1
hit ratio, 7.2.6
KEEP, 7.2.8
KEEP cache, 7.2.4.1
multiple, 7.2.4
RECYCLE cache, 7.2.4.1
business logic, 2.4.1.2.2, 2.5.6
BYTES column
PLAN_TABLE table, 12.10

C

CARDINALITY column
PLAN_TABLE table, 12.10
cardinality, column, 11.1.2.2
cartesian joins, 11.3.6
chained rows, 10.2.4.3
classes
wait events, 5.1.1.1, 10.1.3.2
client/server applications, 9.4.1.2
clusters, 14.8, 14.8
hash and scans of, 11.2.5
scans of, 11.2.4
sorted hash, 14.9
column order
indexes, 2.5.3.5
columns
cardinality, 11.1.2.2
to index, 14.1.3
COMPATIBLE initialization parameter, 4.1.1
components
hardware, 2.4.1.1
software, 2.4.1.2
composite indexes, 14.1.4
composite partitioning
examples of, 12.9.2
conceptual modeling, 3.1.2
consistency
read, 10.2.4.2
consistent gets from cache statistic, 7.2.2.3
consistent mode
TKPROF, 21.4.4.1
constraints, 14.1.10
contention
library cache latch, 10.3.10.3
memory, 7.1, 10
shared pool, 10.3.10.3
tuning, 10
wait events, 10.3.10
context switches, 9.4.1.4.2
CONTROL_FILES initialization parameter, 4.1.1
CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter
enabling automatic database diagnostic monitoring, 6.2
cost
optimizer calculation, 11.1.1
COST column
PLAN_TABLE table, 12.10
cost-based optimizations
procedures for plan stability, 20.2
upgrading to, 20.2.2
CPUs, 2.4.1.1.1
statistics, 5.1.2.1, 10.1.2.1.3
utilization, 9.4.1
CREATE INDEX statement
PARALLEL clause, 4.2.3
CREATE OUTLINE statement, 20.1.5
create_extended_statistics, 13.5.2.1.1, 13.5.2.2.1
CREATE_STORED_OUTLINES initialization parameter, 20.1.5, 20.1.5.1
CREATE_STORED_OUTLINES parameter, 20.1.5
current mode
TKPROF, 21.4.4.1
CURSOR_NUM column
TKPROF_TABLE table, 21.4.5.3
CURSOR_SHARING initialization parameter, 7.3.2.1
CURSOR_SPACE_FOR_TIME initialization parameter, 7.3.6
cursors
accessing, 7.3.2.6
sharing, 7.3.2.6

D

data
and transactions, 2.4.1.2.4
cache, 9.1.1
gathering, 5.1
modeling, 2.5.2
queries, 2.4.2
searches, 2.4.2
data dictionary, 7.3.4.1.2
statistics in, 13.7.1
views used in optimization, 13.7.1
Data Pump
Export utility
statistics on system-generated columns names, 13.5.4
Import utility
copying statistics, 13.5.4
database monitoring, 1.2.1, 16.4.1
diagnostic, 6.1
Database Resource Manager, 9.1.3, 9.1.3, 9.4.2, 10.1.2.1.3
database tuning
transient performance problems, 5.3.8
databases
buffers, 7.2.3.2, 7.3.4.2
diagnosing and monitoring, 6.1
size, 2.4.2
statistics, 5.1.1
DATE_OF_INSERT column
TKPROF_TABLE table, 21.4.5.3
db block gets from cache statistic, 7.2.2.3
db file scattered read wait events, 10.2.3, 10.3.2, 10.3.2
actions, 10.3.2.1, 10.3.3.1
db file sequential read wait events, 10.2.3, 10.3.2, 10.3.3
actions, 10.3.3.1
DB time
metric, 6.1.1
statistic, 5.1.1.2
DB_BLOCK_SIZE initialization parameter, 4.1.1, 8.2.1.2
DB_CACHE_ADVICE parameter, 7.2.3.1
DB_CACHE_SIZE initialization parameter, 7.2.3.2, 7.2.4
DB_DOMAIN initialization parameter, 4.1.1
DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 8.2.1.1, 8.2.1.2, 8.2.1.3, 10.3.2, 11.2.1
cost-based optimization, 11.3.2
DB_KEEP_CACHE_SIZE
initialization parameter, 7.2.8
DB_NAME initialization parameter, 4.1.1
DB_nK_CACHE_SIZE initialization parameter, 7.2.3.1
DB_RECYCLE_CACHE_SIZE
initialization parameter, 7.2.9
DB_WRITER_PROCESSES initialization parameter, 10.3.8.3.1
DBA_HIST views, 5.3.5
DBA_HIST_WR_CONTROL view
Automatic Workload Repository settings, 5.3.1.3
DBA_OBJECTS view, 7.2.7
DBA_OUTLINE_HINTS view
stored outline hints, 20.1.7
DBA_OUTLINES view
stored outlines, 20.1.7
DBIO_EXPECTED parameter, 6.2
DBMS_ADDM package
Automatic Database Diagnostic Monitor, 6.3
DBMS_ADVISOR package, 18.1
setting DBIO_EXPECTED, 6.2
setups for ADDM, 6.2, 6.2
DBMS_MONITOR package
End-to-End Application Tracing, 21.1
DBMS_OUTLN package
procedures for managing outlines, 20.1.4
DBMS_OUTLN_EDIT package
procedures for managing outlines, 20.1.4
DBMS_RESULT_CACHE, 7.6.1.3, 7.6.1.3.2
DBMS_SHARED_POOL package
managing the shared pool, 7.3.9
DBMS_SPM
EVOLVE_SQL_PLAN_BASELINE, 15.2.3.2
DBMS_SQLDIAG, 16.6.1.2
DBMS_SQLTUNE package
SQL Tuning Advisor, 17.3.4, 17.4
SQL Tuning Sets, 17.4
dbms_stats functions
create_extended_statistics, 13.5.2.1.1
drop_extended_stats, 13.5.2.1.3, 13.5.2.2.3
gather_table_stats, 13.5.2.2.1
show_extended_stats_name, 13.5.2.1.2
dbms_stats package
method_opt, 13.5.2.1.5
DBMS_STATS package, 13.3.1, 13.3.4, 18.1
managing query optimizer statistics, 11.5.2.3
manually determining sample size for gathering procedures, 13.3.1.1
DBMS_STATS_DISCOVER, 13.5.2.1
DBMS_WORKLOAD_REPOSITORY package
managing the Automatic Workload Repository, 5.3.1, 5.3.2, 5.3.3
DBMS_XPLAN package
displaying plan table output, 12.4
debugging designs, 2.6.4
default cache, 7.2.4.1
deploying applications, 2.7
DEPTH column
TKPROF_TABLE table, 21.4.5.3
design principles, 2.5
designs
debugging, 2.6.4
testing, 2.6.4
validating, 2.6.4
development environments, 2.5.6
diagnostic monitoring, 1.2.1, 6.1, 16.4.1
introduction, 6.1
DIAGNOSTIC_DEST initialization parameter, 21.4.1
direct path
read events, 10.3.4
read events actions, 10.3.4.2
read events causes, 10.3.4.1
wait events, 10.3.5
write events actions, 10.3.5.2
write events causes, 10.3.5.1
disabled constraints, 14.1.10
disks
monitoring operating system file activity, 10.1.2.2
statistics, 5.1.2.3
DISTRIBUTION column
PLAN_TABLE table, 12.10
domain indexes
and EXPLAIN PLAN, 12.9.6
using, 14.7
drop_extended_stats, 13.5.2.1.3, 13.5.2.2.3
dynamic statistics
process, 13.6.2.1
sampling levels, 13.6.2.1
when to use, 13.6.4

E

emergencies
performance, 3.2
Emergency Performance Method, 3.2.1
enabled constraints, 14.1.10
End-to-End Application Tracing, 21, 21.1
action and module names, 2.5.6, 21.1
creating a service, 21.1
DBMS_APPLICATION_INFO package, 21.1
DBMS_MONITOR package, 21.1
enforced constraints, 14.1.10
enqueue wait events, 10.2.3, 10.3.6
actions, 10.3.6.2
statistics, 10.1.3.4.5
equijoins, 16.5.3.1
error message documentation, Preface
estimating workloads, 2.6.2
benchmarking, 2.6.2.2
extrapolating, 2.6.2.1
examples
ALTER SESSION statement, 21.4.2
EXPLAIN PLAN output, 21.4.4.10
SQL trace facility output, 21.4.4.10
EXECUTE_TASK procedure, 18.2.5.5.1
execution plans
capturing SQL plan baselines, 15.2.1
evolving SQL plan baselines, 15.2.3
examples, 21.4.3.1
joins, 11.3.1
loading from a SQL Tuning Set, 15.2.1.2.1
loading from AWR snapshots, 15.2.1.2.1
loading from the cursor cache, 15.2.1.2.2
managing SQL plan baselines, 15.2
overview of, 11.4
plan stability, 20.1
preserving with plan stability, 20.1
selecting SQL plan baselines, 15.2.2
TKPROF, 21.4.3.1, 21.4.3.2
viewing with the utlxpls.sql script, 11.4.1
EXPLAIN PLAN statement
access paths, 11.2.6
and domain indexes, 12.9.6
and full partition-wise joins, 12.9.4
and partial partition-wise joins, 12.9.3
and partitioned objects, 12.9
basic steps, 11.4.1
examples of output, 21.4.4.10
execution order of steps in output, 11.4.1
invoking with the TKPROF program, 21.4.3.2
PLAN_TABLE table, 12.2
restrictions, 12.1.4
scripts for viewing output, 11.4.1
viewing the output, 11.4.1
EXPLAIN_MVIEW procedure, 18.3
expression
mixed-type, 16.5.3.2
Expression Statistics, 13.5.2.2
Extended Statistics, 13.5.2
extended syntax
for specifying tables in hints, 19.2.3
global hints, 19.2.3
EXTENT MANAGEMENT LOCAL
creating temporary tablespaces, 4.1.4.2
extrapolating workloads, 2.6.2.1

F

FAST_START_MTTR_TARGET
and tuning instance recovery, 10.5.3
Fast-Start checkpointing architecture, 10.5.2
Fast-Start Fault Recovery, 10.5, 10.5.2
features, new, Preface
FILESYSTEMIO_OPTIONS initialization parameter, 9.1.1.2
FIRST_ROWS(n) hint, 11.5.2.2
free buffer wait events, 10.2.3, 10.3.8
free lists, 10.3.1.2.1
FULL hint, 14.1.6
full outer joins, 11.3.7.4
full partition-wise joins, 12.9.4
full table scans, 10.3.4.2.2
function-based indexes, 2.5.3.2.3, 14.2

G

GATHER_ INDEX_STATS procedure
in DBMS_STATS package, 13.3.1
GATHER_DATABASE_STATS procedure
in DBMS_STATS package, 13.3.1
GATHER_DICTIONARY_STATS procedure
in DBMS_STATS package, 13.3.1, 13.3.4
GATHER_SCHEMA_STATS procedure
in DBMS_STATS package, 13.3.1, 13.3.4
gather_table_stats, 13.5.2.2.1
GATHER_TABLE_STATS procedure
in DBMS_STATS package, 13.3.1, 13.3.4
GETMISSES column
in V$ROWCACHE table, 7.3.4.1.2
GETS column
in V$ROWCACHE view, 7.3.4.1.2
global hints, 19.2.3
GV$BUFFER_POOL_STATISTICS view, 7.2.6

H

hard parsing, 2.5.5
hardware
components, 2.4.1.1
limitations of components, 2.3.3
sizing of components, 2.3.3
hash
distribution value, 12.10
hash clusters
scans of, 11.2.5
sorted, 14.9
hash joins, 11.3.4
cost-based optimization, 11.3.2
index join, 11.2.3.8.1
hash partitions, 12.9
examples of, 12.9.1
hashing, 14.9
high water mark, 11.2.1
hints
access paths, 16.5.4, 19.1.2.8
as used in outlines, 20.1.1.1
cannot override sample access path, 11.2.7
degree of parallelism, 19.1.2.7
FULL, 14.1.6
global, 19.2.3
global compared to local, 19.2.3
INDEX_FFS, 11.2.3.7
INDEX_JOIN, 11.2.3.8
indexspec syntax, 19.2.4
location syntax, 19.2.2
NO_INDEX, 14.1.6
optimization approach and goal, 19.1.2.1
overriding optimizer choice, 11.2.7
overriding OPTIMIZER_MODE, 11.5.2.2
parallel query option, 19.1.2.7
specifying a query block, 19.2.2
specifying indexes, 19.2.4
tablespec syntax, 19.2.3
using extended syntax, 19.2.3
histograms
frequency, 13.7.2.2
height-balanced, 13.7.2.1
HOLD_CURSOR clause, 7.3.2.6.2
hours of service, 2.4.2
HW enqueue
contention, 10.3.6.2.2

I

ID column
PLAN_TABLE table, 12.10
idle wait events, 10.3.9
SQL*Net message from client, 10.3.18.1
implementing business logic, 2.4.1.2.2
INDEX hint, 14.1.6
INDEX_COMBINE hint, 14.1.6
INDEX_FFS hint, 11.2.3.7, 11.2.3.7.1
INDEX_JOIN hint, 11.2.3.8
indexes
adding columns, 2.5.3.1
appending columns, 2.5.3.1
avoiding the use of, 14.1.6
bitmap, 2.5.3.2.2, 14.5
B-tree, 2.5.3.2.1
choosing columns for, 14.1.3
column order, 2.5.3.5
composite, 14.1.4
costs, 2.5.3.3
creating, 4.2.3
design, 2.5.3
domain, 14.7
dropping, 14.1.1
enforcing uniqueness, 14.1.9
ensuring the use of, 14.1.5
function-based, 2.5.3.2.3, 14.2
improving selectivity, 14.1.4
index joins, 11.2.3.8.1
joins, 11.2.3.8.1
low selectivity, 14.1.6
modifying values of, 14.1.3
non-unique, 14.1.9
partitioned, 2.5.3.2.4
placement on disk, 8.2.2
rebuilding, 14.1.7
re-creating, 14.1.7
reducing I/O, 2.5.3.5
reverse key, 2.5.3.2.5
scans of, 11.2.3
selectivity, 2.5.3.5
selectivity of, 14.1.3
sequences in, 2.5.3.4
serializing in, 2.5.3.4
specifying in hints, 19.2.4
statistics gathering, 13.5
indexspec
hint syntax, 19.2.4
initialization parameters
CONTROL_FILES, 4.1.1
DB_BLOCK_SIZE, 4.1.1
DB_DOMAIN, 4.1.1
DB_FILE_MULTIBLOCK_READ_COUNT, 11.3.2
DB_NAME, 4.1.1
DIAGNOSTIC_DEST, 21.4.1
OPEN_CURSORS, 4.1.1
OPTIMIZER_FEATURES_ENABLE, 11.2.3.7, 11.2.3.8
OPTIMIZER_MODE, 11.5.2.1, 19.1.2.1
PGA_AGGREGATE_TARGET, 4.2.3.1
PROCESSES, 4.1.1
SESSIONS, 4.1.1
STREAMS_POOL_SIZE, 4.1.1, 7.1.3
INLIST ITERATOR operation, 12.9.5
inlists, 12.9.5
instance caging, 9.4
instance configuration
initialization files, 4.1.1
performance considerations, 4.1
instance recovery
Fast-Start Fault Recovery, 10.5.2
performance tuning, 10.5
Internet scalability, 2.3.2
I/O
and SQL statements, 10.3.2.4
contention, 5.1.1.1, 10.1.2.2.1, 10.1.3.2, 10.3.2.2, 10.3.14
excessive I/O waits, 10.3.2.2
monitoring, 10.1.2.2
objects causing I/O waits, 10.3.2.5
reducing, 14.1.4

J

joins
antijoins, 11.3.2
cartesian, 11.3.6
execution plans and, 11.3.1
full outer, 11.3.7.4
hash, 11.3.4
index joins, 11.2.3.8.1
join order and execution plans, 11.4
nested loop, 11.3.3
nested loops and cost-based optimization, 11.3.2
order, 16.5.4, 16.5.4
outer, 11.3.7
partition-wise
examples of full, 12.9.4
examples of partial, 12.9.3
full, 12.9.4
semijoins, 11.3.2
sort merge, 11.3.5
sort-merge and cost-based optimization, 11.3.2, 11.3.2

K

KEEP buffer pool, 7.2.8
KEEP cache, 7.2.4.1

L

LARGE_POOL_SIZE initialization parameter, 7.3.5.1
latch contention
library cache latches, 10.2.1.2
shared pool latches, 10.2.1.2
latch free wait events, 10.2.3
actions, 10.3.10.1
latch wait events, 10.3.10
latches, 5.3.9.7.3
tuning, 1.1.2.3, 10.3.10.3
library cache
latch contention, 10.3.10.3
latch wait events, 10.3.10.1
lock, 10.3.13
memory allocation, 7.3.4.1.1
pin, 10.3.12
linear scalability, 2.3.3
locks and lock holders
finding, 10.3.6.1
log buffer
space wait events, 10.2.3, 10.3.14
tuning, 7.4.1
log file
parallel write wait events, 10.3.11
switch wait events, 10.3.15
sync wait events, 10.2.3, 10.3.16
log writer processes
tuning, 8.2.3.2
LOG_BUFFER initialization parameter, 7.4
setting, 7.4.2
LRU
aging policy, 7.2.4
latch contention, 10.3.10.3.4

M

managing the user interface, 2.4.1.2.1
materialized views
tuning, 18.3
max session memory statistic, 7.3.5.1.2
MAX_DISPATCHERS initialization parameter, 4.3.1.1
MAX_DUMP_FILE_SIZE initialization parameter
SQL Trace, 21.4.1
MAXOPENCURSORS clause, 7.3.2.6.2
memory
hardware component, 2.4.1.1.2
memory allocation
importance, 7.1
library cache, 7.3.4.1.1
shared SQL areas, 7.3.4.1.1
tuning, 7.1.7
method_opt, 13.5.2.1.5
metrics, 5.1
migrated rows, 10.2.4.3
mirroring
redo logs, 8.2.3.3
modeling
conceptual, 3.1.2
data, 2.5.2
workloads, 2.6.3
monitoring
diagnostic, 1.2.1, 16.4.1
MultiColumn Statistics, 13.5.2.1
multiple buffer pools, 7.2.4

N

NAMESPACE column
V$LIBRARYCACHE view, 7.3.3.2
nested loop joins, 11.3.3
cost-based optimization, 11.3.2
network
hardware component, 2.4.1.1.4
speed, 2.4.2
statistics, 5.1.2.4
network communication wait events, 10.3.18
db file scattered read wait events, 10.3.2
db file sequential read wait events, 10.3.2, 10.3.3
SQL*Net message from Dblink, 10.3.18.2
SQL*Net more data to client, 10.3.18.3
new features, Preface
NO_INDEX hint, 14.1.6
NOT IN subquery, 11.3.2

O

OBJECT_INSTANCE column
PLAN_TABLE table, 12.10
OBJECT_NAME column
PLAN_TABLE table, 12.10
OBJECT_NODE column
PLAN_TABLE table, 12.10
OBJECT_OWNER column
PLAN_TABLE table, 12.10
OBJECT_TYPE column
PLAN_TABLE table, 12.10
object-orientation, 2.5.7
OLAP_PAGE_POOL_SIZE initialization parameter, 7.5.2
OPEN_CURSORS initialization parameter, 4.1.1
operating system
data cache, 9.1.1
monitoring disk I/O, 10.1.2.2
statistics, 5.1.2
OPERATION column
PLAN_TABLE table, 12.10, 12.10
optimization
choosing the approach, 11.5.2.1
cost calculation, 11.1.1
cost-based and choosing an access path, 11.2.7
described, 11.1
hints, 11.2.3.7, 11.2.3.8, 11.5.2.2
manual, 11.5.2.2
operations performed, 11.1.1
optimizer
cost calculation, 11.1.1
estimator, 11.1.2.2
goals, 11.5.2
introduction, 1.1.3.1, 11.1
modes, 17.1
moving to from RBO, 20.2.1
operations, 11.1.1
parameters for setting mode, 11.5.2.1
plan stability, 20.1
query, 1.1.3.1
statistics, 13.1
throughput, 11.5.2
upgrading, 20.2.2
OPTIMIZER column
PLAN_TABLE, 12.10
OPTIMIZER_FEATURES_ENABLE initialization parameter, 11.2.3.7, 11.2.3.8
OPTIMIZER_MODE initialization parameter, 11.5.2.1, 11.5.2.1, 19.1.2.1
hints affecting, 11.5.2.2
OPTIONS column
PLAN_TABLE table, 12.10
Oracle CPU statistics, 10.1.2.1.3
Oracle Enterprise Manager
advisors, 1.2.1
Performance page, 1.2.1
Oracle Forms
control of parsing and private SQL areas, 7.3.2.6.5
Oracle Managed Files, 8.2.5
tuning, 8.2.5
Oracle Orion
calibration tool parameters, 8.4.4
command-line options, 8.4.4
Oracle performance improvement method, 3.1
steps, 3.1.1
order
joins, 16.5.4
OTHER column
PLAN_TABLE table, 12.10
OTHER_TAG column
PLAN_TABLE table, 12.10
outer joins, 11.3.7, 16.5.4.1
outlines
CREATE OUTLINE statement, 20.1.5
creating and using, 20.1.5
description, 20.1
execution plans and plan stability, 20.1
hints, 20.1.1.1
moving tables, 20.1.8
moving to the cost-based optimizer, 20.2.1
storage requirements, 20.1.2
using, 20.1.6
viewing data for, 20.1.7

P

package
DBMS_RESULT_CACHE, 7.6.1.3, 7.6.1.3.2
packages
DBMS_ADVISOR, 18.1
DBMS_STATS, 18.1
page table, 9.4.1.1.2
paging, 9.4.1.2
reducing, 7.1.6.1
PARALLEL clause
CREATE INDEX statement, 4.2.3
parameter
RESULT_CACHE_MAX_SIZE, 7.6.1.3.1
RESULT_CACHE_MODE, 7.6.3.1
PARENT_ID column
PLAN_TABLE table, 12.10
parsing
hard, 2.5.5
Oracle Forms, 7.3.2.6.5
Oracle precompilers, 7.3.2.6.2
reducing unnecessary calls, 7.3.2.6
soft, 2.5.5
PARTITION_ID column
PLAN_TABLE table, 12.10
PARTITION_START column
PLAN_TABLE table, 12.10
PARTITION_STOP column
PLAN_TABLE table, 12.10
partitioned indexes, 2.5.3.2.4
partitioned objects
and EXPLAIN PLAN statement, 12.9
partitioning
distribution value, 12.10
examples of, 12.9.1
examples of composite, 12.9.2
hash, 12.9
range, 12.9
start and stop columns, 12.9.1
partition-wise joins
full, 12.9.4
full, and EXPLAIN PLAN output, 12.9.4
partial, and EXPLAIN PLAN output, 12.9.3
PCTFREE parameter, 4.2, 10.2.4.3
PCTUSED parameter, 10.2.4.3, 10.2.4.3
peeking
bind variables, 11.1.3
performance
emergencies, 3.2
improvement method, 3.1
improvement method steps, 3.1.1
mainframe, 9.2.3
monitoring memory on Windows, 9.4.1.1.1
tools for diagnosing and tuning, 1.2
UNIX-based systems, 9.2.1
viewing execution plans, 11.4.1
Windows, 9.2.2
performance problems
transient, 5.3.8
performance tuning
Fast-Start Fault Recovery, 10.5
instance recovery, 10.5
FAST_START_MTTR_TARGET, 10.5.2
setting FAST_START_MTTR_TARGET, 10.5.3
using V$INSTANCE_RECOVERY, 10.5.2.3
PGA_AGGREGATE_TARGET initialization parameter, 4.1.1, 4.2.3.1, 7.5.1, 9.1.2.2
physical reads from cache statistic, 7.2.2.3
plan stability, 20.1
limitations of, 20.1.1
preserving execution plans, 20.1
procedures for the cost-based optimizer, 20.2
use of hints, 20.1.1
PLAN_TABLE table
BYTES column, 12.10
CARDINALITY column, 12.10
COST column, 12.10
creating, 12.2, 12.2
displaying, 12.4
DISTRIBUTION column, 12.10
ID column, 12.10
OBJECT_INSTANCE column, 12.10
OBJECT_NAME column, 12.10
OBJECT_NODE column, 12.10
OBJECT_OWNER column, 12.10
OBJECT_TYPE column, 12.10
OPERATION column, 12.10
OPTIMIZER column, 12.10
OPTIONS column, 12.10
OTHER column, 12.10
OTHER_TAG column, 12.10
PARENT_ID column, 12.10
PARTITION_ID column, 12.10
PARTITION_START column, 12.10
PARTITION_STOP column, 12.10
POSITION column, 12.10
REMARKS column, 12.10
SEARCH_COLUMNS column, 12.10
STATEMENT_ID column, 12.10
TIMESTAMP column, 12.10
PL/SQL procedures
EXPLAIN_MVIEW, 18.3
TUNE_MVIEW, 18.3
POSITION column
PLAN_TABLE table, 12.10
precompilers
control of parsing and private SQL areas, 7.3.2.6.2
PRIMARY KEY constraint, 14.1.9
PRIVATE_SGA variable, 7.3.5.1.3
privileges
SQL Access Advisor, 18.2.2
proactive monitoring, 1.1.2.4.1
processes
scheduling, 9.4.1.4.1
PROCESSES initialization parameter, 4.1.1
program global area (PGA)
direct path read, 10.3.4
direct path write, 10.3.5
shared servers, 7.3.5.1.1
programming languages, 2.5.6

Q

queries
avoiding the use of indexes, 14.1.6
data, 2.4.2
ensuring the use of indexes, 14.1.5
query optimizer, 1.1.3.1
See optimizer

R

range
distribution value, 12.10
examples of partitions, 12.9.1
partitions, 12.9
rdbms ipc reply wait events, 10.3.17
read consistency, 10.2.4.2
read wait events
direct path, 10.3.4
scattered, 10.3.2
REBUILD clause, 14.1.7
recursive calls, 21.4.4.5
RECYCLE cache, 7.2.4.1
REDO BUFFER ALLOCATION RETRIES statistic, 7.4.2
redo logs, 4.1.3
buffer size, 10.3.14
mirroring, 8.2.3.3
placement on disk, 8.2.3.2
sizing, 4.1.3
space requests, 10.2.4.1
reducing
contention with dispatchers, 4.3.1.1
data dictionary cache misses, 7.3.4.1.2
paging and swapping, 7.1.6.1
unnecessary parse calls, 7.3.2.6
RELEASE_CURSOR clause, 7.3.2.6.2
REMARKS column
PLAN_TABLE table, 12.10
resources
allocation, 2.4.1.2.3, 2.5.6
bottlenecks, 10.3.18.1.2
wait events, 10.3.3
response time, 2.4.2
cost-based approach, 11.5.2.1
result cache, 7.6.1
reverse key indexes, 2.5.3.2.5
rollout strategies
big bang approach, 2.7.1
trickle approach, 2.7.1
round-robin
distribution value, 12.10
row cache objects, 10.3.10.3.6
row sources, 11.4.2
rowids
table access by, 11.2.2
rows
row sources, 11.4.2
rowids used to locate, 11.2.2

S

SAMPLE BLOCK clause, 11.2.6
access path and hints, 11.2.7
SAMPLE clause, 11.2.6
access path and hints cannot override, 11.2.7
sample table scans, 11.2.6
hints cannot override, 11.2.7
sar UNIX command, 9.4.1.1.1
scalability, 2.3.1
factors preventing, 2.3.3, 2.3.3
Internet, 2.3.2
linear, 2.3.3
scans
index, 11.2.3
index joins, 11.2.3.8.1
index of type bitmap, 11.2.3.9
sample table, 11.2.6
sample table and hints cannot override, 11.2.7
scattered read wait events, 10.3.2
actions, 10.3.2.1
SEARCH_COLUMNS column
PLAN_TABLE table, 12.10
segment-level statistics, 10.1.3.5
SELECT statement
SAMPLE clause, 11.2.6
selectivity
creating indexes, 14.1.3
improving for an index, 14.1.4
indexes, 14.1.6
ordering columns in an index, 2.5.3.5
semijoins, 11.3.2
sequential read wait events
actions, 10.3.3.1
service hours, 2.4.2
session memory statistic, 7.3.5.1.2
SESSIONS initialization parameter, 4.1.1
SGA size, 7.4.1
SGA_TARGET initialization parameter, 4.1.1
and Automatic Shared Memory Management, 7.1.3
automatic memory management, 7.1.3
shared pool contention, 10.3.10.3
shared server
performance issues, 4.3
reducing contention, 4.3
tuning, 4.3
tuning memory, 7.3.5.1
shared SQL areas
memory allocation, 7.3.4.1.1
SHARED_POOL_RESERVED_SIZE initialization parameter, 7.3.8.2
SHARED_POOL_SIZE initialization parameter, 7.3.4.1.2, 7.3.4.2, 7.3.8.4
allocating library cache, 7.3.4.1.1
tuning the shared pool, 7.3.5.1.4
SHOW SGA statement, 7.1.6.2
show_extended_stats_name, 13.5.2.1.2
sizing redo logs, 4.1.3
snapshots
about, 5.2.1
soft parsing, 2.5.5
software
components, 2.4.1.2
sort areas
tuning, 7.5
sort merge joins, 11.3.5
cost-based optimization, 11.3.2
SQL Access Advisor, 18.1, 18.1, 18.2.3.2
constants, 18.2.8
creating a task, 18.1.1
EXECUTE_TASK procedure, 18.2.5.5.1
generating the recommendations, 18.1.1
privileges, 18.2.2
recommendation process, 18.2.5.7
steps in using, 18.1.1
SQL management base
about, 15.6
disk space usage, 15.6.1
purging policy, 15.6.2
SQL plan baseline
automatic plan capture, 15.2.1.1
capturing
automatic, 15.2.1.1
manual, 15.2.1.2
evolving manually, 15.2.3.1
evolving with DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE, 15.2.3.2
loading plans from a SQL Tuning Set, 15.2.1.2.1
loading plans from AWR snapshots, 15.2.1.2.1
loading plans from the cursor cache, 15.2.1.2.2
manual plan loading, 15.2.1.2
SQL Tuning Advisor, 15.3
SQL plan baseline, fixed, 15.4
SQL plan baselines
about, 15.2.1
capturing, 15.2.1
displaying, 15.5
enabling, 15.2.2
evolving, 15.2.3
importing and exporting, 15.7
managing, 15.2
plan history, 15.2.1
selecting, 15.2.2
statement log, 15.2.1
SQL statements
avoiding the use of indexes, 14.1.6
ensuring the use of indexes, 14.1.5
execution plans of, 11.4
modifying indexed data, 14.1.3
waiting for I/O, 10.3.2.4
SQL Test Case Builder, 16.6
SQL trace facility, 21.3.1, 21.4.3
example of output, 21.4.4.10
output, 21.4.4.1
statement truncation, 21.4.4.7
steps to follow, 21.4
trace files, 21.4.1
SQL Tuning Advisor, 1.2.1, 16.4.2
administering with APIs, 17.3.4, 17.4
input sources, 17.3.1
overview, 17.2
tuning options, 17.3.2
SQL Tuning Sets
description, 16.4.3, 17.3, 17.3.1, 17.3.4
managing with APIs, 17.4, 17.4
SQL*Net
message from client idle events, 10.3.18.1
message from dblink wait events, 10.3.18.2
more data to client wait events, 10.3.18.3
SQL, recursive, 13.6
SQL_STATEMENT column
TKPROF_TABLE, 21.4.5.3
SQLAccess Advisor, 1.2.1, 16.4.4
SQLTUNE_CATEGORY initialization parameter
determining the SQL Profile category, 17.5.3
ST enqueue
contention, 10.3.6.2.1
start columns
in partitioning and EXPLAIN PLAN statement, 12.9.1
STATEMENT_ID column
PLAN_TABLE table, 12.10
statistics
and STATISTICS_LEVEL initialization parameter, 1.2
baselines, 5.1
consistent gets from cache, 7.2.2.3
databases, 5.1.1
db block gets from cache, 7.2.2.3
displaying in views, 13.7.1
exporting and importing, 13.5.4
gathering, 5.1
gathering stale, 13.3.1.5
gathering using sampling, 13.3.1.1
gathering with DBMS_STATS package, 13.3.1, 13.3.4
gathering with DBMS_STATS procedures, 13.3.1
limitations on restoring previous versions, 13.5.3
locking, 13.5.6
manually gathering, 13.3
max session memory, 7.3.5.1.2
missing, 13.5.8
operating systems, 5.1.2
CPU statistics, 5.1.2.1
disk statistics, 5.1.2.3
network statistics, 5.1.2.4
virtual memory statistics, 5.1.2.2
optimizer, 13.1
optimizer mode, 11.5.2.1
physical reads from cache, 7.2.2.3
restoring previous versions, 13.5.3
segment-level, 10.1.3.5
session memory, 7.3.5.1.2
shared server processes, 4.3.2
stale, 13.3.1.5
system, 13.4
time model, 5.1.1.2
user-defined, 13.3.1.6
when to gather, 13.3.3
STATISTICS_LEVEL initialization parameter, 5.1.3, 10.1.3.1
and Automatic Workload Repository, 5.2
enabling automatic database diagnostic monitoring, 6.2
settings for statistic gathering, 1.2
stop columns
in partitioning and EXPLAIN PLAN statement, 12.9.1
stored outlines
creating and using, 20.1.5
execution plans and plan stability, 20.1
hints, 20.1.1.1
moving tables, 20.1.8
storage requirements, 20.1.2
using, 20.1.6
viewing data for, 20.1.7
STREAMS_POOL_SIZE initialization parameter, 4.1.1, 7.1.3
striping
manual, 8.2.2
subqueries
NOT IN, 11.3.2
unnesting, 16.5.4.1.3
swapping, 9.4.1.1.1, 9.4.1.2
reducing, 7.1.6.1
switching processes, 9.4.1.4.1
system architecture, 2.4
configuration, 2.4.2
hardware components, 2.4.1.1
CPUs, 2.4.1.1.1
I/O subsystems, 2.4.1.1.3
memory, 2.4.1.1.2
networks, 2.4.1.1.4
software components, 2.4.1.2
data and transactions, 2.4.1.2.4
implementing business logic, 2.4.1.2.2
managing the user interface, 2.4.1.2.1
user requests and resource allocation, 2.4.1.2.3
System Global Area tuning, 7.1.6.2

T

tables
creating, 4.2
design, 2.5.3
full scans, 10.3.4.2.2
placement on disk, 8.2.2
setting storage options, 4.2
tablespaces, 4.1.4
creating, 4.1.4, 4.1.4.2
temporary, 4.1.4, 4.1.4.2
tablespec
hint syntax, 19.2.3
templates
SQL Access Advisor, 18.2.3.2
temporary tablespaces, 4.1.4
creating, 4.1.4.2
testing designs, 2.6.4
thrashing, 9.4.1.2
thresholds
adaptive, 5.2.3
throughput
optimizer goal, 11.5.2
time model statistics, 5.1.1.2
TIMED_STATISTICS initialization parameter
SQL Trace, 21.4.1
TIMESTAMP column
PLAN_TABLE table, 12.10
TKPROF program, 21.3.2, 21.4.3
editing the output SQL script, 21.4.5.2
example of output, 21.4.4.10
generating the output SQL script, 21.4.5.1
row source operations, 21.4.4.2
syntax, 21.4.3.2
using the EXPLAIN PLAN statement, 21.4.3.2
wait event information, 21.4.4.3
TKPROF_TABLE, 21.4.5.3, 21.4.5.3
TM enqueue contention, 10.3.6.2.3
tools for performance tuning, 1.2
Top Java
Active Session History report, 5.3.9.5
top PL/SQL
Active Session History report, 5.3.9.4
Top Sessions
Active Session History report, 5.3.9.6
Top SQL
Active Session History report, 5.3.9.3
TRACEFILE_IDENTIFIER initialization parameter
identifying trace files, 21.4.1
tracing
consolidating with trcsess, 21.2
identifying files, 21.4.1
transactions and data, 2.4.1.2.4
trcsess utility, 21.2
trickle rollout strategy, 2.7.1
TUNE_MVIEW procedure, 18.3
tuning
and bottleneck elimination, 1.1.2.4.2
and proactive monitoring, 1.1.2.4.1
latches, 1.1.2.3, 10.3.10.3
logical structure, 14.1.1
memory allocation, 7.1.7
resource contention, 10
shared server, 4.3
sorts, 7.5
SQL Tuning Advisor, 17.2
System Global Area (SGA), 7.1.6.2
TX enqueue contention, 10.3.6.2.4
type conversion, 16.5.3.2

U

undo management, automatic mode, 4.1.2
UNDO TABLESPACE clause, 4.1.2
UNDO_MANAGEMENT initialization parameter, 4.1.1, 4.1.2
UNDO_TABLESPACE initialization parameter, 4.1.1
UNIQUE constraint, 14.1.9
uniqueness, 14.1.9
UNIX system performance, 9.2.1
untransformed column values, 16.5.3.2
upgrade
to the cost-based optimizer, 20.2.2
USE_STORED_OUTLINES parameter, 20.1.6, 20.1.6
user global area (UGA)
shared servers, 4.3, 7.3.5.1
V$SESSTAT, 7.3.5.1.2
user requests, 2.4.1.2.3
USER_DUMP_DEST initialization parameter
SQL Trace, 21.4.1
USER_ID column, TKPROF_TABLE, 21.4.5.3
USER_OUTLINE_HINTS view
stored outline hints, 20.1.7
USER_OUTLINES view
stored outlines, 20.1.7
user_stat_extensions, 13.5.2.1.4, 13.5.2.2.2
user-defined bind variables, 11.1.3
users
interaction method, 2.4.2
interfaces, 2.5.6
location, 2.4.2
network speed, 2.4.2
number of, 2.4.2
requests, 2.5.6
response time, 2.4.2
Using SQL Plan Management, 15
UTLCHN1.SQL script, 10.2.4.3
UTLXPLP.SQL script
displaying plan table output, 12.4
for viewing EXPLAIN PLANs, 11.4.1
UTLXPLS.SQL script
displaying plan table output, 12.4
for viewing EXPLAIN PLANs, 11.4.1
used for displaying EXPLAIN PLANs, 11.4.1

V

V$ACTIVE_SESSION_HISTORY view, 5.1.1.3, 10.1.3.3
V$ADVISOR_PROGRESS view, 17.3.4.5, 17.6
V$BH view, 7.2.7
V$BUFFER_POOL_STATISTICS view, 7.2.6, 7.2.6
V$DB_CACHE_ADVICE view, 7.2.2.1, 7.2.2.3, 7.2.3, 7.2.3.1, 7.2.3.2, 7.2.5
V$EVENT_HISTOGRAM view, 10.1.3.3
V$FILE_HISTOGRAM view, 10.1.3.3
V$JAVA_LIBRARY_CACHE_MEMORY view, 7.3.3.3.3
V$JAVA_POOL_ADVICE view, 7.3.3.3.3
V$LIBRARY_CACHE_MEMORY view, 7.3.3.3.2
V$LIBRARYCACHE view
NAMESPACE column, 7.3.3.2
V$OSSTAT view, 5.1.2.1
V$PGASTAT view, 7.5.1.2.1
V$PROCESS view, 7.5.1.2.2
V$PROCESS_MEMORY view, 7.5.1.2.3
V$QUEUE view, 4.3.2
V$ROWCACHE view
GETMISSES column, 7.3.4.1.2
GETS column, 7.3.4.1.2
performance statistics, 7.3.3.4
V$RSRC_CONSUMER_GROUP view, 10.1.2.1.3
V$SESS_TIME_MODEL view, 5.1.1.2, 10.1.3.3
V$SESSION view, 10.1.3.3, 10.1.3.4, 10.3
V$SESSION_EVENT view, 10.1.3.3, 10.3
V$SESSION_WAIT view, 10.1.3.3, 10.3
V$SESSION_WAIT_CLASS view, 10.1.3.3
V$SESSION_WAIT_HISTORY view, 10.1.3.3, 10.1.3.3, 10.3
V$SESSTAT view, 7.3.5.1.2, 10.1.2.1.3
V$SHARED_POOL_ADVICE view, 7.3.3.3.1
V$SHARED_POOL_RESERVED view, 7.3.8.4
V$SQL_PLAN view
using to display execution plan, 12.1.3.1
V$SQL_PLAN_STATISTICS view
using to display execution plan statistics, 12.1.3.1
V$SQL_PLAN_STATISTICS_ALL view
using to display execution plan information, 12.1.3.1
V$SQL_WORKAREA view, 7.5.1.2.6
V$SQL_WORKAREA_ACTIVE view, 7.5.1.2.5
V$SQL_WORKAREA_HISTOGRAM view, 7.5.1.2.4
V$SYS_TIME_MODEL view, 5.1.1.2, 5.1.2.1, 10.1.3.3
V$SYSMETRIC_HISTORY view, 5.1.2.1
V$SYSSTAT view
redo buffer allocation, 7.4.2
using, 7.2.2.3
V$SYSTEM_EVENT view, 10.1.3.3, 10.3
V$SYSTEM_WAIT_CLASS view, 10.1.3.3
V$TEMP_HISTOGRAM view, 10.1.3.3
V$UNDOSTAT view, 4.1.2
V$WAITSTAT view, 10.1.3.4
validating designs, 2.6.4
views, 2.5.4
DBA_HIST, 5.3.5
statistics, 13.7.1
virtual memory statistics, 5.1.2.2
vmstat UNIX command, 9.4.1.1.1

W

wait events, 5.1.1.1
buffer busy waits, 10.3.1
classes, 5.1.1.1, 10.1.3.2
contention wait events, 10.3.10
direct path, 10.3.5
enqueue, 10.3.6
free buffer waits, 10.3.8
idle wait events, 10.3.9
latch, 10.3.10
library cache latch, 10.3.10.1
log buffer space, 10.3.14
log file parallel write, 10.3.11
log file switch, 10.3.15
log file sync, 10.3.16
network communication wait events, 10.3.18
rdbms ipc reply, 10.3.17
resource wait events, 10.3.3
Windows performance, 9.2.2
workloads, 2.6.2, 2.6.2.1, 2.6.2.2, 2.6.3, 2.6.4