Oracle9i Database Performance Tuning Guide and Reference -- Index

access paths

cluster join, 8-6

cluster scans, 1-41

composite index, 8-8

defined, 1-8

execution plans, 1-5

hash cluster key, 8-6

hash scans, 1-41

index scans, 1-30

indexed cluster key, 8-7

single row by cluster join, 8-4

single row by hash cluster key (with unique key), 8-4

single row by rowid, 8-3

single row by unique or primary key, 8-5

ALL operator, 2-23

ALL_ROWS hint, 1-13, 5-7

allocation

of memory, 14-2

ALTER INDEX statement, 4-7

ALTER SESSION statement

examples, 10-6

OPTIMIZER_GOAL parameter, 1-12

SET SESSION_CACHED_CURSORS clause, 14-40

ALTER SYSTEM statement

DISPATCHERS initialization parameter, 19-3

ANALYZE statement, 22-22

creating histograms, 3-21

AND_EQUAL hint, 4-6, 5-17

anti-joins, 1-67

ANY operator, 2-22

APPEND hint, 5-34

APPINFO clause, 11-11

applications

data warehousing

star queries, 1-68

ApplReg event, 12-15

array interface, 23-13

ARRAYSIZE variable, 11-11

automatic segment-space management, 15-22, 22-29

automatic undo management, 18-2

AUTOTRACE variable, 11-2

BEGIN_SNAP variable, 21-11

BETWEEN comparison operator, 2-24

binary files

formatting using Oracle Trace, 12-3

bind variables, 14-23

optimization, 1-43

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

when to use, 4-12

bitmap join indexes, 4-19

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-27

broadcast

distribution value, 9-26

B-tree indexes, 4-14, 4-17

buffer busy wait events, 22-28

actions, 22-29

buffer caches

reducing buffers, 14-13, 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

CACHE hint, 5-35

caching tables

automatic caching of small tables, 5-36

CARDINALITY column

PLAN_TABLE table, 9-24

CATALOG.SQL script, 13-5

CATPROC.SQL script, 13-5

chained rows, 22-22

CHAR datatype, 13-3

character set database option, 13-3

checkpoints

choosing checkpoint frequency, 17-3

CHOOSE hint, 1-13, 5-9

client/server applications, 16-13

CLUSTER hint, 5-12

clusters, 4-20

hash

scans of, 1-41, 8-4, 8-6

index

scans of, 8-7

joins and, 8-4, 8-6

scans of, 1-41, 8-4

hash, 8-4, 8-6

joins, 8-6

collections, 12-8

columns

pseudocolumns

ROWNUM, 2-37, 2-46, 8-15

selectivity, 3-2

histograms, 3-20

to index, 4-3

command files

registering, 11-11

complex view merging, 2-37

composite indexes, 4-4

composite partitioning

examples of, 9-14

CONNECT BY clause

optimizing view queries, 2-37

Connection event, 12-15

connection manager, 23-14

connection pooling, 19-4

consistency

read, 22-20

consistent gets statistic, 14-10, 18-2

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-43

context switches, 16-13

CONTROL_FILES initialization parameter, 13-12

COST column

PLAN_TABLE table, 9-24

cost-based optimizations, 1-16

extensible optimization, 1-74

histograms, 3-20

procedures for plan stability, 7-11

selectivity of predicates, 3-2

histograms, 3-20

user-defined, 1-76

star queries, 1-68

statistics, 3-2

user-defined, 1-75

upgrading to, 7-12

user-defined costs, 1-76

counter/accumulator views, 24-2

CPU_COUNT initialization parameter, 17-18

CPUs

utilization, 16-12

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, 12-17

current mode

TKPROF, 10-13

current state views, 24-2

CURSOR_NUM column

TKPROF_TABLE table, 10-19

CURSOR_SHARING initialization parameter, 1-71, 14-24, 14-45

CURSOR_SHARING_EXACT hint, 5-39

CURSOR_SPACE_FOR_TIME initialization parameter

setting, 14-39

data cache, 16-2

data dictionary, 14-34

scripts, 13-4

CATALOG.SQL, 13-5

CATPROC.SQL, 13-5

statistics in, 3-15

views used in optimization, 3-15

data indexing, 13-10

data loading, 13-10

Data Viewer

tips on using

collect data for specific wait events, 12-36

data warehousing

dimensions, 1-68

star queries, 1-68

database

buffers, 14-12, 14-35

Database Connection event, 12-2

database creation

manual, 13-2

parameters, 13-2

with Installer, 13-2

database identifier (DBID), 21-3

database options, 13-3

character set, 13-3

location of initial datafile, 13-3

national character set, 13-3

SQL.BSQ file, 13-3

Database Resource Manager, 16-6, 16-7, 16-11, 22-8

databases

creating, 13-2

distributed

statement optimization on, 2-13

datatypes

CHAR, 13-3

NCHAR, 13-3

NVARCHAR, 13-3

NVARCHAR2, 13-3

user-defined

statistics, 1-75

VARCHAR, 13-3

VARCHAR2, 13-3

DATE_OF_INSERT column

TKPROF_TABLE table, 10-19

db block gets statistic, 14-10, 18-2

DB file scattered read wait events, 22-30

actions, 22-31

DB file sequential read wait events

actions, 22-33

