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

defined, 11.4.2

execution plans, 11.4

hash scans, 11.2.5

index scans, 11.2.3

Active Session History, 5.1.1.3

report

activity over time, 5.3.9.8

load profile, 5.3.9.2

top events, 5.3.9.1

top files, 5.3.9.7

top Java, 5.3.9.5

top latches, 5.3.9.7

top objects, 5.3.9.7

top PL/SQL, 5.3.9.4

top sessions, 5.3.9.6

Top SQL, 5.3.9.3

using, 5.3.9

adaptive thresholds, 5.2.3

ALL_OUTLINE_HINTS view

stored outline hints, 20.1.7

ALL_OUTLINES view

stored outlines, 20.1.7

ALL_ROWS hint, 11.5.2.2

allocation of memory, 7.1

ALTER INDEX statement, 14.1.7

ALTER SESSION statement

examples, 21.4.2

SET SESSION_CACHED_CURSORS clause, 7.3.7.2

ANALYZE statement, 13.3.1

antijoins, 11.3.2

applications

deploying, 2.7

design principles, 2.5

development trends, 2.5.7

implementing, 2.5.6

Automatic Database Diagnostic Monitor

actions and rationales of recommendations, 6.1.3

analysis results example, 6.1.4

and DB time, 6.1.1

CONTROL_MANAGEMENT_PACK_ACCESS parameter, 6.2

DBIO_EXPECTED, 6.2

example report, 6.1.4

findings, 6.1.3

overview, 6.1

results, 6.1.3

setups, 6.2

STATISTICS_LEVEL parameter, 6.2

types of problems considered, 6.1.1

types of recommendations, 6.1.3

automatic database diagnostic monitoring, 1.2.1, 16.4.1

automatic segment-space management, 4.1.4.1, 8.2.6.2, 10.3.1.2.1

Automatic Shared Memory Management, 7.1.3

automatic SQL tuning, 1.2.1, 16.4.2

analysis, 17.1

overview, 17

Automatic Tuning Optimizer, 17.1

automatic undo management, 4.1.2, 4.1.2

Automatic Workload Repository, 1.2.1

configuring, 5.2

data gathering, 5.1

DBMS_WORKLOAD_REPOSITORY package, 5.3.1, 5.3.2, 5.3.3

default settings, 5.2.4

factors affecting space usage, 5.2.4

managing with APIs, 5.3.1, 5.3.2, 5.3.3

minimizing space usage, 5.2.4

overview, 5.2

recommendations for retention period, 5.2.4

reports, 5.3.6, 5.3.7, 5.3.8

retention period, 5.2.4

settings in DBA_HIST_WR_CONTROL view, 5.3.1.3

space usage, 5.2.4

statistics collected, 5.2

turning off automatic snapshot collection, 5.2.4

unusual percentages in reports, 5.3.6

views for accessing data, 5.3.5

awrrpt.sql

Automatic Workload Repository report, 5.3.6, 5.3.7, 5.3.8

B

baselines, 1.1.2.2, 5.2.2

performance, 5.1

benchmarking workloads, 2.6.2.2

big bang rollout strategy, 2.7.1

bind variables, 7.3.1.3

peeking, 11.1.3

bitmap indexes, 2.5.3.2.2

inlist iterator, 12.9.5.3

on joins, 14.6

when to use, 14.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.18.1.2

broadcast

distribution value, 12.10

B-tree indexes, 2.5.3.2.1

buffer busy wait events, 10.2.3, 10.3.1

actions, 10.3.1.2

buffer cache

contention, 10.3.2, 10.3.3, 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, 12.10

C

CARDINALITY column

PLAN_TABLE table, 12.10

cardinality, column, 11.1.2.2

cartesian joins, 11.3.6

chained rows, 10.2.4.3

classes

wait events, 5.1.1.1, 10.1.3.2

client/server applications, 9.4.1.2

clusters, 14.8, 14.8

hash and scans of, 11.2.5

scans of, 11.2.4

sorted hash, 14.9

column order

indexes, 2.5.3.5

columns

cardinality, 11.1.2.2

to index, 14.1.3

COMPATIBLE initialization parameter, 4.1.1

components

hardware, 2.4.1.1

software, 2.4.1.2

composite indexes, 14.1.4

composite partitioning

examples of, 12.9.2

conceptual modeling, 3.1.2

consistency

read, 10.2.4.2

consistent gets from cache statistic, 7.2.2.3

consistent mode

TKPROF, 21.4.4.1

constraints, 14.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.1.4.2

CONTROL_FILES initialization parameter, 4.1.1

CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter

