Oracle9i Database Performance Guide and Reference -- Index

access paths

cluster join, 8-6

cluster scans, 1-39

composite index, 8-8

defined, 1-8

execution plans, 1-5

hash cluster key, 8-6

hash scans, 1-39

index scans, 1-29

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

ALL_OBJECTS view, 14-14

ALL_ROWS hint, 1-12, 5-6

allocation

of memory, 14-2

ALTER INDEX statement, 4-7

ALTER SESSION statement

examples, 10-5

OPTIMIZER_GOAL parameter, 1-12

SET SESSION_CACHED_CURSORS clause, 14-38

ALTER SYSTEM statement

DISPATCHERS initialization parameter, 19-4

ANALYZE statement, 22-18

creating histograms, 3-21

AND_EQUAL hint, 4-6, 5-17

anti-joins, 1-64

ANY operator, 2-21

APPEND hint, 5-33

applications

data warehousing

star queries, 1-65

ApplReg event, 12-15

array interface, 23-13

automatic segment-space management, 13-7, 15-20, 22-23

automatic undo management, 18-2

AUTOTRACE variable, 11-2

BEGIN_SNAP variable, 21-11

BETWEEN comparison operator, 2-22

binary files

formatting using Oracle Trace, 12-3

bind variables, 14-21

optimization, 1-41

BITMAP CONVERSION row source, 4-18

bitmap indexes, 4-12, 4-16

inlist iterator, 9-19

maintenance, 4-13

on index-organized tables, 4-15

vs. B-tree indexes, 4-12

when to use, 4-11

bitmap join indexes, 4-18

BITMAP_MERGE_AREA_SIZE initialization parameter, 4-13, 4-17

bitmaps

mapping to rowids, 4-15

block sampling, 3-4

bottlenecks

disk I/O, 15-3

memory, 14-2

network, 22-21

resource, 22-22

broadcast

distribution value, 9-24

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

buffer busy wait events, 22-22

actions, 22-23

buffer caches

reducing buffers, 14-11, 14-33

buffer pools

default cache, 14-12

KEEP cache, 14-12

multiple, 14-11

RECYCLE cache, 14-12

BYTES column

PLAN_TABLE table, 9-21

CACHE hint, 5-34

CARDINALITY column

PLAN_TABLE table, 9-21

CATALOG.SQL script, 13-4

CATPROC.SQL script, 13-4

chained rows, 22-18

CHAR datatype, 13-4

character set database option, 13-4

checkpoints

choosing checkpoint frequency, 17-3

CHOOSE hint, 1-12, 5-8

client/server applications, 16-10

CLUSTER hint, 5-11

clusters, 4-19

hash

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

index

scans of, 8-7

joins and, 8-4, 8-6

scans of, 1-39, 8-4

hash, 8-4, 8-6

joins, 8-6

collections, 12-8

columns

pseudocolumns

ROWNUM, 2-34, 2-43, 8-15

selectivity, 3-2

histograms, 3-20

to index, 4-3

complex view merging, 2-34

composite indexes, 4-4

composite partitioning

examples of, 9-13

CONNECT BY clause

optimizing view queries, 2-34

Connection event, 12-15

connection manager, 23-14

connection pooling, 19-4

consistency

read, 22-17

consistent gets statistic, 14-8, 18-2

consistent mode

TKPROF, 10-13

constants

comparisons and, 2-18

evaluation of expressions, 2-18

when computed, 2-18

constraints, 4-8

contention

disk, 15-3

memory, 14-2, 22-1

tuning, 22-1

wait events, 22-36

context switches, 16-11

CONTROL_FILES initialization parameter, 13-10

COST column

PLAN_TABLE table, 9-21

cost-based optimizations, 1-15

extensible optimization, 1-72

histograms, 3-20

procedures for plan stability, 7-11

selectivity of predicates, 3-2

histograms, 3-20

user-defined, 1-73

star queries, 1-65

statistics, 3-2

user-defined, 1-72

upgrading to, 7-12

user-defined costs, 1-73

counter/accumulator views, 24-2

CPU_COUNT initialization parameter, 17-16

CPUs

utilization, 16-9

CREATE DATABASE statement, 13-3

CREATE INDEX statement

example, 14-59

NOSORT clause, 14-59

PARALLEL clause, 13-8

CREATE OUTLINE statement, 7-5

CREATE_BITMAP_AREA_SIZE initialization parameter, 4-13, 4-16

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

CURSOR_SHARING initialization parameter, 1-68, 14-23, 14-43

CURSOR_SHARING_EXACT hint, 5-37

CURSOR_SPACE_FOR_TIME initialization parameter

setting, 14-37

data cache, 16-2

data dictionary, 14-32

scripts, 13-4

CATALOG.SQL, 13-4

CATPROC.SQL, 13-4

statistics in, 3-15

views used in optimization, 3-15

data indexing, 13-8

data loading, 13-8

Data Viewer

tips on using

collect data for specific wait events, 12-35

data warehousing

dimensions, 1-65

star queries, 1-65

database

buffers, 14-11, 14-33

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

location of initial datafile, 13-4

national character set, 13-4

SQL.BSQ file, 13-4

Database Resource Manager, 16-4, 16-5, 16-9, 22-7

databases

creating, 13-2

distributed

statement optimization on, 2-12

datatypes

CHAR, 13-4

NCHAR, 13-4

