Index

A  B  C  D  E  F  G& nbsp; H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  

---

A

access paths

cluster scans, 1 4-27

defined, 14-17

execution plans, 14-15

hash scans, 14-28

index scans, < a href="optimops.htm#51691">14-21

Active Session History, 5-4

addmrpt.sql

Automatic Database Diagnostic Monitor, 6-8

advisors

accessing with Oracle Enterprise Manager, 1-7

ALL_OUTLINE_HINTS view

s tored outline hints, 18-9

ALL_OUTLINES view

stored outlines, 18-9

ALL_ROWS hint, 14-5, 17-13

ALL_ROWS optimizer mode parameter, 14-4

allocation

of memory, 7-2

ALTER INDEX statement, 16-7

ALTER SESSION st atement

examples, 20-14

SET SESSION_CACHED_ CURSORS clause, 7-42

ANALYZE statement, 15-7

antijoins, 14-30

APPEND hint, 17-41

applications

deploying, 2-26

design principles, 2-13

development trends, 2-21

implementing, 2-19

arra y interface, 11-13

Automatic Database Diagnostic Monitor, i-xxviii, 6-2

accessing with Oracle Enterprise Manage r, 6-7

actions and rationales of recommendations, 6-5

addmrpt.sql report, 6-8

analysis results exampl e, 6-5

and DB time, 6-3

DBIO_EXPECTED, 6-6

DBMS_ADVISOR package, 6-10

example report, 6-5

findings, 6-4

overview, 6-3

results, 6-4

running with APIs, 6-10

setups, 6-6

STATISTICS_LEVEL parameter, 6-6

types of problems considered, 6-3

types of recommendations, 6-4

automatic database diagnostic monitoring, 1-7, 12-6

automatic segment-space management, 4-6, 8-12, 10-26

Automatic Shared Memory Management, 7-3

automatic SQL tuning, 1-7, 12-7

analysis, 13-2< /dd>

features, 13-1

overview, 13- 2

Automatic Tuning Optimizer, 13-2

automat ic undo management, 4-4

mode, 4-4

Automatic Workload Repository, i-xxviii, 1-7

accessing with Oracle Enterprise Manager, 5-12< /a>

data gathering, 5-2

DBMS_WORKLOAD_REPOSITORY package , 5-13

default settings, 5-11

factors affecting space usage, 5-11

managing with APIs, 5-13

minimizing space usage, 5-11

over view, 5-10

recommendations for retention period, 5-12

reports, 5-17

retention period, 5-11

settings in DBA_HIST_WR_CONTROL view, 5-15

space usage, 5-11

statistics collected, 5-10

turning off automatic snapshot collection, 5-12

unusual percentages in reports, 5-17

views for accessing data, 5-16

awrrpt.sql

Automatic Workload Repository report, 5-17

---

B

baselines, 1-3

performance, 5-2

preserv ed snapshot sets, 5-12

benchmarking workloads, 2-23

big bang rollout strategy, 2-26

bind variab les, 7-24

peeking, 14-12

bitmap indexes, 2-15

inlist iterator, 19-22

on joins, 16-12

when to use, 16-12

block cleanout, 10-19

block size

choosing, 8-11

optimal, 8-11

bottlenecks

elimination, 1-5

fixing, 3-2

identifying, 3-2

memory, 7-2< /dd>

resource, 10-24

broadcast

distribution value, 19-27

B-tree indexes, 2-15

buffer busy wait events, 10-17, 10-25

actions, 10-26

buffer cache

contention, 10-27, 10-29, 10-42

hit ratio, 7-12

reducing buffers, 7-14, 7-36

buffer pools

default cache, 7-16

hit ratio, 7-17

KEEP, 7-19

KEEP cache, 7-16

multiple, 7-15

RECYCLE cache, 7-15

business logic, 2-9, 2-19

BYTES column

PLAN_TABLE table, 19-24

---

C

CACHE hint, 17-42

caching tables

automatic caching of small tables, < a href="hintsref.htm#13719">17-43

CARDINALITY column

PLAN_TAB LE table, 19-24

cartesian joins, 14-36

chained rows, 10-20

CHOOSE hint, 14-5

CHOOSE optimizer mode parameter, 14-4

classes

wait events, 5-3, 10-8

client/server applications, 9-11

CLUST ER hint, 17-17

clusters, 16-14

hash and scans of, 14-28

scans of, 14-27

sorted hash, 16-15

column or der

indexes, 2-17

columns

to index, 16-4

COMPATIBLE initialization p arameter, 4-3

components

hardware, 2-7

software, 2-8

compos ite indexes, 16-5

composite partitioning

exa mples of, 19-16

conceptual modeling, 3-5

connection manager, 11-14

consistency

read, 10-18

consistent gets from cache sta tistic, 7-11

consistent mode

TKPROF, 20-21

constraints, 16-9

contention

library cache latch, 10-42

memory, 7-2, 10-1

shared pool, 10-42

tuning, 10-1

w ait events, 10-40

context switches, 9-11

CONTROL_FILES initialization parameter, 4-2

cos t

optimizer calculation, 14-9

CO ST column

PLAN_TABLE table, 19-24

cost-based optimizations, 14-9

procedures for plan stability, < a href="outlines.htm#16851">18-12

upgrading to, 18-14

cpu statistics, 10-4

CPU_COSTING hint, 14-5

CPUs, 2-7

statistics, 5-6

utilization, 9-10

CREATE INDEX statement

PARALLEL clause, 4-10

CREATE OUTLINE statement, 18-5

CREATE_STORED_OUTLINES initializa tion parameter, 18-5, 18-6

CREATE_STORED_OUTL INES parameter, 18-5

current mode

