Skip Headers

Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)

Part Number A96533-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
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 join, 8-6
cluster scans, 1-35
composite index, 8-8
defined, 1-22
execution plans, 1-18
hash cluster key, 8-7
hash scans, 1-36
index scans, 1-28
indexed cluster key, 8-7
single row by cluster join, 8-4
single row by hash cluster key (with unique key), 8-5
single row by rowid, 8-4
single row by unique or primary key, 8-6
ALL operator, 2-23
ALL_ROWS
optimizer mode parameter, 1-7
ALL_ROWS hint, 1-8, 5-7
allocation
of memory, 14-2
ALTER INDEX statement, 4-7
ALTER SESSION statement
examples, 10-5
SET SESSION_CACHED_CURSORS clause, 14-40
ALTER SYSTEM statement
DISPATCHERS initialization parameter, 19-4
ANALYZE statement, 1-8, 22-22
creating histograms, 3-22
AND_EQUAL hint, 4-6, 5-17
anti-joins, 1-43
transformations not allowed, 1-44
ANY operator, 2-22
APPEND hint, 5-34
APPINFO
tuning, 11-9
applications
data warehousing and star queries, 1-44
ApplReg event, 12-15
array interface, 23-13
ARRAYSIZE
tuning, 11-10
automatic segment-space management, 15-22, 22-28
automatic undo management, 18-2
AUTOTRACE
settings, 11-2
system variable, 11-2
autotrace
SQL*Plus, 11-1

B

BEGIN_SNAP variable, 21-12
BETWEEN comparison operator, 2-24
binary files
formatting using Oracle Trace, 12-3
bind variables, 14-22
optimization, 1-38
BITMAP CONVERSION row source, 4-18
bitmap indexes, 4-12, 4-17
compared with B-tree indexes, 4-13
inlist iterator, 9-19
maintenance, 4-14
on index-organized tables, 4-16
on joins, 4-19
when to use, 4-12
BITMAP_MERGE_AREA_SIZE initialization parameter, 4-14, 4-17
bitmaps
mapping to rowids, 4-16
block sampling, 3-4
bottlenecks
disk I/O, 15-3
memory, 14-2
resource, 22-26
broadcast
distribution value, 9-26
B-tree indexes, 4-15, 4-18
buffer busy wait events, 22-27
actions, 22-28
buffer caches
reducing buffers, 14-12, 14-35
buffer pools
default cache, 14-14
KEEP cache, 14-14
multiple, 14-13
RECYCLE cache, 14-14
BYTES column
PLAN_TABLE table, 9-24

C

CACHE hint, 5-35
caching tables
automatic caching of small tables, 5-35
CARDINALITY column
PLAN_TABLE table, 9-24
cartesian joins, 1-50
CATALOG.SQL script, 13-5
CATPROC.SQL script, 13-5
chained rows, 22-21
CHAR datatype, 13-3
character sets
database options, 13-3
checkpoints
choosing checkpoint frequency, 17-3
CHOOSE
optimizer mode parameter, 1-7
CHOOSE hint, 1-8, 5-9
CLEAR TIMING command
SQL*Plus, 11-7
client/server applications, 16-12
CLUSTER hint, 5-11
clusters, 4-20
hash and scans of, 1-36
joins and, 8-4, 8-6
scans of, 1-35, 8-4
scans of hash, 8-5, 8-7
scans of index, 8-7
scans of joins, 8-6
collections, 12-8
columns
pseudocolumn ROWNUM, 2-36, 2-45
ROWNUM pseudocolumn, 8-15
selectivity, 3-2
selectivity estimates and histograms, 3-20
to index, 4-3
command files
registering, 11-9
complex view merging, 2-37
composite indexes, 4-4
composite partitioning
examples of, 9-14
CONNECT BY clause
optimizing view queries, 2-36
Connection event, 12-15
connection manager, 23-14
connection pooling, 19-4
consistency
read, 22-20
consistent gets statistic, 14-9, 18-3
consistent mode
TKPROF, 10-13
constants
comparisons and, 2-19
evaluation of expressions, 2-19
when computed, 2-19
constraints, 4-8
contention
disk, 15-3
memory, 14-2, 22-1
tuning, 22-1
wait events, 22-41
context switches, 16-13
CONTROL_FILES initialization parameter, 13-13
cost
optimizer calculation, 1-10
COST column
PLAN_TABLE table, 9-24
cost-based optimizations, 1-10
extensible optimization, 1-61
histograms, 3-20
procedures for plan stability, 7-12
selectivity of predicates, 3-2
selectivity of predicates and histograms, 3-20
selectivity of predicates for user-defined, 1-62
star queries, 1-44
statistics, 3-2
statistics and user-defined, 1-62
upgrading to, 7-14
user-defined costs, 1-63
counter/accumulator views, 24-2
CPU_COUNT initialization parameter, 17-19
CPUs
utilization, 16-11
CREATE DATABASE statement, 13-3
CREATE INDEX statement
example, 14-71
NOSORT clause, 14-71
PARALLEL clause, 13-11
CREATE OUTLINE statement, 7-5
CREATE_BITMAP_AREA_SIZE initialization parameter, 4-14, 4-17
CREATE_STORED_OUTLINES parameter, 7-4
creating databases, 13-2
manually, 13-2
parameters, 13-2
with Installer, 13-2
cross-facility 3 event, 12-18
cross-product items
See also cross-facility 3 event
current mode
TKPROF, 10-13
current state views, 24-2
CURSOR_NUM column
TKPROF_TABLE table, 10-19
CURSOR_SHARING initialization parameter, 1-58, 14-24, 14-44
CURSOR_SHARING_EXACT hint, 5-39
CURSOR_SPACE_FOR_TIME initialization parameter
setting, 14-39

D

