Index

registering with the database, 5-1, 12-7

OLTP, 3-1

parallel query, 3-4

parallel server, 3-8

ARCH process, 21-16

multiple, 26-75

architecture and CPU, 18-10

array interface, 22-2

asynchronous I/O, 26-29

attributes, 30-4

audit trail, 12-4

AUTOEXTEND, 20-28

Average Elapsed Time data view, 15-10

B*-tree index, 6-15, 6-19

backups

data warehouse, 11-9

disk mirroring, 26-44

tuning, 20-52

bandwidth, 26-3

Basic Statistics for Parse/Execute/Fetch drilldown data view, 15-18

BEGIN_DISCRETE_TRANSACTION procedure, 9-1, 9-3

benefit of tuning, 2-3

binary files

formatting using Oracle Trace, 15-5

bind variables, 19-16

bitmap

mapping to ROWIDs, 6-17

BITMAP CONVERSION row source, 6-20

bitmap index, 6-13, 6-18, 11-7

creating, 6-16

inlist iterator, 13-23

maintenance, 6-15

size, 6-20

storage considerations, 6-14

when to use, 6-13

BITMAP keyword, 6-16

BITMAP_MERGE_AREA_SIZE parameter, 6-15, 6-19, 7-22

block contention, 2-11

block size, 20-15

bottlenecks

disk I/O, 20-20

memory, 19-1

broadcast

reducing cache misses, 19-29

buffer cache, 2-10

memory allocation, 19-29

partitioning, 19-33

reducing buffers, 19-29

tuning, 19-25

buffer get, 4-5

buffer not pinned statistics, 19-27

buffer pinned statistics, 19-27

buffer pool

default cache, 19-31

keep cache, 19-32

multiple, 19-31, 19-32

RECYCLE cache, 19-31

syntax, 19-35

BUFFER_POOL clause, 19-35

BUFFER_POOL_name parameter, 19-34

build methods, 29-5

business rule, 1-8, 2-3, 2-7

cache hit ratios

callback

CATPARR.SQL script, 19-29

CATPERF.SQL file, 19-37

chained rows, 20-30

channel bandwidth, 17-4

CHECKPOINT option

ALTER SYSTEM statement, 25-6

checkpoints

choosing checkpoint frequency, 20-39

performance, 20-39

redo log maintenance, 20-39

tuning, 20-38

CHOOSE hint, 7-40

CKPT process, 20-41

client/server applications, 3-9, 18-4

CLUSTER hint, 7-43

clusters, 6-24

collections in Oracle Trace, 15-4, 15-22

columns, to index, 6-4

common joins, 31-9

COMPATIBLE parameter, 6-16, 26-66

and parallel query, 7-57

complete refresh, 32-4

composite indexes, 6-5

composite partitioning, 26-46

examples of, 13-17

performance considerations, 26-49

concurrent users

increasing the number of, 26-10

CONNECT BY, 13-26

connection load balancing

multi-threaded server, 23-5

connection manager, 22-4

in the multi-threaded server, 23-3

connection multiplexing

with the multi-threaded server, 23-3

connection pooling, 21-9

with the multi-threaded server, 23-3

connection-to-dispatcher

recommended ratio, 23-3

consistency, read, 18-7

consistent gets statistic, 19-26, 21-4, 21-22

consistent mode, TKPROF, 14-13

constraints, 6-10, 30-10, 31-22

contention

redo allocation latch, 21-19

redo copy latches, 21-19

rollback segments, 21-3

tuning, 21-1

tuning resource, 2-11

context area, 2-10

context switching, 18-4

cost-based optimization, 7-3, 11-8, 27-27

parallel execution, 27-27

procedures for Plan Stability, 7-32

upgrading to, 7-34

cost-based rewrite, 31-2

count column, SQL trace, 14-13

CPU

checking utilization, 18-3

detecting problems, 18-3

system architecture, 18-10

tuning, 18-1

utilization, 18-1, 26-3

cpu column, SQL trace, 14-13

CPU Statistics data view, 15-11

CPU Statistics for Parse/Execute/Fetch drilldown data view, 15-18

CPU_COUNT initialization parameter, 25-16

CREATE CLUSTER statement, 6-26

CREATE DIMENSION statement, 30-3

CREATE INDEX statement, 27-21

examples, 20-37

NOSORT option, 20-37

CREATE MATERIALIZED VIEW statement, 29-4

enabling query rewrite, 31-4

CREATE OUTLINE statement, 7-28

CREATE SNAPSHOT statement, 29-2

CREATE TABLE AS SELECT, 11-3, 27-18, 27-19, 27-31

CREATE TABLE statement

STORAGE clause, 20-24

TABLESPACE clause, 20-24

CREATE TABLESPACE statement, 20-24

CREATE_BITMAP_AREA_SIZE parameter, 6-15, 6-18

CREATE_STORED_OUTLINES parameter, 7-28

current column, SQL trace, 14-13

current mode, TKPROF, 14-13

CURSOR_NUM column

TKPROF_TABLE, 14-19

CURSOR_SPACE_FOR_TIME parameter

data

sources for tuning, 12-1

volume, 12-2

data block size, 20-15

data cache, 24-2

data dictionary, 12-2

data dictionary cache, 2-10, 19-20

Data Mart Builder, 28-9

Data Mart Designer, 28-9

data marts, 28-1

data sufficiency check, 31-13

data views in Oracle Trace, 15-6

data warehouse, 28-1

ANALYZE statement, 11-4

backup, 11-9

bitmap index, 11-7

data mart, 28-1