TKPROF, 20-21

CURSOR_NUM column

TKPROF_T ABLE table, 20-28

CURSOR_SHARING initialization parameter, 7-26, 7-46, 14-8

CURSOR_S HARING_EXACT hint, 17-46

CURSOR_SPACE_FOR_TIME initialization parameter

setting, 7-40

cursors

accessing, 7-27

sharing, 7-27

---

D

data

and transactions, 2-9

cache, 9-2

gathering, 5-2

modeling, 2-14

queries, 2-12

searches, 2-12

data dictionary, 7-36

statistics in, 15-19

views used in optimization, 15-19

database monitoring, 1-7, 12-6

diagnostic, 6-2

Database Resource Manager, 9-5, 9-9, 10-5

databases

buffers, 7-14, 7-36

diagnosing and monitoring, 6-2

size, 2-13

statistics , 5-3

DATE_OF_INSERT column

TKPR OF_TABLE table, 20-28

db block gets from cache statistic, 7-12

db file scattered read wait events, 10-17, 10-27

actions, 10-27, 10-30

db file sequential read wait events, 10-17, 10-27, 10-29

actions, 10-30

DB time

metric, 6-3

statistic, 5-4

DB_BLOCK_SIZE initialization parameter, 4-3, 8- 4

DB_CACHE_ADVICE parameter, 7-13

DB_CACHE_SIZE initia lization parameter, 7-14, 7-15

DB_DOMAIN initiali zation parameter, 4-2

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 8-3, 8-4, 8-5, 10-27, 14-8, 14-19

cost-based optimization, 14-31

DB_KEEP_CACHE_SIZE

initialization parameter, 7-19

DB_NAME initia lization parameter, 4-2

DB_nK_CACHE_SIZE initialization parameter, 7-14

DB_RECYCLE_CACHE_SIZE

initialization parameter, 7-20

DB_WRITER_PROCESSES initialization parameter, 10-38

DBA_HIST views, 5-16

DBA_HIST_WR_ CONTROL view

Automatic Workload Repository settings, 5-15

DBA_OBJECTS view, 7-18

DBA_OUTLINE_HINTS view

stored outline hints, 18-9

DBA_OUTLINES view

stored outlines, 18-9

DBIO_ EXPECTED parameter, 6-6

DBMS_ADVISOR package

Automatic Database Diagnostic Monitor, 6-8, 6-10

setting DBIO_EXPECTED, 6-7

setups for ADDM, 6-6, 6-7

DBMS_MONITOR package

End to End Application Tracing, 20-3

DBMS_OUTLN package

procedures for managing outlines, 18-4

D BMS_OUTLN_EDIT package

procedures for managing outlines, 18-4

DBMS_SHARED_POOL package

managing the shared pool, 7-44

DBMS_SQLTUNE package

SQL Profiles, 13-10

SQL Tuning Advisor, 13-8

SQ L Tuning Sets, 13-13

DBMS_STATS package, 1 5-7

managing query optimizer statistics, 14-6, 15-3

manually determining sample size for gathering procedures, 15-9

DBMS_WORKLOAD_REPOSITORY package

managing the Automatic Work load Repository, 5-13

DBMS_XPLAN package

displaying plan table output, 19-7

debugging designs, 2-24

default cache, 7-16

deployin g applications, 2-26

DEPTH column

TKPROF_TAB LE table, 20-28

design principles, 2-13

designs

debugging, 2-24

testing, 2-24

validating, 2-24

development environments, 2-19

diagnostic monitoring, 1-7, 6-2, 12-6

introduction, 6-2

direct path

read events, 10-31

read events actions, 10-32

read events causes, 10-32

wait events, 10-33

write events actions, 10-33

write events causes, 10-33

d irect-path INSERT, 17-41

disabled constraints, 16- 9

disks

monitoring operating system file activity, 10-5

statistics, 5-7

DISPATC HERS initialization parameter, 11-3

distribution

hints for, 17-38

DISTRIBUTION column

PLAN_TABLE table, 19-26

domain indexes

and EXPLAIN PLAN, 19-22

using, 16-1 3

DRIVING_SITE hint, 17-47

dynamic samplin g

improving performance, 15-17

level settings , 15-17, 15-18

process, 1 5-16

purpose, 15-16

when to use, 15-17

DYNAMIC_SAMPLING hint, 17-47

---

E

emergencies

performance, 3-8

Emergency Performance Method, 3-9

enabled constraints, 16-9

End to End Application Tracing, 20-1, 20-2

accessing with Oracle Enterprise Manager, 2 0-3

action and module names, 2-21, 20-2

creating a service, 20-2

DBMS_APPLICATION_INFO package, 20-2

DBMS_MONITOR package, 20-3

< dd class="L1IX">enforced constraints, 16-9

enqueue wait events, 10-17, 10-34

actions, 10-35

statistics, 10-11

equijoins, 12-9

error message documentation, i-xxi

estimating workloads, 2-23

benc hmarking, 2-23

extrapolating, 2-23

examples

ALTER SESSION statement, 20-14

EXPLAIN PLAN output, 20-25

SQL trace facility output, 20-25

execution plans

examples, 20-16

joins, 14-30

overview of, 14-15

plan stability, 18-2

preserving with plan stability, 18-2

TKPROF, 20-1 6, 20-18

viewing with the utlxpls.sql script, 14-15

EXPLAIN PLAN statement

access paths, 14-28

and domain indexes, 19-22

and full p artition-wise joins, 19-20

and partial partition-wise joins, 19-18

and partitioned objects, 19-14

basic st eps, 14-15

examples of output, 20-25

execution order of steps in output, 14-15

invoking with the TKPRO F program, 20-18