DB file sequential/scattered read wait events, 22-30, 22-32

DB_BLOCK_BUFFERS initialization parameter, 14-13, 14-35

DB_BLOCK_SIZE initialization parameter, 13-2, 13-13, 15-14

DB_CACHE_ADVICE parameter, 14-12

DB_CACHE_SIZE initialization parameter, 13-13, 14-13

DB_DOMAIN initialization parameter, 13-12

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 1-72, 15-13, 15-14, 15-15, 22-30

cost-based optimization, 1-67

DB_NAME initialization parameter, 13-12

DB_nK_CACHE_SIZE initialization parameter, 14-12

DB_WRITER_PROCESSES initialization parameter, 22-41

DBA_OBJECTS view, 14-16

DBID (database identifier), 21-3

DBMS_APPLICATION_INFO package, 11-11

DBMS_JOB procedure, 21-8

DBMS_JOB.INTERVAL procedure, 21-8

DBMS_OUTLN package, 7-4

DBMS_OUTLN_EDIT package, 7-4

DBMS_SHARED_POOL package, 14-43

DBMS_STATS package, 3-5, 3-6

creating histograms, 3-21

default cache, 14-14

DEFAULT_TABLESPACE variable, 21-5

deleting data, 21-21

deleting snapshots, 21-21

DEPTH column

TKPROF_TABLE table, 10-19

deterministic functions, 2-29

dictionary managed tablespaces, 21-3

dimensions

star joins, 1-68

star queries, 1-68

direct path read events, 22-34

actions, 22-35

causes, 22-35

direct path wait events, 22-36

direct path write events

actions, 22-36

causes, 22-36

direct-path INSERT, 5-34

disabled constraints, 4-8

Disconnect event, 12-15

disks

contention, 15-3

monitoring operating system file activity, 22-8

dispatcher processes, 19-4

DISPATCHERS initialization parameter, 19-3, 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-29

distribution

hints for, 5-31

DISTRIBUTION column

PLAN_TABLE table, 9-25

DML locks, 24-17

domain indexes

and EXPLAIN PLAN, 9-19

extensible optimization, 1-74

user-defined statistics, 1-75

using, 4-19

duration events

in Oracle Trace, 12-2, 12-15

DYNAMIC_SAMPLING hint, xxxii, 5-39

enabled constraints, 4-8

END_SNAP variable, 21-11

enforced constraints, 4-8

enqueue wait events

actions, 22-38

EPC_ERROR.LOG file, 12-37

equijoins, 6-9

ErrorStack event, 12-15

event timings, 21-20

examples

ALTER SESSION statement, 10-6

concurrently creating tablespaces, 13-7

CREATE DATABASE script, 13-4

CREATE INDEX statement, 14-71

creating indexes efficiently, 13-11

executing required data dictionary scripts, 13-5

execution plan, 8-20

EXPLAIN PLAN output, 8-20, 10-16

full table scan, 8-21

indexed query, 8-21

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

V$LATCH view, 24-10

V$LATCH_CHILDREN view, 24-12

V$LATCHHOLDER view, 24-13

V$LIBRARYCACHE view, 24-15

V$LOCK view, 24-19

V$OPEN_CURSOR view, 24-22, 24-23

V$PROCESS view, 24-26

V$ROLLSTAT view, 24-28

V$SESSION view, 24-34

V$SESSION_EVENT view, 24-36

V$SESSION_WAIT view, 24-38

V$SQLAREA view, 24-57, 24-58

V$SQLTEXT view, 24-59

Execute event, 12-15

execution plans

accessing views, 2-40, 2-43, 2-44

complex statements, 2-34

compound queries, 2-49, 2-50, 2-51

examples, 2-34, 8-20, 10-7

execution sequence of, 1-9

joining views, 2-47

joins, 1-44

OR operators, 2-32, 8-18

overview of, 1-5

plan stability, 7-2

preserving with plan stability, 7-2

TKPROF, 10-8, 10-11

viewing, 1-5

EXPLAIN PLAN statement

access paths, 1-29, 8-3, 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-19

and full partition-wise joins, 9-17

and partial partition-wise joins, 9-16

and partitioned objects, 9-11

examples of output, 8-20, 10-16

invoking with the TKPROF program, 10-11

PLAN_TABLE table, 9-4

restrictions, 9-22

Export utility

copying statistics, 3-2

exporting data, 21-21

extensible optimization, 1-74

user-defined costs, 1-76

user-defined selectivity, 1-76

user-defined statistics, 1-75

FACT hint, 5-23

fact tables

star joins, 1-68

star queries, 1-68

fast full index scans, 1-39

FAST_START_IO_TARGET initialization parameter, 17-4, 17-8

FAST_START_MTTR_TARGET initialization parameter, 17-4, 17-8, 17-12

FAST_START_PARALLEL_ROLLBACK initialization parameter, 17-18

fast-start checkpoints

FAST_START_MTTR_TARGET initialization parameter, 17-5

LOG_CHECKPOINT_INTERVAL initialization parameter, 17-6

LOG_CHECKPOINT_TIMEOUT initialization parameter, 17-6

fast-start on-demand rollback, 17-17

fast-start parallel rollback, 17-17

features, new, xxxi

Fetch event, 12-15

FIRST_ROWS hint, 1-13

