Index

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

A

access paths

cluster scans, 14-27

defined, 14-17

execution plans, 14-15

hash scans, 14-28

index scans, 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

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

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

array interface, 11-13

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

accessing with Oracle Enterprise Manager, 6-7

actions and rationales of recommendations, 6-5

addmrpt.sql report, 6-8

analysis results example, 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

features, 13-1

overview, 13-2

Automatic Tuning Optimizer, 13-2

automatic undo management, 4-4

mode, 4-4

Automatic Workload Repository, i-xxviii, 1-7

accessing with Oracle Enterprise Manager, 5-12

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

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

preserved snapshot sets, 5-12

benchmarking workloads, 2-23

big bang rollout strategy, 2-26

bind variables, 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

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

CARDINALITY column

PLAN_TABLE 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

CLUSTER hint, 17-17

clusters, 16-14

hash and scans of, 14-28

scans of, 14-27

sorted hash, 16-15

column order

indexes, 2-17

columns

to index, 16-4

COMPATIBLE initialization parameter, 4-3

components

hardware, 2-7

software, 2-8

composite indexes, 16-5

composite partitioning

examples of, 19-16

conceptual modeling, 3-5

connection manager, 11-14

consistency

read, 10-18

consistent gets from cache statistic, 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

wait events, 10-40

context switches, 9-11

CONTROL_FILES initialization parameter, 4-2

cost

optimizer calculation, 14-9

COST column

PLAN_TABLE table, 19-24

cost-based optimizations, 14-9

procedures for plan stability, 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 initialization parameter, 18-5, 18-6

CREATE_STORED_OUTLINES parameter, 18-5

current mode

TKPROF, 20-21

CURSOR_NUM column

TKPROF_TABLE table, 20-28

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

CURSOR_SHARING_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

TKPROF_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 initialization parameter, 7-14, 7-15

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

DBMS_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

SQL Tuning Sets, 13-13

DBMS_STATS package, 15-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 Workload Repository, 5-13

DBMS_XPLAN package

displaying plan table output, 19-7

debugging designs, 2-24

default cache, 7-16

deploying applications, 2-26

DEPTH column

TKPROF_TABLE 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

direct-path INSERT, 17-41

disabled constraints, 16-9

disks

monitoring operating system file activity, 10-5

statistics, 5-7

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

DRIVING_SITE hint, 17-47

dynamic sampling

improving performance, 15-17

level settings, 15-17, 15-18

process, 15-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, 20-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

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

benchmarking, 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-16, 20-18

viewing with the utlxpls.sql script, 14-15

EXPLAIN PLAN statement

access paths, 14-28

and domain indexes, 19-22

and full partition-wise joins, 19-20

and partial partition-wise joins, 19-18

and partitioned objects, 19-14

basic steps, 14-15

examples of output, 20-25

execution order of steps in output, 14-15

invoking with the TKPROF program, 20-18

PLAN_TABLE table, 19-5

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

extended syntax

for specifying tables in hints, 17-7

global hints, 17-7

EXTENT MANAGEMENT LOCAL

creating temporary tablespaces, 4-7

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 parameter, 14-4

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

free lists, 10-26

FULL hint, 16-7, 17-16

full outer joins, 14-39

full partition-wise joins, 19-20

full table scans, 10-32

function-based indexes, 2-15, 16-10

G

GATHER_ INDEX_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_JOB in Maintenance Window, 15-3

automatically gathering optimizer statistics, 15-3

GATHER_DICTIONARY_STATS procedure

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

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_ASC, 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_PARALLEL_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 goal, 17-12

optimizer, 17-2

ORDERED, 17-32

ORDERED hint, 14-31

overriding optimizer choice, 14-29

overriding OPTIMIZER_MODE, 14-5

PARALLEL, 17-37

parallel 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

USE_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

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

column 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 values 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 in, 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_SIZE, 4-3

DB_DOMAIN, 4-2

DB_FILE_MULTIBLOCK_READ_COUNT, 14-31