PLAN_TABLE table, 19-5

< dd class="L2IX">restrictions, 19-5

scripts for viewing output, 14-15

viewing the output, 14-15

Export utility

statistics on system-generated columns names, 15-15

expression

mixed-type, 12-10< /dd>

extended syntax

for specifying tables in hints, 17-7

global hints, 17-7

EXTENT MANAGEMENT LOCAL

creating temporary tablespaces, 4-7

< /dd>

extrapolating workloads, 2-23

---

F

FACT hint, 17-29

features, new, i-xxvii

FILESYSTEMIO_OPTIONS initialization parameter, 9-3

FIRST_ROWS optimizer mode parameter, 14-4

FIRST_ROWS(n) hint, 14-5 , 17-14

FIRST_ROWS_n

optimizer mode parame ter, 14-4

free buffer wait events, 10-17, 10-37

free lists, 10-26

FULL hint, 16-7, 17-16

f ull outer joins, 14-39

full partition-wise joins, 19-20

full table scans, 10-32

function-based in dexes, 2-15, 16-10

---

G

GATHER_ IND EX_STATS procedure

in DBMS_STATS package, 15-8

GATHER_DATABASE_STATS procedure

in DBMS_STATS package, 15-8

GATHER_DATABASE_STATS_JOB_PROC procedure

and GATHER_STATS_JO B in Maintenance Window, 15-3

automatically gathering optimizer statistics, 15-3

GATHER_DICTIONARY_STATS procedure

i n DBMS_STATS package, 15-8

GATHER_SCHEMA_STATS procedure

in DBMS_STATS package, 15-8

GATHER_STATS_JOB

automatically gathering optimizer statistics, 15-3

GATHER_TABLE_STATS procedure

in DBMS_STATS package, 15-8

GETMISSES column

in V$ROWCACHE table, 7-36

GETS column

in V$ROWCACHE view, 7-36

global hints, 17-7

GV$BUFFER_POO L_STATISTICS view, 7-17

---

H

hard parsing, 2-18

hardware

components, 2-7

limitations of components, 2-6

sizing of components, 2-6

hash

distribution value, 19-27

hash clusters

scans of, 14-28

sorted, 16-15

HASH hint, 17-17

hash joins, 14-34

cost-based optimization, 14-31

index join, 14-27

hash partitions, 19-14

examples of, 19-14

hashing, 16-15

high water mark, 14-18

hints

access paths, 12-17, 17-15, 17 -23

ALL_ROWS, 17-13

APPEND, 17-41

as used in outlines, 18-3

CACHE, 17-42

cannot override sample access path, 14-29

CLUSTER, 17-17

CURSOR_SHARING_EXACT, 17-46

degree of parallelism, 17-36

DRIVING_SITE, 17-47

DYNAMIC_SAMPLING, 17-47

FACT, 17-29

FIRST_ROWS(n), 17-14

FULL, 16-7, 17-16

global, 17-7

global compared to local, 17-7

HASH, 17-17

how to use, 17-2

< dd class="L2IX">INDEX, 17-17

INDEX_ASC, 17-19

INDEX_COMBINE, 17-19

INDEX_DESC, 17-20

INDEX_FFS, 14-26

INDEX_JOIN, 14-27

INDEX_SS, 17-22

INDEX_SS_AS C, 17-22

INDEX_SS_DESC, 17-23

indexspec syntax, 17-9

join operations, 17- 32

LEADING, 17-31

location syntax, 17-6

MERGE, 17-27

NO_EXPAND, 17-25

NO_FACT, 17-29

NO_INDEX, 16-7, 17-18

NO_INDEX_FFS, 17-21

NO_INDEX_SS, 17-23

NO_MERGE, 17-27

NO_PARALLEL, 17-37

NO_PARALLE L_INDEX, 17-40

NO_PUSH_PRED, 17-44

NO_PUSH_SUBQ, 17-45

NO_QUERY_TRANSFORMATION, 17-24

NO_REWRITE, 17-26

NO_UNNEST, 17-30

NO_USE_HASH, 17-36

NO_USE_ MERGE, 17-35

NO_USE_NL, 17-33

NOAPPEND, 17-42

NOCACHE, 17-43

NOPARALLEL, 17-37

NOPARALLEL_INDEX, 17-40

NOREWRITE, 17-26

optimization approach and g oal, 17-12

optimizer, 17-2

ORDERED, 17-32

ORDERED hint, 14-31

overriding optimizer choice, 14-29

overriding OPTIMIZER_MODE, < a href="optimops.htm#78323">14-5

PARALLEL, 17-37

par allel query option, 17-36

PQ_DISTRIBUTE, 17-38

PUSH_PRED, 17-44

PUSH_SUBQ, 17-45

QB_NAME, 17-46

REWRITE, 17-25

RULE, 17-15

specifying a query block, 17-6

specifying indexes, 17-9

SPREAD_MIN_ANALYSIS, 17-48

STAR_TRANSFORMATION, 17-28

syntax, 17-4

tablespec syntax, 17-7

UNNEST, 17-30

USE_CONCAT, 17-24

USE_HASH, 17-35

U SE_MERGE, 17-34

USE_NL, 17-33

USE_NL_WITH_INDEX, 17-34

using extended syntax, 17-7

histograms

frequency, 15 -22

height-balanced, 15-20

viewing, 15-20

HOLD_CURSOR clause, 7-28

hours of service, 2-12

HW enqueue

contention, 10-35

---

I

ID column

PLAN_TABLE table, 19-24

idle wait events, 10-48

SQL*Net message from client, 10-23

< /dl>

implementing business logic, 2-9

Import utility

copying statistics, 15-15

