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

defined, 13.4.2.2

execution plans, 13.4.2

hash scans, 13.5.5

index scans, 13.5.3

Active Session History, 5.1.1.3

addmrpt.sql

Automatic Database Diagnostic Monitor, 6.2.5.1

advisors

accessing with Oracle Enterprise Manager, 1.2.1

ALL_OUTLINE_HINTS view

stored outline hints, 18.1.7

ALL_OUTLINES view

stored outlines, 18.1.7

ALL_ROWS hint, 13.2.2

allocation

of memory, 7.1

ALTER INDEX statement, 15.1.7

ALTER SESSION statement

examples, 20.4.2

SET SESSION_CACHED_CURSORS clause, 7.3.7

ANALYZE statement, 14.3.1

antijoins, 13.6.2

applications

deploying, 2.7

design principles, 2.5

development trends, 2.5.7

implementing, 2.5.6

Automatic Database Diagnostic Monitor, 6.1

accessing with Oracle Enterprise Manager, 6.2.4

actions and rationales of recommendations, 6.2.1

addmrpt.sql report, 6.2.5.1

analysis results example, 6.2.2

and DB time, 6.2

DBIO_EXPECTED, 6.2.3

DBMS_ADVISOR package, 6.2.5.2

example report, 6.2.2

findings, 6.2.1

overview, 6.2

results, 6.2.1

running with APIs, 6.2.5.2

setups, 6.2.3

STATISTICS_LEVEL parameter, 6.2.3

types of problems considered, 6.2

types of recommendations, 6.2

automatic database diagnostic monitoring, 1.2.1, 11.4

automatic segment-space management, 4.1.4.1, 8.2.6.2, 10.3.2.2.1

Automatic Shared Memory Management, 7.1.2

automatic SQL tuning, 1.2.1, 11.4

analysis, 12.1.2

features, 12

overview, 12.1

Automatic Tuning Optimizer, 12.1.1.2

automatic undo management, 4.1.2

mode, 4.1.2

Automatic Workload Repository, 1.2.1

accessing with Oracle Enterprise Manager, 5.2.1

data gathering, 5.1

DBMS_WORKLOAD_REPOSITORY package, 5.2.2

default settings, 5.2

factors affecting space usage, 5.2

managing with APIs, 5.2.2

minimizing space usage, 5.2

overview, 5.2

recommendations for retention period, 5.2

reports, 5.2.4

retention period, 5.2

settings in DBA_HIST_WR_CONTROL view, 5.2.2.3

space usage, 5.2

statistics collected, 5.2

turning off automatic snapshot collection, 5.2

unusual percentages in reports, 5.2.4

views for accessing data, 5.2.3

awrrpt.sql

Automatic Workload Repository report, 5.2.4

B

baselines, 1.1.2.2

performance, 5.1

preserved snapshot sets, 5.2

benchmarking workloads, 2.6.2.2

big bang rollout strategy, 2.7.1

bind variables, 7.3.1.3

peeking, 13.4.1.2

bitmap indexes, 2.5.3.2.2

inlist iterator, 19.8.5.3

on joins, 15.6

when to use, 15.5

block cleanout, 10.2.4.2

block size

choosing, 8.2.6

optimal, 8.2.6

bottlenecks

elimination, 1.1.2.4.2

fixing, 3.1

identifying, 3.1

memory, 7.1

resource, 10.3.1.1.2

broadcast

distribution value, 19.9

B-tree indexes, 2.5.3.2.1

buffer busy wait events, 10.2.3, 10.3.2

actions, 10.3.2.2

buffer cache

contention, 10.3.3, 10.3.4, 10.3.10.2

hit ratio, 7.2.2.3

reducing buffers, 7.2.3.2, 7.3.4.2

buffer pools

default cache, 7.2.4.1

hit ratio, 7.2.6

KEEP, 7.2.8

KEEP cache, 7.2.4.1

multiple, 7.2.4

RECYCLE cache, 7.2.4.1

business logic, 2.4.1.2.2, 2.5.6

BYTES column

PLAN_TABLE table, 19.9

C

CARDINALITY column

PLAN_TABLE table, 19.9

cartesian joins, 13.6.6

chained rows, 10.2.4.3

classes

wait events, 5.1.1.1, 10.1.3.2

client/server applications, 9.4.2

clusters, 15.8, 15.8

hash and scans of, 13.5.5

scans of, 13.5.4

sorted hash, 15.9

column order

indexes, 2.5.3.5

columns

to index, 15.1.3

COMPATIBLE initialization parameter, 4.1.1

components

hardware, 2.4.1.1

software, 2.4.1.2

composite indexes, 15.1.4

composite partitioning

examples of, 19.8.2

conceptual modeling, 3.1.2

consistency

read, 10.2.4.2

consistent gets from cache statistic, 7.2.2.3

consistent mode

TKPROF, 20.4.4.1

constraints, 15.1.10

contention

library cache latch, 10.3.10.3

memory, 7.1, 10

shared pool, 10.3.10.3

tuning, 10

wait events, 10.3.10

context switches, 9.4.4.2

CONTROL_FILES initialization parameter, 4.1.1

cost

optimizer calculation, 13.4

COST column

PLAN_TABLE table, 19.9

cost-based optimizations, 13.4

procedures for plan stability, 18.2

upgrading to, 18.2.2

cpu statistics, 10.1.2.1.3

CPUs, 2.4.1.1.1

statistics, 5.1.2.1

utilization, 9.4

CREATE INDEX statement

PARALLEL clause, 4.2.3

CREATE OUTLINE statement, 18.1.5

CREATE_STORED_OUTLINES initialization parameter, 18.1.5, 18.1.5.1

CREATE_STORED_OUTLINES parameter, 18.1.5

current mode

TKPROF, 20.4.4.1

CURSOR_NUM column