enabling automatic database diagnostic monitoring, 6.2

cost

optimizer calculation, 11.1.1

COST column

PLAN_TABLE table, 12.10

cost-based optimizations

procedures for plan stability, 20.2

upgrading to, 20.2.2

CPUs, 2.4.1.1.1

statistics, 5.1.2.1, 10.1.2.1.3

utilization, 9.4.1

CREATE INDEX statement

PARALLEL clause, 4.2.3

CREATE OUTLINE statement, 20.1.5

create_extended_statistics, 13.3.1.6.1, 13.3.1.7.1

CREATE_STORED_OUTLINES initialization parameter, 20.1.5, 20.1.5.1

CREATE_STORED_OUTLINES parameter, 20.1.5

current mode

TKPROF, 21.4.4.1

CURSOR_NUM column

TKPROF_TABLE table, 21.4.5.3

CURSOR_SHARING initialization parameter, 7.3.2.1

CURSOR_SPACE_FOR_TIME initialization parameter, 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, 13.7.1

views used in optimization, 13.7.1

Data Pump

Export utility

statistics on system-generated columns names, 13.5.3

Import utility

copying statistics, 13.5.3

database monitoring, 1.2.1, 16.4.1

diagnostic, 6.1

Database Resource Manager, 9.1.3, 9.1.3, 9.4.2, 10.1.2.1.3

database tuning

transient performance problems, 5.3.8

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

db block gets from cache statistic, 7.2.2.3

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

actions, 10.3.2.1, 10.3.3.1

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

actions, 10.3.3.1

DB time

metric, 6.1.1

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.2, 11.2.1

cost-based optimization, 11.3.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.8.3.1

DBA_HIST views, 5.3.5

DBA_HIST_WR_CONTROL view

Automatic Workload Repository settings, 5.3.1.3

DBA_OBJECTS view, 7.2.7

DBA_OUTLINE_HINTS view

stored outline hints, 20.1.7

DBA_OUTLINES view

stored outlines, 20.1.7

DBIO_EXPECTED parameter, 6.2

DBMS_ADDM package

Automatic Database Diagnostic Monitor, 6.3

DBMS_ADVISOR package, 18.1

setting DBIO_EXPECTED, 6.2

setups for ADDM, 6.2, 6.2

DBMS_MONITOR package

End to End Application Tracing, 21.1

DBMS_OUTLN package

procedures for managing outlines, 20.1.4

DBMS_OUTLN_EDIT package

procedures for managing outlines, 20.1.4

DBMS_RESULT_CACHE, 7.6.1.3, 7.6.1.3.2

DBMS_SHARED_POOL package

managing the shared pool, 7.3.9

DBMS_SPM

EVOLVE_SQL_PLAN_BASELINE, 15.2.3.2

DBMS_SQLDIAG, 16.6.1.2

DBMS_SQLTUNE package

SQL Tuning Advisor, 17.3.4, 17.4

SQL Tuning Sets, 17.4

dbms_stats functions

create_extended_statistics, 13.3.1.6.1

drop_extended_stats, 13.3.1.6.3, 13.3.1.7.3

gather_table_stats, 13.3.1.7.1

show_extended_stats_name, 13.3.1.6.2

DBMS_STATS package, 13.3.1, 13.3.4, 18.1

managing query optimizer statistics, 11.5.2.3

manually determining sample size for gathering procedures, 13.3.1.1

dbms_stats package

method_opt, 13.3.1.6.5

DBMS_STATS_DISCOVER, 13.3.1.6

DBMS_WORKLOAD_REPOSITORY package

managing the Automatic Workload Repository, 5.3.1, 5.3.2, 5.3.3

DBMS_XPLAN package

displaying plan table output, 12.4

debugging designs, 2.6.4

default cache, 7.2.4.1

deploying applications, 2.7

DEPTH column

TKPROF_TABLE table, 21.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, 16.4.1

introduction, 6.1

direct path

read events, 10.3.4

read events actions, 10.3.4.2

read events causes, 10.3.4.1

wait events, 10.3.5

write events actions, 10.3.5.2

write events causes, 10.3.5.1

disabled constraints, 14.1.10

disks

monitoring operating system file activity, 10.1.2.2

statistics, 5.1.2.3

DISTRIBUTION column

PLAN_TABLE table, 12.10

domain indexes

and EXPLAIN PLAN, 12.9.6

using, 14.7

drop_extended_stats, 13.3.1.6.3, 13.3.1.7.3

dynamic sampling

process, 13.6.2.2

when to use, 13.6.3

E

emergencies

performance, 3.2