INDEX hint, 16-7, 17-17

INDEX_ASC hint, 17-19

INDEX_COMBINE hint, 16-7, 17-19

INDEX_DESC hint, 17-20

INDEX_FFS hint, 14-26, 14-27

INDEX_JOIN hint, 14-27

INDEX_SS hint, 17-22

INDEX_SS_ASC hint, 17-22

INDEX_SS_DESC hint, 17-23

indexes

adding columns, 2-15

appending columns, 2-15

avoiding the use of, 16-6

bitmap, 2-15, 16-12

B-tree, 2-15

choosing columns for, 16-4

co lumn order, 2-17

composite, 16-5

costs, 2-16

creating, 4-9

design, 2-14

domain, 16-13

dropping, 16-2

enforcing uniqueness, 16-8

ensuring the use of, 16-6

function-based, 2-15, 16-10

improving selectivity, 16-5

index joins, 14-27

joins, 14-27

low selectivity, 16-6

modifying valu es of, 16-4

non-unique, 16-8

partitioned, 2-16

placement on disk, 8-7

rebuilding, 16-7

re-creating, 16 -7

reducing I/O, 2-17

reverse key, 2-16

scans of, 14-21

selectivity, 2-17

selectivity of, 16-4

sequences i n, 2-16

serializing in, 2-16

specifying in hints, 17-9

statistics gathering, 15 -13

index-organized tables, 2-15

indexspec

hint syntax, 17-9

initialization parameters

CONTROL_FILES, 4-2

DB_BLOCK_SIZ E, 4-3

DB_DOMAIN, 4-2

D B_FILE_MULTIBLOCK_READ_COUNT, 14-31

DB_NAME, 4-2< /a>

OPEN_CURSORS, 4-2

OPTIMIZER_DYNAMIC_SAMPLING, i-xxx, 15-16, 15-17

OPT IMIZER_FEATURES_ENABLE, 14-26, 14-27

OPTIMIZE R_MODE, 14-4, 17-13

PGA_AGGREGATE_TARGET, 4-10

PROCESSES, 4-3

SESSION _CACHED_CURSORS, 7-42

SESSIONS, 4-3

SQL_TRACE, 20-14

STREAMS_POOL_SIZE, 4- 4

USER_DUMP_DEST, 20-12

INLIST ITERATOR op eration, 19-21

inlists, 19-21

INSERT statement

append, 17-41

instance configuration

initialization files, 4-2

performance considerations, 4-2

Internet scalability, 2-4

I/O

and SQL statements, 10-29

contention, 5-3, 10 -6, 10-8, 10-28, 10 -46

excessive I/O waits, 10-28

monitoring, 10-5

objects causing I/O waits, 10-29

reducing, 16-5

IOT (index-organized table), 2-15

---

J

joins

antijoins, 14-30

cartesian, 14-36

executio n plans and, 14-30

full outer, 14-39

hash, 14-34

index joins, 14-27< /dd>

join order and execution plans, 14-15

nested loop, 14-32

nested loops and cost-based optimization, 14-31< /a>

order, 12-18

outer, 14- 36

parallel, and PQ_DISTRIBUTE hint, 17-38

partition -wise

examples of full, 19-20

examples of pa rtial, 19-18

full, 19-20

semijoins, 14-30

sort merge, 14-35< /dd>

sort-merge and cost-based optimization, 14-31

---

K

KEEP buffer pool, 7-19

KEEP cache, 7 -16

---

L< /font>

LARGE_POOL_SIZE initialization parameter, 7-37

latch contention

library cache latches, 10-14

shared pool latches, 10-14

latch free wa it events, 10-17

actions, 10-40

latch wait events, 10-40

latch es

tuning, 1-4, 10-42

LEADING hint, 17-31

library cache

latch contention, 10-42

latch wait events, 10-40

lock, 10-45

memory allocation, 7-35

pin, 10-45

< /dl>

linear scalability, 2-5

locks and lock holders

finding, 10-34

log buffer

space wait events, 10-17, 10-46< /a>

tuning, 7-49

log file

parallel write wait events, 10-45

switch wait events, 10-46

sync wait events, 10-17, 10-47

log writer processes

tuning, < a href="iodesign.htm#23739">8-8

LOG_BUFFER initialization parameter, 7 -48

setting, 7-50

LRU

aging policy, 7-15

latch contention, 10-44

---

M

managing the user interface, 2-8

max session memory statistic, 7-39

MAX_DISPATCHER S initialization parameter, 4-12

MAX_DUMP_FILE_SIZE initialization parameter

SQL Trace, 20-12

MAXOPENCURSORS c lause, 7-28

memory

hardware component, 2-8

Memory Advisor

accessing with Oracl e Enterprise Manager, 7-2

memory allocation

importance, 7-2

library cache, 7-35

shared SQL areas, 7-35

tuning, 7-7

MERGE hint, 17-27

metrics, 5-2

migrated rows, 10-20

mirrori ng

redo logs, 8-9

modeling

conceptual, 3-5

data, 2-14

workloads, 2-24

monitoring

diagnostic, 1-7, 12-6

multiple buffer pools, 7-15

---

N

NAMESPACE colu mn

V$LIBRARYCACHE view, 7-30

neste d loop joins, 14-32

cost-based optimization, 14-31

network

array interface, 11-13

detecting performance problems, 11-6

hardw are component, 2-8

problem solving, 11-8

Session Data Unit, 11-14

speed, 2-12

statistics, 5-7

tuning, 11-1

network communication wait events, 10-23

db file scattered read wait events, 10-27

db file sequ ential read wait events, 10-27, 10-29

SQL*Net message from Dblink, 10-24

SQL*Net more data to client, 10-25