TKPROF_TABLE table, 20.4.5.3

CURSOR_SHARING initialization parameter, 7.3.2.1, 7.3.10.3, 13.3.2

CURSOR_SPACE_FOR_TIME initialization parameter

setting, 7.3.6

cursors

accessing, 7.3.2.6

sharing, 7.3.2.6

D

data

and transactions, 2.4.1.2.4

cache, 9.1.1

gathering, 5.1

modeling, 2.5.2

queries, 2.4.2

searches, 2.4.2

data dictionary, 7.3.4.1.2

statistics in, 14.6.1

views used in optimization, 14.6.1

database monitoring, 1.2.1, 11.4

diagnostic, 6.1

Database Resource Manager, 9.1.3, 9.1.3, 9.3, 10.1.2.1.3

databases

buffers, 7.2.3.2, 7.3.4.2

diagnosing and monitoring, 6.1

size, 2.4.2

statistics, 5.1.1

DATE_OF_INSERT column

TKPROF_TABLE table, 20.4.5.3

db block gets from cache statistic, 7.2.2.3

db file scattered read wait events, 10.2.3, 10.3.3, 10.3.3

actions, 10.3.3.1, 10.3.4.1

db file sequential read wait events, 10.2.3, 10.3.3, 10.3.4

actions, 10.3.4.1

DB time

metric, 6.2

statistic, 5.1.1.2

DB_BLOCK_SIZE initialization parameter, 4.1.1, 8.2.1.2

DB_CACHE_ADVICE parameter, 7.2.3.1

DB_CACHE_SIZE initialization parameter, 7.2.3.2, 7.2.4

DB_DOMAIN initialization parameter, 4.1.1

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 8.2.1.1, 8.2.1.2, 8.2.1.3, 10.3.3, 13.3.2, 13.5.1

cost-based optimization, 13.6.2

DB_KEEP_CACHE_SIZE

initialization parameter, 7.2.8

DB_NAME initialization parameter, 4.1.1

DB_nK_CACHE_SIZE initialization parameter, 7.2.3.1

DB_RECYCLE_CACHE_SIZE

initialization parameter, 7.2.9

DB_WRITER_PROCESSES initialization parameter, 10.3.9.3.1

DBA_HIST views, 5.2.3

DBA_HIST_WR_CONTROL view

Automatic Workload Repository settings, 5.2.2.3

DBA_OBJECTS view, 7.2.7

DBA_OUTLINE_HINTS view

stored outline hints, 18.1.7

DBA_OUTLINES view

stored outlines, 18.1.7

DBIO_EXPECTED parameter, 6.2.3

DBMS_ADVISOR package, 17.1

Automatic Database Diagnostic Monitor, 6.2.5, 6.2.5.2

setting DBIO_EXPECTED, 6.2.3

setups for ADDM, 6.2.3, 6.2.3

DBMS_MONITOR package

End to End Application Tracing, 20.1.2

DBMS_OUTLN package

procedures for managing outlines, 18.1.4

DBMS_OUTLN_EDIT package

procedures for managing outlines, 18.1.4

DBMS_SHARED_POOL package

managing the shared pool, 7.3.9

DBMS_SQLTUNE package

SQL Profiles, 12.4

SQL Tuning Advisor, 12.2.5

SQL Tuning Sets, 12.3.2

DBMS_STATS package, 14.3.1, 17.1

managing query optimizer statistics, 13.2.3, 14.1

manually determining sample size for gathering procedures, 14.3.1.1

DBMS_WORKLOAD_REPOSITORY package

managing the Automatic Workload Repository, 5.2.2

DBMS_XPLAN package

displaying plan table output, 19.4

debugging designs, 2.6.4

default cache, 7.2.4.1

deploying applications, 2.7

DEPTH column

TKPROF_TABLE table, 20.4.5.3

design principles, 2.5

designs

debugging, 2.6.4

testing, 2.6.4

validating, 2.6.4

development environments, 2.5.6

diagnostic monitoring, 1.2.1, 6.1, 11.4

introduction, 6.1

direct path

read events, 10.3.5

read events actions, 10.3.5.2

read events causes, 10.3.5.1

wait events, 10.3.6

write events actions, 10.3.6.2

write events causes, 10.3.6.1

disabled constraints, 15.1.10

disks

monitoring operating system file activity, 10.1.2.2

statistics, 5.1.2.3

DISTRIBUTION column

PLAN_TABLE table, 19.9

domain indexes

and EXPLAIN PLAN, 19.8.6

using, 15.7

dynamic sampling

improving performance, 14.5.6.3

level settings, 14.5.6.4, 14.5.6.4

process, 14.5.6.1

purpose, 14.5.6

when to use, 14.5.6.2

E

emergencies

performance, 3.2

Emergency Performance Method, 3.2.1

enabled constraints, 15.1.10

End to End Application Tracing, 20, 20.1

accessing with Oracle Enterprise Manager, 20.1.1

action and module names, 2.5.6, 20.1

creating a service, 20.1

DBMS_APPLICATION_INFO package, 20.1

DBMS_MONITOR package, 20.1.2

enforced constraints, 15.1.10

enqueue wait events, 10.2.3, 10.3.7

actions, 10.3.7.2

statistics, 10.1.3.4.5

equijoins, 11.5.3.1

error message documentation, Preface

estimating workloads, 2.6.2

benchmarking, 2.6.2.2

extrapolating, 2.6.2.1

examples

ALTER SESSION statement, 20.4.2

EXPLAIN PLAN output, 20.4.4.10

SQL trace facility output, 20.4.4.10

EXECUTE_TASK procedure, 17.2.5.3.1

execution plans

examples, 20.4.3.1

joins, 13.6.1

overview of, 13.4.2

plan stability, 18.1

preserving with plan stability, 18.1