data cache, 16-2
data dictionary, 14-34
CATALOG.SQL scripts, 13-5
CATPROC.SQL scripts, 13-5
scripts, 13-5
statistics in, 3-15
views used in optimization, 3-15
data indexing, 13-10
data loading, 13-10
Data Viewer
collecting data for specific wait events, 12-35
tips on using, 12-35
data warehousing
dimensions, 1-44
star queries, 1-44
Database Connection event, 12-2
database options, 13-3
Database Resource Manager, 16-6, 16-10, 22-8
databases
buffers, 14-12, 14-34
character set options, 13-3
creating, 13-2
creating manually, 13-2
creation parameters, 13-2
creation with Installer, 13-2
distributed statement optimization on, 2-13
identifier (DBID), 21-3
location of initial datafile, 13-4
national character set options, 13-4
optimization on distributed statement, 2-13
SQL.BSQ file options, 13-4
datatypes
CHAR, 13-3
NCHAR, 13-4
NVARCHAR, 13-4
NVARCHAR2, 13-4
user-defined and statistics, 1-62
VARCHAR, 13-3
VARCHAR2, 13-3
DATE_OF_INSERT column
TKPROF_TABLE table, 10-19
db block gets statistic, 14-9, 18-3
DB file scattered read wait events, 22-29
actions, 22-30
DB file sequential read wait events
actions, 22-32
DB file sequential/scattered read wait events, 22-29, 22-31
DB_BLOCK_BUFFERS initialization parameter, 14-12, 14-35
DB_BLOCK_SIZE initialization parameter, 13-2, 13-14, 15-14
DB_CACHE_ADVICE parameter, 14-12
DB_CACHE_SIZE initialization parameter, 13-14, 14-13
DB_DOMAIN initialization parameter, 13-13
DB_FILE_MULTIBLOCK_READ_COUNT
initialization parameter, 1-24
DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 1-58, 15-13, 15-14, 22-29
cost-based optimization, 1-43
DB_KEEP_CACHE_SIZE
initialization parameter, 14-17
DB_NAME initialization parameter, 13-13
DB_nK_CACHE_SIZE initialization parameter, 14-12
DB_RECYCLE_CACHE_SIZE
initialization parameter, 14-18
DB_WRITER_PROCESSES initialization parameter, 22-40
DBA_OBJECTS view, 14-16
DBID
database identifier, 21-3
Statspack, 21-25
DBMS_APPLICATION_INFO package, 11-9
DBMS_JOB procedure, 21-8
DBMS_JOB.INTERVAL procedure, 21-9
DBMS_OUTLN package, 7-4
DBMS_OUTLN_EDIT package, 7-4
DBMS_SHARED_POOL package, 14-42, 14-43
DBMS_STATS package, 1-8, 3-5, 3-6
creating histograms, 3-22
default cache, 14-14
DEFAULT_TABLESPACE variable, 21-6
DEFINE OFF
tuning, 11-10
deleting
data, 21-23
snapshots, 21-23
DEPTH column
TKPROF_TABLE table, 10-19
deterministic functions
PL/SQL, 2-28
dictionary managed tablespaces, 21-4
dimensions
star joins, 1-44
star queries, 1-44
direct path read events, 22-33
actions, 22-34
causes, 22-34
direct path wait events, 22-35
direct path write events
actions, 22-35
causes, 22-35
direct-path INSERT, 5-34
disabled constraints, 4-8
Disconnect event, 12-15
disk reads and buffer gets
monitoring, 11-9
disks
contention, 15-3
monitoring operating system file activity, 22-8
dispatcher processes, 19-4
DISPATCHERS initialization parameter, 19-4, 23-3
DISTINCT operator
optimizing views, 2-37
distributed databases
statement optimization on, 2-13
distributed transactions
optimizing, 2-13
sample table scan not supported, 1-36
distribution
hints for, 5-31
DISTRIBUTION column
PLAN_TABLE table, 9-25
DML locks, 24-18
domain indexes
and EXPLAIN PLAN, 9-20
extensible optimization, 1-61
user-defined statistics, 1-62
using, 4-19
DRIVING_SITE hint, 5-27
duration events
in Oracle Trace, 12-2, 12-15
dynamic performance views, 24-2
DYNAMIC_SAMPLING hint, xxxii, 5-39

E

enabled constraints, 4-8
END_SNAP variable, 21-12
enforced constraints, 4-8
enqueue wait events
actions, 22-37
EPC_ERROR.LOG file, 12-37
equijoins, 6-10
ErrorStack event, 12-15
event timings, 21-21
examples
ALTER SESSION statement, 10-5
concurrently creating tablespaces, 13-7
CREATE DATABASE script, 13-4
CREATE INDEX statement, 14-71
creating indexes efficiently, 13-12
executing required data dictionary scripts, 13-5
execution plan, 8-18
EXPLAIN PLAN output, 8-18, 10-16
full table scan, 8-19
indexed query, 8-19
minimal initialization file, 13-14
NOSORT clause, 14-71
SET TRANSACTION statement, 18-3
SQL trace facility output, 10-16
V$DB_OBJECT_CACHE view, 24-5
V$FILESTAT view, 24-8
V$LATCH view, 24-11
V$LATCH_CHILDREN view, 24-13
V$LATCHHOLDER view, 24-14
V$LIBRARYCACHE view, 24-16
V$LOCK view, 24-20
V$OPEN_CURSOR view, 24-23, 24-24
V$PROCESS view, 24-27
V$ROLLSTAT view, 24-29
V$SESSION view, 24-35
V$SESSION_EVENT view, 24-37
V$SESSION_WAIT view, 24-39
V$SQLAREA view, 24-58, 24-59
V$SQLTEXT view, 24-60
Execute event, 12-15
execution plans
accessing views, 2-39, 2-42, 2-43
comparing with PLAN_HASH_VALUE, 24-45
complex statements, 2-34
compound queries, 2-48, 2-49, 2-50
examples, 2-34, 8-18, 10-7
execution sequence of, 1-23
joining views, 2-46
joins, 1-40
optimizer path, 11-3
OR operators, 2-31, 8-18
overview of, 1-18
plan stability, 7-2
preserving with plan stability, 7-2
table output, 11-3
TKPROF, 10-7, 10-11
viewing with the utlxpls.sql script, 1-18
EXPLAIN PLAN statement
access paths, 1-36, 8-4, 8-5, 8-6, 8-7, 8-8, 8-9, 8-10, 8-11, 8-12, 8-13, 8-14, 8-15
and domain indexes, 9-20
and full partition-wise joins, 9-17
and partial partition-wise joins, 9-16
and partitioned objects, 9-12
basic steps, 1-19
examples of output, 8-18, 10-16
execution order of steps in output, 1-19
invoking with the TKPROF program, 10-11
PLAN_TABLE table, 9-4
restrictions, 9-22
scripts for viewing output, 1-19
viewing the output, 1-18
Export utility
copying statistics, 3-2
exporting data, 21-22
extensible optimization, 1-61
user-defined costs, 1-63
user-defined selectivity, 1-62
user-defined statistics, 1-62

F

FACT hint, 5-22
fact tables
star joins, 1-44
star queries, 1-44
fast full index scans, 1-34
FAST_START_IO_TARGET initialization parameter, 17-4, 17-5
FAST_START_MTTR_TARGET initialization parameter, 17-4, 17-5, 17-9, 17-12
FAST_START_PARALLEL_ROLLBACK initialization parameter, 17-19
FastCGI
iSQL*Plus, 11-14
fast-start checkpoints
FAST_START_MTTR_TARGET initialization parameter, 17-6
LOG_CHECKPOINT_INTERVAL initialization parameter, 17-7
LOG_CHECKPOINT_TIMEOUT initialization parameter, 17-7
fast-start on-demand rollback, 17-18
fast-start parallel rollback, 17-18
features, new, xxxi
Fetch event, 12-15
FIRST_ROWS
optimizer mode parameter, 1-7
FIRST_ROWS hint, 1-8
FIRST_ROWS(n) hint, 1-8, 5-7
FIRST_ROWS_n
optimizer mode parameter, 1-7
FLUSH OFF
tuning, 11-10
FORCE_UNION_REWRITE hint, xxxii, 5-19
FORMAT statement
in Oracle Trace, 12-3
formatter tables
in Oracle Trace, 12-3
free buffer wait events, 22-39
FULL hint, 4-6, 5-10
full outer joins, 1-54
full partition-wise joins, 9-17
full table scans, 8-14, 8-19, 22-34
rule-based optimizer, 8-14
function-based indexes, 4-10
functions
PL/SQL deterministic, 2-28
SQL and optimizing view queries, 2-43
user-defined and extensible optimization, 1-61

