Skip Headers

Oracle® Database Performance Tuning Guide
10g Release 1 (10.1)

Part Number B10752-01
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
Feedback

Go to previous page
Previous
View PDF

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, 14-27
defined, 14-17
execution plans, 14-15
hash scans, 14-28
index scans, 14-21
Active Session History, 5-4
addmrpt.sql
Automatic Database Diagnostic Monitor, 6-8
advisors
accessing with Oracle Enterprise Manager, 1-7
ALL_OUTLINE_HINTS view
stored outline hints, 18-9
ALL_OUTLINES view
stored outlines, 18-9
ALL_ROWS hint, 14-5, 17-13
ALL_ROWS optimizer mode parameter, 14-4
allocation
of memory, 7-2
ALTER INDEX statement, 16-7
ALTER SESSION statement
examples, 20-14
SET SESSION_CACHED_CURSORS clause, 7-42
ANALYZE statement, 15-7
antijoins, 14-30
APPEND hint, 17-41
applications
deploying, 2-26
design principles, 2-13
development trends, 2-21
implementing, 2-19
array interface, 11-13
Automatic Database Diagnostic Monitor, i-xxviii, 6-2
accessing with Oracle Enterprise Manager, 6-7
actions and rationales of recommendations, 6-5
addmrpt.sql report, 6-8
analysis results example, 6-5
and DB time, 6-3
DBIO_EXPECTED, 6-6
DBMS_ADVISOR package, 6-10
example report, 6-5
findings, 6-4
overview, 6-3
results, 6-4
running with APIs, 6-10
setups, 6-6
STATISTICS_LEVEL parameter, 6-6
types of problems considered, 6-3
types of recommendations, 6-4
automatic database diagnostic monitoring, 1-7, 12-6
automatic segment-space management, 4-6, 8-12, 10-26
Automatic Shared Memory Management, 7-3
automatic SQL tuning, 1-7, 12-7
analysis, 13-2
features, 13-1
overview, 13-2
Automatic Tuning Optimizer, 13-2
automatic undo management, 4-4
mode, 4-4
Automatic Workload Repository, i-xxviii, 1-7
accessing with Oracle Enterprise Manager, 5-12
data gathering, 5-2
DBMS_WORKLOAD_REPOSITORY package, 5-13
default settings, 5-11
factors affecting space usage, 5-11
managing with APIs, 5-13
minimizing space usage, 5-11
overview, 5-10
recommendations for retention period, 5-12
reports, 5-17
retention period, 5-11
settings in DBA_HIST_WR_CONTROL view, 5-15
space usage, 5-11
statistics collected, 5-10
turning off automatic snapshot collection, 5-12
unusual percentages in reports, 5-17
views for accessing data, 5-16
awrrpt.sql
Automatic Workload Repository report, 5-17

B

baselines, 1-3
performance, 5-2
preserved snapshot sets, 5-12
benchmarking workloads, 2-23
big bang rollout strategy, 2-26
bind variables, 7-24
peeking, 14-12
bitmap indexes, 2-15
inlist iterator, 19-22
on joins, 16-12
when to use, 16-12
block cleanout, 10-19
block size
choosing, 8-11
optimal, 8-11
bottlenecks
elimination, 1-5
fixing, 3-2
identifying, 3-2
memory, 7-2
resource, 10-24
broadcast
distribution value, 19-27
B-tree indexes, 2-15
buffer busy wait events, 10-17, 10-25
actions, 10-26
buffer cache
contention, 10-27, 10-29, 10-42
hit ratio, 7-12
reducing buffers, 7-14, 7-36
buffer pools
default cache, 7-16
hit ratio, 7-17
KEEP, 7-19
KEEP cache, 7-16
multiple, 7-15
RECYCLE cache, 7-15
business logic, 2-9, 2-19
BYTES column
PLAN_TABLE table, 19-24

C

CACHE hint, 17-42
caching tables
automatic caching of small tables, 17-43
CARDINALITY column
PLAN_TABLE table, 19-24
cartesian joins, 14-36
chained rows, 10-20
CHOOSE hint, 14-5
CHOOSE optimizer mode parameter, 14-4
classes
wait events, 5-3, 10-8
client/server applications, 9-11
CLUSTER hint, 17-17
clusters, 16-14
hash and scans of, 14-28
scans of, 14-27
sorted hash, 16-15
column order
indexes, 2-17
columns
to index, 16-4
COMPATIBLE initialization parameter, 4-3
components
hardware, 2-7
software, 2-8
composite indexes, 16-5
composite partitioning
examples of, 19-16
conceptual modeling, 3-5
connection manager, 11-14
consistency
read, 10-18
consistent gets from cache statistic, 7-11
consistent mode
TKPROF, 20-21
constraints, 16-9
contention
library cache latch, 10-42
memory, 7-2, 10-1
shared pool, 10-42
tuning, 10-1
wait events, 10-40
context switches, 9-11
CONTROL_FILES initialization parameter, 4-2
cost
optimizer calculation, 14-9
COST column
PLAN_TABLE table, 19-24
cost-based optimizations, 14-9
procedures for plan stability, 18-12
upgrading to, 18-14
cpu statistics, 10-4
CPU_COSTING hint, 14-5
CPUs, 2-7
statistics, 5-6
utilization, 9-10
CREATE INDEX statement
PARALLEL clause, 4-10
CREATE OUTLINE statement, 18-5
CREATE_STORED_OUTLINES initialization parameter, 18-5, 18-6
CREATE_STORED_OUTLINES parameter, 18-5
current mode
TKPROF, 20-21
CURSOR_NUM column
TKPROF_TABLE table, 20-28
CURSOR_SHARING initialization parameter, 7-26, 7-46, 14-8
CURSOR_SHARING_EXACT hint, 17-46
CURSOR_SPACE_FOR_TIME initialization parameter
setting, 7-40
cursors
accessing, 7-27
sharing, 7-27