Emergency Performance Method, 3.2.1

enabled constraints, 14.1.10

End to End Application Tracing, 21, 21.1

action and module names, 2.5.6, 21.1

creating a service, 21.1

DBMS_APPLICATION_INFO package, 21.1

DBMS_MONITOR package, 21.1

enforced constraints, 14.1.10

enqueue wait events, 10.2.3, 10.3.6

actions, 10.3.6.2

statistics, 10.1.3.4.5

equijoins, 16.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, 21.4.2

EXPLAIN PLAN output, 21.4.4.10

SQL trace facility output, 21.4.4.10

EXECUTE_TASK procedure, 18.2.5.5.1

execution plans

capturing SQL plan baselines, 15.2.1

evolving SQL plan baselines, 15.2.3

examples, 21.4.3.1

joins, 11.3.1

loading from a SQL Tuning Set, 15.2.1.2.1

loading from AWR snapshots, 15.2.1.2.1

loading from the cursor cache, 15.2.1.2.2

managing SQL plan baselines, 15.2

overview of, 11.4

plan stability, 20.1

preserving with plan stability, 20.1

selecting SQL plan baselines, 15.2.2

TKPROF, 21.4.3.1, 21.4.3.2

viewing with the utlxpls.sql script, 11.4.1

EXPLAIN PLAN statement

access paths, 11.2.6

and domain indexes, 12.9.6

and full partition-wise joins, 12.9.4

and partial partition-wise joins, 12.9.3

and partitioned objects, 12.9

basic steps, 11.4.1

examples of output, 21.4.4.10

execution order of steps in output, 11.4.1

invoking with the TKPROF program, 21.4.3.2

PLAN_TABLE table, 12.2

restrictions, 12.1.4

scripts for viewing output, 11.4.1

viewing the output, 11.4.1

EXPLAIN_MVIEW procedure, 18.3

expression

mixed-type, 16.5.3.2

Expression Statistics, 13.3.1.7

Extended Statistics, 13.3.1.5

extended syntax

for specifying tables in hints, 19.2.3

global hints, 19.2.3

EXTENT MANAGEMENT LOCAL

creating temporary tablespaces, 4.1.4.2

extrapolating workloads, 2.6.2.1

F

FAST_START_MTTR_TARGET

and tuning instance recovery, 10.5.3

Fast-Start checkpointing architecture, 10.5.2

Fast-Start Fault Recovery, 10.5, 10.5.2

features, new, Preface

FILESYSTEMIO_OPTIONS initialization parameter, 9.1.1.2

FIRST_ROWS(n) hint, 11.5.2.2

free buffer wait events, 10.2.3, 10.3.8

free lists, 10.3.1.2.1

FULL hint, 14.1.6

full outer joins, 11.3.7.4

full partition-wise joins, 12.9.4

full table scans, 10.3.4.2.2

function-based indexes, 2.5.3.2.3, 14.2

G

GATHER_ INDEX_STATS procedure

in DBMS_STATS package, 13.3.1

GATHER_DATABASE_STATS procedure

in DBMS_STATS package, 13.3.1

GATHER_DICTIONARY_STATS procedure

in DBMS_STATS package, 13.3.1, 13.3.4

GATHER_SCHEMA_STATS procedure

in DBMS_STATS package, 13.3.1, 13.3.4

gather_table_stats, 13.3.1.7.1

GATHER_TABLE_STATS procedure

in DBMS_STATS package, 13.3.1, 13.3.4

GETMISSES column

in V$ROWCACHE table, 7.3.4.1.2

GETS column

in V$ROWCACHE view, 7.3.4.1.2

global hints, 19.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, 12.10

hash clusters

scans of, 11.2.5

sorted, 14.9

hash joins, 11.3.4

cost-based optimization, 11.3.2

index join, 11.2.3.8.1

hash partitions, 12.9

examples of, 12.9.1

hashing, 14.9

high water mark, 11.2.1

hints

access paths, 16.5.4, 19.1.2.8

as used in outlines, 20.1.1.1

cannot override sample access path, 11.2.7

degree of parallelism, 19.1.2.7

FULL, 14.1.6

global, 19.2.3

global compared to local, 19.2.3

INDEX_FFS, 11.2.3.7

INDEX_JOIN, 11.2.3.8

indexspec syntax, 19.2.4

location syntax, 19.2.2

NO_INDEX, 14.1.6

optimization approach and goal, 19.1.2.1

overriding optimizer choice, 11.2.7

overriding OPTIMIZER_MODE, 11.5.2.2

parallel query option, 19.1.2.7

specifying a query block, 19.2.2