new features, i-xxvii

NO_CPU_COSTING hint, 14-5

NO_EXPAND hint, 17-25

NO_FACT hint, 17-29

NO_INDEX hint, 16-7, 17-18

NO_INDEX_FFS hint, 17-21

NO_INDEX_SS hint, 17-23

NO_MERGE hin t, 17-27

NO_PARALLEL hint, 17-37

NO_PARALLEL_INDEX, 17-40

NO_PUSH_PRED hint, 17-44

NO_PUSH_SUBQ hint, 17-45

NO_QUERY_TRANSFO RMATION hint, 17-24

NO_REWRITE hint, 17-26

NO_UNNEST hint, 17-30

NO_USE_HASH hint, 17-36

NO_USE_MERGE hint, 17-35

NO_USE_NL hint, 17-33

NOAPPEND hint, 17-42

NOCACHE hint, 17-43

NOPARALLEL hint, 17 -37

NOPARALLEL_INDEX hint, 17-40

NOREWRITE hint, 17-26

NOT IN subquery, 14-30

---

O

OBJECT_INSTANCE column

PLAN_TABLE table, 19-24

OBJECT_NAME column

PLAN_TABLE table, 19-23

OBJECT_NODE column

PLAN_TABLE table, 19-23

OBJECT_OWNER column

PLAN_TABLE table, 19-23

OBJECT_TYPE column

PLAN_TABLE ta ble, 19-24

object-orientation, 2-22

OLAP_PAGE_POOL_SIZE initialization parameter, 7-68

OPEN_CURS ORS initialization parameter, 4-2

increasing cursors for each se ssion, 7-36

operating system

data cache, 9-2

monitoring disk I/O, 10-5

< dd class="L2IX">statistics, 5-5

OPERATION column

PLAN_TABLE table, 19-23, 19-27

optimization

and dynamic sampling, 14-6

choosing the approach, 14-4

cost calculation, 14-9

cost-based, 14-9

cost-based and choosing an access path, 14-28

described, 1-6, 14-2

hints, 14-5, 14-26, 14-27

manual, 14-5

operations performed, 14-2

optimizer

cost calculation, 14-9

goals, 14-3< /a>

introduction, 1-6, 14-2

modes, 13-2

moving to from RBO, 18-12

operations, 14-2

parameters for setting mode, 14-4

plan stability, 18-2

qu ery, 1-6

response time, 14-3

statistics, 15-2

throughput, 14-3

upgrading, 18-14

OPTIMIZER column

PLAN_TABLE, 19-24

optimizer mode parameters < dl class="L2IX">

ALL_ROWS, 14-4

CHOOSE, 14-4

FIRST_ROWS, 14-4

FIRST_ROWS_n, 14-4

RULE, 14-4

OPTIM IZER_DYNAMIC_SAMPLING initialization parameter, i-xxx, 15-16, 15-17

OPTIMIZER_FEATURES_ENABLE initialization parameter, 1 4-6, 14-26, 14-27

OPTIMIZER_INDEX_CACHING initialization parameter, 14-8

OPTIMIZER_INDEX_COST_ADJ initialization parame ter, 14-8

OPTIMIZER_MODE initialization parameter, 14-4, 14-8, 17-13

hints af fecting, 14-5

OPTIONS column

PLA N_TABLE table, 19-23

OPTMIZER_DYNAMIC_SAMPLING initialization paramet er, 14-6

Oracle CPU statistics, 10-4

Oracle Enterprise Manager

accessing advisors, 1-7

accessing SQL Tuning Sets, 13-12

accessing t he SQL Tuning Advisor, 13-7

accessing the SQLAccess Advisor, 12-7

advisors, 1-7

Outline Editor , 18-8

Performance page, 1-7

Oracle Forms, 20-14

control of parsing an d private SQL areas, 7-29

Oracle Net Configuration Assistant, 11-14

Oracle performance improvement method, 3-2

steps, 3-3

Oracle Trace

obsoleted, i-xxxi

removed from Oracle releases, i-xxxi

Oracle-managed files, 8-10

tuning, 8-10

order

joins, 12-18

ORDERED hint, 14-31, 17-32

OTHER column

PLAN_T ABLE table, 19-26

OTHER_TAG column

PLAN_TABLE table, 19-25

outer joins, 1 2-19, 14-36

Outline Editor, 18-8

outlines

CREATE OUTLINE statement, 18-5

creating and using, 18-5

description, 18-2

execution plans and plan stability, 18-2

hints , 18-3

moving tables, 18-10

moving to the cost-based optimizer, 18-12

storage requirements, 18-4

using, 18-6

viewing data fo r, 18-9

---

< font face="Arial, Helvetica, sans-serif">P

page table, 9-11

paging, 9-11

reducing, 7-6

PARALLEL clause

CREATE INDEX statement, 4-10

parallel execution

hints, 17-37

PARALLEL hint, 17-37

parallel joins

and PQ_DISTRIBUTE hint, 17-38

PARENT_ID column

PLAN_TABLE table, 19-24

parsing

hard, 2-18

Oracle Forms, 7-29

Oracle precompilers, 7-28

reducing unnecessary calls, 7-27

soft, 2-18

PARTITION_ID column

PLAN_TABLE table, 19-26

PARTITION_START column

PLAN_TABLE tabl e, 19-25

PARTITION_STOP column

PL AN_TABLE table, 19-26

partitioned indexes, 2-16

partitioned objects

and EXPLAIN PLAN statement, 19-14

partitioning

distribution value, 19-27

examples of, 19-14

examples of c omposite, 19-16

hash, 19-14

range, 19-14

start and stop columns, 19-15

< /dl>

partition-wise joins