D

data
and transactions, 2-9
cache, 9-2
gathering, 5-2
modeling, 2-14
queries, 2-12
searches, 2-12
data dictionary, 7-36
statistics in, 15-19
views used in optimization, 15-19
database monitoring, 1-7, 12-6
diagnostic, 6-2
Database Resource Manager, 9-5, 9-9, 10-5
databases
buffers, 7-14, 7-36
diagnosing and monitoring, 6-2
size, 2-13
statistics, 5-3
DATE_OF_INSERT column
TKPROF_TABLE table, 20-28
db block gets from cache statistic, 7-12
db file scattered read wait events, 10-17, 10-27
actions, 10-27, 10-30
db file sequential read wait events, 10-17, 10-27, 10-29
actions, 10-30
DB time
metric, 6-3
statistic, 5-4
DB_BLOCK_SIZE initialization parameter, 4-3, 8-4
DB_CACHE_ADVICE parameter, 7-13
DB_CACHE_SIZE initialization parameter, 7-14, 7-15
DB_DOMAIN initialization parameter, 4-2
DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 8-3, 8-4, 8-5, 10-27, 14-8, 14-19
cost-based optimization, 14-31
DB_KEEP_CACHE_SIZE
initialization parameter, 7-19
DB_NAME initialization parameter, 4-2
DB_nK_CACHE_SIZE initialization parameter, 7-14
DB_RECYCLE_CACHE_SIZE
initialization parameter, 7-20
DB_WRITER_PROCESSES initialization parameter, 10-38
DBA_HIST views, 5-16
DBA_HIST_WR_CONTROL view
Automatic Workload Repository settings, 5-15
DBA_OBJECTS view, 7-18
DBA_OUTLINE_HINTS view
stored outline hints, 18-9
DBA_OUTLINES view
stored outlines, 18-9
DBIO_EXPECTED parameter, 6-6
DBMS_ADVISOR package
Automatic Database Diagnostic Monitor, 6-8, 6-10
setting DBIO_EXPECTED, 6-7
setups for ADDM, 6-6, 6-7
DBMS_MONITOR package
End to End Application Tracing, 20-3
DBMS_OUTLN package
procedures for managing outlines, 18-4
DBMS_OUTLN_EDIT package
procedures for managing outlines, 18-4
DBMS_SHARED_POOL package
managing the shared pool, 7-44
DBMS_SQLTUNE package
SQL Profiles, 13-10
SQL Tuning Advisor, 13-8
SQL Tuning Sets, 13-13
DBMS_STATS package, 15-7
managing query optimizer statistics, 14-6, 15-3
manually determining sample size for gathering procedures, 15-9
DBMS_WORKLOAD_REPOSITORY package
managing the Automatic Workload Repository, 5-13
DBMS_XPLAN package
displaying plan table output, 19-7
debugging designs, 2-24
default cache, 7-16
deploying applications, 2-26
DEPTH column
TKPROF_TABLE table, 20-28
design principles, 2-13
designs
debugging, 2-24
testing, 2-24
validating, 2-24
development environments, 2-19
diagnostic monitoring, 1-7, 6-2, 12-6
introduction, 6-2
direct path
read events, 10-31
read events actions, 10-32
read events causes, 10-32
wait events, 10-33
write events actions, 10-33
write events causes, 10-33
direct-path INSERT, 17-41
disabled constraints, 16-9
disks
monitoring operating system file activity, 10-5
statistics, 5-7
DISPATCHERS initialization parameter, 11-3
distribution
hints for, 17-38
DISTRIBUTION column
PLAN_TABLE table, 19-26
domain indexes
and EXPLAIN PLAN, 19-22
using, 16-13
DRIVING_SITE hint, 17-47
dynamic sampling
improving performance, 15-17
level settings, 15-17, 15-18
process, 15-16
purpose, 15-16
when to use, 15-17
DYNAMIC_SAMPLING hint, 17-47

E

emergencies
performance, 3-8
Emergency Performance Method, 3-9
enabled constraints, 16-9
End to End Application Tracing, 20-1, 20-2
accessing with Oracle Enterprise Manager, 20-3
action and module names, 2-21, 20-2
creating a service, 20-2
DBMS_APPLICATION_INFO package, 20-2
DBMS_MONITOR package, 20-3
enforced constraints, 16-9
enqueue wait events, 10-17, 10-34
actions, 10-35
statistics, 10-11
equijoins, 12-9
error message documentation, i-xxi
estimating workloads, 2-23
benchmarking, 2-23
extrapolating, 2-23
examples
ALTER SESSION statement, 20-14
EXPLAIN PLAN output, 20-25
SQL trace facility output, 20-25
execution plans
examples, 20-16
joins, 14-30
overview of, 14-15
plan stability, 18-2
preserving with plan stability, 18-2
TKPROF, 20-16, 20-18
viewing with the utlxpls.sql script, 14-15
EXPLAIN PLAN statement
access paths, 14-28
and domain indexes, 19-22
and full partition-wise joins, 19-20
and partial partition-wise joins, 19-18
and partitioned objects, 19-14
basic steps, 14-15
examples of output, 20-25
execution order of steps in output, 14-15
invoking with the TKPROF program, 20-18
PLAN_TABLE table, 19-5
restrictions, 19-5
scripts for viewing output, 14-15
viewing the output, 14-15
Export utility
statistics on system-generated columns names, 15-15
expression
mixed-type, 12-10
extended syntax
for specifying tables in hints, 17-7
global hints, 17-7
EXTENT MANAGEMENT LOCAL
creating temporary tablespaces, 4-7
extrapolating workloads, 2-23