FIRST_ROWS(n) hint, 5-7

FORCE_UNION_REWRITE hint, xxxii, 5-20

FORMAT statement

in Oracle Trace, 12-3

formatter tables

in Oracle Trace, 12-3

free buffer wait events, 22-40

FULL hint, 4-6, 5-11

full partition-wise joins, 9-17

full table scans, 8-14, 8-21, 22-35

rule-based optimizer, 8-14

function-based indexes, 4-10

functions

PL/SQL

deterministic, 2-29

SQL

optimizing view queries, 2-44

user-defined

extensible optimization, 1-74

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, 11-4, 11-8

GROUP BY clause

NOSORT clause, 14-72

optimizing views, 2-37

hash

distribution value, 9-26

hash clusters

scans of, 1-41, 8-4, 8-6

HASH hint, 5-12

hash joins

index join, 1-40

hash partitions, 9-11

examples of, 9-12

HASH_AJ hint, 1-67, 5-28, 5-29

HASH_AREA_SIZE initialization parameter, 1-72

HASH_JOIN_ENABLED initialization parameter, 1-72

HASH_SJ hint, 1-68, 5-29

hashing, 4-21

HIGH_VALUE statistics, 1-42

hint

NL_AJ hint, 5-28

hints, 5-2

access paths, 5-10, 5-18

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-41

CHOOSE hint, 5-9

CLUSTER hint, 5-12

CURSOR_SHARING_EXACT hint, 5-39

degree of parallelism, 5-29

EXPAND_GSET_TO_UNION hint, 5-20

extensible optimization, 1-75

FACT hint, 5-23

FIRST_ROWS hint, 5-7

FIRST_ROWS(n) hint, 5-7

FORCE_UNION_REWRITE hint, 5-20

FULL hint, 4-6, 5-11

global, 5-44

HASH hint, 5-12

HASH_AJ hint, 5-28

HASH_SJ hint, 5-29

how to use, 5-2

INDEX hint, 4-6, 5-13, 5-24

INDEX_ASC hint, 5-14

INDEX_DESC hint, 5-15, 5-16

INDEX_FFS, 1-38

INDEX_JOIN, 1-40

join operations, 5-25

LEADING hint, 5-28

MERGE hint, 5-21

MERGE_AJ and HASH_AJ, 1-67

MERGE_AJ hint, 5-28

MERGE_SJ and HASH_SJ, 1-68

MERGE_SJ hint, 5-29

NL_AJ hint, 5-28

NL_SJ hint, 5-29

NO_EXPAND hint, 5-19

NO_FACT hint, 5-23

NO_INDEX, 4-6

NO_INDEX hint, 5-17

NO_MERGE hint, 5-22

NO_PUSH_PRED hint, 5-38

NO_PUSH_SUBQ, 5-38

NO_PUSH_SUBQ hint, 5-38

NO_UNNEST hint, 5-37

NOCACHE hint, 5-35

NOPARALLEL hint, 5-31

NOREWRITE hint, 5-21

optimization approach and goal, 5-6

ORDERED hint, 1-67, 5-24

overriding optimizer choice, 1-41

overriding OPTIMIZER_MODE and OPTIMIZER_GOAL, 1-13

PARALLEL hint, 5-30

parallel query option, 5-29

PQ_DISTRIBUTE hint, 5-31

PUSH_PRED hint, 5-37

PUSH_SUBQ hint, 5-38

REWRITE hint, 5-19

ROWID hint, 5-11

STAR hint, 5-24

UNNEST hint, 5-36

USE_CONCAT hint, 5-18

USE_MERGE hint, 5-26

USE_NL hint, 5-25

histograms, 3-20

number of buckets, 3-22

HOLD_CURSOR clause, 14-27

ID column

PLAN_TABLE table, 9-24

idle wait events, 22-50

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-13

index joins, 1-40

INDEX_ASC hint, 5-14

INDEX_COMBINE hint, 4-6, 4-15

INDEX_DESC hint, 5-15, 5-16

INDEX_FFS hint, 1-38, 1-39

INDEX_JOIN hint, 1-40

indexes

avoiding the use of, 4-6

bitmap, 4-12, 4-17

choosing columns for, 4-3

cluster

scans of, 8-7

composite, 4-4

scans of, 8-8

creating, 13-11

domain, 4-19

domain indexes

extensible optimization, 1-74

user-defined statistics, 1-75

dropping, 4-2

enforcing uniqueness, 4-8

ensuring the use of, 4-6

example, 8-21

function-based, 4-10

index joins, 1-40

modifying values of, 4-4

non-unique, 4-8

optimization and, 2-30, 8-17

placement on disk, 15-17

rebuilding, 4-7

re-creating, 4-7

scans of, 1-30

bounded range, 8-9

cluster key, 8-7

composite, 8-8

MAX or MIN, 8-12

ORDER BY, 8-13

restrictions, 8-14

single-column, 8-8

unbounded range, 8-10

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-12

initialization parameters

CONTROL_FILES, 13-12

CPU_COUNT, 17-18

DB_BLOCK_SIZE, 13-2, 13-13

DB_CACHE_SIZE, 13-13

DB_DOMAIN, 13-12

DB_FILE_MULTIBLOCK_READ_COUNT, 1-67

DB_NAME, 13-2, 13-12