specifying indexes, 19.2.4

tablespec syntax, 19.2.3

using extended syntax, 19.2.3

histograms

frequency, 13.7.2.2

height-balanced, 13.7.2.1

HOLD_CURSOR clause, 7.3.2.6.2

hours of service, 2.4.2

HW enqueue

contention, 10.3.6.2.2

I

ID column

PLAN_TABLE table, 12.10

idle wait events, 10.3.9

SQL*Net message from client, 10.3.18.1

implementing business logic, 2.4.1.2.2

INDEX hint, 14.1.6

INDEX_COMBINE hint, 14.1.6

INDEX_FFS hint, 11.2.3.7, 11.2.3.7.1

INDEX_JOIN hint, 11.2.3.8

indexes

adding columns, 2.5.3.1

appending columns, 2.5.3.1

avoiding the use of, 14.1.6

bitmap, 2.5.3.2.2, 14.5

B-tree, 2.5.3.2.1

choosing columns for, 14.1.3

column order, 2.5.3.5

composite, 14.1.4

costs, 2.5.3.3

creating, 4.2.3

design, 2.5.3

domain, 14.7

dropping, 14.1.1

enforcing uniqueness, 14.1.9

ensuring the use of, 14.1.5

function-based, 2.5.3.2.3, 14.2

improving selectivity, 14.1.4

index joins, 11.2.3.8.1

joins, 11.2.3.8.1

low selectivity, 14.1.6

modifying values of, 14.1.3

non-unique, 14.1.9

partitioned, 2.5.3.2.4

placement on disk, 8.2.2

rebuilding, 14.1.7

re-creating, 14.1.7

reducing I/O, 2.5.3.5

reverse key, 2.5.3.2.5

scans of, 11.2.3

selectivity, 2.5.3.5

selectivity of, 14.1.3

sequences in, 2.5.3.4

serializing in, 2.5.3.4

specifying in hints, 19.2.4

statistics gathering, 13.5

indexspec

hint syntax, 19.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, 11.3.2

DB_NAME, 4.1.1

OPEN_CURSORS, 4.1.1

OPTIMIZER_FEATURES_ENABLE, 11.2.3.7, 11.2.3.8

OPTIMIZER_MODE, 11.5.2.1, 19.1.2.1

PGA_AGGREGATE_TARGET, 4.2.3.1

PROCESSES, 4.1.1

SESSIONS, 4.1.1

SQL_TRACE, 21.4.2, 21.4.2

STREAMS_POOL_SIZE, 4.1.1, 7.1.3

USER_DUMP_DEST, 21.4.1

INLIST ITERATOR operation, 12.9.5

inlists, 12.9.5

instance caging, 9.4

instance configuration

initialization files, 4.1.1

performance considerations, 4.1

instance recovery

Fast-Start Fault Recovery, 10.5.2

performance tuning, 10.5

Internet scalability, 2.3.2

I/O

and SQL statements, 10.3.2.4

contention, 5.1.1.1, 10.1.2.2.1, 10.1.3.2, 10.3.2.2, 10.3.14

excessive I/O waits, 10.3.2.2

monitoring, 10.1.2.2

objects causing I/O waits, 10.3.2.5

reducing, 14.1.4

J

joins

antijoins, 11.3.2

cartesian, 11.3.6

execution plans and, 11.3.1

full outer, 11.3.7.4

hash, 11.3.4

index joins, 11.2.3.8.1

join order and execution plans, 11.4

nested loop, 11.3.3

nested loops and cost-based optimization, 11.3.2

order, 16.5.4, 16.5.4

outer, 11.3.7

partition-wise

examples of full, 12.9.4

examples of partial, 12.9.3

full, 12.9.4

semijoins, 11.3.2

sort merge, 11.3.5

sort-merge and cost-based optimization, 11.3.2, 11.3.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, 5.3.9.7.3

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.6.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, 18.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, 21.4.1

MAXOPENCURSORS clause, 7.3.2.6.2

memory

hardware component, 2.4.1.1.2

memory allocation

importance, 7.1

library cache, 7.3.4.1.1

shared SQL areas, 7.3.4.1.1

tuning, 7.1.7

method_opt, 13.3.1.6.5

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

MultiColumn Statistics, 13.3.1.6

multiple buffer pools, 7.2.4

N

NAMESPACE column

V$LIBRARYCACHE view, 7.3.3.2

nested loop joins, 11.3.3

cost-based optimization, 11.3.2

network

hardware component, 2.4.1.1.4

speed, 2.4.2

statistics, 5.1.2.4

network communication wait events, 10.3.18