F

FACT hint, 17-29
features, new, i-xxvii
FILESYSTEMIO_OPTIONS initialization parameter, 9-3
FIRST_ROWS optimizer mode parameter, 14-4
FIRST_ROWS(n) hint, 14-5, 17-14
FIRST_ROWS_n
optimizer mode parameter, 14-4
free buffer wait events, 10-17, 10-37
free lists, 10-26
FULL hint, 16-7, 17-16
full outer joins, 14-39
full partition-wise joins, 19-20
full table scans, 10-32
function-based indexes, 2-15, 16-10

G

GATHER_ INDEX_STATS procedure
in DBMS_STATS package, 15-8
GATHER_DATABASE_STATS procedure
in DBMS_STATS package, 15-8
GATHER_DATABASE_STATS_JOB_PROC procedure
and GATHER_STATS_JOB in Maintenance Window, 15-3
automatically gathering optimizer statistics, 15-3
GATHER_DICTIONARY_STATS procedure
in DBMS_STATS package, 15-8
GATHER_SCHEMA_STATS procedure
in DBMS_STATS package, 15-8
GATHER_STATS_JOB
automatically gathering optimizer statistics, 15-3
GATHER_TABLE_STATS procedure
in DBMS_STATS package, 15-8
GETMISSES column
in V$ROWCACHE table, 7-36
GETS column
in V$ROWCACHE view, 7-36
global hints, 17-7
GV$BUFFER_POOL_STATISTICS view, 7-17

H

hard parsing, 2-18
hardware
components, 2-7
limitations of components, 2-6
sizing of components, 2-6
hash
distribution value, 19-27
hash clusters
scans of, 14-28
sorted, 16-15
HASH hint, 17-17
hash joins, 14-34
cost-based optimization, 14-31
index join, 14-27
hash partitions, 19-14
examples of, 19-14
hashing, 16-15
high water mark, 14-18
hints
access paths, 12-17, 17-15, 17-23
ALL_ROWS, 17-13
APPEND, 17-41
as used in outlines, 18-3
CACHE, 17-42
cannot override sample access path, 14-29
CLUSTER, 17-17
CURSOR_SHARING_EXACT, 17-46
degree of parallelism, 17-36
DRIVING_SITE, 17-47
DYNAMIC_SAMPLING, 17-47
FACT, 17-29
FIRST_ROWS(n), 17-14
FULL, 16-7, 17-16
global, 17-7
global compared to local, 17-7
HASH, 17-17
how to use, 17-2
INDEX, 17-17
INDEX_ASC, 17-19
INDEX_COMBINE, 17-19
INDEX_DESC, 17-20
INDEX_FFS, 14-26
INDEX_JOIN, 14-27
INDEX_SS, 17-22
INDEX_SS_ASC, 17-22
INDEX_SS_DESC, 17-23
indexspec syntax, 17-9
join operations, 17-32
LEADING, 17-31
location syntax, 17-6
MERGE, 17-27
NO_EXPAND, 17-25
NO_FACT, 17-29
NO_INDEX, 16-7, 17-18
NO_INDEX_FFS, 17-21
NO_INDEX_SS, 17-23
NO_MERGE, 17-27
NO_PARALLEL, 17-37
NO_PARALLEL_INDEX, 17-40
NO_PUSH_PRED, 17-44
NO_PUSH_SUBQ, 17-45
NO_QUERY_TRANSFORMATION, 17-24
NO_REWRITE, 17-26
NO_UNNEST, 17-30
NO_USE_HASH, 17-36
NO_USE_MERGE, 17-35
NO_USE_NL, 17-33
NOAPPEND, 17-42
NOCACHE, 17-43
NOPARALLEL, 17-37
NOPARALLEL_INDEX, 17-40
NOREWRITE, 17-26
optimization approach and goal, 17-12
optimizer, 17-2
ORDERED, 17-32
ORDERED hint, 14-31
overriding optimizer choice, 14-29
overriding OPTIMIZER_MODE, 14-5
PARALLEL, 17-37
parallel query option, 17-36
PQ_DISTRIBUTE, 17-38
PUSH_PRED, 17-44
PUSH_SUBQ, 17-45
QB_NAME, 17-46
REWRITE, 17-25
RULE, 17-15
specifying a query block, 17-6
specifying indexes, 17-9
SPREAD_MIN_ANALYSIS, 17-48
STAR_TRANSFORMATION, 17-28
syntax, 17-4
tablespec syntax, 17-7
UNNEST, 17-30
USE_CONCAT, 17-24
USE_HASH, 17-35
USE_MERGE, 17-34
USE_NL, 17-33
USE_NL_WITH_INDEX, 17-34
using extended syntax, 17-7
histograms
frequency, 15-22
height-balanced, 15-20
viewing, 15-20
HOLD_CURSOR clause, 7-28
hours of service, 2-12
HW enqueue
contention, 10-35

I