G

GATHER_ INDEX_STATS procedure
in DBMS_STATS package, 3-6
GATHER_DATABASE_STATS procedure
in DBMS_STATS package, 3-6
GATHER_SCHEMA_STATS procedure
in DBMS_STATS package, 3-6
GATHER_TABLE_STATS procedure
in DBMS_STATS package, 3-6
GETMISSES column
in V$ROWCACHE table, 14-34
GETS column
in V$ROWCACHE view, 14-34
global hints, 5-44
GLOGIN.SQL
site profile, 11-3
GROUP BY clause
NOSORT clause, 14-72
optimizing views, 2-37

H

hash
distribution value, 9-26
hash clusters
scans of, 1-36, 8-5, 8-7
HASH hint, 5-12
hash joins, 1-47
index join, 1-35
hash partitions, 9-12
examples of, 9-12
HASH_AJ hint, 1-43, 5-28
HASH_AREA_SIZE initialization parameter, 1-58
HASH_JOIN_ENABLED initialization parameter, 1-59
HASH_SJ hint, 1-44, 5-28
hashing, 4-21
HIGH_VALUE statistics, 1-38
hints, 5-2
access paths, 5-9, 5-17
ALL_ROWS hint, 5-7
AND_EQUAL hint, 4-6, 5-17
as used in outlines, 7-3
CACHE hint, 5-35
cannot override sample access path, 1-37
CHOOSE hint, 5-9
CLUSTER hint, 5-11
CURSOR_SHARING_EXACT hint, 5-39
degree of parallelism, 5-29
EXPAND_GSET_TO_UNION hint, 5-19
extensible optimization, 1-62
FACT hint, 5-22
FIRST_ROWS hint, 5-7
FIRST_ROWS(n) hint, 5-7
FORCE_UNION_REWRITE hint, 5-20
FULL hint, 4-6, 5-10
global, 5-44
HASH hint, 5-12
HASH_AJ hint, 5-28
HASH_SJ hint, 5-28
how to use, 5-2
INDEX hint, 4-6, 5-12, 5-23
INDEX_ASC hint, 5-14
INDEX_DESC hint, 5-14, 5-15
INDEX_FFS, 1-34
INDEX_JOIN, 1-35
join operations, 5-24
LEADING hint, 5-27
MERGE hint, 5-20
MERGE_AJ and HASH_AJ, 1-43
MERGE_AJ hint, 5-28
MERGE_SJ and HASH_SJ, 1-44
MERGE_SJ hint, 5-28
NL_AJ hint, 5-28
NL_SJ hint, 5-28
NO_EXPAND hint, 5-18
NO_FACT hint, 5-22
NO_INDEX, 4-6
NO_INDEX hint, 5-16
NO_MERGE hint, 5-21
NO_PUSH_PRED hint, 5-37
NO_PUSH_SUBQ, 5-38
NO_PUSH_SUBQ hint, 5-38
NO_UNNEST hint, 5-37
NOCACHE hint, 5-35
NOPARALLEL hint, 5-30
NOREWRITE hint, 5-20
optimization approach and goal, 5-6
ORDERED hint, 1-43, 5-23
overriding optimizer choice, 1-37
overriding OPTIMIZER_MODE, 1-8
PARALLEL hint, 5-29
parallel query option, 5-29
PQ_DISTRIBUTE hint, 5-31
PUSH_PRED hint, 5-37
PUSH_SUBQ hint, 5-37
REWRITE hint, 5-19
ROWID hint, 5-11
STAR hint, 5-23
syntax, 5-3
UNNEST hint, 5-36
USE_CONCAT hint, 5-18
USE_MERGE hint, 5-25
USE_NL hint, 5-24
histograms, 3-20
number of buckets, 3-22
HOLD_CURSOR clause, 14-27

I

ID column
PLAN_TABLE table, 9-24
idle timeout
tuning, 11-14
idle wait events, 22-49
SQL*Net message from client, 22-26
Import utility
copying statistics, 3-2
IN operator, 2-22
merging views, 2-38
IN subquery, 2-37
INDEX hint, 4-6, 4-15, 5-12
index joins, 1-35
INDEX_ASC hint, 5-14
INDEX_COMBINE hint, 4-6, 4-15
INDEX_DESC hint, 5-14, 5-15
INDEX_FFS hint, 1-34
INDEX_JOIN hint, 1-35
indexes
avoiding the use of, 4-6
bitmap, 4-12, 4-17
choosing columns for, 4-3
composite, 4-4, 8-8
creating, 13-11
domain, 4-19
domain indexes and extensible optimization, 1-61
domain indexes and user-defined statistics, 1-62
dropping, 4-2
enforcing uniqueness, 4-8
ensuring the use of, 4-6
example, 8-19
function-based, 4-10
improving selectivity, 4-4
index joins, 1-35
low selectivity, 4-6
modifying values of, 4-4
non-unique, 4-8
optimization and, 2-30, 8-17
placement on disk, 15-16
rebuilding, 4-7
re-creating, 4-7
restrictions on scans of, 8-14
scans of, 1-28
scans of bounded range, 8-10
scans of cluster key, 8-7
scans of composite, 8-8
scans of MAX or MIN, 8-13
scans of ORDER BY, 8-13
scans of single-column, 8-8
scans of unbounded range, 8-11
selectivity of, 4-3
statement conversion and, 2-30, 8-17
statistics gathering, 3-9
indexing data, 13-10
information views, 24-4
initial database creation, 13-2
initialization files, 13-2, 13-13
initialization parameters
CONTROL_FILES, 13-13
CPU_COUNT, 17-19
DB_BLOCK_SIZE, 13-2, 13-14
DB_CACHE_SIZE, 13-14
DB_DOMAIN, 13-13
DB_FILE_MULTIBLOCK_READ_COUNT, 1-43
DB_NAME, 13-2, 13-13
FAST_START_PARALLEL_ROLLBACK, 17-19
in Oracle Trace, 12-7
INITRANS, 13-8
JAVA_POOL_SIZE, 13-14
JOB_QUEUE_PROCESSES, 21-9
LOG_ARCHIVE_XXX, 13-14
LOG_CHECKPOINT_INTERVAL, 17-7
LOG_CHECKPOINT_TIMEOUT, 17-7
LOG_PARALLELISM, 17-8
OPEN_CURSORS, 13-13
OPTIMIZER_FEATURES_ENABLE, 1-34, 1-35, 2-37
OPTIMIZER_MODE, 1-6, 5-7, 8-2
PARALLEL_MAX_SERVERS, 17-8
PGA_AGGREGATE_TARGET, 13-11
PROCESSES, 13-14
RECOVERY_PARALLELISM, 17-8
SESSION_CACHED_CURSORS, 14-40
SESSIONS, 13-14
SHARED_POOL_SIZE, 13-14
SORT_AREA_SIZE, 1-42, 13-12
SQL_TRACE, 10-6
TIMED_STATISTICS, 21-7
USER_DUMP_DEST, 10-4
INIT.ORA file
ORACLE_TRACE_ENABLE parameter, 12-35
INITRANS initialization parameter, 13-8
IN-lists, 5-14, 5-18
input parameters
SNAP and MODIFY_STATSPACK_PARAMETERS, 21-20
INPUT_IO item, 12-16
INSERT statement
append, 5-34
instance configuration, 13-13
instance numbers, 21-3
INSTANCE_NUMBER
Statspack, 21-25
instrumentation
of Oracle Server, 12-15
INTERSECT operator
example, 2-50
optimizing view queries, 2-36
intratransaction recovery, 17-19
I/O
and SQL statements, 22-31
balancing, 15-4
excessive I/O waits, 22-30
objects causing I/O waits, 22-31
reducing, 4-4
iSQL*Plus
FastCGI, 11-14
idle timeout, 11-14
interpreting statistics, 11-13
iSQLPlusHashTableSize, 11-13
iSQLPlusNumberOfThreads, 11-13
iSQLPlusTimeOutInterval, 11-13
parameters for tuning, 11-13
server statistics report, 11-11
statistics report, 11-11
tuning statistics, 11-13
isqlplus.conf file, 11-13
iSQLPlusHashTableSize
tuning, 11-13
iSQLPlusNumberOfThreads
impact on iSQLPlusHashTableSize, 11-13
impact on request load, 11-13
tuning, 11-13
iSQLPlusTimeOutInterval
tuning, 11-13
items
cross-product, 12-17
standard resource utilization, 12-16
types of, 12-16