db file scattered read wait events, 10.3.2

db file sequential read wait events, 10.3.2, 10.3.3

SQL*Net message from Dblink, 10.3.18.2

SQL*Net more data to client, 10.3.18.3

new features, Preface

NO_INDEX hint, 14.1.6

NOT IN subquery, 11.3.2

O

OBJECT_INSTANCE column

PLAN_TABLE table, 12.10

OBJECT_NAME column

PLAN_TABLE table, 12.10

OBJECT_NODE column

PLAN_TABLE table, 12.10

OBJECT_OWNER column

PLAN_TABLE table, 12.10

OBJECT_TYPE column

PLAN_TABLE table, 12.10

object-orientation, 2.5.7

OLAP_PAGE_POOL_SIZE initialization parameter, 7.5.2

OPEN_CURSORS initialization parameter, 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, 12.10, 12.10

optimization

and dynamic sampling, 11.5.2.3

choosing the approach, 11.5.2.1

cost calculation, 11.1.1

cost-based and choosing an access path, 11.2.7

described, 11.1

hints, 11.2.3.7, 11.2.3.8, 11.5.2.2

manual, 11.5.2.2

operations performed, 11.1.1

optimizer

cost calculation, 11.1.1

estimator, 11.1.2.2

goals, 11.5.2

introduction, 1.1.3.1, 11.1

modes, 17.1

moving to from RBO, 20.2.1

operations, 11.1.1

parameters for setting mode, 11.5.2.1

plan stability, 20.1

query, 1.1.3.1

statistics, 13.1

throughput, 11.5.2

upgrading, 20.2.2

OPTIMIZER column

PLAN_TABLE, 12.10

OPTIMIZER_FEATURES_ENABLE initialization parameter, 11.2.3.7, 11.2.3.8

OPTIMIZER_MODE initialization parameter, 11.5.2.1, 11.5.2.1, 19.1.2.1

hints affecting, 11.5.2.2

OPTIONS column

PLAN_TABLE table, 12.10

OPTMIZER_DYNAMIC_SAMPLING initialization parameter, 11.5.2.3

Oracle CPU statistics, 10.1.2.1.3

Oracle Enterprise Manager

advisors, 1.2.1

Performance page, 1.2.1

Oracle Forms, 21.4.2

control of parsing and private SQL areas, 7.3.2.6.5

Oracle Managed Files, 8.2.5

tuning, 8.2.5

Oracle Orion

calibration tool parameters, 8.4.4

command-line options, 8.4.4

Oracle performance improvement method, 3.1

steps, 3.1.1

order

joins, 16.5.4

OTHER column

PLAN_TABLE table, 12.10

OTHER_TAG column

PLAN_TABLE table, 12.10

outer joins, 11.3.7, 16.5.4.1

outlines

CREATE OUTLINE statement, 20.1.5

creating and using, 20.1.5

description, 20.1

execution plans and plan stability, 20.1

hints, 20.1.1.1

moving tables, 20.1.8

moving to the cost-based optimizer, 20.2.1

storage requirements, 20.1.2

using, 20.1.6

viewing data for, 20.1.7

P

package

DBMS_RESULT_CACHE, 7.6.1.3, 7.6.1.3.2

packages

DBMS_ADVISOR, 18.1

DBMS_STATS, 18.1

page table, 9.4.1.1.2

paging, 9.4.1.2

reducing, 7.1.6.1

PARALLEL clause

CREATE INDEX statement, 4.2.3

parameter

RESULT_CACHE_MAX_SIZE, 7.6.1.3.1

RESULT_CACHE_MODE, 7.6.3.1

PARENT_ID column

PLAN_TABLE table, 12.10

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

PARTITION_START column

PLAN_TABLE table, 12.10

PARTITION_STOP column

PLAN_TABLE table, 12.10

partitioned indexes, 2.5.3.2.4

partitioned objects

and EXPLAIN PLAN statement, 12.9

partitioning

distribution value, 12.10

examples of, 12.9.1

examples of composite, 12.9.2

hash, 12.9

range, 12.9

start and stop columns, 12.9.1

partition-wise joins

full, 12.9.4

full, and EXPLAIN PLAN output, 12.9.4

partial, and EXPLAIN PLAN output, 12.9.3

PCTFREE parameter, 4.2, 10.2.4.3

PCTUSED parameter, 10.2.4.3, 10.2.4.3

peeking

bind variables, 11.1.3

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.1

tools for diagnosing and tuning, 1.2

UNIX-based systems, 9.2.1

viewing execution plans, 11.4.1

Windows, 9.2.2

performance problems