NVARCHAR, 13-4

NVARCHAR2, 13-4

user-defined

statistics, 1-72

VARCHAR, 13-4

VARCHAR2, 13-4

DATE_OF_INSERT column

TKPROF_TABLE table, 10-18

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

DB file scattered read wait events, 22-25

actions, 22-25

DB file sequential/scattered read wait events, 22-25, 22-27

DB_BLOCK_BUFFERS initialization parameter, 13-10, 14-11, 14-33

DB_BLOCK_SIZE initialization parameter, 13-3, 13-10, 15-12

DB_CACHE_ADVICE parameter, 14-6, 14-10

DB_CACHE_SIZE initialization parameter, 14-12

DB_DOMAIN initialization parameter, 13-10

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 1-69, 13-7, 15-11, 15-12, 15-13, 22-25

cost-based optimization, 1-64

DB_NAME initialization parameter, 13-10

DB_nK_CACHE_SIZE initialization parameter, 14-11

DB_WRITER_PROCESSES initialization parameter, 22-35

DBA_OBJECTS view, 14-14

DBID (database identifier), 21-3

DBMS_JOB procedure, 21-7

DBMS_JOB.INTERVAL procedure, 21-8

DBMS_OUTLN package, 7-4

DBMS_OUTLN_EDIT package, 7-4

DBMS_SHARED_POOL package, 14-41

DBMS_STATS package, 3-5

creating histograms, 3-21

default cache, 14-12

DEFAULT_TABLESPACE variable, 21-6

deleting data, 21-20

deleting snapshots, 21-20

DEPTH column

TKPROF_TABLE table, 10-18

deterministic functions, 2-26

dictionary managed tablespaces, 21-4

dimensions

star joins, 1-65

star queries, 1-65

direct path read events, 22-28

actions, 22-29

causes, 22-29

direct path wait events, 22-30

direct path write events

actions, 22-30

causes, 22-30

direct-path INSERT, 5-33

disabled constraints, 4-8

Disconnect event, 12-15

disks

contention, 15-3

monitoring OS file activity, 22-8

dispatcher processes, 19-4

DISPATCHERS initialization parameter, 19-4, 23-3

DISTINCT operator

optimizing views, 2-34

distributed databases

statement optimization on, 2-12

distributed transactions

optimizing, 2-12

sample table scan not supported, 1-27

distribution

hints for, 5-29

DISTRIBUTION column

PLAN_TABLE table, 9-22

DML locks, 24-18

domain indexes

and EXPLAIN PLAN, 9-19

extensible optimization, 1-72

user-defined statistics, 1-72

using, 4-18

DRIVING_SITE hint, 5-25

duration events

in Oracle Trace, 12-2, 12-15

dynamic performance views

enabling statistics, 10-4

enabled constraints, 4-8

END_SNAP variable, 21-11

enforced constraints, 4-8

enqueue wait events

actions, 22-31

EPC_ERROR.LOG file, 12-36

equijoins, 6-8

ErrorStack event, 12-15

event timings, 21-19

examples

ALTER SESSION statement, 10-5

concurrently creating tablespaces, 13-6

CREATE DATABASE script, 13-4

CREATE INDEX statement, 14-59

creating indexes efficiently, 13-9

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

NOSORT clause, 14-59

SET TRANSACTION statement, 18-3

SQL trace facility output, 10-16

V$DB_OBJECT_CACHE view, 24-4

V$FILESTAT view, 24-7

V$LATCH view, 24-10

V$LATCH_CHILDREN view, 24-12

V$LATCH_HOLDER view, 24-13

V$LIBRARYCACHE view, 24-15

V$LOCK view, 24-19, 24-20

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

V$PROCESS view, 24-28

V$ROLLSTAT view, 24-29

V$SESSION view, 24-34

V$SESSION_EVENT view, 24-36

V$SESSION_WAIT view, 24-39

V$SQLAREA view, 24-54, 24-55

V$SQLTEXT view, 24-56

V$SYSTEM_EVENT view, 24-63

Execute event, 12-15

execution plans

accessing views, 2-37, 2-39, 2-41

complex statements, 2-32

compound queries, 2-46, 2-47, 2-48

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

execution sequence of, 1-9

joining views, 2-44

joins, 1-42

OR operators, 2-29, 8-18

overview of, 1-5

plan stability, 7-2

preserving with plan stability, 7-2

TKPROF, 10-7, 10-10

viewing, 1-5

EXPLAIN PLAN statement

access paths, 1-28, 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-10

PLAN_TABLE table, 9-4

restrictions, 9-20

Export utility

copying statistics, 3-2

exporting data, 21-19

extensible optimization, 1-72

user-defined costs, 1-73

user-defined selectivity, 1-73

user-defined statistics, 1-72

FACT hint, 5-21

fact tables

star joins, 1-65

star queries, 1-65

fast full index scans, 1-37

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

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

fast-start parallel rollback, 17-15

features, new, xxix

Fetch event, 12-15

FIRST_ROWS hint, 1-12

FIRST_ROWS(n) hint, 5-7

FORMAT statement

in Oracle Trace, 12-3

formatter tables

in Oracle Trace, 12-3

free buffer wait events, 22-34

free lists, 13-7

FULL hint, 4-6, 5-10

full partition-wise joins, 9-17

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

rule-based optimizer, 8-14

function-based indexes, 4-9

functions