J

JAVA_POOL_SIZE initialization parameter, 13-14
JOB_QUEUE_PROCESSES initialization parameter, 21-9
joins
anti-joins, 1-43
cartesian, 1-50
cluster, 8-4
convert to subqueries, 2-33
execution plans and, 1-40
full outer, 1-54
hash, 1-47
index joins, 1-35
join order and execution plans, 1-18
join order and selectivity of predicates, 1-62, 3-2, 3-20
nested loop, 1-45
nested loops and cost-based optimization, 1-42
optimization of, 8-16
outer, 1-51
outer and non-null values for nulls, 2-45
parallel, and PQ_DISTRIBUTE hint, 5-31
partition-wise
examples of full, 9-17
examples of partial, 9-16
full, 9-17
sample table scan not supported, 1-36
searches on clusters, 8-6
select-project-join views, 2-35
semi-joins, 1-43
sort merge, 1-49
sort-merge and cost-based optimization, 1-42
sort-merge example, 8-12
star joins, 1-44
star queries, 1-44

K

KEEP cache, 14-14
keys
searches, 8-5

L

LARGE_POOL_SIZE initialization parameter, 14-36
latch free wait events
actions, 22-42
latches
tuning, 24-12
LEADING hint, 5-27
level 7 snapshot
Statspack, 21-18
library cache
memory allocation, 14-34
LIKE operator, 2-22
Lmode modes, 24-19
load balancing, 15-4
loading data, 13-10
locally managed tablespaces, 21-4
location of initial datafile
database options, 13-4
lock types
common, 24-17
ST (space transaction) locks, 24-18
TM (DML) locks, 24-18
TX (row transaction) locks, 24-18
UL (user defined) locks, 24-19
locking rows, 13-8
locks and lock holders
finding, 22-36
log buffer tuning, 14-47
log file switch wait events, 22-46
log writer processes
tuning, 15-18
LOG_ARCHIVE_XXX initialization parameter, 13-14
LOG_BUFFER initialization parameter, 14-47
setting, 14-48
LOG_CHECKPOINT_INTERVAL initialization parameter, 17-3
recovery time, 17-7
LOG_CHECKPOINT_TIMEOUT initialization parameter, 17-4
recovery time, 17-7
LOG_PARALLELISM initialization parameter, 17-8
LogicalTX event, 12-15
lookup tables
star queries, 1-44
LOW_VALUE statistics, 1-38
LRU
aging policy, 14-13
latch contention, 22-45

M

manual database creation, 13-2
max session memory statistic, 14-37
MAX_DISPATCHERS initialization parameter, 19-4
MAX_DUMP_FILE_SIZE initialization parameter
SQL Trace, 10-4
MAX_SHARED_SERVERS initialization parameter, 19-7
MAXOPENCURSORS clause, 14-27
MAXRS_SIZE item, 12-16
mean time to recover, 17-4
advisory, xxxv
See also MTTR
memory allocation
importance, 14-2
library cache, 14-34
shared SQL areas, 14-34
sort areas, 14-69
tuning, 14-5
MERGE hint, 5-20
MERGE_AJ hint, 1-43, 5-28
MERGE_SJ hint, 1-44, 5-28
merging complex views, 2-37
merging views into statements, 2-35
migrated rows, 22-21
Migration event, 12-15
MINUS operator
optimizing view queries, 2-36
mirroring
redo logs, 15-19
modes
Lmode, 24-19
request, 24-19
monitoring
disk reads and buffer gets, 11-9
MTBF (mean time between failures)
tuning I/O, 15-2
MTTR
initialization parameter, 17-6
mean time to recover advisory, xxxv
See also mean time to recover
multiple buffer pools, 14-13

N

NAMESPACE column
V$LIBRARYCACHE view, 14-29
national character set database option, 13-4
NCHAR datatype, 13-4
nested loop joins, 1-45
cost-based optimization, 1-42
network
array interface, 23-13
detecting performance problems, 23-6
problem solving, 23-8
Session Data Unit, 23-13
tuning, 23-1
network communication wait events, 22-25
DB file sequential/scattered read wait events, 22-29, 22-31
SQL*Net message from Dblink, 22-27
new features, xxxi
NL_AJ hint, 5-28
NL_SJ hint, 5-28
NLS_SORT initialization parameter
ORDER BY access path, 8-13
NO_EXPAND hint, 5-18
NO_FACT hint, 5-22
NO_INDEX hint, 4-6, 5-16
NO_MERGE hint, 5-21
NO_PUSH_PRED hint, 5-37
NO_UNNEST hint, 5-37
NOAPPEND hint, 5-34
NOCACHE hint, 5-35
NOPARALLEL hint, 5-30
NOPARALLEL_INDEX hint, 5-33
NOREWRITE hint, 5-20
NOSORT clause, 14-71, 14-72
NOT IN subquery, 1-43
NOT operator, 2-24
NT performance, 16-7
nulls
non-null values for, 2-45
NUM_DISTINCT column
USER_TAB_COLUMNS view, 1-38
NUM_ROWS column
USER_TABLES view, 1-38
NVARCHAR datatype, 13-4
NVARCHAR2 datatype, 13-4

O