TKPROF, 20.4.3.1, 20.4.3.2

viewing with the utlxpls.sql script, 13.4.2.1

EXPLAIN PLAN statement

access paths, 13.5.6

and domain indexes, 19.8.6

and full partition-wise joins, 19.8.4

and partial partition-wise joins, 19.8.3

and partitioned objects, 19.8

basic steps, 13.4.2.1

examples of output, 20.4.4.10

execution order of steps in output, 13.4.2.1

invoking with the TKPROF program, 20.4.3.2

PLAN_TABLE table, 19.2

restrictions, 19.1.4

scripts for viewing output, 13.4.2.1

viewing the output, 13.4.2.1

EXPLAIN_MVIEW procedure, 17.3

Export utility

statistics on system-generated columns names, 14.5.2

expression

mixed-type, 11.5.3.2

extended syntax

for specifying tables in hints, 16.2.3

global hints, 16.2.3

EXTENT MANAGEMENT LOCAL

creating temporary tablespaces, 4.1.4.2

extrapolating workloads, 2.6.2.1

F

features, new, Preface

FILESYSTEMIO_OPTIONS initialization parameter, 9.1.1.2

FIRST_ROWS(n) hint, 13.2.2

free buffer wait events, 10.2.3, 10.3.9

free lists, 10.3.2.2.1

FULL hint, 15.1.6

full outer joins, 13.6.7.4

full partition-wise joins, 19.8.4

full table scans, 10.3.5.2.2

function-based indexes, 2.5.3.2.3, 15.2

G

GATHER_ INDEX_STATS procedure

in DBMS_STATS package, 14.3.1

GATHER_DATABASE_STATS procedure

in DBMS_STATS package, 14.3.1

GATHER_DATABASE_STATS_JOB_PROC procedure

and GATHER_STATS_JOB in Maintenance Window, 14.2.1

automatically gathering optimizer statistics, 14.2.1

GATHER_DICTIONARY_STATS procedure

in DBMS_STATS package, 14.3.1

GATHER_SCHEMA_STATS procedure

in DBMS_STATS package, 14.3.1

GATHER_STATS_JOB

automatically gathering optimizer statistics, 14.2.1

GATHER_TABLE_STATS procedure

in DBMS_STATS package, 14.3.1

GETMISSES column

in V$ROWCACHE table, 7.3.4.1.2

GETS column

in V$ROWCACHE view, 7.3.4.1.2

global hints, 16.2.3

GV$BUFFER_POOL_STATISTICS view, 7.2.6

H

hard parsing, 2.5.5

hardware

components, 2.4.1.1

limitations of components, 2.3.3

sizing of components, 2.3.3

hash

distribution value, 19.9

hash clusters

scans of, 13.5.5

sorted, 15.9

hash joins, 13.6.4

cost-based optimization, 13.6.2

index join, 13.5.3.8.1

hash partitions, 19.8

examples of, 19.8.1

hashing, 15.9

high water mark, 13.5.1

hints

access paths, 11.5.4, 16.1.2.2, 16.1.2.3

as used in outlines, 18.1.1.1

cannot override sample access path, 13.5.7

degree of parallelism, 16.1.2.6

FULL, 15.1.6

global, 16.2.3

global compared to local, 16.2.3

how to use, 16.1

INDEX_FFS, 13.5.3.7

INDEX_JOIN, 13.5.3.8

indexspec syntax, 16.2.4

join operations, 16.1.2.5

location syntax, 16.2.2

NO_INDEX, 15.1.6

optimization approach and goal, 16.1.2.1

optimizer, 16.1

ORDERED hint, 13.6.2

overriding optimizer choice, 13.5.7

overriding OPTIMIZER_MODE, 13.2.2

parallel query option, 16.1.2.6

specifying a query block, 16.2.2

specifying indexes, 16.2.4

tablespec syntax, 16.2.3

using extended syntax, 16.2.3

histograms

frequency, 14.6.2.2

height-balanced, 14.6.2.1

viewing, 14.6.2

HOLD_CURSOR clause, 7.3.2.6.2

hours of service, 2.4.2

HW enqueue

contention, 10.3.7.2.2

I

ID column

PLAN_TABLE table, 19.9

idle wait events, 10.4

SQL*Net message from client, 10.3.1.1

implementing business logic, 2.4.1.2.2

Import utility

copying statistics, 14.5.2

INDEX hint, 15.1.6

INDEX_COMBINE hint, 15.1.6

INDEX_FFS hint, 13.5.3.7, 13.5.3.7.1

INDEX_JOIN hint, 13.5.3.8

indexes

adding columns, 2.5.3.1

appending columns, 2.5.3.1

avoiding the use of, 15.1.6

bitmap, 2.5.3.2.2, 15.5

B-tree, 2.5.3.2.1

choosing columns for, 15.1.3

column order, 2.5.3.5

composite, 15.1.4

costs, 2.5.3.3

creating, 4.2.3

design, 2.5.3

domain, 15.7

dropping, 15.1.1

enforcing uniqueness, 15.1.9

ensuring the use of, 15.1.5

function-based, 2.5.3.2.3, 15.2

improving selectivity, 15.1.4

index joins, 13.5.3.8.1

joins, 13.5.3.8.1

low selectivity, 15.1.6

modifying values of, 15.1.3

non-unique, 15.1.9

partitioned, 2.5.3.2.4

placement on disk, 8.2.2

rebuilding, 15.1.7

re-creating, 15.1.7

reducing I/O, 2.5.3.5

reverse key, 2.5.3.2.5

scans of, 13.5.3

selectivity, 2.5.3.5

selectivity of, 15.1.3

sequences in, 2.5.3.4

serializing in, 2.5.3.4

specifying in hints, 16.2.4

statistics gathering, 14.5

index-organized tables, 2.5.3.1

indexspec