PL/SQL

deterministic, 2-26

SQL

optimizing view queries, 2-41

user-defined

extensible optimization, 1-72

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

GETS column

in V$ROWCACHE view, 14-32

global hints, 5-39

GLOGIN.SQL, 11-3, 11-7

GROUP BY clause

NOSORT clause, 14-59

optimizing views, 2-34

hash

distribution value, 9-24

hash clusters

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

HASH hint, 5-11

hash joins

index join, 1-38

hash partitions, 9-11

examples of, 9-11

HASH_AJ hint, 1-64, 5-26, 5-27

HASH_AREA_SIZE initialization parameter, 1-69

HASH_JOIN_ENABLED initialization parameter, 1-69

HASH_SJ hint, 1-65, 5-27

hashing, 4-21

HIGH_VALUE statistics, 1-40

hint

NL_AJ hint, 5-26

hints, 5-2

access paths, 5-9, 5-17

ALL_ROWS hint, 5-6

AND_EQUAL hint, 4-6, 5-17

as used in outlines, 7-3

CACHE hint, 5-34

cannot override sample access path, 1-39

CHOOSE hint, 5-8

CLUSTER hint, 5-11

CURSOR_SHARING_EXACT hint, 5-37, 1

degree of parallelism, 5-28

extensible optimization, 1-72

FACT hint, 5-21

FIRST_ROWS hint, 5-7

FIRST_ROWS(n) hint, 5-7

FULL hint, 4-6, 5-10

global, 5-39

HASH hint, 5-11

HASH_AJ hint, 5-26

HASH_SJ hint, 5-27

how to use, 5-2

INDEX hint, 4-6, 5-12, 5-22

INDEX_ASC hint, 5-13

INDEX_DESC hint, 5-14, 5-15

INDEX_FFS, 1-37

INDEX_JOIN, 1-38

join operations, 5-23

LEADING hint, 5-26

MERGE_AJ and HASH_AJ, 1-64

MERGE_AJ hint, 5-26

MERGE_SJ and HASH_SJ, 1-65

MERGE_SJ hint, 5-27

NL_AJ hint, 5-26

NL_SJ hint, 5-27

NO_EXPAND hint, 5-18

NO_FACT hint, 5-21

NO_INDEX, 4-6

NO_INDEX hint, 5-16

NO_MERGE hint, 5-20

NO_PUSH_PRED hint, 5-36

NO_UNNEST hint, 5-35

NOCACHE hint, 5-34

NOPARALLEL hint, 5-29

NOREWRITE hint, 5-19

optimization approach and goal, 5-6

ORDERED hint, 1-64, 5-22

overriding optimizer choice, 1-39

overriding OPTIMIZER_MODE and OPTIMIZER_GOAL, 1-12

PARALLEL hint, 5-28

parallel query option, 5-28

PQ_DISTRIBUTE hint, 5-29

PUSH_PRED hint, 5-36

PUSH_SUBQ hint, 5-36

REWRITE hint, 5-19

ROWID hint, 5-11

STAR hint, 5-22

UNNEST hint, 5-35

USE_CONCAT hint, 5-18

USE_MERGE hint, 5-24

USE_NL hint, 5-23

histograms, 3-20

number of buckets, 3-22

HOLD_CURSOR clause, 14-25

ID column

PLAN_TABLE table, 9-21

idle wait events, 22-43

SQL*Net message from client, 22-21

Import utility

copying statistics, 3-2

IN operator, 2-21

merging views, 2-35

IN subquery, 2-34

INDEX hint, 4-6, 4-14, 5-12

index joins, 1-38

INDEX_ASC hint, 5-13

INDEX_COMBINE hint, 4-6, 4-14

INDEX_DESC hint, 5-14, 5-15

INDEX_FFS hint, 1-37, 1-38

INDEX_JOIN hint, 1-38

indexes

avoiding the use of, 4-6

bitmap, 4-11, 4-12, 4-16

choosing columns for, 4-3

cluster

scans of, 8-7

composite, 4-4

scans of, 8-8

creating, 13-8

domain, 4-18

domain indexes

extensible optimization, 1-72

user-defined statistics, 1-72

dropping, 4-2

enforcing uniqueness, 4-8

ensuring the use of, 4-6

example, 8-21

function-based, 4-9

index joins, 1-38

modifying values of, 4-4

non-unique, 4-8

optimization and, 2-28, 8-17

placement on disk, 15-14

rebuilding, 4-7

recreating, 4-6

scans of, 1-29

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-28, 8-17

statistics, gathering, 3-8

indexing data, 13-8

information views, 24-3

initial database creation, 13-2

initialization files, 13-2, 13-9

initialization parameters

CONTROL_FILES, 13-10

CPU_COUNT, 17-16

DB_BLOCK_BUFFERS, 13-10

DB_BLOCK_SIZE, 13-3, 13-10

DB_DOMAIN, 13-10

DB_FILE_MULTIBLOCK_READ_COUNT, 1-64

DB_NAME, 13-3, 13-10

FAST_START_PARALLEL_ROLLBACK, 17-16

in Oracle Trace, 12-7

INITRANS, 13-7

JAVA_POOL_SIZE, 13-10

JOB_QUEUE_PROCESSES, 21-8

LOG_ARCHIVE_XXX, 13-10

LOG_CHECKPOINT_INTERVAL, 17-6