OBJECT_INSTANCE column
PLAN_TABLE table, 9-23
OBJECT_NAME column
PLAN_TABLE table, 9-23
OBJECT_NODE column
PLAN_TABLE table, 9-23
OBJECT_OWNER column
PLAN_TABLE table, 9-23
OBJECT_TYPE column
PLAN_TABLE table, 9-23
OPEN_CURSORS initialization parameter, 13-13
increasing cursors for each session, 14-34
operating system
data cache, 16-2
monitoring disk I/O, 22-8
OPERATION column
PLAN_TABLE table, 9-23, 9-27
OPTIMAL parameter, 18-3
optimization
choosing the approach, 1-6
conversion of expressions and predicates, 2-2
cost calculation, 1-10
cost-based, 1-10
cost-based and choosing an access path, 1-37
cost-based and histograms, 3-20
cost-based and star queries, 1-44
cost-based and user-defined costs, 1-63
cost-based examples, 1-37
cost-based on remote databases, 2-14
described, 1-3
DISTINCT, 2-37
distributed SQL statements, 2-13
extensible optimizer, 1-61
fast-response method, 1-9
GROUP BY views, 2-37
hints, 1-8, 1-34, 1-35
manual, 1-8
merging complex views, 2-37
merging views into statements, 2-35
non-null values for nulls, 2-45
operations performed, 1-5
rule-based, 8-2, 8-3, 8-16
selectivity of predicates, 3-2
selectivity of predicates and histograms, 3-20
selectivity of predicates for user-defined, 1-62
select-project-join views, 2-35
semi-joins, 1-43
statistics, 3-2
statistics for user-defined, 1-62
transitivity and, 2-25
without merging, 2-45
optimizer
cost calculation, 1-10
execution path, 11-3
goals, 1-5
introduction, 1-3
operations, 1-5
plan stability, 7-2
response time, 1-5
throughput, 1-5
OPTIMIZER column
PLAN_TABLE, 9-24
optimizer mode parameters
ALL_ROWS, 1-7
CHOOSE, 1-7
FIRST_ROWS, 1-7
FIRST_ROWS_n, 1-7
RULE, 1-7
OPTIMIZER_FEATURES_ENABLE initialization parameter, 1-34, 1-35, 1-56, 2-37
OPTIMIZER_INDEX_CACHING initialization parameter, 1-59
OPTIMIZER_INDEX_COST_ADJ initialization parameter, 1-59
OPTIMIZER_MAX_PERMUTATIONS initialization parameter, 1-59
OPTIMIZER_MODE initialization parameter, 1-6, 1-7, 1-60, 5-7, 8-2
hints affecting, 1-8
OPTIONS column
PLAN_TABLE table, 9-23
Oracle Forms, 10-6
control of parsing and private SQL areas, 14-27
Oracle Net Configuration Assistant, 23-14
Oracle Performance Manager
illustration, 20-5
Oracle Real Application Clusters
and Statspack, 21-25
Oracle SQL Analyze
illustration, 6-3
Oracle Trace, 12-1
accessing collected data, 12-3
binary files, 12-3
collection results, 12-12
collections, 12-8
command-line interface, 12-3
deleting files, 12-7
deprecated, xxxvii
deprecated in future release, xxxvii
duration events, 12-2
events, 12-2
FORMAT statement, 12-3
formatter tables, 12-3
parameters, 12-7
point events, 12-2
reporting utility, 12-14
START statement, 12-3, 12-4
STOP statement, 12-3, 12-6
ORACLE_TRACE_COLLECTION_NAME initialization parameter, 12-7
ORACLE_TRACE_COLLECTION_PATH initialization parameter, 12-8
ORACLE_TRACE_COLLECTION_SIZE initialization parameter, 12-8
ORACLE_TRACE_ENABLE initialization parameter, 12-8, 12-35
ORACLE_TRACE_FACILITY_NAME initialization parameter, 12-8, 12-9
ORACLE_TRACE_FACILITY_PATH initialization parameter, 12-8
Oracle-managed files, 15-20
tuning, 15-20
ORDERED hint, 1-43, 5-23
ORDERED_PREDICATES hint, 5-38
OTHER column
PLAN_TABLE table, 9-25
OTHER_TAG column
PLAN_TABLE table, 9-24
outer joins, 1-51
non-null values for nulls, 2-45
Outline Editor
illustration, 7-7
outlines
CREATE OUTLINE statement, 7-5
creating and using, 7-4
execution plans and plan stability, 7-2
hints, 7-3
moving tables, 7-11
storage requirements, 7-4
using, 7-6
using to move to the cost-based optimizer, 7-13
viewing data for, 7-10
OUTPUT_IO item, 12-16
overloaded disks, 15-10

P

page table, 16-12
PAGEFAULT_IO item, 12-16
PAGEFAULTS item, 12-16
paging, 16-12
reducing, 14-4
parallel broadcast, 1-57
PARALLEL clause
CREATE INDEX statement, 13-11
RECOVER statement, 17-8
parallel execution
hints, 5-29
PARALLEL hint, 5-29
parallel joins
and PQ_DISTRIBUTE hint, 5-31
parallel recovery, 17-8
PARALLEL_MAX_SERVERS initialization parameter, 17-8
parameter files, 13-2
parameters
iSQL*Plus tuning, 11-13
SNAP and MODIFY_STATSPACK_PARAMETERS, 21-20
PARENT_ID column
PLAN_TABLE table, 9-24
Parse event, 12-15
parsing
Oracle Forms, 14-27
Oracle precompilers, 14-27
reducing unnecessary calls, 14-26
PARTITION_ID column
PLAN_TABLE table, 9-25
PARTITION_START column
PLAN_TABLE table, 9-24
PARTITION_STOP column
PLAN_TABLE table, 9-25
PARTITION_VIEW_ENABLED initialization parameter, 1-60
partitioned objects
and EXPLAIN PLAN statement, 9-12
partitioning
distribution value, 9-26
examples of, 9-12
examples of composite, 9-14
hash, 9-12
range, 9-12
start and stop columns, 9-13
partitions
statistics, 3-4
partition-wise joins
full, 9-17
full, and EXPLAIN PLAN output, 9-17
partial, and EXPLAIN PLAN output, 9-16
PCTFREE parameter, 13-8, 22-22
PCTINCREASE parameter, 18-4
PCTUSED parameter, 22-22
performance
generating reports, 21-9
mainframe, 16-8
NT, 16-7
of SQL statements, 11-2
running reports, 21-3, 21-9
UNIX-based systems, 16-7
viewing execution plans, 1-18
Performance Monitor
NT, 16-12
PERFSTAT user, 21-3, 21-4, 21-15
PGA_AGGREGATE_TARGET initialization parameter, 13-11, 14-50
physical reads statistic, 14-10
PhysicalTX event, 12-15
plan stability, 7-2
limitations of, 7-2
preserving execution plans, 7-2
procedures for the cost-based optimizer, 7-12
use of hints, 7-2
PLAN_HASH_VALUE
V$SQL view column, 24-45
PLAN_TABLE table
BYTES column, 9-24
CARDINALITY column, 9-24
COST column, 9-24
creating, 9-4, 11-2
DISTRIBUTION column, 9-25
ID column, 9-24
OBJECT_INSTANCE column, 9-23
OBJECT_NAME column, 9-23
OBJECT_NODE column, 9-23
OBJECT_OWNER column, 9-23
OBJECT_TYPE column, 9-23
OPERATION column, 9-23
OPTIMIZER column, 9-24
OPTIONS column, 9-23
OTHER column, 9-25
OTHER_TAG column, 9-24
PARENT_ID column, 9-24
PARTITION_ID column, 9-25
PARTITION_START column, 9-24
PARTITION_STOP column, 9-25
POSITION column, 9-24
REMARKS column, 9-23
SEARCH_COLUMNS column, 9-24
STATEMENT_ID column, 9-23
TIMESTAMP column, 9-23
PL/SQL
deterministic functions, 2-28
PLUSTRACE
creating role, 11-2
granting role, 11-3
PLUSTRACE role, 11-2
point events
in Oracle Trace, 12-2, 12-15
POOL attribute, 19-4
POSITION column
PLAN_TABLE table, 9-24
PQ_DISTRIBUTE hint, 5-31
precompilers
control of parsing and private SQL areas, 14-27
predicates
pushing into a view, 2-38, 2-43
pushing into a view examples, 2-39, 2-41
selectivity, 3-2
selectivity estimates and histograms, 3-20
selectivity for user-defined, 1-62
PRIMARY KEY constraint, 4-8
primary keys
optimization, 2-34
searches, 8-6
PRIVATE_SGA variable, 14-38
procedures
DBMS_JOB, 21-8
DBMS_JOB.INTERVAL, 21-9
deterministic functions, 2-28
STATSPACK.MODIFY_STATSPACK_PARAMETER, 21-16, 21-19
STATSPACK.SNAP, 21-7, 21-8, 21-19
processes
dispatcher process configuration, 19-4
priority, 16-5
scheduler, 16-5
scheduling, 16-13
PROCESSES initialization parameter, 13-14
program global area (PGA)
direct path read, 22-33
direct path write, 22-35
shared servers, 14-36
pseudocolumns
ROWNUM and optimizing view queries, 2-36, 2-45
ROWNUM cannot use indexes, 8-15
PUSH_PRED hint, 5-37