FAST_START_PARALLEL_ROLLBACK, 17-18

in Oracle Trace, 12-7

INITRANS, 13-8

JAVA_POOL_SIZE, 13-13

JOB_QUEUE_PROCESSES, 21-8

LOG_ARCHIVE_XXX, 13-13

LOG_CHECKPOINT_INTERVAL, 17-6

LOG_CHECKPOINT_TIMEOUT, 17-6

OPEN_CURSORS, 13-12

OPTIMIZER_FEATURES_ENABLE, 1-38, 1-40, 2-37

OPTIMIZER_MODE, 1-11, 5-7, 8-2

PARALLEL_MAX_SERVERS, 17-7

PROCESSES, 13-13

RECOVERY_PARALLELISM, 17-7

SESSION_CACHED_CURSORS, 14-40

SESSIONS, 13-13

SHARED_POOL_SIZE, 13-13

SORT_AREA_SIZE, 1-66, 13-11

SQL_TRACE, 10-6

TIMED_STATISTICS, 21-6

USER_DUMP_DEST, 10-4

INIT.ORA file

ORACLE_TRACE_ENABLE parameter, 12-36

INITRANS initialization parameter, 13-8

IN-lists, 5-14, 5-18

input parameters, table, 21-18

INPUT_IO item, 12-16

INSERT statement

append, 5-34

installation scripts

SPCREATE, 21-5

instance configuration, 13-12

instance numbers, 21-3

instrumentation

of Oracle Server, 12-15

INTERSECT operator

example, 2-51

optimizing view queries, 2-37

intra transaction recovery, 17-18

I/O

and SQL statements, 22-32

balancing, 15-3

excessive I/O waits, 22-31

objects causing I/O waits, 22-32

iSQL*Plus Server Statistics, 11-12

items

cross-product, 12-17

standard resource utilization, 12-16

types of, 12-16

JAVA_POOL_SIZE initialization parameter, 13-13

JOB_QUEUE_PROCESSES initialization parameter, 21-8

joins

anti-joins, 1-67

cluster, 8-4

searches on, 8-6

convert to subqueries, 2-34

execution plans and, 1-44

index joins, 1-40

join order

execution plans, 1-5

selectivity of predicates, 1-76, 3-2, 3-20

nested loops

cost-based optimization, 1-66

optimization of, 8-15

outer

non-null values for nulls, 2-46

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-29

select-project-join views, 2-36

semi-joins, 1-67

sort-merge

cost-based optimization, 1-66

example, 8-12

star joins, 1-68

star queries, 1-68

KEEP cache, 14-14

keys

searches, 8-4

LARGE_POOL_SIZE initialization parameter, 14-36

latch free wait events

actions, 22-43

latches

tuning, 24-12

LEADING hint, 5-28

library cache

memory allocation, 14-34

LIKE operator, 2-22

Lmode modes, 24-18

load balancing, 15-3

loading data, 13-10

locally managed tablespaces, 21-3

location of initial datafile database option, 13-3

lock types, 24-17

ST (space transaction) locks, 24-18

TM (DML) locks, 24-17

TX (row transaction) locks, 24-17

UL (user defined) locks, 24-18

locking rows, 13-8

locks and lock holders

finding, 22-37

log buffer tuning, 14-47

log file switch wait events, 22-48

log writer processes

tuning, 15-18

LOG_ARCHIVE_XXX initialization parameter, 13-13

LOG_BUFFER initialization parameter, 14-47

setting, 14-48

LOG_CHECKPOINT_INTERVAL initialization parameter, 17-3

recovery time, 17-6

LOG_CHECKPOINT_TIMEOUT initialization parameter, 17-4

recovery time, 17-6

LogicalTX event, 12-15

lookup tables

star queries, 1-68

LOW_VALUE statistics, 1-42

LRU

aging policy, 14-13

latch contention, 22-47

manual database creation, 13-2

max session memory statistic, 14-37

MAX_DISPATCHERS initialization parameter, 19-3

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

memory allocation

importance, 14-2

library cache, 14-34

shared SQL areas, 14-34

sort areas, 14-69

tuning, 14-5

MERGE hint, 5-21

MERGE_AJ hint, 1-67, 5-28, 5-29

MERGE_SJ hint, 1-68, 5-29

merging complex views, 2-37

merging views into statements, 2-36

migrated rows, 22-22

Migration event, 12-15

MINUS operator

optimizing view queries, 2-37

mirroring

redo logs, 15-19

modes

Lmode, 24-18

request, 24-18

MTBF (mean time between failures)

tuning I/O, 15-2

multiple buffer pools, 14-13

NAMESPACE column

V$LIBRARYCACHE view, 14-29

national character set database option, 13-3

NCHAR datatype, 13-3

nested loop joins

cost-based optimization, 1-66

network

array interface, 23-13

detecting performance problems, 23-6

problem solving, 23-8

Session Data Unit, 23-14

tuning, 23-1

network communication wait events, 22-26

DB file sequential/scattered read wait events, 22-30, 22-32

SQL*Net message from Dblink, 22-27

new features, xxxi

NL_AJ hint, 5-28

NL_SJ hint, 5-29

NLS_SORT initialization parameter

ORDER BY access path, 8-13

NO_EXPAND hint, 5-19

NO_FACT hint, 5-23