LOG_CHECKPOINT_TIMEOUT, 17-6

MAX_DUMP_FILE_SIZE, 10-4

OPEN_CURSORS, 13-10

OPTIMIZER_FEATURES_ENABLE, 1-37, 1-38, 2-34

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

PARALLEL_MAX_SERVERS, 17-7

PROCESSES, 13-10

RECOVERY_PARALLELISM, 17-7

SESSION_CACHED_CURSORS, 14-38

SESSIONS, 13-10

SHARED_POOL_SIZE, 13-10

SORT_AREA_SIZE, 1-64, 13-9

SQL_TRACE, 10-6

TIMED_STATISTICS, 10-4, 21-7

USER_DUMP_DEST, 10-4

INIT.ORA file

ORACLE_TRACE_ENABLE parameter, 12-35

INITRANS initialization parameter, 13-7

IN-lists, 5-13, 5-18

input parameters, table, 21-18

INPUT_IO item, 12-16

INSERT statement

append, 5-33

installation scripts

SPCREATE, 21-5

instance configuration, 13-9

instance numbers, 21-3

instrumentation

of Oracle Server, 12-15

INTERSECT operator

example, 2-48

optimizing view queries, 2-34

intra transaction recovery, 17-16

I/O

and SQL statements, 22-26

balancing, 15-3

excessive I/O waits, 22-26

objects causing I/O waits, 22-27

items

cross-product, 12-17

standard resource utilization, 12-16

types of, 12-16

JAVA_POOL_SIZE initialization parameter, 13-10

JOB_QUEUE_PROCESSES initialization parameter, 21-8

joins

anti-joins, 1-64

cluster, 8-4

searches on, 8-6

convert to subqueries, 2-31

execution plans and, 1-42

index joins, 1-38

join order

execution plans, 1-5

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

nested loops

cost-based optimization, 1-63

optimization of, 8-15

outer

non-null values for nulls, 2-43

parallel, and PQ_DISTRIBUTE hint, 5-29

partition-wise

examples of full, 9-17

examples of partial, 9-16

full, 9-17

sample table scan not supported, 1-27

select-project-join views, 2-33

semi-joins, 1-64

sort-merge

cost-based optimization, 1-63

example, 8-12

star joins, 1-65

star queries, 1-65

KEEP cache, 14-12

keys

searches, 8-4

LARGE_POOL_SIZE initialization parameter, 14-34

latch free wait events

actions, 22-36

latches

tuning, 24-11

LEADING hint, 5-26

library cache

memory allocation, 14-32

LIKE operator, 2-20

Lmode modes, 24-18

load balancing, 15-3

loading data, 13-8

locally managed tablespaces, 21-4

location of initial datafile database option, 13-4

lock types, 24-17

ST (space transaction) locks, 24-18

TM (DML) locks, 24-18

TX (row transaction) locks, 24-17

UL (user defined) locks, 24-18

locking rows, 13-7

log buffer tuning, 14-45

log file switch wait events, 22-41

log writer processes

tuning, 15-16

LOG_ARCHIVE_XXX initialization parameter, 13-10

LOG_BUFFER initialization parameter, 14-45

setting, 14-46

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

LOW_VALUE statistics, 1-40

LRU

aging policy, 14-12

latch contention, 22-40

manual database creation, 13-2

max session memory statistic, 14-35

MAX_DISPATCHERS initialization parameter, 19-4

MAX_DUMP_FILE_SIZE initialization parameter, 10-4

SQL Trace, 10-4

MAX_SHARED_SERVERS initialization parameter, 19-7

MAXOPENCURSORS clause, 14-25

MAXRS_SIZE item, 12-16

memory

reducing usage, 14-60

memory allocation

importance, 14-2

library cache, 14-32

shared SQL areas, 14-32

sort areas, 14-57

tuning, 14-4

MERGE hint, 5-19

MERGE_AJ hint, 1-64, 5-26, 5-27

MERGE_SJ hint, 1-65, 5-27

merging complex views, 2-34

merging views into statements, 2-33

migrated rows, 22-18

Migration event, 12-15

MINUS operator

optimizing view queries, 2-34

mirroring

redo logs, 15-17

modes

Lmode, 24-18

request, 24-18

multiple buffer pools, 14-11

NAMESPACE column

V$LIBRARYCACHE view, 14-27

national character set database option, 13-4

NCHAR datatype, 13-4

nested loops joins

cost-based optimization, 1-63

network

array interface, 23-13

detecting performance problems, 23-6

problem solving, 23-8

Session Data Unit, 23-14

tuning, 23-1

network bottlenecks, 22-21

network communication wait events, 22-20

DB file sequential/scattered read wait events, 22-25, 22-27

SQL*Net message from Dblink, 22-22

new features, xxix

NL_AJ hint, 5-26

NL_SJ hint, 5-27

NLS_SORT initialization parameter

ORDER BY access path, 8-13

NO_EXPAND hint, 5-18

NO_FACT hint, 5-21

NO_INDEX hint, 4-6, 5-16

NO_MERGE hint, 5-20

NO_PUSH_PRED hint, 5-36

NO_UNNEST hint, 5-35

NOAPPEND hint, 5-34

NOCACHE hint, 5-34

NOPARALLEL hint, 5-29

NOPARALLEL_INDEX hint, 5-32

NOREWRITE hint, 5-19

NOSORT clause, 14-59