hint syntax, 16.2.4

initialization parameters

CONTROL_FILES, 4.1.1

DB_BLOCK_SIZE, 4.1.1

DB_DOMAIN, 4.1.1

DB_FILE_MULTIBLOCK_READ_COUNT, 13.6.2

DB_NAME, 4.1.1

OPEN_CURSORS, 4.1.1

OPTIMIZER_DYNAMIC_SAMPLING, 14.5.6.1, 14.5.6.3, 14.5.6.4

OPTIMIZER_FEATURES_ENABLE, 13.5.3.7, 13.5.3.8

OPTIMIZER_MODE, 13.2.1, 16.1.2.1

PGA_AGGREGATE_TARGET, 4.2.3.1

PROCESSES, 4.1.1

SESSION_CACHED_CURSORS, 7.3.7

SESSIONS, 4.1.1

SQL_TRACE, 20.4.2, 20.4.2

STREAMS_POOL_SIZE, 4.1.1, 7.1.2

USER_DUMP_DEST, 20.4.1

INLIST ITERATOR operation, 19.8.5

inlists, 19.8.5

instance configuration

initialization files, 4.1.1

performance considerations, 4.1

Internet scalability, 2.3.2

I/O

and SQL statements, 10.3.3.4

contention, 5.1.1.1, 10.1.2.2, 10.1.3.2, 10.3.3.2, 10.3.14

excessive I/O waits, 10.3.3.2

monitoring, 10.1.2.2

objects causing I/O waits, 10.3.3.5

reducing, 15.1.4

IOT (index-organized table), 2.5.3.1

J

joins

antijoins, 13.6.2

cartesian, 13.6.6

execution plans and, 13.6.1

full outer, 13.6.7.4

hash, 13.6.4

index joins, 13.5.3.8.1

join order and execution plans, 13.4.2

nested loop, 13.6.3

nested loops and cost-based optimization, 13.6.2

order, 11.5.4, 11.5.4

outer, 13.6.7

partition-wise

examples of full, 19.8.4

examples of partial, 19.8.3

full, 19.8.4

semijoins, 13.6.2

sort merge, 13.6.5

sort-merge and cost-based optimization, 13.6.2, 13.6.2

K

KEEP buffer pool, 7.2.8

KEEP cache, 7.2.4.1

L

LARGE_POOL_SIZE initialization parameter, 7.3.5.1

latch contention

library cache latches, 10.2.1.2

shared pool latches, 10.2.1.2

latch free wait events, 10.2.3

actions, 10.3.10.1

latch wait events, 10.3.10

latches

tuning, 1.1.2.3, 10.3.10.3

library cache

latch contention, 10.3.10.3

latch wait events, 10.3.10.1

lock, 10.3.13

memory allocation, 7.3.4.1.1

pin, 10.3.12

linear scalability, 2.3.3

locks and lock holders

finding, 10.3.7.1

log buffer

space wait events, 10.2.3, 10.3.14

tuning, 7.4.1

log file

parallel write wait events, 10.3.11

switch wait events, 10.3.15

sync wait events, 10.2.3, 10.3.16

log writer processes

tuning, 8.2.3.2

LOG_BUFFER initialization parameter, 7.4

setting, 7.4.2

LRU

aging policy, 7.2.4

latch contention, 10.3.10.3.4

M

managing the user interface, 2.4.1.2.1

materialized views

tuning, 17.3

max session memory statistic, 7.3.5.1.2

MAX_DISPATCHERS initialization parameter, 4.3.1.1

MAX_DUMP_FILE_SIZE initialization parameter

SQL Trace, 20.4.1

MAXOPENCURSORS clause, 7.3.2.6.2

memory

hardware component, 2.4.1.1.2

Memory Advisor

accessing with Oracle Enterprise Manager, 7.1

memory allocation

importance, 7.1

library cache, 7.3.4.1.1

shared SQL areas, 7.3.4.1.1

tuning, 7.1.6

metrics, 5.1

migrated rows, 10.2.4.3

mirroring

redo logs, 8.2.3.3

modeling

conceptual, 3.1.2

data, 2.5.2

workloads, 2.6.3

monitoring

diagnostic, 1.2.1, 11.4

multiple buffer pools, 7.2.4

N

NAMESPACE column

V$LIBRARYCACHE view, 7.3.3.2

nested loop joins, 13.6.3

cost-based optimization, 13.6.2

network

hardware component, 2.4.1.1.4

speed, 2.4.2

statistics, 5.1.2.4

network communication wait events, 10.3.1

db file scattered read wait events, 10.3.3

db file sequential read wait events, 10.3.3, 10.3.4

SQL*Net message from Dblink, 10.3.1.2

SQL*Net more data to client, 10.3.1.3

new features, Preface

NO_INDEX hint, 15.1.6

NOT IN subquery, 13.6.2

O

OBJECT_INSTANCE column

PLAN_TABLE table, 19.9

OBJECT_NAME column

PLAN_TABLE table, 19.9

OBJECT_NODE column

PLAN_TABLE table, 19.9

OBJECT_OWNER column

PLAN_TABLE table, 19.9

OBJECT_TYPE column

PLAN_TABLE table, 19.9

object-orientation, 2.5.7

OLAP_PAGE_POOL_SIZE initialization parameter, 7.5.2

OPEN_CURSORS initialization parameter, 4.1.1

increasing cursors for each session, 7.3.4.1.1

operating system

data cache, 9.1.1

monitoring disk I/O, 10.1.2.2

statistics, 5.1.2

OPERATION column

PLAN_TABLE table, 19.9, 19.9

optimization

and dynamic sampling, 13.2.3

choosing the approach, 13.2.1

cost calculation, 13.4

cost-based, 13.4

cost-based and choosing an access path, 13.5.7

described, 13.1