NO_INDEX hint, 4-6, 5-17

NO_MERGE hint, 5-22

NO_PUSH_PRED hint, 5-38

NO_UNNEST hint, 5-37

NOAPPEND hint, 5-35

NOCACHE hint, 5-35

NOPARALLEL hint, 5-31

NOPARALLEL_INDEX hint, 5-34

NOREWRITE hint, 5-21

NOSORT clause, 14-71, 14-72

NOT IN subquery, 1-67

NOT operator, 2-24

NT performance, 16-8

nulls

non-null values for, 2-46

NUM_DISTINCT column

USER_TAB_COLUMNS view, 1-42

NUM_ROWS column

USER_TABLES view, 1-42

NVARCHAR datatype, 13-3

NVARCHAR2 datatype, 13-3

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-12

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-11

conversion of expressions and predicates, 2-2

cost-based, 1-16

choosing an access path, 1-41

examples of, 1-42

histograms, 3-20

remote databases and, 2-14

star queries, 1-68

user-defined costs, 1-76

described, 1-3

DISTINCT, 2-37

distributed SQL statements, 2-13

extensible optimizer, 1-74

GROUP BY views, 2-37

hints, 1-13, 1-38, 1-40

manual, 1-13

merging complex views, 2-37

merging views into statements, 2-36

non-null values for nulls, 2-46

operations performed, 1-4

rule-based, 8-2, 8-15

selectivity of predicates, 3-2

histograms, 3-20

user-defined, 1-76

select-project-join views, 2-36

semi-joins, 1-67

statistics, 3-2

user-defined, 1-75

transitivity and, 2-25

without merging, 2-46

optimizer, 1-3

plan stability, 7-2

OPTIMIZER column

PLAN_TABLE, 9-24

OPTIMIZER_FEATURES_ENABLE initialization parameter, 1-38, 1-40, 1-69, 2-37

OPTIMIZER_GOAL clause, 1-12

OPTIMIZER_GOAL initialization parameter, 1-73

OPTIMIZER_INDEX_CACHING initialization parameter, 1-72

OPTIMIZER_INDEX_COST_ADJ initialization parameter, 1-72

OPTIMIZER_MAX_PERMUTATIONS initialization parameter, 1-73

OPTIMIZER_MODE initialization parameter, 1-11, 1-13, 1-73, 5-7, 8-2

hints affecting, 1-12

OPTIONS column

PLAN_TABLE table, 9-23

Oracle Forms, 10-6

control of parsing and private SQL areas, 14-28

Oracle managed files, 15-21

Oracle Net Configuration Assistant, 23-14

Oracle Real Application Clusters

and Statspack, 21-24

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

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

ORACLE_TRACE_COLLECTION_NAME initialization parameter, 12-8

ORACLE_TRACE_COLLECTION_PATH initialization parameter, 12-8

ORACLE_TRACE_COLLECTION_SIZE initialization parameter, 12-8

ORACLE_TRACE_ENABLE initialization parameter, 12-8, 12-36

ORACLE_TRACE_FACILITY_NAME initialization parameter, 12-8, 12-9

ORACLE_TRACE_FACILITY_PATH initialization parameter, 12-8

Oracle-managed files

tuning, 15-21

ORDERED hint, 1-67, 5-24

ORDERED_PREDICATES hint, 5-38

OTHER column

PLAN_TABLE table, 9-25

OTHER_TAG column

PLAN_TABLE table, 9-24

outer joins

non-null values for nulls, 2-46

outlines

CREATE OUTLINE statement, 7-5

creating and using, 7-4

execution plans and plan stability, 7-2

hints, 7-3

moving tables, 7-9

storage requirements, 7-4

using, 7-6

using to move to the cost-based optimizer, 7-11

viewing data for, 7-9

OUTPUT_IO item, 12-16

overloaded disks, 15-10

page table, 16-13

PAGEFAULT_IO item, 12-16

PAGEFAULTS item, 12-16

paging, 16-13

reducing, 14-4

parallel broadcast, 1-71

PARALLEL clause

CREATE INDEX statement, 13-11

RECOVER statement, 17-7

parallel execution

hints, 5-30

PARALLEL hint, 5-30

parallel joins

and PQ_DISTRIBUTE hint, 5-31

parallel recovery, 17-7

PARALLEL_MAX_SERVERS initialization parameter, 17-7

parameter files, 13-2

PARENT_ID column

PLAN_TABLE table, 9-24

Parse event, 12-15

parsing

Oracle Forms, 14-28

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-73

partitioned objects

and EXPLAIN PLAN statement, 9-11

partitioning

distribution value, 9-26

examples of, 9-12

examples of composite, 9-14

hash, 9-11

range, 9-11

start and stop columns, 9-12

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-7, 22-23

PCTINCREASE parameter, 18-4

PCTUSED parameter, 22-23

performance

mainframe, 16-8

NT, 16-8

of SQL statements, 11-1

reports

generating, 21-9

running, 21-3, 21-9

UNIX-based systems, 16-7

viewing execution plans, 1-5

Performance Monitor

NT, 16-13

PERFSTAT user, 21-3, 21-4, 21-14

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-11

use of hints, 7-2

PLAN_TABLE table, 11-2

BYTES column, 9-24

CARDINALITY column, 9-24