DB_NAME, 4-2

OPEN_CURSORS, 4-2

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

OPTIMIZER_FEATURES_ENABLE, 14-26, 14-27

OPTIMIZER_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 operation, 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

execution plans and, 14-30

full outer, 14-39

hash, 14-34

index joins, 14-27

join order and execution plans, 14-15

nested loop, 14-32

nested loops and cost-based optimization, 14-31

order, 12-18

outer, 14-36

parallel, and PQ_DISTRIBUTE hint, 17-38

partition-wise

examples of full, 19-20

examples of partial, 19-18

full, 19-20

semijoins, 14-30

sort merge, 14-35

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

K

KEEP buffer pool, 7-19

KEEP cache, 7-16

L

LARGE_POOL_SIZE initialization parameter, 7-37

latch contention

library cache latches, 10-14

shared pool latches, 10-14

latch free wait events, 10-17

actions, 10-40

latch wait events, 10-40

latches

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

linear scalability, 2-5

locks and lock holders

finding, 10-34

log buffer

space wait events, 10-17, 10-46

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, 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_DISPATCHERS initialization parameter, 4-12

MAX_DUMP_FILE_SIZE initialization parameter

SQL Trace, 20-12

MAXOPENCURSORS clause, 7-28

memory

hardware component, 2-8

Memory Advisor

accessing with Oracle 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

mirroring

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 column

V$LIBRARYCACHE view, 7-30

nested loop joins, 14-32

cost-based optimization, 14-31

network

array interface, 11-13

detecting performance problems, 11-6

hardware 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 sequential 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 hint, 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_TRANSFORMATION 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 table, 19-24

object-orientation, 2-22

OLAP_PAGE_POOL_SIZE initialization parameter, 7-68

OPEN_CURSORS initialization parameter, 4-2

increasing cursors for each session, 7-36

operating system

data cache, 9-2

monitoring disk I/O, 10-5

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

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

query, 1-6

response time, 14-3

statistics, 15-2

throughput, 14-3

upgrading, 18-14

OPTIMIZER column

PLAN_TABLE, 19-24

optimizer mode parameters

ALL_ROWS, 14-4

CHOOSE, 14-4

FIRST_ROWS, 14-4

FIRST_ROWS_n, 14-4

RULE, 14-4

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

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

OPTIMIZER_INDEX_CACHING initialization parameter, 14-8

OPTIMIZER_INDEX_COST_ADJ initialization parameter, 14-8

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

hints affecting, 14-5

OPTIONS column

PLAN_TABLE table, 19-23

OPTMIZER_DYNAMIC_SAMPLING initialization parameter, 14-6

Oracle CPU statistics, 10-4

Oracle Enterprise Manager

accessing advisors, 1-7

accessing SQL Tuning Sets, 13-12

accessing the 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 and 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_TABLE table, 19-26

OTHER_TAG column

PLAN_TABLE table, 19-25

outer joins, 12-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 for, 18-9

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 table, 19-25

PARTITION_STOP column

PLAN_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 composite, 19-16

hash, 19-14

range, 19-14

start and stop columns, 19-15

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 Windows, 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, 18-2

limitations of, 18-2

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

DISTRIBUTION column, 19-26

ID column, 19-24

OBJECT_INSTANCE column, 19-24

OBJECT_NAME column, 19-23

OBJECT_NODE column, 19-23

OBJECT_OWNER column, 19-23

OBJECT_TYPE column, 19-24

OPERATION column, 19-23

OPTIMIZER column, 19-24

OPTIONS column, 19-23

OTHER column, 19-26

OTHER_TAG column, 19-25

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 areas, 7-28

preserved snapshots, 5-12

PRIMARY KEY constraint, 16-8

PRIVATE_SGA variable, 7-40

proactive monitoring, 1-4

processes

scheduling, 9-11

PROCESSES 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_NAME hint, 17-46

queries

avoiding the use of indexes, 16-6

data, 2-12

ensuring the use of indexes, 16-6

query optimizer, 1-6