hints, 13.2.2, 13.5.3.7, 13.5.3.8

manual, 13.2.2

operations performed, 13.1

optimizer

cost calculation, 13.4

goals, 13.2, 13.2

introduction, 1.1.3.1, 13.1

modes, 12.1.1

moving to from RBO, 18.2.1

operations, 13.1

parameters for setting mode, 13.2.1

plan stability, 18.1

query, 1.1.3.1

response time, 13.2

statistics, 14.1

throughput, 13.2

upgrading, 18.2.2

OPTIMIZER column

PLAN_TABLE, 19.9

OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, 14.5.6.1, 14.5.6.3, 14.5.6.4

OPTIMIZER_FEATURES_ENABLE initialization parameter, 13.3.1, 13.5.3.7, 13.5.3.8

OPTIMIZER_INDEX_CACHING initialization parameter, 13.3.2

OPTIMIZER_INDEX_COST_ADJ initialization parameter, 13.3.2

OPTIMIZER_MODE initialization parameter, 13.2.1, 13.2.1, 13.3.2, 13.3.2, 16.1.2.1

hints affecting, 13.2.2

OPTIONS column

PLAN_TABLE table, 19.9

OPTMIZER_DYNAMIC_SAMPLING initialization parameter, 13.2.3

Oracle CPU statistics, 10.1.2.1.3

Oracle Enterprise Manager

accessing advisors, 1.2.1

accessing SQL Tuning Sets, 12.3.1

accessing the SQLAccess Advisor, 11.4

advisors, 1.2.1

Outline Editor, 18.1.6

Performance page, 1.2.1

Oracle Forms, 20.4.2

control of parsing and private SQL areas, 7.3.2.6.5

Oracle performance improvement method, 3.1

steps, 3.1.1

Oracle-managed files, 8.2.5

tuning, 8.2.5

order

joins, 11.5.4

ORDERED hint, 13.6.2

OTHER column

PLAN_TABLE table, 19.9

OTHER_TAG column

PLAN_TABLE table, 19.9

outer joins, 11.5.4.1, 13.6.7

Outline Editor, 18.1.6

outlines

CREATE OUTLINE statement, 18.1.5

creating and using, 18.1.5

description, 18.1

execution plans and plan stability, 18.1

hints, 18.1.1.1

moving tables, 18.1.8

moving to the cost-based optimizer, 18.2.1

storage requirements, 18.1.2

using, 18.1.6

viewing data for, 18.1.7

P

packages

DBMS_ADVISOR, 17.1

DBMS_STATS, 17.1

page table, 9.4.1.2

paging, 9.4.2

reducing, 7.1.5.1

PARALLEL clause

CREATE INDEX statement, 4.2.3

PARENT_ID column

PLAN_TABLE table, 19.9

parsing

hard, 2.5.5

Oracle Forms, 7.3.2.6.5

Oracle precompilers, 7.3.2.6.2

reducing unnecessary calls, 7.3.2.6

soft, 2.5.5

PARTITION_ID column

PLAN_TABLE table, 19.9

PARTITION_START column

PLAN_TABLE table, 19.9

PARTITION_STOP column

PLAN_TABLE table, 19.9

partitioned indexes, 2.5.3.2.4

partitioned objects

and EXPLAIN PLAN statement, 19.8

partitioning

distribution value, 19.9

examples of, 19.8.1

examples of composite, 19.8.2

hash, 19.8

range, 19.8

start and stop columns, 19.8.1

partition-wise joins

full, 19.8.4

full, and EXPLAIN PLAN output, 19.8.4

partial, and EXPLAIN PLAN output, 19.8.3

PCTFREE parameter, 4.2, 10.2.4.3

PCTUSED parameter, 10.2.4.3, 10.2.4.3

peeking

bind variables, 13.4.1.2

performance

emergencies, 3.2

improvement method, 3.1

improvement method steps, 3.1.1

mainframe, 9.2.3

monitoring memory on Windows, 9.4.1.1

tools for diagnosing and tuning, 1.2

UNIX-based systems, 9.2.1

viewing execution plans, 13.4.2.1

Windows, 9.2.2

PGA_AGGREGATE_TARGET initialization parameter, 4.1.1, 4.2.3.1, 7.5.1, 9.1.2.2, 13.3.2

physical reads from cache statistic, 7.2.2.3

plan stability, 18.1

limitations of, 18.1.1

preserving execution plans, 18.1

procedures for the cost-based optimizer, 18.2

use of hints, 18.1.1

PLAN_TABLE table

BYTES column, 19.9

CARDINALITY column, 19.9

COST column, 19.9

creating, 19.2, 19.2

displaying, 19.4

DISTRIBUTION column, 19.9

ID column, 19.9

OBJECT_INSTANCE column, 19.9

OBJECT_NAME column, 19.9

OBJECT_NODE column, 19.9

OBJECT_OWNER column, 19.9

OBJECT_TYPE column, 19.9

OPERATION column, 19.9

OPTIMIZER column, 19.9

OPTIONS column, 19.9

OTHER column, 19.9

OTHER_TAG column, 19.9

PARENT_ID column, 19.9

PARTITION_ID column, 19.9

PARTITION_START column, 19.9

PARTITION_STOP column, 19.9

POSITION column, 19.9

REMARKS column, 19.9

SEARCH_COLUMNS column, 19.9

STATEMENT_ID column, 19.9

TIMESTAMP column, 19.9

PL/SQL procedures

EXPLAIN_MVIEW, 17.3

TUNE_MVIEW, 17.3

POSITION column

PLAN_TABLE table, 19.9

precompilers

control of parsing and private SQL areas, 7.3.2.6.2

preserved snapshots, 5.2

PRIMARY KEY constraint, 15.1.9

PRIVATE_SGA variable, 7.3.5.1.3

privileges

SQL Access Advisor, 17.2.2