transient, 5.3.8

performance tuning

Fast-Start Fault Recovery, 10.5

instance recovery, 10.5

FAST_START_MTTR_TARGET, 10.5.2

setting FAST_START_MTTR_TARGET, 10.5.3

using V$INSTANCE_RECOVERY, 10.5.2.3

PGA_AGGREGATE_TARGET initialization parameter, 4.1.1, 4.2.3.1, 7.5.1, 9.1.2.2

physical reads from cache statistic, 7.2.2.3

plan stability, 20.1

limitations of, 20.1.1

preserving execution plans, 20.1

procedures for the cost-based optimizer, 20.2

use of hints, 20.1.1

PLAN_TABLE table

BYTES column, 12.10

CARDINALITY column, 12.10

COST column, 12.10

creating, 12.2, 12.2

displaying, 12.4

DISTRIBUTION column, 12.10

ID column, 12.10

OBJECT_INSTANCE column, 12.10

OBJECT_NAME column, 12.10

OBJECT_NODE column, 12.10

OBJECT_OWNER column, 12.10

OBJECT_TYPE column, 12.10

OPERATION column, 12.10

OPTIMIZER column, 12.10

OPTIONS column, 12.10

OTHER column, 12.10

OTHER_TAG column, 12.10

PARENT_ID column, 12.10

PARTITION_ID column, 12.10

PARTITION_START column, 12.10

PARTITION_STOP column, 12.10

POSITION column, 12.10

REMARKS column, 12.10

SEARCH_COLUMNS column, 12.10

STATEMENT_ID column, 12.10

TIMESTAMP column, 12.10

PL/SQL procedures

EXPLAIN_MVIEW, 18.3

TUNE_MVIEW, 18.3

POSITION column

PLAN_TABLE table, 12.10

precompilers

control of parsing and private SQL areas, 7.3.2.6.2

PRIMARY KEY constraint, 14.1.9

PRIVATE_SGA variable, 7.3.5.1.3

privileges

SQL Access Advisor, 18.2.2

proactive monitoring, 1.1.2.4.1

processes

scheduling, 9.4.1.4.1

PROCESSES initialization parameter, 4.1.1

program global area (PGA)

direct path read, 10.3.4

direct path write, 10.3.5

shared servers, 7.3.5.1.1

programming languages, 2.5.6

Q

queries

avoiding the use of indexes, 14.1.6

data, 2.4.2

ensuring the use of indexes, 14.1.5

query optimizer, 1.1.3.1

See optimizer

R

range

distribution value, 12.10

examples of partitions, 12.9.1

partitions, 12.9

rdbms ipc reply wait events, 10.3.17

read consistency, 10.2.4.2

read wait events

direct path, 10.3.4

scattered, 10.3.2

REBUILD clause, 14.1.7

recursive calls, 21.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

data dictionary cache misses, 7.3.4.1.2

paging and swapping, 7.1.6.1

unnecessary parse calls, 7.3.2.6

RELEASE_CURSOR clause, 7.3.2.6.2

REMARKS column

PLAN_TABLE table, 12.10

resources

allocation, 2.4.1.2.3, 2.5.6

bottlenecks, 10.3.18.1.2

wait events, 10.3.3

response time, 2.4.2

cost-based approach, 11.5.2.1

result cache, 7.6.1

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

row cache objects, 10.3.10.3.6

row sources, 11.4.2

rowids

table access by, 11.2.2

rows

row sources, 11.4.2

rowids used to locate, 11.2.2

S

SAMPLE BLOCK clause, 11.2.6

access path and hints, 11.2.7

SAMPLE clause, 11.2.6

access path and hints cannot override, 11.2.7

sample table scans, 11.2.6

hints cannot override, 11.2.7

sar UNIX command, 9.4.1.1.1

scalability, 2.3.1

factors preventing, 2.3.3, 2.3.3

Internet, 2.3.2

linear, 2.3.3

scans

index, 11.2.3

index joins, 11.2.3.8.1

index of type bitmap, 11.2.3.9

sample table, 11.2.6

sample table and hints cannot override, 11.2.7

scattered read wait events, 10.3.2

actions, 10.3.2.1

SEARCH_COLUMNS column

PLAN_TABLE table, 12.10

segment-level statistics, 10.1.3.5

SELECT statement

SAMPLE clause, 11.2.6

selectivity

creating indexes, 14.1.3

improving for an index, 14.1.4

indexes, 14.1.6

ordering columns in an index, 2.5.3.5

semijoins, 11.3.2

sequential read wait events

actions, 10.3.3.1

service hours, 2.4.2