dimension tables

(lookup tables), 28-5

fact tables (detail tables), 28-6

features, 11-1

hierarchies

rolling up and drilling down, 30-2

introduction, 11-1

Oracle Parallel Server, 11-6

parallel aware optimizer, 11-6

parallel index creation, 11-3

parallel load, 11-4

partition, 11-4

partitioned table, 26-47

database

staging database, 28-1

statistics, gathering, 7-16

database administrator (DBA), 1-8

Database Connection event, 15-5

database writer process (DBWn)

behavior on checkpoints, 20-39

tuning, 18-8, 26-76

DATAFILE clause, 20-24

datafile placement on disk, 20-21

DATE_OF_INSERT column

TKPROF_TABLE, 14-18

db block gets statistic, 19-26, 21-4, 21-22

DB_BLOCK_BUFFERS parameter, 19-29, 19-34, 20-43

DB_BLOCK_LRU_LATCHES parameter, 19-34, 19-40

DB_BLOCK_MAX_DIRTY_TARGET

parameter, 20-41

DB_BLOCK_SIZE parameter

and parallel query, 26-28

tuning backups, 20-53

DB_FILE_DIRECT_IO_COUNT

parameter, 20-53

DB_FILE_MULTIBLOCK_READ_COUNT parameter, 7-21, 20-37, 26-28

DBA locking, 27-13

DBA_DATA_FILES view, 26-79

DBA_EXTENTS view, 26-79

DBA_HISTOGRAMS view, 7-6

DBA_INDEXES view, 6-16

DBA_OBJECTS view, 19-32

DBA_TAB_COLUMNS view, 7-5, 7-6

DBMS_APPLICATION_INFO package, 5-2, 5-3

DBMS_MVIEW package, 32-6

DBMS_MVIEW.REFRESH procedure, 32-3

DBMS_MVIEW.REFRESH_ALL_MVIEWS procedure, 32-3

DBMS_MVIEW.REFRESH_DEPENDENT procedure, 32-3

DBMS_OLAP package, 29-22, 32-15, 32-17

DBMS_OLAP.RECOMMEND_MV procedure, 29-22

DBMS_SHARED_POOL package, 10-3, 19-12, 19-24

DBMS_STATISTICS package, 31-3

DBMS_STATS package, 7-9, 32-16

DBMSPOOL.SQL script, 10-3, 19-12

DBMSUTL.SQL, 5-2

decision support, 3-3

processes, 27-3

query characteristics, 26-20

systems (DSS), 1-2

tuning, 26-3

with OLTP, 3-5

decision support systems (DSS)

dimensions, 30-1

decomposition of SQL statements, 8-3

default cache, 19-31

degree of parallelism

and adaptive multi-user, 26-7

setting, 26-7

demand rate, 1-5

dependencies

dimensions, 29-21

materialized views, 29-21

DEPTH column

TKPROF_TABLE, 14-19

design dictionary, 12-4

designing and tuning, 2-10

detail report in Oracle Trace, 15-6

detail tables, 28-6

same as fact tables, 28-6

details property sheet in Oracle Trace, 15-14

device bandwidth, 17-4

evaluating, 20-16

device latency, 17-5

diagnosing tuning problems, 17-1

dictionary-mapped tablespaces, 20-28

dimension tables, 11-8, 28-6

(lookup tables), 28-5

normalized, 30-7

dimensions, 30-1, 30-10, 31-22

altering, 30-11

creating, 30-3

dependencies, 29-21

dimension tables (lookup tables), 28-5

dropping, 30-12

hierarchies

rolling up and drilling down, 30-2

in a decision support system, 30-1

using, 30-1

validating, 30-10

direct-load insert, 26-76

external fragmentation, 27-13

disabled constraint, 6-10

Discoverer, 28-9

discrete transactions

example, 9-4

processing, 9-2, 9-3

when to use, 9-1

disk affinity

and parallel query, 27-16

disabling with MPP, 26-39

with MPP, 26-71

disk column, SQL trace, 14-13

Disk Reads data view, 15-9

Disk Reads/Execution Ratio data view, 15-9

Disk Reads/Logical Reads Ratio data view, 15-9

Disk Reads/Rows Fetched Ratio data view, 15-9

DISK_ASYNCH_IO parameter, 26-29

DISKRATIO

to distribute backup I/O, 20-52

disks

contention, 20-20, 20-21

distributing I/O, 20-20

I/O requirements, 20-4

layout options, 20-15

monitoring OS file activity, 20-17

number required, 20-4

placement of datafiles, 20-21

placement of redo log files, 20-21

reducing contention, 20-20

speed characteristics, 20-3

testing performance, 20-5

dispatcher processes (Dnnn), 21-9

dispatcher-to-connection

recommended ratio, 23-3

distributed query, 8-1, 8-10

distributing I/O, 20-20, 20-24

distribution

hints for, 7-58

DIUTIL package, 10-4

DML_LOCKS parameter, 26-26, 26-28

DOMAIN INDEX

and EXPLAIN PLAN, 13-24

domain indexes

using, 6-23

drilldown data views in Oracle Trace, 15-16

Basic Statistics for Parse/Execute/Fetch, 15-18

CPU Statistics for Parse/Execute/Fetch, 15-18

Parse Statistics, 15-18

Row Statistics for Execute/Fetch, 15-19

drilling down, 30-2

hierarchies, 30-2

DROP MATERIALIZED VIEW statement, 29-4

prebuilt tables, 29-17

DROP_BY_CAT

procedure of OUTLN_PKG, 7-30