Q

queries
avoiding the use of indexes, 4-6
compound and optimization of, 2-48
compound converted to ORs, 2-30
compound with ORs converted to, 8-17
ensuring the use of indexes, 4-6
optimizing IN subquery, 2-37
SAMPLE clause and cost-based optimization, 1-4
star queries, 1-44
tracing, 11-7
QUERY_REWRITE_ENABLED initialization parameter, 1-60

R

range
distribution value, 9-26
range partitions, 9-12
examples of, 9-12
read consistency, 22-20
read events
direct path, 22-33
read wait events
scattered, 22-29
REBUILD clause, 4-7
RECOVER statement
PARALLEL clause, 17-8
recovery
parallel intratransaction recovery, 17-19
parallel processes for, 17-8
PARALLEL_MAX_SERVERS initialization parameter, 17-8
setting number of processes to use, 17-8
RECOVERY_PARALLELISM initialization parameter, 17-8
recursive calls, 10-14
RECYCLE cache, 14-14
REDO BUFFER ALLOCATION RETRIES statistic, 14-47
redo logs, 13-5
mirroring, 15-19
placement on disk, 15-17
sizing, 13-5
reducing
contention with dispatchers, 19-4
contention with operating system processes, 16-5
contention with shared servers, 19-5
data dictionary cache misses, 14-34
paging and swapping, 14-4
rollback segment contention, 18-3
unnecessary parse calls, 14-26
RELEASE_CURSOR clause, 14-27
REMARKS column
PLAN_TABLE table, 9-23
removing
data, 21-23
snapshots, 21-23
REPORT_NAME variable, 21-12
reports
performance, 21-3, 21-9
SPREPORT.SQL, 21-9
SPREPSQL.SQL, 21-10
Statspack, 21-10
request modes, 24-19
resource bottlenecks, 22-26
resource wait events, 22-31
response time
cost-based approach, 1-7
optimizer goal, 1-5
optimizing, 1-5, 5-7
REWRITE hint, 5-18
rollback segments, 22-21
assigning to transactions, 18-3
choosing how many, 18-3
creating, 18-3
rollback tablespaces, 13-6
rollbacks
fast-start on-demand, 17-18
fast-start parallel, 17-18
round-robin
distribution value, 9-26
row locking, 13-8
row sampling, 3-4
row sources, 1-22
row transaction locks, 24-18
ROWID hint, 5-11
rowids
mapping to bitmaps, 4-16
table access by, 1-27
ROWNUM pseudocolumn
cannot use indexes, 8-15
optimizing view queries, 2-36, 2-45
rows
row sources, 1-22
rowids used to locate, 1-27, 8-4
setting number retrieved at one time, 11-10
RowSource event, 12-2, 12-15
RULE
optimizer mode parameter, 1-7
RULE hint
OPTIMIZER_MODE and, 1-8
rule-based optimization, 8-2, 8-3

S