proactive monitoring, 1.1.2.4.1

processes

scheduling, 9.4.4.1

PROCESSES initialization parameter, 4.1.1

program global area (PGA)

direct path read, 10.3.5

direct path write, 10.3.6

shared servers, 7.3.5.1.1

programming languages, 2.5.6

Q

queries

avoiding the use of indexes, 15.1.6

data, 2.4.2

ensuring the use of indexes, 15.1.5

query optimizer, 1.1.3.1

See optimizer

R

range

distribution value, 19.9

examples of partitions, 19.8.1

partitions, 19.8

rdbms ipc reply wait events, 10.3.17

read consistency, 10.2.4.2

read wait events

direct path, 10.3.5

scattered, 10.3.3

REBUILD clause, 15.1.7

recursive calls, 20.4.4.5

RECYCLE cache, 7.2.4.1

REDO BUFFER ALLOCATION RETRIES statistic, 7.4.2

redo logs, 4.1.3

buffer size, 10.3.14

mirroring, 8.2.3.3

placement on disk, 8.2.3.2

sizing, 4.1.3

space requests, 10.2.4.1

reducing

contention with dispatchers, 4.3.1.1

contention with shared servers, 4.3.2

data dictionary cache misses, 7.3.4.1.2

paging and swapping, 7.1.5.1

unnecessary parse calls, 7.3.2.6

RELEASE_CURSOR clause, 7.3.2.6.2

REMARKS column

PLAN_TABLE table, 19.9

resources

allocation, 2.4.1.2.3, 2.5.6

bottlenecks, 10.3.1.1.2

wait events, 10.3.4

response time, 2.4.2

cost-based approach, 13.2.1

optimizer goal, 13.2

optimizing, 13.2

reverse key indexes, 2.5.3.2.5

rollout strategies

big bang approach, 2.7.1

trickle approach, 2.7.1

round-robin

distribution value, 19.9

row cache objects, 10.3.10.3.6

row sources, 13.4.2.2

rowids

table access by, 13.5.2

rows

row sources, 13.4.2.2

rowids used to locate, 13.5.2

S

SAMPLE BLOCK clause, 13.5.6

access path and hints, 13.5.7

SAMPLE clause, 13.5.6

access path and hints cannot override, 13.5.7

sample table scans, 13.5.6

hints cannot override, 13.5.7

sar UNIX command, 9.4.1.1

scalability, 2.3.1

factors preventing, 2.3.3, 2.3.3

Internet, 2.3.2

linear, 2.3.3

scans

index, 13.5.3

index joins, 13.5.3.8.1

index of type bitmap, 13.5.3.9

sample table, 13.5.6

sample table and hints cannot override, 13.5.7

scattered read wait events, 10.3.3

actions, 10.3.3.1

SEARCH_COLUMNS column

PLAN_TABLE table, 19.9

segment-level statistics, 10.1.3.5

SELECT statement

SAMPLE clause, 13.5.6

selectivity

creating indexes, 15.1.3

improving for an index, 15.1.4

indexes, 15.1.6

ordering columns in an index, 2.5.3.5

semijoins, 13.6.2

sequential read wait events

actions, 10.3.4.1

service hours, 2.4.2

session memory statistic, 7.3.5.1.2

SESSION_CACHED_CURSORS initialization parameter, 7.3.7

SESSIONS initialization parameter, 4.1.1

SGA size, 7.4.1

SGA_TARGET initialization parameter, 4.1.1

and Automatic Shared Memory Management, 7.1.2

automatic memory management, 7.1.2

shared pool contention, 10.3.10.3

shared server

performance issues, 4.3

reducing contention, 4.3

tuning, 4.3

tuning memory, 7.3.5.1

shared SQL areas

memory allocation, 7.3.4.1.1

SHARED_POOL_RESERVED_SIZE initialization parameter, 7.3.8.2

SHARED_POOL_SIZE initialization parameter, 7.3.4.1.2, 7.3.4.2, 7.3.8.4

allocating library cache, 7.3.4.1.1

tuning the shared pool, 7.3.5.1.4

SHOW SGA statement, 7.1.5.2

sizing redo logs, 4.1.3

snapshots

preserved set, 5.2

soft parsing, 2.5.5

software

components, 2.4.1.2

sort areas

tuning, 7.5

sort merge joins, 13.6.5

cost-based optimization, 13.6.2

SQL Access Advisor, 17.1, 17.1, 17.2.3.2

constants, 17.2.8

creating a task, 17.1.1

defining the workload, 17.1.1

EXECUTE_TASK procedure, 17.2.5.3.1

generating the recommendations, 17.1.1

implementing the recommendations, 17.1.1

maintaining workloads, 17.2.4.8

privileges, 17.2.2

quick tune, 17.2.6

recommendation process, 17.2.5.6

steps in using, 17.1.1

workload objects, 17.2.4.1

SQL Access Advisor workloads

maintaining, 17.2.4.8

SQL Profiles

description, 12.1.2.2

managing with APIs, 12.4

SQL statements

avoiding the use of indexes, 15.1.6

ensuring the use of indexes, 15.1.5

execution plans of, 13.4.2

modifying indexed data, 15.1.3

waiting for I/O, 10.3.3.4

SQL trace facility, 20.3.1, 20.4.3

example of output, 20.4.4.10

output, 20.4.4.1

statement truncation, 20.4.4.7

steps to follow, 20.4

trace files, 20.4.1

SQL Tuning Advisor, 1.2.1, 11.4

administering with APIs, 12.2.5

input sources, 12.2.1

overview, 12.2

tuning options, 12.2.2

SQL Tuning Sets

accessing with Oracle Enterprise Manager, 12.3.1

description, 11.4, 12.2, 12.2.1, 12.2.5

managing with APIs, 12.3, 12.3.2