ID column
PLAN_TABLE table, 19-24
idle wait events, 10-48
SQL*Net message from client, 10-23
implementing business logic, 2-9
Import utility
copying statistics, 15-15
INDEX hint, 16-7, 17-17
INDEX_ASC hint, 17-19
INDEX_COMBINE hint, 16-7, 17-19
INDEX_DESC hint, 17-20
INDEX_FFS hint, 14-26, 14-27
INDEX_JOIN hint, 14-27
INDEX_SS hint, 17-22
INDEX_SS_ASC hint, 17-22
INDEX_SS_DESC hint, 17-23
indexes
adding columns, 2-15
appending columns, 2-15
avoiding the use of, 16-6
bitmap, 2-15, 16-12
B-tree, 2-15
choosing columns for, 16-4
column order, 2-17
composite, 16-5
costs, 2-16
creating, 4-9
design, 2-14
domain, 16-13
dropping, 16-2
enforcing uniqueness, 16-8
ensuring the use of, 16-6
function-based, 2-15, 16-10
improving selectivity, 16-5
index joins, 14-27
joins, 14-27
low selectivity, 16-6
modifying values of, 16-4
non-unique, 16-8
partitioned, 2-16
placement on disk, 8-7
rebuilding, 16-7
re-creating, 16-7
reducing I/O, 2-17
reverse key, 2-16
scans of, 14-21
selectivity, 2-17
selectivity of, 16-4
sequences in, 2-16
serializing in, 2-16
specifying in hints, 17-9
statistics gathering, 15-13
index-organized tables, 2-15
indexspec
hint syntax, 17-9
initialization parameters
CONTROL_FILES, 4-2
DB_BLOCK_SIZE, 4-3
DB_DOMAIN, 4-2
DB_FILE_MULTIBLOCK_READ_COUNT, 14-31
DB_NAME, 4-2
OPEN_CURSORS, 4-2
OPTIMIZER_DYNAMIC_SAMPLING, i-xxx, 15-16, 15-17
OPTIMIZER_FEATURES_ENABLE, 14-26, 14-27
OPTIMIZER_MODE, 14-4, 17-13
PGA_AGGREGATE_TARGET, 4-10
PROCESSES, 4-3
SESSION_CACHED_CURSORS, 7-42
SESSIONS, 4-3
SQL_TRACE, 20-14
STREAMS_POOL_SIZE, 4-4
USER_DUMP_DEST, 20-12
INLIST ITERATOR operation, 19-21
inlists, 19-21
INSERT statement
append, 17-41
instance configuration
initialization files, 4-2
performance considerations, 4-2
Internet scalability, 2-4
I/O
and SQL statements, 10-29
contention, 5-3, 10-6, 10-8, 10-28, 10-46
excessive I/O waits, 10-28
monitoring, 10-5
objects causing I/O waits, 10-29
reducing, 16-5
IOT (index-organized table), 2-15

J

joins
antijoins, 14-30
cartesian, 14-36
execution plans and, 14-30
full outer, 14-39
hash, 14-34
index joins, 14-27
join order and execution plans, 14-15
nested loop, 14-32
nested loops and cost-based optimization, 14-31
order, 12-18
outer, 14-36
parallel, and PQ_DISTRIBUTE hint, 17-38
partition-wise
examples of full, 19-20
examples of partial, 19-18
full, 19-20
semijoins, 14-30
sort merge, 14-35
sort-merge and cost-based optimization, 14-31

K

KEEP buffer pool, 7-19
KEEP cache, 7-16

L

LARGE_POOL_SIZE initialization parameter, 7-37
latch contention
library cache latches, 10-14
shared pool latches, 10-14
latch free wait events, 10-17
actions, 10-40
latch wait events, 10-40
latches
tuning, 1-4, 10-42
LEADING hint, 17-31
library cache
latch contention, 10-42
latch wait events, 10-40
lock, 10-45
memory allocation, 7-35
pin, 10-45
linear scalability, 2-5
locks and lock holders
finding, 10-34
log buffer
space wait events, 10-17, 10-46
tuning, 7-49
log file
parallel write wait events, 10-45
switch wait events, 10-46
sync wait events, 10-17, 10-47
log writer processes
tuning, 8-8
LOG_BUFFER initialization parameter, 7-48
setting, 7-50
LRU
aging policy, 7-15
latch contention, 10-44

M

managing the user interface, 2-8
max session memory statistic, 7-39
MAX_DISPATCHERS initialization parameter, 4-12
MAX_DUMP_FILE_SIZE initialization parameter
SQL Trace, 20-12
MAXOPENCURSORS clause, 7-28
memory
hardware component, 2-8
Memory Advisor
accessing with Oracle Enterprise Manager, 7-2
memory allocation
importance, 7-2
library cache, 7-35
shared SQL areas, 7-35
tuning, 7-7
MERGE hint, 17-27
metrics, 5-2
migrated rows, 10-20
mirroring
redo logs, 8-9
modeling
conceptual, 3-5
data, 2-14
workloads, 2-24
monitoring
diagnostic, 1-7, 12-6
multiple buffer pools, 7-15

N