SAMPLE BLOCK clause, 1-36
access path, 1-36
access path and hints cannot override, 1-37
SAMPLE clause, 1-36
access path, 1-36
access path and hints cannot override, 1-37
cost-based optimization, 1-4
sample table scans, 1-36
hints cannot override, 1-37
sar UNIX command, 16-12
scans
bounded range, 8-10
cluster, 8-4, 8-5, 8-6, 8-7
cluster key, 8-7
composite index, 8-8
full table, 8-14
full table using rule-based optimizer, 8-14
hash cluster, 8-5, 8-7
index, 1-28
index bounded range, 8-10
index cluster key, 8-7
index joins, 1-35
index of type bitmap, 1-35
index restrictions, 8-14
index with ORDER BY, 8-13
MAX or MIN of index, 8-13
range, 8-8
range of MAX or MIN, 8-13
range with ORDER BY, 8-13
sample table, 1-36
sample table and hints cannot override, 1-37
single-column index, 8-8
unbounded range, 8-11
unbounded range index, 8-11
unique, 8-6, 8-7
scattered read wait events, 22-29
actions, 22-30
schemas
star schemas, 1-44
SCPU item, 12-16
scripts
registering automatically, 11-9
SPAUTO.SQL, 21-9
SPCPKG.SQL, 21-6
SPCREATE.SQL, 21-5
SPCTAB.SQL, 21-6
SPCUSR.SQL, 21-6
SPPURGE.SQL, 21-23
SPTRUNC.SQL, 21-25
Statspack documentation scripts, 21-28
Statspack installation scripts, 21-27
Statspack performance data maintenance scripts, 21-28
Statspack reporting and automation scripts, 21-27
Statspack supplied scripts, 21-26
upgrading Statspack scripts, 21-27
SEARCH_COLUMNS column
PLAN_TABLE table, 9-24
segment-level statistics, 22-14
SELECT statement
SAMPLE clause, 1-36
SAMPLE clause and access path, 1-36, 1-37
SAMPLE clause and cost-based optimization, 1-4
selectivity, 3-2
histograms, 3-20
improving for an index, 4-4
indexes, 4-3, 4-6
SQL statement predicate, 3-2
user-defined, 1-62
select-project-join views, 2-35
semi-joins, 1-43
transformations not allowed, 1-44
sequential read wait events
actions, 22-32
SERVEROUTPUT
tuning, 11-10
Session Data Unit (SDU), 23-13
session id, 21-19
session memory statistic, 14-37
SESSION_CACHED_CURSORS initialization parameter, 14-40
SESSIONS initialization parameter, 13-14
SET AUTOTRACE, 11-2
SET command
APPINFO variable, 11-9
ARRAYSIZE variable, 11-10
SET TRANSACTION statement, 18-3
setting
system variables for SQL*PLUS performance, 11-9
SGA size, 14-47
shared server
performance issues, 19-2
reducing contention, 19-2
tuning, 19-2
tuning memory, 14-35
shared SQL areas
memory allocation, 14-34
SHARED_POOL_RESERVED_SIZE initialization parameter, 14-42
SHARED_POOL_SIZE initialization parameter, 13-14, 14-34, 14-42
allocating library cache, 14-34
tuning the shared pool, 14-38
sharing data, 21-22
SHOW SGA statement, 14-5
sizing redo logs, 13-5
snapshot levels, 21-15, 21-17
snapshot thresholds, 21-15, 21-17
snapshots
begin and end, 21-10
databases identifier (DBID), 21-3
deleting, 21-23
instance numbers, 21-3
levels, 21-15, 21-17
removing, 21-23
SNAP_ID, 21-3
Statspack, 21-3
taken by Statspack, 21-3
taking snapshots, 21-7
thresholds, 21-15, 21-17
SOME operator, 2-22
sort areas
memory allocation, 14-69
sort merge joins, 1-49
access path, 8-12
cost-based optimization, 1-42
example, 8-12
SORT_AREA_SIZE initialization parameter, 1-60, 4-14, 13-12
configuring, 14-66
cost-based optimization and, 1-42
See also PGA_AGGREGATE_TARGET initialization parameter
tuning sorts, 14-70
use PGA_AGGREGATE_TARGET, 1-42, 1-60, 13-12
sorts
(disk) statistic, 14-68
(memory) statistic, 14-68
avoiding on index creation, 14-71
space transaction locks, 24-18
SPAUTO.SQL script, 21-8, 21-9, 21-27
SPCPKG.LIS output file, 21-6
SPCPKG.SQL script, 21-6, 21-27
SPCREATE.SQL script, 21-5, 21-27
running, 21-6
SPCTAB.LIS output file, 21-6
SPCTAB.SQL script, 21-6, 21-27
SPCUSR.LIS output file, 21-6
SPCUSR.SQL script, 21-6, 21-27
SPDOC.TXT
Statspack documentation, 21-28
SPDROP.SQL script, 21-26, 21-27
SPDTAB.LIS output file, 21-26
SPDTAB.SQL script, 21-26, 21-27
SPDUSR.LIS output file, 21-26
SPDUSR.SQL script, 21-26, 21-27
SPPURGE.SQL script, 21-23, 21-28
SPREPINS.SQL script, 21-27
SPREPORT.SQL script, 21-27
performance report, 21-9
running the script, 21-10
SPREPSQL.SQL script, 21-27
performance report, 21-10
SPTRUNC.SQL script, 21-25, 21-28
SPUEXP.PAR parameter file, 21-28
SPUP816.SQL script, 21-27
SPUP817.SQL script, 21-27
SPUP90.SQL script, 21-27
SQL functions
optimizing view queries, 2-43
SQL Parse event, 12-2
SQL statements
avoiding the use of indexes, 4-6
complex, 2-33
complex optimizing, 2-33
converting examples, 2-30, 8-17
distributed optimization, 2-13
ensuring the use of indexes, 4-6
execution plans of, 1-18
modifying indexed data, 4-4
optimization of complex statements, 2-33
optimization of distributed, 2-13
optimizing complex, 2-33
processing overview, 1-2
thresholds, 21-15, 21-17
waiting for I/O, 22-31
SQL trace facility, 10-2, 10-6
example of output, 10-16
output, 10-13
statement truncation, 10-15
steps to follow, 10-3
trace files, 10-5
SQL*Loader, 13-11
SQL*Net message from client idle events, 22-26
SQL*Net message from dblink wait events, 22-27
SQL*Plus
autotrace, 11-1
BEGIN_SNAP variable, 21-12
CLEAR TIMING command, 11-7
DEFAULT_TABLESPACE variable, 21-6
END_SNAP variable, 21-12
REPORT_NAME variable, 21-12
statistics, 11-4
system variables influencing performance, 11-9
TEMPORARY_TABLESPACE variable, 21-6
TIMING command, 11-7
SQL_STATEMENT column
TKPROF_TABLE, 10-18
SQL_TRACE
initialization parameter, 10-6
SQL.BSQ file, 13-4
SQLSegment event, 12-15
ST locks, 24-18
standard resource utilization items, 12-16
STAR hint, 5-23
star joins, 1-44
star query, 1-44
star transformation, 5-21
STAR_TRANSFORMATION hint, 5-21
STAR_TRANSFORMATION_ENABLED initialization parameter, 1-61, 5-22
start columns
in partitioning and EXPLAIN PLAN statement, 9-13
START statement in Oracle Trace, 12-3, 12-4
STATEMENT_ID column
PLAN_TABLE table, 9-23
statistics
automated collecting, 21-8
automated gathering, 21-8
collecting, 11-7, 21-8
collection interval, 21-9
consistent gets, 14-9, 18-3
database server, 11-4
db block gets, 14-9, 18-3
estimates and block sampling, 3-4
estimates and row sampling, 3-4
exporting and importing, 3-2
extensible optimization, 1-61
from B-tree or bitmap index, 3-9
gathering with DBMS_STATS package, 3-6
generating, 3-3
generating and managing with DBMS_STATS, 3-5
generating for cost-based optimization, 3-3
HIGH_VALUE and LOW_VALUE, 1-38
max session memory, 14-37
optimizer mode, 1-7
optimizer use of, 1-10, 3-2
partitions and subpartitions, 3-4
physical reads, 14-10
segment-level, 22-14
selectivity of predicates, 3-2
selectivity of predicates and histograms, 3-20
selectivity of predicates for user-defined, 1-62
session memory, 14-37
shared server processes, 19-5
sorts (disk), 14-68
sorts (memory), 14-68
SQL*Plus, 11-4
user-defined statistics, 1-62
Statspack
and Oracle Real Application Clusters, 21-25
automatic statistics gathering, 21-8
compared with BSTAT/ESTAT, 20-7, 21-3
DBID, 21-25
documentation, 21-28
exporting data, 21-22
installation scripts, 21-27
installing in batch mode, 21-6
installing interactively, 21-4
INSTANCE_NUMBER, 21-25
level 7 snapshot, 21-18
performance data maintenance scripts, 21-28
removing, 21-26
reporting and automation scripts, 21-27
running reports, 21-10
scripts, 21-26
sharing data, 21-22
SNAP_ID, 21-3
snapshots, 21-3
space requirements, 21-4
SPCREATE.SQL, 21-5
SPDOC.TXT, 21-28
uninstalling, 21-26
upgrading scripts, 21-27
using DBMS_JOB to gather statistics, 21-8
STATSPACK.MODIFY_STATSPACK_PARAMETER procedure, 21-16, 21-19
STATSPACK.SNAP procedure, 21-7, 21-8, 21-19
stop columns
in partitioning and EXPLAIN PLAN statement, 9-13
STOP statement in Oracle Trace, 12-3, 12-6
STORAGE clause
OPTIMAL parameter, 18-3
stored outlines
creating and using, 7-4
execution plans and plan stability, 7-2
hints, 7-3
moving tables, 7-11
storage requirements, 7-4
using, 7-6
viewing data for, 7-10
striping, 15-4
manual, 15-16
subpartitions
statistics, 3-4
subqueries
converting to joins, 2-33
NOT IN, 1-43
optimizing IN subquery, 2-37
subquery unnesting, 6-21
substitution variables
parsing, 11-10
swapping, 16-12
reducing, 14-4
switching processes, 16-13
System Global Area tuning, 14-4
system statistics
gathering, 3-6
system variables
influencing SQL*Plus performance, 11-9