NOT IN subquery, 1-64

NOT operator, 2-23

NT performance, 16-6

nulls

non-null values for, 2-43

NUM_DISTINCT column

USER_TAB_COLUMNS view, 1-40

NUM_ROWS column

USER_TABLES view, 1-40

NVARCHAR datatype, 13-4

NVARCHAR2 datatype, 13-4

OBJECT_INSTANCE column

PLAN_TABLE table, 9-21

OBJECT_NAME column

PLAN_TABLE table, 9-21

OBJECT_NODE column

PLAN_TABLE table, 9-21

OBJECT_OWNER column

PLAN_TABLE table, 9-21

OBJECT_TYPE column

PLAN_TABLE table, 9-21

OEM

See Oracle Enterprise Manager (OEM)

OPEN_CURSORS initialization parameter, 13-10

increasing cursors per session, 14-32

operating system

data cache, 16-2

monitoring disk I/O, 22-8

OPERATION column

PLAN_TABLE table, 9-20, 9-25

OPTIMAL parameter, 18-3

optimization

choosing the approach, 1-11

conversion of expressions and predicates, 2-2

cost-based, 1-15

choosing an access path, 1-39

examples of, 1-40

histograms, 3-20

remote databases and, 2-13

star queries, 1-65

user-defined costs, 1-73

described, 1-3

DISTINCT, 2-34

distributed SQL statements, 2-12

extensible optimizer, 1-72

GROUP BY views, 2-34

hints, 1-12, 1-37, 1-38

manual, 1-12

merging complex views, 2-34

merging views into statements, 2-33

non-null values for nulls, 2-43

operations performed, 1-4

rule-based, 8-2, 8-15

selectivity of predicates, 3-2

histograms, 3-20

user-defined, 1-73

select-project-join views, 2-33

semi-joins, 1-64

statistics, 3-2

user-defined, 1-72

transitivity and, 2-23

without merging, 2-43

optimizer, 1-3

plan stability, 7-2

OPTIMIZER column

PLAN_TABLE, 9-21

OPTIMIZER_FEATURES_ENABLE initialization parameter, 1-37, 1-38, 1-66, 2-34

OPTIMIZER_GOAL clause, 1-12

OPTIMIZER_GOAL initialization parameter, 1-70

OPTIMIZER_INDEX_CACHING initialization parameter, 1-69

OPTIMIZER_INDEX_COST_ADJ initialization parameter, 1-69

OPTIMIZER_MAX_PERMUTATIONS initialization parameter, 1-70

OPTIMIZER_MODE initialization parameter, 1-11, 1-12, 1-70, 5-6, 8-2

hints affecting, 1-12

OPTIONS column

PLAN_TABLE table, 9-21

Oracle Enterprise Manager

Oracle Expert, 4-3

Oracle Index Tuning Wizard, 4-3

SQL Analyze, 4-3

Oracle Forms, 10-5

control of parsing and private SQL areas, 14-26

Oracle managed files, 15-18

Oracle Net Configuration Assistant, 23-14

Oracle Real Application Clusters

and Statspack, 21-22

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

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

ORACLE_TRACE_FACILITY_NAME initialization parameter, 12-8, 12-9

ORACLE_TRACE_FACILITY_PATH initialization parameter, 12-8

Oracle-managed files

tuning, 15-18

ORDERED hint, 1-64, 5-22

ORDERED_PREDICATES hint, 5-37

OTHER column

PLAN_TABLE table, 9-22

OTHER_TAG column

PLAN_TABLE table, 9-21

outer joins

non-null values for nulls, 2-43

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

page table, 16-10

PAGEFAULT_IO item, 12-16

PAGEFAULTS item, 12-16

paging, 16-10

reducing, 14-3

PARALLEL clause

CREATE INDEX statement, 13-8

RECOVER statement, 17-7

parallel execution

hints, 5-28

PARALLEL hint, 5-28

parallel joins

and PQ_DISTRIBUTE hint, 5-29

parallel recovery, 17-7

PARALLEL_BROADCAST_ENABLED initialization parameter, 1-71

PARALLEL_MAX_SERVERS initialization parameter, 17-7

parameter files, 13-2

PARENT_ID column

PLAN_TABLE table, 9-21

Parse event, 12-15

parsing

Oracle Forms, 14-26

Oracle precompilers, 14-25

reducing unnecessary calls, 14-24

PARTITION_ID column

PLAN_TABLE table, 9-22

PARTITION_START column

PLAN_TABLE table, 9-22

PARTITION_STOP column

PLAN_TABLE table, 9-22

PARTITION_VIEW_ENABLED initialization parameter, 1-71

partitioned objects

and EXPLAIN PLAN statement, 9-11

partitioning

distribution value, 9-24

examples of, 9-11

examples of composite, 9-13

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

PCTINCREASE parameter, 18-4

PCTUSED parameter, 13-7, 22-18

performance

mainframe, 16-6

NT, 16-6

of SQL statements, 11-1

reports

generating, 21-8

running, 21-3, 21-8

UNIX-based systems, 16-5

viewing execution plans, 1-5

Performance Monitor

NT, 16-10

PERFSTAT user, 21-3, 21-4, 21-13

physical reads statistic, 14-8

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

CARDINALITY column, 9-21

COST column, 9-21

DISTRIBUTION column, 9-22

ID column, 9-21

OBJECT_INSTANCE column, 9-21