NAMESPACE column
V$LIBRARYCACHE view, 7-30
nested loop joins, 14-32
cost-based optimization, 14-31
network
array interface, 11-13
detecting performance problems, 11-6
hardware component, 2-8
problem solving, 11-8
Session Data Unit, 11-14
speed, 2-12
statistics, 5-7
tuning, 11-1
network communication wait events, 10-23
db file scattered read wait events, 10-27
db file sequential read wait events, 10-27, 10-29
SQL*Net message from Dblink, 10-24
SQL*Net more data to client, 10-25
new features, i-xxvii
NO_CPU_COSTING hint, 14-5
NO_EXPAND hint, 17-25
NO_FACT hint, 17-29
NO_INDEX hint, 16-7, 17-18
NO_INDEX_FFS hint, 17-21
NO_INDEX_SS hint, 17-23
NO_MERGE hint, 17-27
NO_PARALLEL hint, 17-37
NO_PARALLEL_INDEX, 17-40
NO_PUSH_PRED hint, 17-44
NO_PUSH_SUBQ hint, 17-45
NO_QUERY_TRANSFORMATION hint, 17-24
NO_REWRITE hint, 17-26
NO_UNNEST hint, 17-30
NO_USE_HASH hint, 17-36
NO_USE_MERGE hint, 17-35
NO_USE_NL hint, 17-33
NOAPPEND hint, 17-42
NOCACHE hint, 17-43
NOPARALLEL hint, 17-37
NOPARALLEL_INDEX hint, 17-40
NOREWRITE hint, 17-26
NOT IN subquery, 14-30

O

OBJECT_INSTANCE column
PLAN_TABLE table, 19-24
OBJECT_NAME column
PLAN_TABLE table, 19-23
OBJECT_NODE column
PLAN_TABLE table, 19-23
OBJECT_OWNER column
PLAN_TABLE table, 19-23
OBJECT_TYPE column
PLAN_TABLE table, 19-24
object-orientation, 2-22
OLAP_PAGE_POOL_SIZE initialization parameter, 7-68
OPEN_CURSORS initialization parameter, 4-2
increasing cursors for each session, 7-36
operating system
data cache, 9-2
monitoring disk I/O, 10-5
statistics, 5-5
OPERATION column
PLAN_TABLE table, 19-23, 19-27
optimization
and dynamic sampling, 14-6
choosing the approach, 14-4
cost calculation, 14-9
cost-based, 14-9
cost-based and choosing an access path, 14-28
described, 1-6, 14-2
hints, 14-5, 14-26, 14-27
manual, 14-5
operations performed, 14-2
optimizer
cost calculation, 14-9
goals, 14-3
introduction, 1-6, 14-2
modes, 13-2
moving to from RBO, 18-12
operations, 14-2
parameters for setting mode, 14-4
plan stability, 18-2
query, 1-6
response time, 14-3
statistics, 15-2
throughput, 14-3
upgrading, 18-14
OPTIMIZER column
PLAN_TABLE, 19-24
optimizer mode parameters
ALL_ROWS, 14-4
CHOOSE, 14-4
FIRST_ROWS, 14-4
FIRST_ROWS_n, 14-4
RULE, 14-4
OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, i-xxx, 15-16, 15-17
OPTIMIZER_FEATURES_ENABLE initialization parameter, 14-6, 14-26, 14-27
OPTIMIZER_INDEX_CACHING initialization parameter, 14-8
OPTIMIZER_INDEX_COST_ADJ initialization parameter, 14-8
OPTIMIZER_MODE initialization parameter, 14-4, 14-8, 17-13
hints affecting, 14-5
OPTIONS column
PLAN_TABLE table, 19-23
OPTMIZER_DYNAMIC_SAMPLING initialization parameter, 14-6
Oracle CPU statistics, 10-4
Oracle Enterprise Manager
accessing advisors, 1-7
accessing SQL Tuning Sets, 13-12
accessing the SQL Tuning Advisor, 13-7
accessing the SQLAccess Advisor, 12-7
advisors, 1-7
Outline Editor, 18-8
Performance page, 1-7
Oracle Forms, 20-14
control of parsing and private SQL areas, 7-29
Oracle Net Configuration Assistant, 11-14
Oracle performance improvement method, 3-2
steps, 3-3
Oracle Trace
obsoleted, i-xxxi
removed from Oracle releases, i-xxxi
Oracle-managed files, 8-10
tuning, 8-10
order
joins, 12-18
ORDERED hint, 14-31, 17-32
OTHER column
PLAN_TABLE table, 19-26
OTHER_TAG column
PLAN_TABLE table, 19-25
outer joins, 12-19, 14-36
Outline Editor, 18-8
outlines
CREATE OUTLINE statement, 18-5
creating and using, 18-5
description, 18-2
execution plans and plan stability, 18-2
hints, 18-3
moving tables, 18-10
moving to the cost-based optimizer, 18-12
storage requirements, 18-4
using, 18-6
viewing data for, 18-9

P