SQL Workload Journal, 17.2.5.5

SQL*Net

message from client idle events, 10.3.1.1

message from dblink wait events, 10.3.1.2

more data to client wait events, 10.3.1.3

SQL_STATEMENT column

TKPROF_TABLE, 20.4.5.3

SQL_TRACE

initialization parameter, 20.4.2

SQLAccess Advisor, 1.2.1, 11.4

accessing with Oracle Enterprise Manager, 11.4

SQLTUNE_CATEGORY initialization parameter

determining the SQL Profile category, 12.1.2.2

ST enqueue

contention, 10.3.7.2.1

STAR_TRANSFORMATION_ENABLED initialization parameter, 13.3.2

start columns

in partitioning and EXPLAIN PLAN statement, 19.8.1

STATEMENT_ID column

PLAN_TABLE table, 19.9

statistics

and STATISTICS_LEVEL initialization parameter, 1.2

automatic gathering, 14.2

baselines, 5.1

collecting on external tables, 14.2.3.1

consistent gets from cache, 7.2.2.3

databases, 5.1.1

db block gets from cache, 7.2.2.3

displaying in views, 14.6.1

enabling automatic gathering, 14.2.2

exporting and importing, 14.5.2

GATHER_STATS_JOB, 14.2.1

gathering, 5.1

gathering stale, 14.3.1.5

gathering using sampling, 14.3.1.1

gathering with DBMS_STATS package, 14.3.1

gathering with DBMS_STATS procedures, 14.3.1

generating for query optimization, 14.2

histograms, 14.6.2

limitations on restoring previous versions, 14.5.1

locking, 14.5.4

manually gathering, 14.3

max session memory, 7.3.5.1.2

missing, 14.5.7

operating systems, 5.1.2

CPU statistics, 5.1.2.1

disk statistics, 5.1.2.3

network statistics, 5.1.2.4

virtual memory statistics, 5.1.2.2

optimizer, 14.1

optimizer mode, 13.2.1

optimizer use of, 13.4

physical reads from cache, 7.2.2.3

restoring previous versions, 14.5.1

segment-level, 10.1.3.5

session memory, 7.3.5.1.2

shared server processes, 4.3.2

stale, 14.3.1.5

system, 14.4

time model, 5.1.1.2

user-defined, 14.3.1.6

when to gather, 14.3.2

STATISTICS_LEVEL initialization parameter, 5.1.3, 10.1.3.1

and Automatic Workload Repository, 5.2

enabling automatic database diagnostic monitoring, 6.2.3

settings for statistic gathering, 1.2

stop columns

in partitioning and EXPLAIN PLAN statement, 19.8.1

stored outlines

creating and using, 18.1.5

execution plans and plan stability, 18.1

hints, 18.1.1.1

moving tables, 18.1.8

storage requirements, 18.1.2

using, 18.1.6

viewing data for, 18.1.7

STREAMS_POOL_SIZE initialization parameter, 4.1.1, 7.1.2

striping

manual, 8.2.2

subqueries

NOT IN, 13.6.2

unnesting, 11.5.4.1.3

swapping, 9.4.1.1, 9.4.2

reducing, 7.1.5.1

switching processes, 9.4.4.1

system architecture, 2.4

configuration, 2.4.2

hardware components, 2.4.1.1

CPUs, 2.4.1.1.1

I/O subsystems, 2.4.1.1.3

memory, 2.4.1.1.2

networks, 2.4.1.1.4

software components, 2.4.1.2

data and transactions, 2.4.1.2.4

implementing business logic, 2.4.1.2.2

managing the user interface, 2.4.1.2.1

user requests and resource allocation, 2.4.1.2.3

System Global Area tuning, 7.1.5.2

T

tables

creating, 4.2

design, 2.5.3

full scans, 10.3.5.2.2

placement on disk, 8.2.2

setting storage options, 4.2

tablespaces, 4.1.4

creating, 4.1.4

creating temporary, 4.1.4.2

temporary, 4.1.4, 4.1.4.2

tablespec

hint syntax, 16.2.3

templates

SQL Access Advisor, 17.2.3.2

temporary tablespaces, 4.1.4

creating, 4.1.4.2

testing designs, 2.6.4

thrashing, 9.4.2

throughput

optimizer goal, 13.2

optimizing, 13.2

time model statistics, 5.1.1.2

TIMED_STATISTICS initialization parameter

SQL Trace, 20.4.1

TIMESTAMP column

PLAN_TABLE table, 19.9

TKPROF program, 20.3.2, 20.4.3

editing the output SQL script, 20.4.5.2

example of output, 20.4.4.10

generating the output SQL script, 20.4.5.1

row source operations, 20.4.4.2

syntax, 20.4.3.2

using the EXPLAIN PLAN statement, 20.4.3.2

wait event information, 20.4.4.3

TKPROF_TABLE, 20.4.5.3

querying, 20.4.5.3

TM enqueue

contention, 10.3.7.2.3

tools

for performance tuning, 1.2

TRACEFILE_IDENTIFIER initialization parameter

identifying trace files, 20.4.1

tracing

consolidating with trcsess, 20.2

identifying files, 20.4.1

transactions and data, 2.4.1.2.4

trcsess utility, 20.2

trickle rollout strategy, 2.7.1

TUNE_MVIEW procedure, 17.3

tuning

and bottleneck elimination, 1.1.2.4.2

and proactive monitoring, 1.1.2.4.1

latches, 1.1.2.3, 10.3.10.3

logical structure, 15.1.1

memory allocation, 7.1.6

resource contention, 10

shared server, 4.3

sorts, 7.5

SQL Tuning Advisor, 12.2

System Global Area (SGA), 7.1.5.2

TX enqueue

contention, 10.3.7.2.4

type conversion, 11.5.3.2

U

undo management