See optimizer

R

range

distribution value, 19-27

examples of partitions, 19-14

partitions, 19-14

rdbms ipc reply wait events, 10-48

read consistency, 10-18

read wait events

direct path, 10-31

scattered, 10-27

REBUILD clause, 16-7

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 dispatchers, 4-12

contention with shared servers, 4-13

data dictionary cache misses, 7-36

paging and swapping, 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

bottlenecks, 10-24

wait events, 10-29

response time, 2-12

cost-based approach, 14-4

optimizer goal, 14-3

optimizing, 14-3, 17-14

reverse 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 locate, 14-20

RULE hint, 17-15

RULE optimizer mode parameter, 14-4

rule-based optimization

desupport notice, xxix

migration of applications to CBO, xxix

obsolescence, xxix

S

SAMPLE BLOCK clause, 14-28

access path and hints cannot override, 14-29

SAMPLE clause, 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

index, 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_TABLE 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

improving 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

SESSIONS initialization parameter, 4-3

SGA size, 7-49

SGA_TARGET initialization parameter, 4-3

and Automatic Shared Memory Management, 7-3

automatic memory 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_SIZE initialization parameter, 7-36, 7-44

allocating library cache, 7-35

tuning the shared pool, 7-40

SHOW SGA statement, 7-7

sizing redo logs, 4-5

snapshots

preserved set, 5-12

soft parsing, 2-18

software

components, 2-8

sort areas

tuning, 7-51

sort merge joins, 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

avoiding the use of indexes, 16-6

ensuring the use of indexes, 16-6

execution plans of, 14-15

modifying indexed 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 follow, 20-11

trace files, 20-13

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

tuning 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

message from client idle events, 10-23

message from dblink wait events, 10-24

more data to client wait events, 10-25

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

STAR_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 initialization parameter, 1-6

automatic gathering, 15-3

baselines, 5-2

collecting on external tables, 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 gathering, 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 optimization, 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

operating systems, 5-5

CPU statistics, 5-6

disk statistics, 5-7

network statistics, 5-7

virtual memory statistics, 5-7

optimizer, 15-2

optimizer mode, 14-4

optimizer use of, 14-9

physical reads from cache, 7-12

restoring previous versions, 15-13

segment-level, 10-12

session memory, 7-39

shared server processes, 4-13

stale, 15-10

system, 15-11

time model, 5-4

user-defined, 15-10

when to gather, 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

storage requirements, 18-4

using, 18-6

viewing data for, 18-9

STREAMS_POOL_SIZE initialization parameter, 4-4, 7-4

striping

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

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

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

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

trickle rollout strategy, 2-26

tuning

and bottleneck 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 parameter, 4-3, 4-4

UNDO_TABLESPACE initialization 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 optimizer, 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_INDEX 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, 18-9

USER_OUTLINES view

stored outlines, 18-9

user-defined bind variables, 14-12

users

interaction method, 2-11

interfaces, 2-19

location, 2-11

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

V$FILE_HISTOGRAM view, 10-10

V$JAVA_LIBRARY_CACHE_MEMORY view, 7-33

V$JAVA_POOL_ADVICE view, 7-33

V$LIBRARY_CACHE_MEMORY view, 7-33

V$LIBRARYCACHE view

NAMESPACE column, 7-30

V$OSSTAT view, 5-6

V$QUEUE view, 4-13

V$ROWCACHE view

GETMISSES column, 7-36

GETS column, 7-36

performance statistics, 7-34

V$RSRC_CONSUMER_GROUP view, 10-5

V$SESS_TIME_MODEL 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_WAIT 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$SHARED_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$UNDOSTAT 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

contention 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 parallel write, 10-45

log file switch, 10-46

log file sync, 10-47

network communication wait events, 10-23

rdbms ipc reply, 10-48

resource wait events, 10-29

Windows performance, 9-6

workloads

estimating, 2-23

benchmarking, 2-23

extrapolating, 2-23

modeling, 2-24

testing, 2-24