page table, 9-11
paging, 9-11
reducing, 7-6
PARALLEL clause
CREATE INDEX statement, 4-10
parallel execution
hints, 17-37
PARALLEL hint, 17-37
parallel joins
and PQ_DISTRIBUTE hint, 17-38
PARENT_ID column
PLAN_TABLE table, 19-24
parsing
hard, 2-18
Oracle Forms, 7-29
Oracle precompilers, 7-28
reducing unnecessary calls, 7-27
soft, 2-18
PARTITION_ID column
PLAN_TABLE table, 19-26
PARTITION_START column
PLAN_TABLE table, 19-25
PARTITION_STOP column
PLAN_TABLE table, 19-26
partitioned indexes, 2-16
partitioned objects
and EXPLAIN PLAN statement, 19-14
partitioning
distribution value, 19-27
examples of, 19-14
examples of composite, 19-16
hash, 19-14
range, 19-14
start and stop columns, 19-15
partition-wise joins
full, 19-20
full, and EXPLAIN PLAN output, 19-20
partial, and EXPLAIN PLAN output, 19-18
PCTFREE parameter, 4-7, 10-20
PCTUSED parameter, 10-20
peeking
bind variables, 14-12
performance
emergencies, 3-8
improvement method, 3-2
improvement method steps, 3-3
mainframe, 9-6
monitoring memory on Windows, 9-10
tools for diagnosing and tuning, 1-6
UNIX-based systems, 9-6
viewing execution plans, 14-15
Windows, 9-6
PGA_AGGREGATE_TARGET initialization parameter, 4-3, 4-10, 7-52, 9-4, 14-9
physical reads from cache statistic, 7-12
plan stability, 18-2
limitations of, 18-2
preserving execution plans, 18-2
procedures for the cost-based optimizer, 18-12
use of hints, 18-2
PLAN_TABLE table
BYTES column, 19-24
CARDINALITY column, 19-24
COST column, 19-24
creating, 19-5
displaying, 19-7
DISTRIBUTION column, 19-26
ID column, 19-24
OBJECT_INSTANCE column, 19-24
OBJECT_NAME column, 19-23
OBJECT_NODE column, 19-23
OBJECT_OWNER column, 19-23
OBJECT_TYPE column, 19-24
OPERATION column, 19-23
OPTIMIZER column, 19-24
OPTIONS column, 19-23
OTHER column, 19-26
OTHER_TAG column, 19-25
PARENT_ID column, 19-24
PARTITION_ID column, 19-26
PARTITION_START column, 19-25
PARTITION_STOP column, 19-26
POSITION column, 19-24
REMARKS column, 19-23
SEARCH_COLUMNS column, 19-24
STATEMENT_ID column, 19-23
TIMESTAMP column, 19-23
POSITION column
PLAN_TABLE table, 19-24
PQ_DISTRIBUTE hint, 17-38
precompilers
control of parsing and private SQL areas, 7-28
preserved snapshots, 5-12
PRIMARY KEY constraint, 16-8
PRIVATE_SGA variable, 7-40
proactive monitoring, 1-4
processes
scheduling, 9-11
PROCESSES initialization parameter, 4-3
program global area (PGA)
direct path read, 10-31
direct path write, 10-33
shared servers, 7-38
programming languages, 2-19
PUSH_PRED hint, 17-44
PUSH_SUBQ hint, 17-45

Q

QB_NAME hint, 17-46
queries
avoiding the use of indexes, 16-6
data, 2-12
ensuring the use of indexes, 16-6
query optimizer, 1-6
See optimizer

R

range
distribution value, 19-27
examples of partitions, 19-14
partitions, 19-14
rdbms ipc reply wait events, 10-48
read consistency, 10-18
read wait events
direct path, 10-31
scattered, 10-27
REBUILD clause, 16-7
recursive calls, 20-23
RECYCLE cache, 7-15
REDO BUFFER ALLOCATION RETRIES statistic, 7-49
redo logs, 4-5
buffer size, 10-46
mirroring, 8-9
placement on disk, 8-8
sizing, 4-5
space requests, 10-18
reducing
contention with dispatchers, 4-12
contention with shared servers, 4-13
data dictionary cache misses, 7-36
paging and swapping, 7-6
unnecessary parse calls, 7-27
RELEASE_CURSOR clause, 7-28
REMARKS column
PLAN_TABLE table, 19-23
resources
allocation, 2-9, 2-19
bottlenecks, 10-24
wait events, 10-29
response time, 2-12
cost-based approach, 14-4
optimizer goal, 14-3
optimizing, 14-3, 17-14
reverse key indexes, 2-16
REWRITE hint, 17-25
rollout strategies
big bang approach, 2-26
trickle approach, 2-26
round-robin
distribution value, 19-27
row cache objects, 10-45
row sources, 14-17
rowids
table access by, 14-20
rows
row sources, 14-17
rowids used to locate, 14-20
RULE hint, 17-15
RULE optimizer mode parameter, 14-4
rule-based optimization
desupport notice, xxix
migration of applications to CBO, xxix
obsolescence, xxix

S