DROP_UNUSED

procedure of OUTLN_PKG, 7-30

DSS database

dimensions, 30-1

DSS memory, 26-20

duration events in Oracle Trace, 15-5

dynamic extension, 20-26

avoiding, 20-28

dynamic performance views

enabling statistics, 14-4

for tuning, 16-1

elapsed column, SQL trace, 14-13

enabled constraint, 6-10

enforced constraint, 6-10

ENFORCED mode, 31-19

ENQUEUE_RESOURCES parameter, 26-26, 26-28

Enterprise Manager, 12-7

equijoin, 4-9

errors

common tuning, 2-14

during discrete transactions, 9-3

estimating materialized view size, 32-20

EVALUATE_UTILIZATION_W package, 32-21

events in Oracle Trace, 15-5

examples

ALTER SESSION statement, 14-5

CREATE INDEX statement, 20-37

DATAFILE clause, 20-24

discrete transactions, 9-4

execution plan, 4-7

EXPLAIN PLAN output, 4-7, 13-25, 14-15

full table scan, 4-8

indexed query, 4-8

NOSORT option, 20-37

SET TRANSACTION statement, 20-29

SQL trace facility output, 14-15

STORAGE clause, 20-24

table striping, 20-24

TABLESPACE clause, 20-24

executable code as data source, 12-4

Execute Elapsed Time data view, 15-10

execution plans, 13-1

examples, 4-7, 14-6

parallel operations, 27-30

Plan Stability, 7-25

preserving with Plan Stability, 7-25

TKPROF, 14-7, 14-10

expectations for tuning, 1-9

EXPLAIN PLAN statement, 31-20

and DOMAIN INDEX, 13-24

and full partition-wise joins, 13-21

and partial partition-wise joins, 13-19

and partitioned objects, 13-13

examples of output, 4-7, 13-25, 14-15

formatting output for, 13-24

introduction, 12-6

invoking with the TKPROF program, 14-10

parallel query, 27-30

PLAN_TABLE, 13-2

query parallelization, 26-73

restrictions, 13-28

SQL decomposition, 8-6

extents

size, 26-66

temporary, 26-70

unlimited, 20-28

fact tables, 11-8, 28-6

same as detail tables, 28-6

facts, 30-1

failover, 11-6, 25-17

FAST_START_IO_TARGET initialization parameter

controlling checkpoints with, 20-40

recovery time and the, 25-4

FAST_START_PARALLEL_ROLLBACK initialization parameter, 25-16

FAST_START_PARALLEL_ROLLBACK parameter

recovery parameter, 26-25

fast-start checkpoints

controlling checkpoints, 20-40

FAST_START_IO_TARGET initialization parameter, 25-4

LOG_CHECKPOINT_INTERVAL initialization parameter, 25-4

LOG_CHECKPOINT_TIMEOUT initialization parameter, 25-3

fast-start on-demand rollback, 25-15

fast-start parallel rollback, 25-15

Fetch Elapsed Time data view, 15-11

file storage, designing, 20-5

FILESPERSET parameter

tuning backups, 20-53

FIRST_ROWS hint, 7-39, 26-24

FORMAT statement in Oracle Trace, 15-19, 15-20

formatter tables in Oracle Trace, 15-5

formatting

EXPLAIN PLAN output, 13-24

fragmentation, external, 27-13

free lists

adding, 21-22

contention, 21-21

reducing contention, 21-22

FREELISTS parameter, 26-75

FULL hint, 6-7, 7-42

full partition-wise joins, 13-21, 26-52

full table scan, 4-8

function-based indexes, 6-12

GATHER_ INDEX_STATS

GATHER_DATABASE_STATS

GATHER_SCHEMA_STATS

GATHER_TABLE_STATS

GC_FILES_TO_LOCKS parameter, 27-13

GC_ROLLBACK_LOCKS parameter, 27-14

GC_ROLLBACK_SEGMENTS parameter, 27-14

GETMISSES, V$ROWCACHE table, 19-20

GETS, V$ROWCACHE table, 19-20

global index, 26-74

goals for tuning, 1-9, 2-12

GROUP BY

decreasing demand for, 27-7

NOSORT, 20-38

grouping

compatibility check, 31-16

conditions, 31-23

GV$FILESTAT view, 26-78

handle

user, 25-23

hash

hash partitioning, 13-14, 26-46

hash area, 2-11, 27-3

HASH hint, 7-44

hash join, 26-22, 27-3

examples of, 13-15

HASH_AJ hint, 7-44, 7-55, 7-56

HASH_AREA_SIZE parameter, 7-21, 26-21

and parallel execution, 26-21

example, 27-7

relationship to memory, 27-7

HASH_JOIN_ENABLED parameter, 7-22

HASH_MULTIBLOCK_IO_COUNT parameter, 7-22, 26-29

HASH_SJ hint, 7-44, 7-49, 7-56

hashed partitions

performance considerations, 26-48

hashing, 6-25

HASHKEYS parameter

CREATE CLUSTER statement, 6-26

hierarchies, 30-2

multiple, 30-6

rolling up and drilling down, 30-2

hints, 7-36

histogram

creating, 7-5

number of buckets, 7-6

viewing, 7-6

HOLD_CURSOR, 19-10

ID column

in avoiding dynamic extension, 20-28

INDEX hint, 6-7, 6-16, 7-44

index join, 27-7

INDEX_ASC hint, 7-46

INDEX_COMBINE hint, 6-7, 6-16

INDEX_DESC hint, 7-46, 7-47

INDEX_FFS hint, 6-9, 7-48, 11-3