OBJECT_NAME column, 9-21

OBJECT_NODE column, 9-21

OBJECT_OWNER column, 9-21

OBJECT_TYPE column, 9-21

OPERATION column, 9-20

OPTIMIZER column, 9-21

OPTIONS column, 9-21

OTHER column, 9-22

OTHER_TAG column, 9-21

PARENT_ID column, 9-21

PARTITION_ID column, 9-22

PARTITION_START column, 9-22

PARTITION_STOP column, 9-22

POSITION column, 9-21

REMARKS column, 9-20

SEARCH_COLUMNS column, 9-21

STATEMENT_ID column, 9-20

TIMESTAMP column, 9-20

PL/SQL

deterministic functions, 2-26

PLUSTRACE role, 11-2

point events

in Oracle Trace, 12-2, 12-15

POOL attribute, 19-4

POSITION column

PLAN_TABLE table, 9-21

PQ_DISTRIBUTE hint, 5-29

precompilers

control of parsing and private SQL areas, 14-25

predicates

pushing into a view, 2-36, 2-41

examples, 2-36, 2-39

selectivity, 3-2

histograms, 3-20

user-defined, 1-73

PRIMARY KEY constraint, 4-8

primary keys

optimization, 2-32

searches, 8-5

PRIVATE_SGA variable, 14-36

procedures

DBMS_JOB, 21-7

DBMS_JOB.INTERVAL, 21-8

deterministic functions, 2-26

STATSPACK.MODIFY_STATSPACK_PARAMETER, 21-15, 21-17

STATSPACK.SNAP, 21-6, 21-17

process

dispatcher process configuration, 19-4

processes

priority, 16-3

scheduler, 16-3

scheduling, 16-11

PROCESSES initialization parameter, 13-10

program global area (PGA)

direct path read, 22-28

direct path write, 22-30

shared servers, 14-35, 19-2

pseudocolumns

ROWNUM

cannot use indexes, 8-15

optimizing view queries, 2-34, 2-43

PUSH_PRED hint, 5-36

queries

avoiding the use of indexes, 4-6

compound

optimization of, 2-46

ORs converted to, 2-28, 8-17

ensuring the use of indexes, 4-6

optimizing IN subquery, 2-34

SAMPLE clause

cost-based optimization, 1-14

star queries, 1-65

QUERY_REWRITE_ENABLED initialization parameter, 1-71

range

distribution value, 9-24

range partitions, 9-11

examples of, 9-11

raw device, 16-2

read consistency, 22-17

read events

direct path, 22-28

read wait events

scattered, 22-25

REBUILD clause, 4-7

RECOVER statement

PARALLEL clause, 17-7

recovery

parallel

intra transaction recovery, 17-16

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

REDO BUFFER ALLOCATION RETRIES statistic, 14-45

redo logs, 13-5

mirroring, 15-17

placement on disk, 15-15

sizing, 13-5

reducing

contention

dispatchers, 19-3

OS processes, 16-3

shared servers, 19-5

data dictionary cache misses, 14-32

paging and swapping, 14-3

rollback segment contention, 18-2

unnecessary parse calls, 14-24

RELEASE_CURSOR clause, 14-25

REMARKS column

PLAN_TABLE table, 9-20

removing data, 21-20

removing snapshots, 21-20

REPORT_NAME variable, 21-11

reports

performance, 21-3, 21-8

Statspack, 21-9

request modes, 24-18

resource bottlenecks, 22-22

resource wait events, 22-27

response time, 1-10

cost-based approach, 1-11

optimizing, 1-10, 5-7

REWRITE hint, 5-18

rollback segments, 22-17

assigning to transactions, 18-3

choosing how many, 18-2

creating, 18-2

rollback tablespaces, 13-6

rollbacks

fast-start on-demand, 17-15

fast-start parallel, 17-15

round-robin

distribution value, 9-24

row locking, 13-7

row sampling, 3-4

row sources, 1-8

row transaction locks, 24-17

ROWID hint, 5-11

rowids

mapping to bitmaps, 4-15

table access by, 1-28

ROWNUM pseudocolumn

cannot use indexes, 8-15

optimizing view queries, 2-34, 2-43

rows

row sources, 1-8

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

RowSource event, 12-2, 12-15

RULE hint

OPTIMIZER_MODE and, 1-12

rule-based optimization, 8-2

SAMPLE BLOCK clause, 1-27

access path, 1-28

hints cannot override, 1-39

SAMPLE clause, 1-27

access path, 1-28

hints cannot override, 1-39

cost-based optimization, 1-14

sample table scans, 1-27, 1-28

hints cannot override, 1-39

sar UNIX command, 16-10

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

bitmap, 1-38

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

range, 8-8

bounded, 8-9

MAX or MIN, 8-12

ORDER BY, 8-13

unbounded, 8-10

sample table, 1-27, 1-28

hints cannot override, 1-39

unique, 8-5, 8-7

scattered read wait events, 22-25

actions, 22-25

schemas

star schemas, 1-65

SCPU item, 12-16

scripts

SPAUTO.SQL, 21-8

SPCPKG, 21-5

SPCREATE, 21-5

SPCTAB, 21-5

SPCUSR, 21-5

SPPURGE.SQL, 21-20

SPTRUNC.SQL, 21-22

Statspack documentation scripts, 21-25

Statspack installation scripts, 21-24