COST column, 9-24

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-29

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-39, 2-44

examples, 2-40, 2-42

selectivity, 3-2

histograms, 3-20

user-defined, 1-76

PRIMARY KEY constraint, 4-8

primary keys

optimization, 2-34

searches, 8-5

PRIVATE_SGA variable, 14-38

procedures

DBMS_JOB, 21-8

DBMS_JOB.INTERVAL, 21-8

deterministic functions, 2-29

STATSPACK.MODIFY_STATSPACK_PARAMETER, 21-16, 21-18

STATSPACK.SNAP, 21-6, 21-18

process

dispatcher process configuration, 19-4

processes

priority, 16-5

scheduler, 16-5

scheduling, 16-13

PROCESSES initialization parameter, 13-13

program global area (PGA)

direct path read, 22-34

direct path write, 22-36

shared servers, 14-37

pseudocolumns

ROWNUM

cannot use indexes, 8-15

optimizing view queries, 2-37, 2-46

PUSH_PRED hint, 5-37

queries

avoiding the use of indexes, 4-6

compound

optimization of, 2-49

ORs converted to, 2-30, 8-17

ensuring the use of indexes, 4-6

optimizing IN subquery, 2-37

SAMPLE clause

cost-based optimization, 1-15

star queries, 1-68

QUERY_REWRITE_ENABLED initialization parameter, 1-73

range

distribution value, 9-26

range partitions, 9-11

examples of, 9-12

read consistency, 22-20

read events

direct path, 22-34

read wait events

scattered, 22-30

REBUILD clause, 4-7

RECOVER statement

PARALLEL clause, 17-7

recovery

parallel

intra transaction recovery, 17-18

parallel processes for, 17-7

PARALLEL_MAX_SERVERS initialization parameter, 17-7

setting number of processes to use, 17-7

RECOVERY_PARALLELISM initialization parameter, 17-7

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-18

sizing, 13-5

reducing

contention

dispatchers, 19-3

operating system processes, 16-5

shared servers, 19-5

data dictionary cache misses, 14-34

paging and swapping, 14-4

rollback segment contention, 18-2

unnecessary parse calls, 14-26

RELEASE_CURSOR clause, 14-27

REMARKS column

PLAN_TABLE table, 9-23

removing data, 21-21

removing snapshots, 21-21

REPORT_NAME variable, 21-11

reports

performance, 21-3, 21-9

Statspack, 21-10

request modes, 24-18

resource bottlenecks, 22-27

resource wait events, 22-32

response time, 1-10

cost-based approach, 1-12

optimizing, 1-10, 5-7

REWRITE hint, 5-19

rollback segments, 22-21

assigning to transactions, 18-3

choosing how many, 18-2

creating, 18-2

rollback tablespaces, 13-6

rollbacks

fast-start on-demand, 17-17

fast-start parallel, 17-17

round-robin

distribution value, 9-26

row locking, 13-8

row sampling, 3-4

row sources, 1-8

row transaction locks, 24-17

ROWID hint, 5-11

rowids

mapping to bitmaps, 4-16

table access by, 1-29

ROWNUM pseudocolumn

cannot use indexes, 8-15

optimizing view queries, 2-37, 2-46

rows

row sources, 1-8

rowids used to locate, 1-29, 8-3

setting number retrieved at one time, 11-11

RowSource event, 12-2, 12-15

RULE hint

OPTIMIZER_MODE and, 1-13

rule-based optimization, 8-2

SAMPLE BLOCK clause, 1-29

access path, 1-29

hints cannot override, 1-41

SAMPLE clause, 1-29

access path, 1-29

hints cannot override, 1-41

cost-based optimization, 1-15

sample table scans, 1-29

hints cannot override, 1-41

sar UNIX command, 16-13

scans

cluster, 8-4, 8-6

indexed, 8-7

full table, 8-14

rule-based optimizer, 8-14

hash cluster, 8-4, 8-6

index, 1-30

bitmap, 1-40

bounded range, 8-9

cluster key, 8-7

composite, 8-8

MAX or MIN, 8-12

ORDER BY, 8-13

restrictions, 8-14

single-column, 8-8

unbounded range, 8-10

index joins, 1-40

range, 8-8

bounded, 8-9

MAX or MIN, 8-12

ORDER BY, 8-13

unbounded, 8-10

sample table, 1-29

hints cannot override, 1-41

unique, 8-5, 8-7

scattered read wait events, 22-30

actions, 22-31

schemas

star schemas, 1-68

SCPU item, 12-16

scripts

SPAUTO.SQL, 21-8

SPCPKG, 21-5

SPCREATE, 21-5

SPCTAB, 21-5

SPCUSR, 21-5

SPPURGE.SQL, 21-21

SPTRUNC.SQL, 21-23

Statspack documentation scripts, 21-26

Statspack installation scripts, 21-25

Statspack performance data maintenance scripts, 21-26

Statspack reporting and automation scripts, 21-25

Statspack supplied scripts, 21-25

upgrading Statspack scripts, 21-26

SEARCH_COLUMNS column

PLAN_TABLE table, 9-24

segment-level statistics, 22-14

SELECT statement

SAMPLE clause, 1-29

access path, 1-29, 1-41

cost-based optimization, 1-15

selectivity, 3-2

histograms, 3-20

index, 4-3