indexes

INITIAL extent size, 26-66, 27-13

initialization parameters

initialization parameters in Oracle Trace, 15-21

inlists, 7-46, 7-50

INSERT

append, 7-61

functionality, 27-22

internal write batch size, 20-43

intra transaction recovery, 25-16

I/O

analyzing needs, 20-2

asynchronous, 26-29

balancing, 20-22

distributing, 20-20, 20-24

insufficient, 17-5

multiple buffer pools, 19-32

parallel execution, 26-3

Statistics for Parse/Execute/Fetch, 15-18

striping to avoid bottleneck, 26-37

testing disk performance, 20-5

tuning, 2-11, 20-2

ISOLATION LEVEL, 9-5

join compatibility, 31-9

joins, 29-10

full partition-wise, 26-52

parallel, and PQ_DISTRIBUTE hint, 7-58

partial partition-wise, 26-57

partition-wise, 26-52

partition-wise, examples of full, 13-21

partition-wise, examples of partial, 13-19

partition-wise, full, 13-21

LARGE_POOL_SIZE, 20-54

LARGE_POOL_SIZE parameter, 26-12

latches

contention, 2-12, 18-9

redo allocation latch, 21-16

redo copy latches, 21-16

least recently used list (LRU), 18-8

library cache, 2-10

memory allocation, 19-15

tuning, 19-13

listening queue, 22-3

load

parallel, 11-4, 26-68

load balancing, 11-6, 20-22

multi-threaded server, 23-5

LOBS

temporary, 19-30

local index, 26-74

local striping, 26-40

lock contention, 2-11

log, 21-15

log buffer tuning, 2-10, 19-7

log writer process (LGWR) tuning, 20-21, 20-40

LOG_BUFFER parameter, 19-6, 20-40

and parallel execution, 26-26

setting, 21-16

LOG_CHECKPOINT_INTERVAL initialization parameter, 20-39

recovery time, 25-4

LOG_CHECKPOINT_TIMEOUT initialization parameter, 20-39

recovery time, 25-3

LOG_SIMULTANEOUS_COPIES parameter, 21-19

LOG_SMALL_ENTRY_MAX_SIZE parameter, 21-19

LOGGING option, 26-76

Logical Reads data view, 15-9

Logical Reads/Rows Fetched Ratio data view, 15-9

logical structure of database, 2-9

long waits

definition, 20-47

lookup tables, 28-5

(dimension tables), 28-5

LRU

aging policy, 19-31

latch, 19-34, 19-40

latch contention, 19-40, 21-20

Management Information Base (MIB), 12-5

manual refresh, 32-6

manual striping, 26-39

massively parallel system, 26-3

materialized views, 11-2

max session memory statistic, 19-22

MAX_DUMP_FILE_SIZE, 14-4

SQL Trace parameter, 14-4

MAXEXTENTS keyword, 26-66, 27-13

MAXOPENCURSORS, 19-10

MAXOPENFILES parameter

tuning backups, 20-53

media recovery, 26-70

memory

configure at 2 levels, 26-20

insufficient, 17-4

process classification, 27-3

reducing usage, 19-42

memory allocation

shared SQL areas, 19-15

sort areas, 20-34

tuning, 19-2, 19-41

users, 19-6

memory/user/server relationship, 27-2

MERGE hint, 7-64

MERGE_AJ hint, 7-49, 7-55, 7-56

method

MIB, 12-5

mirroring

redo log files, 20-21

monitoring, 12-5

parallel processing, 26-78

MPP

disk affinity, 26-39

MTS

large pool, 19-21

shared pool, 19-21

MTS_DISPATCHERS parameter, 21-8, 21-9

MTS_MAX_DISPATCHERS parameter, 21-8, 23-3

MTS_MAX_SERVERS parameter, 21-12, 23-4

MTS_SERVERS parameter, 23-4

setting, 21-13

multi-block reads, 20-27

MULTIBLOCK_READ_COUNT parameter, 26-66

multiple archiver processes, 26-75

multiple buffer pools, 19-31, 19-32, 19-35

multiple hierarchies, 30-6

multiple user handles, 25-23

multiplexing

with multi-threaded server, 23-1

multi-purpose applications, 3-5

multi-threaded server, 27-3

benefits, 23-1

configuring dispatchers, 23-3

connection pooling, 23-3

context area size, 2-10

definition, 23-1

large pool, 19-21

performance issues, 23-8

reducing contention, 21-5

setting up, 23-1

shared pool, 19-21

shared pool and, 19-21

tuning, 21-5, 23-1

tuning memory, 23-6

views with statistics for, 23-7

multi-threaded server

connection multiplexing, 23-3

multi-tier systems, 18-12

NAMESPACE column

V$LIBRARYCACHE table, 19-13

nested loop join, 27-3

nested query, 27-18

network

array interface, 22-2

bandwidth, 17-5

constraints, 17-5

detecting performance problems, 22-1

prestarting processes, 22-2

problem solving, 22-2

Session Data Unit, 22-3, 23-5

NO_PUSH_JOIN_PRED hint, 7-66

NOAPPEND hint, 7-62, 27-23

NOARCHIVELOG mode, 26-77

NOCACHE hint, 7-64

NOLOGGING option, 26-71, 26-76, 27-19, 27-21, 27-23

NOPARALLEL attribute, 27-17

NOPARALLEL hint, 7-58

NOPARALLEL_INDEX hint, 7-63

NOREWRITE hint, 7-51, 31-4, 31-21

NOSORT option, 20-37, 20-38