T

tables
creating, 13-7
dimensions and star queries, 1-44
fact tables and star queries, 1-44
formatter in Oracle Trace, 12-3
full scans, 22-34
lookup tables, 1-44
placement on disk, 15-16
setting storage options, 13-7
tablespaces, 13-6
creating, 13-6
dictionary managed, 21-4
locally managed, 21-4
rollback, 13-6
temporary, 13-6
TCP.NODELAY parameter, 23-14
temporary tablespaces, 13-6
TEMPORARY_TABLESPACE variable, 21-6
thrashing, 16-12
thread, 16-5
thresholds
SQL statement, 21-17
SQL statements, 21-15
throughput
cost-based approach, 1-7
optimizer goal, 1-5
optimizing, 1-5, 5-7
TIMED_STATISTICS initialization parameter, 21-7
SQL Trace, 10-4
TIMESTAMP column
PLAN_TABLE table, 9-23
TIMING command
SQL*Plus, 11-7
TKPROF program, 10-3, 10-6
editing the output SQL script, 10-17
example of output, 10-16
generating the output SQL script, 10-17
syntax, 10-8
using the EXPLAIN PLAN statement, 10-11
TKPROF_TABLE, 10-18
querying, 10-18
TM locks, 24-18
Trace, Oracle, 12-1
tracing
queries, 11-7
tracing statements
for performance statistics, 11-5
for query execution path, 11-5
using a database link, 11-6
with parallel query option, 11-7
transactions
assigning rollback segments, 18-3
TRIMOUT
tuning, 11-10
TRIMSPOOL
tuning, 11-11
truncating data, 21-25
tuning
DEFINE OFF, 11-10
FLUSH OFF, 11-10
iSQL*Plus parameters, 11-13
latches, 24-12
logical structure, 4-2
memory allocation, 14-5
resource contention, 22-1
SERVEROUTPUT, 11-10
SET ARRAYSIZE, 11-10
shared server, 19-2
SQL statements, 11-2
System Global Area (SGA), 14-4
TRIMOUT, 11-10
TRIMSPOOL, 11-11
TX locks, 24-18

U

UCPU item, 12-16
UL locks, 24-19
UNION ALL operator
examples, 2-31, 2-33, 2-48
optimizing view queries, 2-36
transforming OR into, 2-30, 8-17
UNION operator
examples, 2-39, 2-49
optimizing view queries, 2-36
UNIQUE constraint, 4-8
unique keys
optimization, 2-34
searches, 8-6
uniqueness, 4-8
UNIX system performance, 16-7
UNNEST hint, 5-36
upgrade
to the cost-based optimizer, 7-14
USE_CONCAT hint, 5-18
USE_MERGE hint, 5-25
USE_NL hint, 5-24
USE_STORED_OUTLINES parameter, 7-6
user defined locks, 24-19
user global area (UGA)
shared servers, 14-35, 19-2
V$SESSTAT, 14-37
USER_DUMP_DEST initialization parameter, 10-4
SQL Trace, 10-4
USER_ID column
TKPROF_TABLE, 10-19
USER_OUTLINE_HINTS view
stored outline hints, 7-10
USER_OUTLINES view
stored outlines, 7-10
USER_TAB_COL_STATISTICS view, 1-38
USER_TAB_COLUMNS view, 1-38
USER_TABLES view, 1-38
user-defined costs, 1-63
UTLCHN1.SQL script, 22-22
utlxplp.sql
SQL script for viewing EXPLAIN PLANs, 1-19
utlxpls.sql
SQL script for viewing EXPLAIN PLANs, 1-19

V

V$BH view, 14-15
V$BUFFER_POOL_STATISTICS view, 14-15
V$DATAFILE view, 24-7
V$DB_CACHE_ADVICE view, 14-6, 14-9, 14-11, 14-12, 14-15
V$DB_OBJECT_CACHE view, 24-5
V$FAST_START_SERVERS view, 17-19
V$FAST_START_TRANSACTIONS view, 17-19
V$FILESTAT view, 24-6
V$INSTANCE_RECOVERY view, 17-9
V$LATCH view, 24-9
V$LATCH_CHILDREN view, 24-13
V$LATCHHOLDER view, 24-13
V$LIBBRARY_CACHE_MEMORY view, 24-16
V$LIBRARYCACHE view, 24-15
NAMESPACE column, 14-29
V$LOCK view, 24-17
V$MTTR_TARGET_ADVICE view, 24-21
V$MYSTAT view, 24-22
V$OPEN_CURSOR view, 24-23
V$PARAMETER view, 24-25
V$PROCESS view, 24-26
V$QUEUE view, 19-5
V$ROLLSTAT view, 24-28
V$ROWCACHE view, 24-29
GETMISSES column, 14-34
GETS column, 14-34
performance statistics, 14-32
V$RSRC_CONSUMER_GROUP view, 22-8
V$SEGMENT_STATISTICS view, 24-31
V$SEGSTAT view, 24-32
V$SEGSTAT_NAME view, 24-32
V$SESSION view, 24-33
V$SESSION_EVENT view, 24-36
network information, 23-6
V$SESSION_WAIT view, 22-13, 24-37
network information, 23-6
V$SESSTAT view, 22-8, 24-41
network information, 23-6
statistics, 24-42
using, 14-37
V$SHARED_POOL_ADVICE view, 24-45
V$SHARED_POOL_RESERVED view, 14-42
V$SQL view, 24-45
PLAN_HASH_VALUE column, 24-45
V$SQL_PLAN view, 24-46
V$SQL_PLAN_STATISTICS view, 24-51
V$SQL_PLAN_STATISTICS_ALL view, 24-53
V$SQLAREA view, 24-57
V$SQLTEXT view, 24-59
V$STATISTICS_LEVEL view, 24-61
V$SYSSTAT view, 24-61
redo buffer allocation, 14-47
statistics, 24-63
tuning sorts, 14-68
using, 14-9
V$SYSTEM_EVENT view, 24-67
V$SYSTEM_PARAMETER view, 24-25
V$UNDOSTAT view, 13-15, 24-69
V$WAITSTAT view, 22-13, 24-70
VARCHAR datatype, 13-3
VARCHAR2 datatype, 13-3
variables
bind variables and optimization, 1-38
views
complex view merging, 2-37
counter/accumulator, 24-2
current state views, 24-2
dynamic performance, 24-2
histograms, 3-25
information views, 24-4
non-null values for nulls, 2-45
select-project-join views, 2-35
statistics, 3-15
vmstat UNIX command, 16-12

W

wait events
buffer busy waits, 22-27
contention wait events, 22-41
direct path, 22-35
event timings, 21-21
free buffer waits, 22-39
idle wait events, 22-49
log file switch, 22-46
network communication wait events, 22-25
reasons for, 24-70
resource wait events, 22-31
time units, 21-20