session memory statistic, 7.3.5.1.2

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.3

automatic memory management, 7.1.3

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.6.2

show_extended_stats_name, 13.3.1.6.2

sizing redo logs, 4.1.3

snapshots

about, 5.2.1

soft parsing, 2.5.5

software

components, 2.4.1.2

sort areas

tuning, 7.5

sort merge joins, 11.3.5

cost-based optimization, 11.3.2

SQL Access Advisor, 18.1, 18.1, 18.2.3.2

constants, 18.2.8

creating a task, 18.1.1

EXECUTE_TASK procedure, 18.2.5.5.1

generating the recommendations, 18.1.1

privileges, 18.2.2

recommendation process, 18.2.5.7

steps in using, 18.1.1

SQL management base

about, 15.6

disk space usage, 15.6.1

purging policy, 15.6.2

SQL plan baseline

automatic plan capture, 15.2.1.1

capturing

automatic, 15.2.1.1

manual, 15.2.1.2

evolving manually, 15.2.3.1

evolving with DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE, 15.2.3.2

loading plans from a SQL Tuning Set, 15.2.1.2.1

loading plans from AWR snapshots, 15.2.1.2.1

loading plans from the cursor cache, 15.2.1.2.2

manual plan loading, 15.2.1.2

SQL Tuning Advisor, 15.3

SQL plan baseline, fixed, 15.4

SQL plan baselines

about, 15.2.1

capturing, 15.2.1

displaying, 15.5

enabling, 15.2.2

evolving, 15.2.3

importing and exporting, 15.7

managing, 15.2

plan history, 15.2.1

selecting, 15.2.2

statement log, 15.2.1

SQL statements

avoiding the use of indexes, 14.1.6

ensuring the use of indexes, 14.1.5

execution plans of, 11.4

modifying indexed data, 14.1.3

waiting for I/O, 10.3.2.4

SQL Test Case Builder, 16.6

SQL trace facility, 21.3.1, 21.4.3

example of output, 21.4.4.10

output, 21.4.4.1

statement truncation, 21.4.4.7

steps to follow, 21.4

trace files, 21.4.1

SQL Tuning Advisor, 1.2.1, 16.4.2

administering with APIs, 17.3.4, 17.4

input sources, 17.3.1

overview, 17.2

tuning options, 17.3.2

SQL Tuning Sets

description, 16.4.3, 17.3, 17.3.1, 17.3.4

managing with APIs, 17.4, 17.4

SQL*Net

message from client idle events, 10.3.18.1

message from dblink wait events, 10.3.18.2

more data to client wait events, 10.3.18.3

SQL_STATEMENT column

TKPROF_TABLE, 21.4.5.3

SQL_TRACE

initialization parameter, 21.4.2

SQLAccess Advisor, 1.2.1, 16.4.4

SQLTUNE_CATEGORY initialization parameter

determining the SQL Profile category, 17.5.3

ST enqueue

contention, 10.3.6.2.1

start columns

in partitioning and EXPLAIN PLAN statement, 12.9.1

STATEMENT_ID column

PLAN_TABLE table, 12.10

statistics

and STATISTICS_LEVEL initialization parameter, 1.2

baselines, 5.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, 13.7.1

exporting and importing, 13.5.3

gathering, 5.1

gathering stale, 13.3.1.8

gathering using sampling, 13.3.1.1

gathering with DBMS_STATS package, 13.3.1, 13.3.4

gathering with DBMS_STATS procedures, 13.3.1

limitations on restoring previous versions, 13.5.2

locking, 13.5.5

manually gathering, 13.3

max session memory, 7.3.5.1.2

missing, 13.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, 13.1

optimizer mode, 11.5.2.1

physical reads from cache, 7.2.2.3

restoring previous versions, 13.5.2

segment-level, 10.1.3.5

session memory, 7.3.5.1.2

shared server processes, 4.3.2

stale, 13.3.1.8

system, 13.4

time model, 5.1.1.2

user-defined, 13.3.1.9

when to gather, 13.3.3

STATISTICS_LEVEL initialization parameter, 5.1.3, 10.1.3.1

and Automatic Workload Repository, 5.2

enabling automatic database diagnostic monitoring, 6.2

settings for statistic gathering, 1.2

stop columns

in partitioning and EXPLAIN PLAN statement, 12.9.1

stored outlines

creating and using, 20.1.5

execution plans and plan stability, 20.1

hints, 20.1.1.1

moving tables, 20.1.8

storage requirements, 20.1.2

using, 20.1.6

viewing data for, 20.1.7