user-defined, 1-76

select-project-join views, 2-36

semi-joins, 1-67

sequential read wait events

actions, 22-33

Session Data Unit (SDU), 23-14

session id, 21-18

session memory statistic, 14-37

SESSION_CACHED_CURSORS initialization parameter, 14-40

SESSIONS initialization parameter, 13-13

SET AUTOTRACE, 11-2

SET command

APPINFO variable, 11-11

ARRAYSIZE variable, 11-11

SET TRANSACTION statement, 18-3

SGA size, 14-47

shared server

performance issues, 19-2

reducing contention, 19-2

tuning, 19-2

tuning memory, 14-36

shared SQL areas

memory allocation, 14-34

SHARED_POOL_RESERVED_SIZE initialization parameter, 14-42

SHARED_POOL_SIZE initialization parameter, 13-13, 14-34, 14-42

allocating library cache, 14-34

tuning the shared pool, 14-38

sharing data, 21-21

SHOW SGA statement, 14-5

sizing redo logs, 13-5

snapshot levels, 21-15, 21-16

snapshot thresholds, 21-15, 21-17

snapshots

begin, end, 21-9

database identifier (DBID), 21-3

deleting, 21-21

instance numbers, 21-3

levels, 21-15, 21-16

removing, 21-21

SNAP_ID, 21-3

taken by Statspack, 21-2

taking snapshots, 21-6

thresholds, 21-15, 21-17

SOME operator, 2-22

sort areas

memory allocation, 14-69

sort merge joins

access path, 8-12

cost-based optimization, 1-66

example, 8-12

SORT_AREA_SIZE initialization parameter, 1-74, 4-14, 13-11

cost-based optimization and, 1-66

tuning sorts, 14-70

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

SPCPKG script, 21-5

SPCREATE script, 21-5

SPCTAB script, 21-5

SPCUSR script, 21-5

SPPURGE.SQL script, 21-21

SPREPORT.SQL file, 21-10

SPTRUNC.SQL script, 21-23

SQL

functions

optimizing view queries, 2-44

SQL Parse event, 12-2

SQL statements

avoiding the use of indexes, 4-6

complex, 2-34

optimizing, 2-34

converting

examples of, 2-30, 8-17

distributed

optimization of, 2-13

ensuring the use of indexes, 4-6

execution plans of, 1-5

modifying indexed data, 4-4

optimization

complex statements, 2-34

thresholds, 21-15, 21-17

waiting for I/O, 22-32

SQL trace facility, 10-2, 10-7

example of output, 10-16

output, 10-13

statement truncation, 10-15

steps to follow, 10-3

trace files, 10-5

SQL*Loader, 13-10

SQL*Net message from client idle events, 22-26

SQL*Net message from dblink wait events, 22-27

SQL*Plus

variables

BEGIN_SNAP, 21-11

DEFAULT_TABLESPACE, 21-5

END_SNAP, 21-11

REPORT_NAME, 21-11

TEMPORARY_TABLESPACE, 21-5

SQL_STATEMENT column

TKPROF_TABLE, 10-19

SQL_TRACE initialization parameter, 10-6

SQL.BSQ file, 13-3

SQLSegment event, 12-15

ST locks, 24-18

standard resource utilization items, 12-16

STAR hint, 5-24

star joins, 1-68

star query, 1-68

star transformation, 5-22

STAR_TRANSFORMATION hint, 5-22

STAR_TRANSFORMATION_ENABLED initialization parameter, 1-74, 5-23

start columns

in partitioning and EXPLAIN PLAN statement, 9-12

START statement in Oracle Trace, 12-3, 12-4

STATEMENT_ID column

PLAN_TABLE table, 9-23

statistics, 11-4

automated collecting, 21-8

automated gathering, 21-8

collecting, 21-8

collection interval, 21-8

consistent gets, 14-10, 18-2

db block gets, 14-10, 18-2

estimated

block sampling, 3-4

row sampling, 3-4

exporting and importing, 3-2

extensible optimization, 1-74

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-42

max session memory, 14-37

optimizer mode, 1-11

optimizer use of, 1-16, 3-2

partitions and subpartitions, 3-4

physical reads, 14-10

segment-level, 22-14

selectivity of predicates, 3-2

histograms, 3-20

user-defined, 1-76

session memory, 14-37

shared server processes, 19-5

sorts (disk), 14-68

sorts (memory), 14-68

user-defined statistics, 1-75

Statspack

and Oracle Real Application Clusters, 21-24

compared with BSTAT/ESTAT, 20-6, 21-3

documentation scripts, 21-26

exporting data, 21-21

installation

batch mode, 21-5

interactive, 21-4

installation scripts, 21-25

performance data maintenance scripts, 21-26

removing, 21-24

reporting and automation scripts, 21-25

running reports, 21-10

scripts, 21-25

sharing data, 21-21

space requirements, 21-3

uninstalling, 21-24

upgrading scripts, 21-26

STATSPACK.MODIFY_STATSPACK_PARAMETER procedure, 21-16, 21-18

STATSPACK.SNAP procedure, 21-6, 21-18

stop columns

in partitioning and EXPLAIN PLAN statement, 9-12

STOP statement in Oracle Trace, 12-3, 12-7

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-9

storage requirements, 7-4

using, 7-6