NT performance, 24-4

Number of Rows Processed data view, 15-11

OBJECT_INSTANCE column

OBJECT_NAME column

OBJECT_NODE column

OBJECT_OWNER column

OBJECT_TYPE column

../../server.815/a67846/toc.htmStmtFetch, 25-24

../../server.815/a67846/toc.htmTransRollback, 25-24

online transaction processing (OLTP), 1-2, 3-1

processes, 27-3

with decision support, 3-5

OPEN_CURSORS parameter

allocating more private SQL areas, 19-9

increasing cursors per session, 19-15

operating system

data cache, 24-2

monitoring disk I/O, 20-17

monitoring tools, 12-3

striping, 26-38

tuning, 2-12, 17-6, 19-4

OPERATION column

PLAN_TABLE, 13-4, 13-9

OPTIMAL storage parameter, 20-30

optimization

choosing an approach and goal for, 7-3

query rewrite

matching join graphs, 29-6

query rewrites

OPTIMIZER column

OPTIMIZER_FEATURES_ENABLED parameter, 7-21

OPTIMIZER_INDEX_CACHING

for index optimization, 7-22

OPTIMIZER_INDEX_COST_ADJ

for index optimization, 7-22

OPTIMIZER_MODE, 7-3, 7-4, 7-21, 7-24, 7-38, 11-8, 27-27

OPTIMIZER_PERCENT_PARALLEL parameter, 7-21, 11-7, 26-23, 27-30

OPTIMIZER_SEARCH_LIMIT parameter, 7-22

OPTIONS column

Oracle Express Objects, 28-9

Oracle Designer, 28-9

Oracle Expert, 2-1, 12-11

Oracle Forms, 14-5

control of parsing and private SQL areas, 19-10

Oracle Network Manager, 22-3

Oracle Parallel Server, 3-8, 11-6

CPU, 18-13

disk affinity, 27-16

parallel execution, 27-13

parallel load, 26-69

ST enqueue, 27-12

synchronization points, 2-8

Oracle Parallel Server Management (OPSM), 12-12

Oracle Performance Manager, 12-10

Oracle Precompilers

control of parsing and private SQL areas, 19-10

Oracle Sales Analyzer, 28-9

Oracle Server

client/server configuration, 3-9

configurations, 3-6

events, 15-5

Oracle striping, 26-39

Oracle Trace, 15-1, 19-39, 32-16

accessing collected data, 15-5

binary files, 15-5

collection results, 15-25

collections, 15-4, 15-22

command-line interface, 15-19

data views, 15-6

deleting files, 15-21

details property sheet, 15-14

drilldown data views, 15-16, 15-18

Basic Statistics for Parse/Execute/Fetch, 15-18

CPU Statistics for Parse/Execute/Fetch, 15-18

Parse Statistics, 15-18

Row Statistics for Execute/Fetch, 15-19

duration events, 15-5

events, 15-5

FORMAT statement, 15-19, 15-20

formatter tables, 15-5

formatting data, 15-25

Oracle Trace Data Viewer, 15-6

parameters, 15-21

point events, 15-5

predefined data views, 15-6

reporting utility, 15-6, 15-26

SQL statement property sheet, 15-14

START statement, 15-19, 15-20

STOP statement, 15-19, 15-20

stored procedures, 15-24

using to collect workload data, 15-3

viewing data, 15-12

Oracle Trace Data Viewer, 15-6

Oracle Trace Manager, 15-4, 15-22

used for formatting collections, 15-5

ORACLE_TRACE_COLLECTION_NAME parameter, 15-21, 15-22

ORACLE_TRACE_COLLECTION_PATH parameter, 15-21

ORACLE_TRACE_COLLECTION_SIZE parameter, 15-21

ORACLE_TRACE_ENABLE parameter, 15-21, 15-22

ORACLE_TRACE_FACILITY_NAME parameter, 15-21, 15-22

ORACLE_TRACE_FACILITY_PATH parameter, 15-21

order

preserving, 13-26

ORDER BY, 13-26

decreasing demand for, 27-7

ORDERED hint, 7-51

ORDERED_PREDICATES hint, 7-67

OTHER column

PLAN_TABLE table, 13-5

outer joins, 31-22

outlines

assigning category names to, 7-28

CREATE OUTLINE statement, 7-28

creating and using, 7-28

creating, using, 7-28

dropping, 7-30

dropping unused, 7-30

execution plans and Plan Stability, 7-25

hints, 7-26

managing with OUTLN_PKG, 7-30

matching with SQL statements, 7-27

reassigning categories, 7-31

storage requirements, 7-27

tables, moving, 7-31

using, 7-28

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

viewing data for, 7-29

OUTLN_PKG

package to manage outlines, 7-30

overhead, process, 27-3

overloaded disks, 20-20

oversubscribing resources, 27-5, 27-9

packages

DBMS_APPLICATION_INFO, 5-2, 5-3

DBMS_SHARED_POOL, 10-3

DBMS_TRANSACTION, 9-4

DIUTIL, 10-4

OUTLN_PKG, 7-30

registering with the database, 5-1, 12-7

STANDARD, 10-4

page table, 18-4

paging, 17-4, 18-4, 26-84, 27-5, 27-31

SGA, 19-41

parallel aware optimizer, 11-6

PARALLEL clause, 27-22, 27-23

RECOVER statement, 25-14

PARALLEL CREATE INDEX statement, 26-25

PARALLEL CREATE TABLE AS SELECT, 11-3

external fragmentation, 27-13

resources required, 26-25

parallel execution, 3-4