SAMPLE BLOCK clause, 14-28
access path and hints cannot override, 14-29
SAMPLE clause, 14-28
access path and hints cannot override, 14-29
sample table scans, 14-28
hints cannot override, 14-29
sar UNIX command, 9-10
scalability, 2-3
factors preventing, 2-5
Internet, 2-4
linear, 2-5
scans
index, 14-21
index joins, 14-27
index of type bitmap, 14-27
sample table, 14-28
sample table and hints cannot override, 14-29
scattered read wait events, 10-27
actions, 10-27
SEARCH_COLUMNS column
PLAN_TABLE table, 19-24
segment-level statistics, 10-12
SELECT statement
SAMPLE clause, 14-28
SAMPLE clause and access path, 14-29
selectivity
creating indexes, 16-4
improving for an index, 16-5
indexes, 16-6
ordering columns in an index, 2-17
semijoins, 14-30
sequential read wait events
actions, 10-30
service hours, 2-12
Session Data Unit (SDU), 11-14
session memory statistic, 7-39
SESSION_CACHED_CURSORS initialization parameter, 7-42
SESSIONS initialization parameter, 4-3
SGA size, 7-49
SGA_TARGET initialization parameter, 4-3
and Automatic Shared Memory Management, 7-3
automatic memory management, 7-3
shared pool contention, 10-42
shared server
performance issues, 4-10
reducing contention, 4-11
tuning, 4-11
tuning memory, 7-37
shared SQL areas
memory allocation, 7-35
SHARED_POOL_RESERVED_SIZE initialization parameter, 7-43
SHARED_POOL_SIZE initialization parameter, 7-36, 7-44
allocating library cache, 7-35
tuning the shared pool, 7-40
SHOW SGA statement, 7-7
sizing redo logs, 4-5
snapshots
preserved set, 5-12
soft parsing, 2-18
software
components, 2-8
sort areas
tuning, 7-51
sort merge joins, 14-35
cost-based optimization, 14-31
SPREAD_MIN_ANALYSIS hint, 17-48
SQL Profiles
description, 13-3
managing with APIs, 13-10
SQL statements
avoiding the use of indexes, 16-6
ensuring the use of indexes, 16-6
execution plans of, 14-15
modifying indexed data, 16-4
waiting for I/O, 10-29
SQL trace facility, 20-9, 20-15
example of output, 20-25
output, 20-21
statement truncation, 20-24
steps to follow, 20-11
trace files, 20-13
SQL Tuning Advisor, i-xxviii, 1-7, 12-7
accessing with Oracle Enterprise Manager, 13-7
administering with APIs, 13-8
input sources, 13-6
overview, 13-6
tuning options, 13-7
SQL Tuning Sets
accessing with Oracle Enterprise Manager, 13-12
description, 12-7, 13-6
managing with APIs, 13-12, 13-13
SQL*Net
message from client idle events, 10-23
message from dblink wait events, 10-24
more data to client wait events, 10-25
SQL_STATEMENT column
TKPROF_TABLE, 20-28
SQL_TRACE
initialization parameter, 20-14
SQLAccess Advisor, 1-7, 12-7
accessing with Oracle Enterprise Manager, 12-7
SQLTUNE_CATEGORY initialization parameter
determining the SQL Profile category, 13-4
ST enqueue
contention, 10-35
star transformation, 17-28
STAR_TRANSFORMATION hint, 17-28
STAR_TRANSFORMATION_ENABLED initialization parameter, 14-9, 17-29
start columns
in partitioning and EXPLAIN PLAN statement, 19-15
STATEMENT_ID column
PLAN_TABLE table, 19-23
statistics
and STATISTICS_LEVEL initialization parameter, 1-6
automatic gathering, 15-3
baselines, 5-2
collecting on external tables, 15-5
consistent gets from cache, 7-11
databases, 5-3
db block gets from cache, 7-12
displaying in views, 15-19
enabling automatic gathering, 15-4
exporting and importing, 15-14
GATHER_STATS_JOB, 15-3
gathering, 5-2
gathering stale, 15-10
gathering using sampling, 15-8
gathering with DBMS_STATS package, 15-7
gathering with DBMS_STATS procedures, 15-7
generating for query optimization, 15-3
histograms, 15-20
limitations on restoring previous versions, 15-14
locking, 15-15
manually gathering, 15-6
max session memory, 7-39
missing, 15-18
operating systems, 5-5
CPU statistics, 5-6
disk statistics, 5-7
network statistics, 5-7
virtual memory statistics, 5-7
optimizer, 15-2
optimizer mode, 14-4
optimizer use of, 14-9
physical reads from cache, 7-12
restoring previous versions, 15-13
segment-level, 10-12
session memory, 7-39
shared server processes, 4-13
stale, 15-10
system, 15-11
time model, 5-4
user-defined, 15-10
when to gather, 15-11
STATISTICS_LEVEL initialization parameter, 5-9, 10-7
and Automatic Workload Repository, 5-12
enabling automatic database diagnostic monitoring, 6-6
settings for statistic gathering, 1-6
stop columns
in partitioning and EXPLAIN PLAN statement, 19-15
stored outlines
creating and using, 18-5
execution plans and plan stability, 18-2
hints, 18-3
moving tables, 18-10
storage requirements, 18-4
using, 18-6
viewing data for, 18-9
STREAMS_POOL_SIZE initialization parameter, 4-4, 7-4
striping
manual, 8-6
subqueries
NOT IN, 14-30
unnesting, 12-20
swapping, 9-10, 9-11
reducing, 7-6
switching processes, 9-11
system architecture, 2-7
configuration, 2-10
hardware components, 2-7
CPUs, 2-7
I/O subsystems, 2-8
memory, 2-8
networks, 2-8
software components, 2-8
data and transactions, 2-9
implementing business logic, 2-9
managing the user interface, 2-8
user requests and resource allocation, 2-9
System Global Area tuning, 7-7

T