full, 19-20

full, and EXPLAIN PLAN output, 19-20

partial, and EXPLAIN PLAN output, 19-18

PCTFREE parameter, 4-7 , 10-20

PCTUSED parameter, 10-20

peeking

bind variables, 14-12

performance

emergencies, 3-8

improvement method, 3-2

improvement method steps, 3-3

mainframe, 9-6

monitoring memory on Windo ws, 9-10

tools for diagnosing and tuning, 1- 6

UNIX-based systems, 9-6

viewing execution plans, 14-15

Windows, 9-6

PGA_AGGREGATE_TARGET initialization parameter, 4-3, 4-10, 7-52, 9-4, 14-9

physical reads from cache statistic, 7-12

plan stability, 1 8-2

limitations of, 18-2

preserving ex ecution plans, 18-2

procedures for the cost-based optimizer, 18-12

use of hints, 18-2

PLAN_ TABLE table

BYTES column, 19-24

CARDINALITY column, 19-24

COST column, 19-24

creating, 19-5

displaying, 19-7

< dd class="L2IX">DISTRIBUTION column, 19-26

ID column, 19-24

OBJECT_INSTANCE column, 19-24

OBJECT_NAME co lumn, 19-23

OBJECT_NODE column, 19-23

OBJECT_OWNER column, 19-23

OBJECT_TYPE column, 19-24

OPERATION column, 19-23

OPTIMIZER colu mn, 19-24

OPTIONS column, 19-23

OTHER column, 19-26

OTHER_TAG column, 19-25< /a>

PARENT_ID column, 19-24

PARTITION_ID column, 19-26

PARTITION_START column, 19-25

PARTITION_STOP column, 19-26

POSITION column, 19 -24

REMARKS column, 19-23

SEARCH_COLUMNS column, 19-24

STATEMENT_ID column, 19-23

TIMESTAMP column, 19-23

POSITION column

PLAN_TABLE table, 19-24

PQ_DISTRIBUTE hint, 17-38

precompilers

control of parsing and private SQL ar eas, 7-28

preserved snapshots, 5-12

PRIMARY KEY constraint, 16-8

PRIVATE_SGA variable, 7-40

proactive monitoring, 1-4

processes

scheduling, 9-11

P ROCESSES initialization parameter, 4-3

program global area (PGA)

direct path read, 10-31

direct path write, 10-33

shared servers, 7-38

programming languages, 2-19

PUSH_PRED hint, 17-44

PUSH_SUBQ hint, 17-45

---

Q

QB_N AME hint, 17-46

queries

avoiding the use o f indexes, 16-6

data, 2-12

ensuring the use of indexes, 16-6

query optimizer, 1-6

See opti mizer

---

R

range

distribution value, 19-27

examples of partitions, 19-14

partit ions, 19-14

rdbms ipc reply wait events, 10-48

read consistency, 10-18

read wait ev ents

direct path, 10-31

scattered, 10-27

REBUILD clause, 16-7

< dd class="L1IX">recursive calls, 20-23

RECYCLE cache, 7-15

REDO BUFFER ALLOCATION RETRIES statistic, 7-49

redo logs, 4-5

buffer size, 10 -46

mirroring, 8-9

placement on disk, 8-8

sizing, 4-5

space requests, 10-18

reducing

contention with d ispatchers, 4-12

contention with shared servers, 4-13

data dictionary cache misses, 7-36

paging and swa pping, 7-6

unnecessary parse calls, 7-27

RELEASE_CURSOR clause, 7-28

REMARKS column

PLAN_TABLE table, 19-23

resources

allocation, 2-9, 2-19

bot tlenecks, 10-24

wait events, 10-29< /dd>

response time, 2-12

cost-based app roach, 14-4

optimizer goal, 14-3

optimizing, 14-3, 17-14

r everse key indexes, 2-16

REWRITE hint, 17-25

rollout strategies

big bang approach, 2-26

trickle approach, 2-26

round-robin

distribution value, 19-27

row cache objects, 10-45

row sources, 14-17

rowids

table access by, 14-20

rows

row sources, 14-17

rowids used to lo cate, 14-20

RULE hint, 17-15

< dd class="L1IX">RULE optimizer mode parameter, 14-4

rule-based optimization < dl class="L2IX">

desupport notice, xxix

migration of applica tions to CBO, xxix

obsolescence, xxix

< /dl>

---

S

SAMPLE BLOCK clause, 14-28

access path and hints cannot override, 14-29

SAMPLE clause, < a href="optimops.htm#73153">14-28

access path and hints cannot override, 14-29

sample table scans, 14-28

hints cannot override, 14-29

sar UNIX command, 9-10

scalability, 2-3

factors preventing, 2-5

Internet, 2-4

linear, 2-5

scans

inde x, 14-21

index joins, 14-27

index of type bitmap, 14-27

sample table, 14 -28

sample table and hints cannot override, 14-29

scattered read wait events, 10-27

actions, 10-27

SEARCH_COLUMNS column

PLAN_TA BLE table, 19-24

segment-level statistics, 10-12

SELECT statement

SAMPLE clause, 14-28

SAMPLE clause and access path, 14-29

selectivity

creating indexes, 16-4

impr oving for an index, 16-5

indexes, 16-6

ordering columns in an index, 2-17

semijoins, 14-30

sequential read wait events

actions, 10-30

service hours, 2-12

Session Data Unit (SDU), 11-14

session memory statistic, 7-39

SESSION_CACHED_CURSORS initialization parameter, 7-42

< dd class="L1IX">SESSIONS initialization parameter, 4-3

SGA size, 7-49

SGA_TARGET initialization parameter, 4-3

and Automatic Shared Memory Management, 7-3