PARALLEL hint, 7-57, 27-17, 27-22, 27-30

parallel index, 27-21

creation, 11-3

parallel joins

and PQ_DISTRIBUTE hint, 7-58

parallel load, 11-4

example, 26-68

Oracle Parallel Server, 26-69

using, 26-63

parallel partition-wise joins

performance considerations, 26-61

parallel recovery, 25-14

parallel server, 3-8

disk affinity, 27-16

parallel execution tuning, 27-13

tuning, 12-12

PARALLEL_ADAPTIVE_MULTI_USER parameter, 26-7, 26-32

PARALLEL_AUTOMATIC_TUNING parameter, 26-4

PARALLEL_BROADCAST_ENABLE parameter, 26-24

PARALLEL_EXECUTION_MESSAGE_SIZE parameter, 26-23

PARALLEL_MAX_SERVERS initialization parameter, 25-14, 26-10

PARALLEL_MAX_SERVERS parameter, 25-14, 26-9, 26-11, 27-6

and parallel execution, 26-9

PARALLEL_MIN_PERCENT parameter, 26-10, 26-19

PARALLEL_MIN_SERVERS parameter, 26-11

PARALLEL_SERVER_INSTANCES

and parallel execution, 26-19

PARALLEL_THREADS_PER_CPU parameter, 26-4, 26-8

parallelism

degree, overriding, 27-17

enabing for tables and queries, 26-8

setting degree of, 26-7

parameter file, 12-4

PARENT_ID column

Parse Elapsed Time data view, 15-10

Parse Statistics drilldown data view, 15-18

Parse/Execution Ratio data view, 15-10

parsing, 18-6

Oracle Forms, 19-10

Oracle Precompilers, 19-10

reducing unnecessary calls, 19-9

partial partition-wise joins, 26-57

partition

pruning, 26-51

partition elimination, 8-6

partition view, 8-6, 11-4

PARTITION_VIEW_ENABLED parameter, 8-7

partitioned objects

and EXPLAIN PLAN, 13-13

partitioned table, 11-4

data warehouse, 26-47

partitioning

examples of composite, 13-17

examples of, 13-15

hash, 26-46

indexes, 26-46

range, 26-46

start and stop columns, 13-15

partition-wise joins, 26-52

benefits of, 26-60

full, 13-21

full, and EXPLAIN PLAN output, 13-21

parallel, performance considerations for, 26-61

partial, 26-57

partial, and EXPLAIN PLAN output, 13-19

PCM lock, 27-13

PCTFREE, 2-11, 20-31

PCTINCREASE parameter, 20-36

and SQL.BSQ file, 20-33

PCTUSED, 2-11, 20-31

performance

client/server applications, 3-9

decision support applications, 3-3

different types of applications, 3-1

monitoring registered applications, 5-1, 12-7

evaluating, 1-10

key factors, 17-2

mainframe, 24-4

NT, 24-4

OLTP applications, 3-1

Parallel Server, 3-8

UNIX-based systems, 24-4

Performance Manager, 12-10

Performance Monitor, NT, 18-4

PHYRDS column

V$FILESTAT table, 20-19

physical database layout, 26-36

physical reads statistic, 19-26

PHYWRTS column

V$FILESTAT table, 20-19

ping UNIX command, 12-3

pinging, 2-12

PINS column

V$LIBRARYCACHE table, 19-14

Plan Stability, 7-25

limitations of, 7-25

preserving execution plans, 7-25

procedures for the cost-based optimizer, 7-32

use of hints, 7-25

PLAN_TABLE table

ID column, 13-4

OBJECT_INSTANCE column, 13-4

OBJECT_NAME column, 13-4

OBJECT_NODE column, 13-4

OBJECT_OWNER column, 13-4

OBJECT_TYPE column, 13-4

OPERATION column, 13-4

OPTIMIZER column, 13-4

OPTIONS column, 13-4

OTHER column, 13-5

PARENT_ID column, 13-4

POSITION column, 13-5

REMARKS column, 13-3

SEARCH_COLUMNS column, 13-4

STATEMENT_ID column, 13-3

structure, 13-2

TIMESTAMP column, 13-3

PL/SQL

package, 12-7

tuning PL/SQL areas, 19-7

point events in Oracle Trace, 15-5

POOL attribute, 21-9

POSITION column

PLAN_TABLE table, 13-5

POST_TRANSACTION option, 25-22

PQ_DISTRIBUTE hint, 7-58

PRE_PAGE_SGA parameter, 19-5

prebuilt materialized view, 29-4

PRIMARY KEY constraint, 6-10, 27-21

private SQL areas, 19-9

PRIVATE_SGA variable, 23-6

proactive tuning, 2-1

process

and memory contention in parallel processing, 26-10

classes of parallel execution, 26-37, 26-39, 26-72, 27-4

dispatcher process configuration, 21-9

DSS, 27-3

maximum number, 17-6, 27-2

maximum number for parallel query, 27-2

OLTP, 27-3

processing, distributed, 3-9

pruning

partition, 26-51

using DATE columns, 26-52

PRVTPOOL.PLB, 10-4

PUSH_JOIN_PRED hint, 7-65

queries

avoiding the use of indexes, 6-7

distributed, 8-1, 8-10

enabling parallelism for, 26-8

ensuring the use of indexes, 6-6

query column, SQL trace, 14-13

query delta joins, 31-11

query rewrite

initialization parameters, 31-4

matching join graphs, 29-6

methods, 31-6

privileges, 31-4

query server process

tuning, 21-14

RAID, 20-25, 26-45, 26-70