Statspack performance data maintenance scripts, 21-25

Statspack reporting and automation scripts, 21-24

Statspack supplied scripts, 21-24

upgrading Statspack scripts, 21-24

SEARCH_COLUMNS column

PLAN_TABLE table, 9-21

SELECT statement

SAMPLE clause, 1-27

access path, 1-28, 1-39

cost-based optimization, 1-14

selectivity, 3-2

histograms, 3-20

index, 4-3

user-defined, 1-73

select-project-join views, 2-33

semi-joins, 1-64

Session Data Unit (SDU), 23-14

session id, 21-17

session memory statistic, 14-35

SESSION_CACHED_CURSORS initialization parameter, 14-38

SESSIONS initialization parameter, 13-10

SET AUTOTRACE, 11-2

SET TRANSACTION statement, 18-3

SGA size, 14-45

shared server

performance issues, 19-2

reducing contention, 19-2

tuning, 19-2

tuning memory, 14-34

shared SQL areas

memory allocation, 14-32

SHARED_POOL_RESERVED_SIZE initialization parameter, 14-40

SHARED_POOL_SIZE initialization parameter, 13-10, 14-32, 14-40

allocating library cache, 14-32

tuning the shared pool, 14-36

sharing data, 21-19

SHOW SGA statement, 14-4

sizing redo logs, 13-5

snapshot levels, 21-14, 21-16

snapshot thresholds, 21-14, 21-16

snapshots, 21-2

begin, end, 21-9

database identifier (DBID), 21-3

deleting, 21-20

instance numbers, 21-3

levels, 21-14, 21-16

removing, 21-20

SNAP_ID, 21-3

taking snapshots, 21-6

thresholds, 21-14, 21-16

SOME operator, 2-21

sort areas

memory allocation, 14-57

sort merge joins

access path, 8-12

cost-based optimization, 1-63

example, 8-12

SORT_AREA_SIZE initialization parameter, 1-71, 4-13, 13-9

cost-based optimization and, 1-64

tuning sorts, 14-58

sorts

(disk) statistic, 14-56

(memory) statistic, 14-56

avoiding on index creation, 14-59

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

SPREPORT.SQL file, 21-9

SPTRUNC.SQL script, 21-22

SQL

functions

optimizing view queries, 2-41

SQL Parse event, 12-2

SQL statements

avoiding the use of indexes, 4-6

complex, 2-31

optimizing, 2-31

converting

examples of, 2-27, 8-17

distributed

optimization of, 2-12

ensuring the use of indexes, 4-6

execution plans of, 1-5

modifying indexed data, 4-4

optimization

complex statements, 2-31

thresholds, 21-14, 21-16

waiting for I/O, 22-26

SQL trace facility, 10-2, 10-6

example of output, 10-16

output, 10-12

statement truncation, 10-14

steps to follow, 10-3

trace files, 10-4

SQL*Loader, 13-8

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

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

SQL*Plus

variables

BEGIN_SNAP, 21-11

DEFAULT_TABLESPACE, 21-6

END_SNAP, 21-11

REPORT_NAME, 21-11

TEMPORARY_TABLESPACE, 21-6

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

star joins, 1-65

star query, 1-65

star transformation, 5-20

STAR_TRANSFORMATION hint, 5-20

STAR_TRANSFORMATION_ENABLED initialization parameter, 1-71, 5-21

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

statistics, 11-3

automated collecting, 21-7

automated gathering, 21-7

collecting, 21-7

collection interval, 21-8

consistent gets, 14-8, 18-2

db block gets, 14-8, 18-2

estimated

block sampling, 3-4

row sampling, 3-4

exporting and importing, 3-2

extensible optimization, 1-72

from ANALYZE, 3-12

from B-tree or bitmap index, 3-8

gathering with DBMS_STATS package, 3-5

generating, 3-3

generating and managing with DBMS_STATS, 3-5

generating for cost-based optimization, 3-3

HIGH_VALUE and LOW_VALUE, 1-40

max session memory, 14-35

optimizer mode, 1-11

optimizer use of, 1-15, 3-2

partitions and subpartitions, 3-4

physical reads, 14-8

selectivity of predicates, 3-2

histograms, 3-20

user-defined, 1-73

session memory, 14-35

shared server processes, 19-5

sorts (disk), 14-56

sorts (memory), 14-56

user-defined statistics, 1-72

Statspack

and Oracle Real Application Clusters, 21-22

documentation scripts, 21-25

exporting data, 21-19

installation

batch mode, 21-6

interactive, 21-4

installation scripts, 21-24

performance data maintenance scripts, 21-25

removing, 21-23

reporting and automation scripts, 21-24

running reports, 21-9

scripts, 21-24

sharing data, 21-19

space requirements, 21-3

uninstalling, 21-23

upgrading scripts, 21-24

vs. BSTAT/ESTAT, 20-5, 21-3

STATSPACK.MODIFY_STATSPACK_PARAMETER procedure, 21-15, 21-17

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

stop columns

in partitioning and EXPLAIN PLAN statement, 9-12

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

storage requirements, 7-4

using, 7-6

viewing data for, 7-9

striping, 15-3

manual, 15-14

subpartitions

statistics, 3-4

subqueries

converting to joins, 2-31

NOT IN, 1-64

optimizing IN subquery, 2-34

subquery unnesting, 6-19

swapping, 16-10