STREAMS_POOL_SIZE initialization parameter, 4.1.1, 7.1.3

striping

manual, 8.2.2

subqueries

NOT IN, 11.3.2

unnesting, 16.5.4.1.3

swapping, 9.4.1.1.1, 9.4.1.2

reducing, 7.1.6.1

switching processes, 9.4.1.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.6.2

T

tables

creating, 4.2

design, 2.5.3

full scans, 10.3.4.2.2

placement on disk, 8.2.2

setting storage options, 4.2

tablespaces, 4.1.4

creating, 4.1.4, 4.1.4.2

temporary, 4.1.4, 4.1.4.2

tablespec

hint syntax, 19.2.3

templates

SQL Access Advisor, 18.2.3.2

temporary tablespaces, 4.1.4

creating, 4.1.4.2

testing designs, 2.6.4

thrashing, 9.4.1.2

thresholds

adaptive, 5.2.3

throughput

optimizer goal, 11.5.2

time model statistics, 5.1.1.2

TIMED_STATISTICS initialization parameter

SQL Trace, 21.4.1

TIMESTAMP column

PLAN_TABLE table, 12.10

TKPROF program, 21.3.2, 21.4.3

editing the output SQL script, 21.4.5.2

example of output, 21.4.4.10

generating the output SQL script, 21.4.5.1

row source operations, 21.4.4.2

syntax, 21.4.3.2

using the EXPLAIN PLAN statement, 21.4.3.2

wait event information, 21.4.4.3

TKPROF_TABLE, 21.4.5.3, 21.4.5.3

TM enqueue contention, 10.3.6.2.3

tools for performance tuning, 1.2

Top Java

Active Session History report, 5.3.9.5

top PL/SQL

Active Session History report, 5.3.9.4

Top Sessions

Active Session History report, 5.3.9.6

Top SQL

Active Session History report, 5.3.9.3

TRACEFILE_IDENTIFIER initialization parameter

identifying trace files, 21.4.1

tracing

consolidating with trcsess, 21.2

identifying files, 21.4.1

transactions and data, 2.4.1.2.4

trcsess utility, 21.2

trickle rollout strategy, 2.7.1

TUNE_MVIEW procedure, 18.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, 14.1.1

memory allocation, 7.1.7

resource contention, 10

shared server, 4.3

sorts, 7.5

SQL Tuning Advisor, 17.2

System Global Area (SGA), 7.1.6.2

TX enqueue contention, 10.3.6.2.4

type conversion, 16.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, 14.1.9

uniqueness, 14.1.9

UNIX system performance, 9.2.1

untransformed column values, 16.5.3.2

upgrade

to the cost-based optimizer, 20.2.2

USE_STORED_OUTLINES parameter, 20.1.6, 20.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, 21.4.1

SQL Trace, 21.4.1

USER_ID column, TKPROF_TABLE, 21.4.5.3

USER_OUTLINE_HINTS view

stored outline hints, 20.1.7

USER_OUTLINES view

stored outlines, 20.1.7

user_stat_extensions, 13.3.1.6.4, 13.3.1.7.2

user-defined bind variables, 11.1.3

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

Using SQL Plan Management, 15

UTLCHN1.SQL script, 10.2.4.3

UTLXPLP.SQL script

displaying plan table output, 12.4

for viewing EXPLAIN PLANs, 11.4.1

UTLXPLS.SQL script

displaying plan table output, 12.4

for viewing EXPLAIN PLANs, 11.4.1

used for displaying EXPLAIN PLANs, 11.4.1

V

V$ACTIVE_SESSION_HISTORY view, 5.1.1.3, 10.1.3.3

V$ADVISOR_PROGRESS view, 17.3.4.5, 17.6

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

V$SESSTAT view, 7.3.5.1.2, 10.1.2.1.3

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

V$SQL_PLAN_STATISTICS view

using to display execution plan statistics, 12.1.3.1

V$SQL_PLAN_STATISTICS_ALL view

using to display execution plan information, 12.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.3.5

statistics, 13.7.1

virtual memory statistics, 5.1.2.2

vmstat UNIX command, 9.4.1.1.1

W

wait events, 5.1.1.1

buffer busy waits, 10.3.1

classes, 5.1.1.1, 10.1.3.2

contention wait events, 10.3.10

direct path, 10.3.5

enqueue, 10.3.6

free buffer waits, 10.3.8

idle wait events, 10.3.9

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.18

rdbms ipc reply, 10.3.17

resource wait events, 10.3.3

Windows performance, 9.2.2

workloads, 2.6.2, 2.6.2.1, 2.6.2.2, 2.6.3, 2.6.4