viewing data for, 7-9

striping, 15-3

manual, 15-16

subpartitions

statistics, 3-4

subqueries

converting to joins, 2-34

NOT IN, 1-67

optimizing IN subquery, 2-37

subquery unnesting, 6-21

swapping, 16-13

reducing, 14-4

switching processes, 16-13

System Global Area tuning, 14-4

system statistics, gathering, 3-6

tables

creating, 13-7

dimensions

star queries, 1-68

fact tables

star queries, 1-68

formatter in Oracle Trace, 12-3

full scans, 22-35

lookup tables, 1-68

placement on disk, 15-17

setting storage options, 13-7

tablespaces, 13-6

creating, 13-6

dictionary managed, 21-3

locally managed, 21-3

rollback, 13-6

temporary, 13-6

TCP.NODELAY parameter, 23-14

temporary tablespaces, 13-6

TEMPORARY_TABLESPACE variable, 21-5

thrashing, 16-13

thread, 16-5

thresholds, SQL statement, 21-15, 21-17

throughput, 1-10

cost-based approach, 1-11

optimizing, 1-10, 5-7

TIMED_STATISTICS initialization parameter, 21-6

SQL Trace, 10-4

TIMESTAMP column

PLAN_TABLE table, 9-23

TKPROF program, 10-3, 10-7

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-17

Trace, Oracle, 12-1

tracing statements

for performance statistics, 11-5

for query execution path, 11-5

using a database link, 11-7

with parallel query option, 11-8

transactions

assigning rollback segments, 18-3

truncating data, 21-23

tuning, 11-1

latches, 24-12

logical structure, 4-2

memory allocation, 14-5

resource contention, 22-1

shared server, 19-2

System Global Area (SGA), 14-4

TX locks, 24-17

UCPU item, 12-16

UL locks, 24-18

UNION ALL operator

examples, 2-31, 2-33, 2-49, 8-18, 8-20

optimizing view queries, 2-37

transforming OR into, 2-30, 8-17

UNION operator

examples, 2-39, 2-50

optimizing view queries, 2-37

UNIQUE constraint, 4-8

unique keys

optimization, 2-34

searches, 8-5

uniqueness, 4-8

UNIX system performance, 16-7

UNNEST hint, 5-36

upgrade

to the cost-based optimizer, 7-12

USE_CONCAT hint, 5-18

USE_MERGE hint, 5-26

USE_NL hint, 5-25

USE_STORED_OUTLINES parameter, 7-6

user defined locks, 24-18

user global area (UGA)

shared servers, 14-36, 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-9

USER_OUTLINES view

stored outlines, 7-9

USER_TAB_COL_STATISTICS view, 1-42

USER_TAB_COLUMNS view, 1-42

USER_TABLES view, 1-42

user-defined costs, 1-76

UTLCHN1.SQL script, 22-23

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-4

V$FAST_START_SERVERS view, 17-19

V$FAST_START_TRANSACTIONS view, 17-19

V$FILESTAT view, 24-6

V$INSTANCE_RECOVERY view, 17-8

V$LATCH view, 24-9

V$LATCH_CHILDREN view, 24-12

V$LATCHHOLDER view, 24-13

V$LIBRARYCACHE view, 24-14

NAMESPACE column, 14-29

V$LOCK view, 24-16

V$MYSTAT view, 24-21

V$OPEN_CURSOR view, 24-22

V$PARAMETER view, 24-24

V$PROCESS view, 24-25

V$QUEUE view, 19-5

V$ROLLSTAT view, 24-27

V$ROWCACHE view, 24-44

GETMISSES column, 14-34

GETS column, 14-34

performance statistics, 14-32

V$RSRC_CONSUMER_GROUP view, 22-8

V$SESSION view, 24-31

V$SESSION_EVENT view, 24-35

network information, 23-6

V$SESSION_WAIT view, 22-13, 24-36

network information, 23-6

V$SESSTAT view, 22-8, 24-44

network information, 23-6

statistics, 24-41

using, 14-37

V$SHARED_POOL_ADVICE view, 24-43

V$SHARED_POOL_RESERVED view, 14-42

V$SQL view, 24-44

V$SQL_PLAN view, 24-44

V$SQLAREA view, 24-56

V$SQLTEXT view, 24-59

V$SYSSTAT view, 24-44

redo buffer allocation, 14-47

statistics, 24-62

tuning sorts, 14-68

using, 14-9

V$SYSTEM_EVENT view, 24-66

V$SYSTEM_PARAMETER view, 24-24

V$UNDOSTAT view, 13-14, 24-68

V$WAITSTAT view, 22-13, 24-69

VARCHAR datatype, 13-3

VARCHAR2 datatype, 13-3

variables

bind variables

optimization, 1-43

views

complex view merging, 2-37

counter/accumulator, 24-2

current state views, 24-2

histograms, 3-24

information views, 24-4

non-null values for nulls, 2-46

select-project-join views, 2-36

statistics, 3-15

vmstat UNIX command, 16-13

wait events

buffer busy waits, 22-28

contention wait events, 22-43

direct path, 22-36

event timings, 21-20

free buffer waits, 22-40

idle wait events, 22-50

log file switch, 22-48

network communication wait events, 22-26

reasons for, 24-69

resource wait events, 22-32

time units, 21-19