reducing, 14-3

switching processes, 16-11

System Global Area tuning, 14-4

system statistics, gathering, 3-6

tables

creating, 13-7

dimensions

star queries, 1-65

fact tables

star queries, 1-65

formatter in Oracle Trace, 12-3

free list settings, 13-7

full scans, 22-29

lookup tables, 1-65

placement on disk, 15-14

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

thread, 16-3

thresholds, SQL statement, 21-14, 21-16

throughput, 1-10

cost-based approach, 1-11

optimizing, 1-10, 5-6

TIMED_STATISTICS initialization parameter, 10-4, 16-2, 21-7

SQL Trace, 10-4

TIMESTAMP column

PLAN_TABLE table, 9-20

TKPROF program, 10-3, 10-6

editing the output SQL script, 10-17

example of output, 10-16

generating the output SQL script, 10-16

syntax, 10-8

using the EXPLAIN PLAN statement, 10-10

TKPROF_TABLE, 10-18

querying, 10-17

TM locks, 24-18

Trace, Oracle, 12-1

tracing statements

for performance statistics, 11-3

for query execution path, 11-3

using a database link, 11-6

with parallel query option, 11-7

transactions

assigning rollback segments, 18-3

truncating data, 21-22

tuning, 11-1

latches, 24-11

logical structure, 4-2

memory allocation, 14-4

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-29, 2-31, 2-46, 8-18, 8-20

optimizing view queries, 2-34

transforming OR into, 2-28, 8-17

UNION operator

examples, 2-36, 2-47

optimizing view queries, 2-34

UNIQUE constraint, 4-8

unique keys

optimization, 2-32

searches, 8-5

uniqueness, 4-8

UNIX system performance, 16-5

UNNEST hint, 5-35

UNNEST_SUBQUERY parameter, 5-35

upgrade

to the cost-based optimizer, 7-12

USE_CONCAT hint, 5-18

USE_MERGE hint, 5-24

USE_NL hint, 5-23

USE_STORED_OUTLINES parameter, 7-6

user defined locks, 24-18

user global area (UGA)

shared servers, 14-34, 19-2

V$SESSTAT, 14-35

USER_DUMP_DEST initialization parameter, 10-4

SQL Trace, 10-4

USER_ID column

TKPROF_TABLE, 10-18

USER_OUTLINE_HINTS view

stored outline hints, 7-9

USER_OUTLINES view

stored outlines, 7-9

USER_TAB_COL_STATISTICS view, 1-41

USER_TAB_COLUMNS view, 1-40, 1-41

USER_TABLES view, 1-40

user-defined costs, 1-73

UTLCHN1.SQL script, 22-18

V$BH view, 14-13

V$BUFFER_POOL_STATISTICS view, 14-13

V$DATAFILE view, 24-7

V$DB_CACHE_ADVICE view, 14-5, 14-8, 14-9, 14-10, 14-11, 14-13

V$DB_OBJECT_CACHE view, 24-4

V$FAST_START_SERVERS view, 17-16

V$FAST_START_TRANSACTIONS view, 17-16

V$FILESTAT view, 24-6

V$INSTANCE_RECOVERY view, 17-8

V$LATCH view, 24-9

V$LATCH_CHILDREN view, 24-12

V$LATCH_HOLDER view, 24-13

V$LIBRARYCACHE view, 24-15

NAMESPACE column, 14-27

V$LOCK view, 24-17

V$MYSTAT view, 24-22

V$OPEN_CURSOR view, 24-23

V$PARAMETER view, 24-25

V$PROCESS view, 24-27

V$QUEUE view, 19-5

V$ROLLSTAT view, 24-29

V$ROWCACHE view, 24-46

GETMISSES column, 14-32

GETS column, 14-32

performance statistics, 14-30

V$RSRC_CONSUMER_GROUP view, 22-7

V$SESSION view, 24-32

V$SESSION_EVENT view, 24-36

network information, 23-6

V$SESSION_WAIT view, 22-10, 24-38

network information, 23-6

V$SESSTAT view, 22-7, 24-46

network information, 23-6

statistics, 24-42

using, 14-35

V$SHARED_POOL_RESERVED view, 14-40

V$SQL view, 24-46

V$SQL_PLAN view, 24-47

V$SQLAREA view, 24-53

V$SQLTEXT view, 24-56

V$SYSSTAT view, 24-46

redo buffer allocation, 14-45

statistics, 24-58

tuning sorts, 14-56

using, 14-8

V$SYSTEM_EVENT view, 24-63

V$SYSTEM_PARAMETER view, 24-25

V$UNDOSTAT view, 13-11, 24-66

V$WAITSTAT view, 22-10, 24-68

VARCHAR datatype, 13-4

VARCHAR2 datatype, 13-4

variables

bind variables

optimization, 1-41

views

complex view merging, 2-34

counter/accumulator, 24-2

current state views, 24-2

histograms, 3-24

information views, 24-3

non-null values for nulls, 2-43

select-project-join views, 2-33

statistics, 3-15

vmstat UNIX command, 16-10

wait events

buffer busy waits, 22-22

contention wait events, 22-36

direct path, 22-30

event timings, 21-19

free buffer waits, 22-34

idle wait events, 22-43

log file switch, 22-41

network communication wait events, 22-20

reasons for, 24-68

resource wait events, 22-27

time units, 21-18

wait time description, 24-38