automatic memo ry management, 7-3

shared pool contention, 10-42

shared server

performance issues, 4-10

reducing contention, 4-11

tuning, 4-11

tuning memory, 7-37

shared SQL areas

memory allocation, 7-35

SHARED_POOL_RESERVED_SIZE initialization parameter, 7-43

SHARED_POOL_SI ZE initialization parameter, 7-36, 7-44

allocating library cache, 7-35

tuning the shared pool, 7-40

SHOW SGA statement, 7-7

sizing r edo logs, 4-5

snapshots

preserved set, 5-12

soft parsing, 2-18

software

components, 2-8

sor t areas

tuning, 7-51

sort merge jo ins, 14-35

cost-based optimization, 14-31

SPREAD_MIN_ANALYSIS hint, 17-48

SQL Profiles

description, 13-3

managing with APIs, 13-10

SQL statements

avoid ing the use of indexes, 16-6

ensuring the use of indexes, 16-6

execution plans of, 14-15

modifying indexe d data, 16-4

waiting for I/O, 10-29

SQL trace facility, 20-9, 20-15

example of output, 20-25

output, 20-21

statement truncation, 20-24

steps to fol low, 20-11

trace files, 20-13

< dd class="L1IX">SQL Tuning Advisor, i-xxviii, 1-7, 12-7

accessing with Oracle Enterprise Manager, 13-7

administering with APIs, 13-8

input sources , 13-6

overview, 13-6

t uning options, 13-7

SQL Tuning Sets

accessing with Oracle Enterprise Manager, 13-12

description, 12-7, 13-6

managing with APIs, 13-12, 13-13

SQL*Net

messa ge from client idle events, 10-23

message from dblink wait events, 10-24

more data to client wait events, 10-25< /a>

SQL_STATEMENT column

TKPROF_TABLE, 20-28

SQL_TRACE

initialization parameter, 20-14

SQLAccess Advisor, 1-7, 12-7

accessing with Oracle Enterprise Manager, 12 -7

SQLTUNE_CATEGORY initialization parameter

determining the SQL Profile category, 13-4

ST enqueue

contention, 10-35

star transformation, 17-28

STAR_TRANSFORMATION hint, 17-28

STA R_TRANSFORMATION_ENABLED initialization parameter, 14-9, 17-29

start columns

in partitioning and EXPLAIN PLAN statement, 19-15

STATEMENT_ID column

PLAN_TABLE table, 19-23

statistics

and STATISTICS_LEVEL initia lization parameter, 1-6

automatic gathering, 15-3

baselines, 5-2

collecting on external tables, < a href="stats.htm#43281">15-5

consistent gets from cache, 7-11

databases, 5-3

db block gets from cache, 7 -12

displaying in views, 15-19

enabling automatic gathe ring, 15-4

exporting and importing, 15-14

GATHER_STATS_JOB, 15-3

gathering, 5-2

gathering stale, 15-10

gathering using sampling, 15-8

gathering with DBMS_STATS package, 15-7

gathering with DBMS_STATS procedures, 15-7

generating for query optimiza tion, 15-3

histograms, 15-20

limitations on restoring previous versions, 15-14

locking, 15-15

manually gathering, 15-6

max session memory, 7-39

missing, 15-18

operatin g systems, 5-5

CPU statistics, 5-6< /a>

disk statistics, 5-7

network statistics, 5-7

virtual memory statistics, 5-7

optimizer, 15-2

optimizer mode, 14-4< /dd>

optimizer use of, 14-9

physical reads from cache, 7-12

restoring previous versions, 15-13

segment-level, 10-12

session memory, 7-3 9

shared server processes, 4-13

stale, 15-10

system, 15-11

time model, 5-4

user-defined, 15-10

when to gath er, 15-11

STATISTICS_LEVEL initialization parameter, 5-9, 10-7

and Automatic Workload Repository, 5-12

enabling automatic database diagnostic monitoring, 6-6

settings for statistic gathering, 1-6

stop columns

in partitioning and EXPLAIN PLAN statement, 19-15

stored outlines

creating and using, 18-5

execution plans and plan stability, 18-2

hints, 18-3

moving tables, 18-10

< dd class="L2IX">storage requirements, 18-4

using, 18-6

viewing data for, 18-9

STREAMS_POOL_ SIZE initialization parameter, 4-4, 7-4

stripin g

manual, 8-6

subqueries

NOT IN, 14-30

unnesting, 12-20

swapping, 9-10, 9-11

reducing, 7-6

switching processes, 9-11

system architecture, 2-7

configuration, 2-10

hardware components, 2-7

CPUs, 2-7

I/O subsystems, 2-8

memory, 2-8

networks, 2-8

software components, 2-8

data and transactions, 2-9

implementing business logic, 2-9

managing the user interface, 2-8

user requests and resource allocation, 2-9

System Global Area tuning, 7-7

---

T

tables

cr eating, 4-7

design, 2-14

full scans, 10-32

placement on disk, 8-7

setting storage options, 4-7

tablespaces, 4-5

creating, 4-5

creating temporary, 4-6

temporary, 4-6< /dd>

tablespec

hint syntax, 17-7

TCP.NODELAY parameter, 11-14

temporary tablespaces, 4-6

creating, 4-6

testing designs, 2-24

thrashing, 9-11

throughput

cost-based approach, 14-4< /a>

optimizer goal, 14-3

optimizing, 14-3, 17-13

time model statistics, 5-4

TIMED_STATISTICS initialization parameter

SQL Trace, 20-12

TIMESTAMP column

PLAN_TABLE ta ble, 19-23

TKPROF program, 20-11, 20-15

editing the output SQL script, 20- 26