automatic mode, 4.1.2

UNDO TABLESPACE clause, 4.1.2

UNDO_MANAGEMENT initialization parameter, 4.1.1, 4.1.2

UNDO_TABLESPACE initialization parameter, 4.1.1

UNIQUE constraint, 15.1.9

uniqueness, 15.1.9

UNIX system performance, 9.2.1

untransformed column values, 11.5.3.2

upgrade

to the cost-based optimizer, 18.2.2

USE_STORED_OUTLINES parameter, 18.1.6, 18.1.6

user global area (UGA)

shared servers, 4.3, 7.3.5.1

V$SESSTAT, 7.3.5.1.2

user requests, 2.4.1.2.3

USER_DUMP_DEST initialization parameter, 20.4.1

SQL Trace, 20.4.1

USER_ID column

TKPROF_TABLE, 20.4.5.3

USER_OUTLINE_HINTS view

stored outline hints, 18.1.7

USER_OUTLINES view

stored outlines, 18.1.7

user-defined bind variables, 13.4.1.2

users

interaction method, 2.4.2

interfaces, 2.5.6

location, 2.4.2

network speed, 2.4.2

number of, 2.4.2

requests, 2.5.6

response time, 2.4.2

UTLCHN1.SQL script, 10.2.4.3

UTLXPLP.SQL script

displaying plan table output, 19.4

for viewing EXPLAIN PLANs, 13.4.2.1

UTLXPLS.SQL script

displaying plan table output, 19.4

for viewing EXPLAIN PLANs, 13.4.2.1

used for displaying EXPLAIN PLANs, 13.4.2.1

V

V$ACTIVE_SESSION_HISTORY view, 5.1.1.3, 10.1.3.3

V$ADVISOR_PROGRESS view, 12.2.5.4, 12.5

V$BH view, 7.2.7

V$BUFFER_POOL_STATISTICS view, 7.2.6, 7.2.6

V$DB_CACHE_ADVICE view, 7.2.2.1, 7.2.2.3, 7.2.3, 7.2.3.1, 7.2.3.2, 7.2.5

V$EVENT_HISTOGRAM view, 10.1.3.3

V$FILE_HISTOGRAM view, 10.1.3.3

V$JAVA_LIBRARY_CACHE_MEMORY view, 7.3.3.3.3

V$JAVA_POOL_ADVICE view, 7.3.3.3.3

V$LIBRARY_CACHE_MEMORY view, 7.3.3.3.2

V$LIBRARYCACHE view

NAMESPACE column, 7.3.3.2

V$OSSTAT view, 5.1.2.1

V$PGASTAT view, 7.5.1.2.1

V$PROCESS view, 7.5.1.2.2

V$PROCESS_MEMORY view, 7.5.1.2.3

V$QUEUE view, 4.3.2

V$ROWCACHE view

GETMISSES column, 7.3.4.1.2

GETS column, 7.3.4.1.2

performance statistics, 7.3.3.4

V$RSRC_CONSUMER_GROUP view, 10.1.2.1.3

V$SESS_TIME_MODEL view, 5.1.1.2, 10.1.3.3

V$SESSION view, 10.1.3.3, 10.1.3.4, 10.3

V$SESSION_EVENT view, 10.1.3.3, 10.3

V$SESSION_WAIT view, 10.1.3.3, 10.3

V$SESSION_WAIT_CLASS view, 10.1.3.3

V$SESSION_WAIT_HISTORY view, 10.1.3.3, 10.1.3.3

V$SESSTAT view, 10.1.2.1.3

using, 7.3.5.1.2

V$SHARED_POOL_ADVICE view, 7.3.3.3.1

V$SHARED_POOL_RESERVED view, 7.3.8.4

V$SQL_PLAN view

using to display execution plan, 19.1.3.1

V$SQL_PLAN_STATISTICS view

using to display execution plan statistics, 19.1.3.1

V$SQL_PLAN_STATISTICS_ALL view

using to display execution plan information, 19.1.3.1

V$SQL_WORKAREA view, 7.5.1.2.6

V$SQL_WORKAREA_ACTIVE view, 7.5.1.2.5

V$SQL_WORKAREA_HISTOGRAM view, 7.5.1.2.4

V$SYS_TIME_MODEL view, 5.1.1.2, 5.1.2.1, 10.1.3.3

V$SYSMETRIC_HISTORY view, 5.1.2.1

V$SYSSTAT view

redo buffer allocation, 7.4.2

using, 7.2.2.3

V$SYSTEM_EVENT view, 10.1.3.3, 10.3

V$SYSTEM_WAIT_CLASS view, 10.1.3.3

V$TEMP_HISTOGRAM view, 10.1.3.3

V$UNDOSTAT view, 4.1.2

V$WAITSTAT view, 10.1.3.4

validating designs, 2.6.4

views, 2.5.4

DBA_HIST, 5.2.3

statistics, 14.6.1

virtual memory statistics, 5.1.2.2

vmstat UNIX command, 9.4.1.1

W

wait events, 5.1.1.1

buffer busy waits, 10.3.2

classes, 5.1.1.1, 10.1.3.2

contention wait events, 10.3.10

direct path, 10.3.6

enqueue, 10.3.7

free buffer waits, 10.3.9

idle wait events, 10.4

latch, 10.3.10

library cache latch, 10.3.10.1

log buffer space, 10.3.14

log file parallel write, 10.3.11

log file switch, 10.3.15

log file sync, 10.3.16

network communication wait events, 10.3.1

rdbms ipc reply, 10.3.17

resource wait events, 10.3.4

Windows performance, 9.2.2

workload objects, 17.2.4.1

workloads

deleting, 17.2.4.4

estimating, 2.6.2

benchmarking, 2.6.2.2

extrapolating, 2.6.2.1

modeling, 2.6.3

testing, 2.6.4