tables
creating, 4-7
design, 2-14
full scans, 10-32
placement on disk, 8-7
setting storage options, 4-7
tablespaces, 4-5
creating, 4-5
creating temporary, 4-6
temporary, 4-6
tablespec
hint syntax, 17-7
TCP.NODELAY parameter, 11-14
temporary tablespaces, 4-6
creating, 4-6
testing designs, 2-24
thrashing, 9-11
throughput
cost-based approach, 14-4
optimizer goal, 14-3
optimizing, 14-3, 17-13
time model statistics, 5-4
TIMED_STATISTICS initialization parameter
SQL Trace, 20-12
TIMESTAMP column
PLAN_TABLE table, 19-23
TKPROF program, 20-11, 20-15
editing the output SQL script, 20-26
example of output, 20-25
generating the output SQL script, 20-26
row source operations, 20-22
syntax, 20-16
using the EXPLAIN PLAN statement, 20-18
wait event information, 20-23
TKPROF_TABLE, 20-27
querying, 20-27
TM enqueue
contention, 10-36
tools
for performance tuning, 1-6
TRACEFILE_IDENTIFIER initialization parameter
identifying trace files, 20-13
tracing
consolidating with trcsess, 20-7
identifying files, 20-13
transactions and data, 2-9
trcsess utility, i-xxviii, 20-7
trickle rollout strategy, 2-26
tuning
and bottleneck elimination, 1-5
and proactive monitoring, 1-4
latches, 1-4, 10-42
logical structure, 16-2
memory allocation, 7-7
resource contention, 10-1
shared server, 4-11
sorts, 7-51
SQL Tuning Advisor, 13-6
System Global Area (SGA), 7-7
TX enqueue
contention, 10-36
type conversion, 12-10

U

undo management
automatic mode, 4-4
UNDO TABLESPACE clause, 4-4
UNDO_MANAGEMENT initialization parameter, 4-3, 4-4
UNDO_TABLESPACE initialization parameter, 4-4
UNIQUE constraint, 16-8
uniqueness, 16-8
UNIX system performance, 9-6
UNNEST hint, 17-30
untransformed column values, 12-9
upgrade
to the cost-based optimizer, 18-14
USE_CONCAT hint, 17-24
USE_HASH hint, 17-35
USE_MERGE hint, 17-34
USE_NL hint, 17-33
USE_NL_WITH_INDEX hint, 17-34
USE_STORED_OUTLINES parameter, 18-6
user global area (UGA)
shared servers, 4-10, 7-37
V$SESSTAT, 7-39
user requests, 2-9
USER_DUMP_DEST initialization parameter, 20-12
SQL Trace, 20-12
USER_ID column
TKPROF_TABLE, 20-28
USER_OUTLINE_HINTS view
stored outline hints, 18-9
USER_OUTLINES view
stored outlines, 18-9
user-defined bind variables, 14-12
users
interaction method, 2-11
interfaces, 2-19
location, 2-11
network speed, 2-12
number of, 2-11
requests, 2-19
response time, 2-12
UTLCHN1.SQL script, 10-20
UTLXPLP.SQL script
displaying plan table output, 19-7
for viewing EXPLAIN PLANs, 14-15
UTLXPLS.SQL script
displaying plan table output, 19-7
for viewing EXPLAIN PLANs, 14-15
used for displaying EXPLAIN PLANs, 14-16

V

V$ACTIVE_SESSION_HISTORY view, 5-4, 10-9
V$BH view, 7-17
V$BUFFER_POOL_STATISTICS view, 7-17
V$DB_CACHE_ADVICE view, 7-8, 7-11, 7-12, 7-13, 7-14, 7-16
V$EVENT_HISTOGRAM view, 10-10
V$FILE_HISTOGRAM view, 10-10
V$JAVA_LIBRARY_CACHE_MEMORY view, 7-33
V$JAVA_POOL_ADVICE view, 7-33
V$LIBRARY_CACHE_MEMORY view, 7-33
V$LIBRARYCACHE view
NAMESPACE column, 7-30
V$OSSTAT view, 5-6
V$QUEUE view, 4-13
V$ROWCACHE view
GETMISSES column, 7-36
GETS column, 7-36
performance statistics, 7-34
V$RSRC_CONSUMER_GROUP view, 10-5
V$SESS_TIME_MODEL view, 5-4, 10-9
V$SESSION view, 10-9, 10-11, 10-21
V$SESSION_EVENT view, 10-9, 10-21
network information, 11-6
V$SESSION_WAIT view, 10-9, 10-21
network information, 11-6
V$SESSION_WAIT_CLASS view, 10-10
V$SESSION_WAIT_HISTORY view, 10-10
V$SESSTAT view, 10-5
network information, 11-6
using, 7-38
V$SHARED_POOL_ADVICE view, 7-32
V$SHARED_POOL_RESERVED view, 7-44
V$SQL_PLAN view
using to display execution plan, 19-4
V$SQL_PLAN_STATISTICS view
using to display execution plan statistics, 19-4
V$SQL_PLAN_STATISTICS_ALL view
using to display execution plan information, 19-5
V$SYS_TIME_MODEL view, 5-4, 10-9
V$SYSSTAT view
redo buffer allocation, 7-49
using, 7-11
V$SYSTEM_EVENT view, 10-10, 10-21
V$SYSTEM_WAIT_CLASS view, 10-10
V$TEMP_HISTOGRAM view, 10-10
V$UNDOSTAT view, 4-4
V$WAITSTAT view, 10-11
validating designs, 2-24
views, 2-17
DBA_HIST, 5-16
statistics, 15-19
virtual memory statistics, 5-7
vmstat UNIX command, 9-10

W

wait events, 5-3
buffer busy waits, 10-25
classes, 5-3, 10-8
contention wait events, 10-40
direct path, 10-33
enqueue, 10-34
free buffer waits, 10-37
idle wait events, 10-48
latch, 10-40
library cache latch, 10-40
log buffer space, 10-46
log file parallel write, 10-45
log file switch, 10-46
log file sync, 10-47
network communication wait events, 10-23
rdbms ipc reply, 10-48
resource wait events, 10-29
Windows performance, 9-6
workloads
estimating, 2-23
benchmarking, 2-23
extrapolating, 2-23
modeling, 2-24
testing, 2-24