example of output, 20-25

generating the output SQL script, 20-26

row source operations, 20-22

syntax, 20-16

using the EXPLAIN PLAN statement, 20-18

wait event information, 20-23

TKPROF_TABLE, 20-27

querying, 20-27

TM enqueue

contention, 10-36

tools

for performance tuning, 1-6

TRACEFILE_IDENTIFIER initialization parameter

identifying trace files, 20-13

tracing

consolidating with trcsess, 20-7

identifying files, 20-13

transactions and data, 2-9

trcsess utility, i-xxviii, 20-7

trick le rollout strategy, 2-26

tuning

and bottlen eck elimination, 1-5

and proactive monitoring, 1-4

latches, 1-4, 10- 42

logical structure, 16-2

memory allocation, 7-7

resource contention, 10-1

shared server, 4-11

sorts, 7-51

SQL Tuning Advisor, 13-6

System Global Area (SGA), 7-7

TX enqueue

contention, 10-36

type conversion, 12-10

---

U

undo management

automatic mode, 4-4

UNDO TABLESPACE clause, 4-4

UNDO_MANAGEMENT initialization pa rameter, 4-3, 4-4

UNDO_TABLESPACE initializat ion parameter, 4-4

UNIQUE constraint, 16-8

uniqueness, 16-8

UNIX system performance, 9-6

UNNEST hint, 17-30

untransformed column values, 12-9

upgrade

to the cost-based op timizer, 18-14

USE_CONCAT hint, 17-24

USE_HASH hint, 17-35

USE_MERGE hint, 17-34

USE_NL hint, 17-33

USE_NL_WITH_IN DEX hint, 17-34

USE_STORED_OUTLINES parameter, 18 -6

user global area (UGA)

shared servers, 4-10, 7-37

V$SESSTAT, 7-39

user requests, 2-9

USER_DUMP_DEST initialization parameter, 20-12

SQL Trace, 20-12

USER_ID column

TKPROF_TABLE, 20-28

USER_OUTLINE_HINTS view

stored outline hints, 1 8-9

USER_OUTLINES view

stored outlines, 18-9

user-defined bind variables, 14-12

users

interaction method, 2-11

interfa ces, 2-19

location, 2-11

ne twork speed, 2-12

number of, 2-11

requests, 2-19

response time, 2-12

UTLCHN1.SQL script, 10-20

UTLXPLP.SQL script

displaying plan table output, 19-7

for viewing EXPLAIN PLANs, 14-15

UTLXPLS.SQL script

displaying plan table output, 19-7

for viewing EXPLAIN PLANs, 14-15

used for displaying EXPLAIN PLANs, 14-16

---

V

V$ACTIVE_SESSION_HISTORY view, 5-4, 10-9

V$BH view, 7-17

V$BUFFER_POOL_STATIS TICS view, 7-17

V$DB_CACHE_ADVICE view, 7-8, 7-11, 7-12, 7-13, 7-14, 7-16

V$EVENT_HISTOGRAM view, 10-10< /a>

V$FILE_HISTOGRAM view, 10-10

V$JAVA_LIBRARY_CAC HE_MEMORY view, 7-33

V$JAVA_POOL_ADVICE view, 7-33

V$LIBRARY_CACHE_MEMORY view, 7-33

V$LIBRARYCACHE view < dl class="L2IX">

NAMESPACE column, 7-30

V$OSSTAT view , 5-6

V$QUEUE view, 4-13

V$ROWCACHE view

GETMISSES column, 7-36

GET S column, 7-36

performance statistics, 7-34

< /dl>

V$RSRC_CONSUMER_GROUP view, 10-5

V$SESS_TIME_M ODEL view, 5-4, 10-9

V$SESSION view, 10-9, 10-11, 10-21

V$SESSION_EVENT view, 10-9, 10-21

network information, 11-6

V$SESSION_WA IT view, 10-9, 10-21

network information, 11-6

V$SESSION_WAIT_CLASS view, 10-10

V$SESSION_WAIT_HISTORY view, 10-10

V$SESSTAT view, 10-5

network information, 11-6

using, 7-38

V$SHAR ED_POOL_ADVICE view, 7-32

V$SHARED_POOL_RESERVED view, 7-44

V$SQL_PLAN view

using to display execution plan, 19-4

V$SQL_PLAN_STATISTICS view

using to display execution plan statistics, 19-4

V$SQL_PLAN_STATISTICS_ALL view

using to display execution plan information, 19-5

V$SYS_TIME_MODEL view, 5-4, 10-9

V$SYSSTAT view

redo buffer allocation, 7-49

using, 7-11

V$SYSTEM_EVENT view, 10-10, 10-21

V$SYSTEM_WAIT_CLASS view, 10-10

V$TEMP_HISTOGRAM view, 10-10

V$UNDO STAT view, 4-4

V$WAITSTAT view, 10-11

validating designs, 2-24

views, 2 -17

DBA_HIST, 5-16

statistics, 15-19

virtual memory statistics, 5-7

vmstat UNIX command, 9-10

---

W

wait events, 5-3

buffer busy waits, 10-25

classes, 5-3, 10-8

conten tion wait events, 10-40

direct path, 10 -33

enqueue, 10-34

free buffer waits, 10-37

idle wait events, 10-48

latch, 10-40

library cache latch, 10-40

log buffer space, 10-46

log file paral lel write, 10-45

log file switch, 10-46

log file sync, 10-47

network communication wai t events, 10-23

rdbms ipc reply, 10-48< /a>

resource wait events, 10-29

Windows p erformance, 9-6

workloads

estimating, 2-23

benchmarking, 2-23

extrapolating, 2-23

modeling, 2-24

testing, 2-24