random reads, 20-5

random writes, 20-5

range

range partitioning, 13-13, 26-46

examples of, 13-15

performance considerations, 26-47

raw device, 24-2

reactive tuning, 2-3

read consistency, 18-7

READ_CLIENT_INFO

READ_MODULE

read/write operations, 20-5

REBUILD, 6-9

RECOMMEND_MV function, 32-17

RECOMMEND_MV_W function, 32-17

record keeping, 2-13

RECOVER statement

PARALLEL clause, 25-14

recovery

data warehouse, 11-9

effect of checkpoints, 20-39

media, with striping, 26-44

parallel

intra transaction recovery, 25-16

parallel processes for, 25-14

PARALLEL_MAX_SERVERS initialization parameter, 25-14

setting number of processes to use, 25-14

RECOVERY_PARALLELISM initialization parameter, 25-14

recursive calls, 14-14, 20-26

recursive SQL, 10-1

RECYCLE cache, 19-31

redo allocation latch, 21-16, 21-19

REDO BUFFER ALLOCATION RETRIES, 21-15

redo copy latches, 21-17, 21-19

choosing how many, 21-17

redo log buffer tuning, 19-6

redo log files

mirroring, 20-21

placement on disk, 20-21

reducing

buffer cache misses, 19-29

contention

dispatchers, 21-6

OS processes, 24-2

query servers, 21-15

redo log buffer latches, 21-15

shared servers, 21-10

data dictionary cache misses, 19-20

library cache misses, 19-15

paging and swapping, 19-4

rollback segment contention, 21-4

unnecessary parse calls, 19-9

reference tables, 28-5

refresh options, 29-7

registering applications with database, 5-1, 12-7

regression, 27-29, 27-31

Relational Access Manager, 28-9

RELEASE_CURSOR, 19-10

RELOADS column

V$LIBRARYCACHE table, 19-14

REMARKS column

PLAN_TABLE table, 13-3

remote SQL statement, 8-1

Re-Parse Frequency data view, 15-10

reparsing, 18-6

resource

adding, 1-4

consumption, parameters affecting, 26-20

consumption, parameters affecting parallel DML/DDL, 26-25

limiting for users, 26-10

limits, 26-9

oversubscribing, 27-5

oversubscription, 27-9

parallel query usage, 26-20

tuning contention, 2-11

response time, 1-2, 1-3

optimizing, 7-4, 7-39

REWRITE hint, 7-50, 31-4, 31-21

rewrites

hints, 31-21

initialization parameters, 31-4

privileges, 31-4

query optimization

hints, 31-4, 31-21

matching join graphs, 29-6

RMAN

tuning for backups, 20-52

roles in tuning, 1-7

rollback segments, 18-8, 26-25

assigning to transactions, 20-29

choosing how many, 21-4

contention, 21-3

creating, 21-4

detecting dynamic extension, 20-26

dynamic extension, 20-29

ROLLBACK_SEGMENTS parameter, 26-25

rollbacks

fast-start on-demand, 25-15

fast-start parallel, 25-15

rolling up a hierarchy, 30-2

round-robin

Row Statistics for Execute/Fetch drilldown data views, 15-19

ROWID hint, 7-43

ROWIDs

mapping to bitmaps, 6-17

rows column, SQL trace, 14-13

Rows Fetched/Fetch Count Ratio data view, 15-11

Rows Sorted data view, 15-11

RowSource event, 15-5

RULE hint, 7-41, 27-27

rule-based optimization, 7-24

sar UNIX command, 18-4, 26-83

scalability, 11-6, 18-8

scalable operations, 26-72

schema design guidelines

materialized views, 28-7

schema statistics

gathering, 7-14

SEARCH_COLUMN column

serializable transactions, 9-5

segments, 20-26

selectivity, index, 6-4

sequence cache, 2-10

sequential reads, 20-5

sequential writes, 20-5

server/memory/user relationship, 27-2

service time, 1-2, 1-3

Session Data Unit (SDU), 22-3, 23-5

session memory statistic, 19-22

SESSION_CACHED_CURSORS parameter, 18-7, 19-18

SET TRANSACTION statement, 20-29

SET_ACTION

SET_CLIENT_INFO

SET_MODULE

keeping objects pinned in, 10-3

SGA size, 19-7, 26-20

SGA statistics, 16-2

shared pool, 2-10

contention, 2-11

multi-threaded server and, 19-21

tuning, 19-10, 19-23

shared servers

configuring, 23-4

processes, 21-13

shared SQL areas

finding large areas, 10-5

identical SQL statements, 10-2

keeping in the shared pool, 10-3

memory allocation, 19-15

statements considered, 10-1

SHARED_POOL_RESERVED_SIZE parameter, 19-24, 19-25

SHARED_POOL_SIZE parameter, 19-20, 19-25

allocating library cache, 19-15

tuning the shared pool, 19-21

short waits

definition, 20-47

SHOW SGA statement, 19-6

Simple Network Management Protocol (SNMP), 12-5

single table aggregate requirements, 29-13

single tier, 18-11

SIZES procedure, 10-5

skew

workload, 27-32

SNMP, 12-5

sort areas

memory allocation, 20-34

process local area, 2-11

SORT_AREA_RETAINED_SIZE parameter, 19-41, 20-36

SORT_AREA_SIZE parameter, 6-15, 7-21, 19-41, 26-22

and parallel execution, 26-22

tuning sorts, 20-35

SORT_MULTIBLOCK_READ_COUNT parameter, 20-37, 26-29

sorts

