Oracle9i Database Performance Tuning Guide and Reference -- Index

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

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

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 mean time to recover

multiple buffer pools, 14-13

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

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

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

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

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

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

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

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

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

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