(disk) statistic, 20-34

(memory) statistic, 20-34

avoiding on index creation, 20-37

tuning, 20-34

Sorts in Memory data view, 15-11

Sorts on Disk data view, 15-11

source data for tuning, 12-1

space management, 26-70

parallel execution, 27-12

reducing transactions, 27-12

spin count, 18-9

SPINCOUNT parameter, 21-2

SQL area tuning, 19-7

SQL Loader, 26-63

SQL Parse event, 15-5

SQL statement property sheet in Oracle Trace, 15-14

SQL statements

avoiding the use of indexes, 6-7

decomposition, 8-3

ensuring the use of indexes, 6-6

inefficient, 18-7

matching with outlines, 7-27

modifying indexed data, 6-5

recursive, 10-1

reparsing, 18-6

tuning, 2-10

SQL text match, 31-7, 31-22

SQL trace facility, 12-6, 14-2, 14-6, 19-8, 19-39

enabling, 14-4

example of output, 14-15

output, 14-12

parse calls, 19-8

statement truncation, 14-14

steps to follow, 14-3

trace file, 12-3

trace files, 14-4

SQL*Plus script, 12-7

SQL_STATEMENT column

TKPROF_TABLE, 14-18

SQL_TRACE parameter, 14-5

SQL.BSQ file, 20-33

SQLUTLCHAIN.SQL, 12-7

ST enqueue, 27-12

staging database, 28-1

staging file, 28-1

STALE_TOLERATED mode, 31-19

STANDARD package, 10-4

STAR hint, 7-52

star query, 11-8

star schema, 11-8

star transformation, 7-66, 11-8

STAR_TRANSFORMATION hint, 7-66, 11-8

STAR_TRANSFORMATION_ENABLED parameter, 7-67, 11-8

start columns

in partitioning and EXPLAIN PLAN, 13-15

START statement in Oracle Trace, 15-19, 15-20

STATEMENT_ID column

PLAN_TABLE table, 13-3

statistics, 16-2, 27-31, 31-23

stop columns

in partitioning and EXPLAIN PLAN, 13-15

STOP statement in Oracle Trace, 15-19, 15-20

storage

file, 20-5

STORAGE clause

CREATE TABLE statement, 20-24

examples, 20-24

modifying parameters, 20-33

modifying SQL.BSQ, 20-33

OPTIMAL, 20-30

parallel query, 27-21

stored outlines

assigning category names to, 7-28

creating, using, 7-28

dropping, 7-30

dropping unused, 7-30

execution plans and Plan Stability, 7-25

hints, 7-26

managing with OUTLN_PKG, 7-30

matching with SQL statements, 7-27

reassigning categories, 7-31

storage requirements, 7-27

tables, moving, 7-31

using, 7-28

viewing data for, 7-29

stored procedures

in Oracle Trace, 15-24

KEEP, 10-5

READ_MODULE, 5-5

registering with the database, 5-1, 12-7

striping, 20-22, 26-38

analyzing, 26-41

and disk affinity, 27-16

example, 26-63

examples, 20-24

local, 26-40

manual, 20-23, 26-38, 26-39

media recovery, 26-44

operating system, 26-38

operating system software, 20-25

Oracle, 26-39

temporary tablespace, 26-70

subquery

correlated, 27-18

summary advisor, 32-14

summary management, 28-3

switching processes, 18-4

symmetric multiprocessor, 26-3

System Global Area tuning, 19-5

system-specific Oracle documentation

software constraints, 17-6

table statistics

tables

dimension tables (lookup tables), 28-5

enabling parallelism for, 26-8

fact tables, 28-6

formatter, in Oracle Trace, 15-5

placement on disk, 20-22

striping examples, 20-24

tablespace

creating, example, 26-64

dedicated temporary, 26-70

dictionary-mapped, 20-28

temporary, 20-36

TABLESPACE clause, 20-24

CREATE TABLE statement, 20-24

TAPE_ASYNCH_IO parameter, 26-29

TCP.NODELAY option, 22-3

temporary extent, 26-70

TEMPORARY keyword, 20-36

temporary LOBS, 19-30

temporary tablespace

optimizing sort, 20-36

optimizing, 7-4, 7-38

tiers, 18-11

TIMED_STATISTICS parameter, 14-4, 24-1, 26-79

SQL Trace, 14-4

TIMESTAMP column

PLAN_TABLE table, 13-3

TKPROF program, 14-2, 14-6, 19-39

editing the output SQL script, 14-17

example of output, 14-15

generating the output SQL script, 14-17

introduction, 12-6

syntax, 14-7

using the EXPLAIN PLAN statement, 14-10

TKPROF_TABLE, 14-18

querying, 14-17

TNSNAMES.ORA, 25-21

Total Elapsed Time data view, 15-10

Trace, Oracle, 15-1

transaction processing monitor, 18-12, 18-13

TRANSACTIONAL option

SHUTDOWN, 25-22

transactions

assigning rollback segments, 20-29

discrete, 9-1

rate, 27-12

serializable, 9-5

TRANSACTIONS parameter, 26-25

transmission time, 17-5

transparent application failover, 25-19

Transparent Gateway, 8-11

triggers

in tuning OLTP applications, 4-4

TRUSTED mode, 31-19

tuning

access path, 2-10

and design, 2-10

application design, 2-9

business rule, 2-7

checkpoints, 20-38

client/server applications, 3-9

contention, 21-1

CPU, 18-1

data design, 2-7

data sources, 12-1

database logical structure, 2-9

decision support systems, 3-3

diagnosing problems, 17-1