Index

decision support, 3-4, 9-15

OLTP, 3-2

registering with the database, 11-8

parallel query, 3-5

ARCH process, 19-8

architecture and CPU, 18-13

array interface, 22-16

audit trail, 11-4

Average Elapsed Time data view, 14-11

B*-tree index, 12-16, 12-20

backups

cumulative incremental, 20-50, 20-51, 20-52, 20-53

tuning, 20-61

bandwidth, 9-15

Basic Statistics for Parse/Execute/Fetch drilldown data view, 14-19

BEGIN_DISCRETE_TRANSACTION procedure, 17-2, 17-3

benefits

of tuning, 2-3

BETWEEN, 4-68

binary files

formatting using Oracle Trace, 14-5

bind variables, 19-17

optimization, 4-27

BITMAP CONVERSION row source, 12-20

bitmap indexes, 12-14, 12-19

creating, 12-16

inlist iterator, 5-22

maintenance, 12-15

scans of, 4-24

size, 12-21

storage considerations, 12-15

when to use, 12-13

BITMAP keyword, 12-16

BITMAP_MERGE_AREA_SIZE parameter, 4-30, 12-16, 12-19

bitmaps

mapping to rowids, 12-18

block contention, 2-13

bottlenecks

broadcast

reducing cache misses, 19-31

buffer caches, 2-12

memory allocation, 19-31

partitioning, 19-35

reducing buffers, 19-31

tuning, 19-27

buffer get, 2-10

buffer not pinned statistics, 19-29

buffer pinned statistics, 19-29

buffer pools

default cache, 19-33

keep cache, 19-33

multiple, 19-32, 19-33

RECYCLE cache, 19-33

syntax, 19-36

BUFFER_POOL clause, 19-36

BUFFER_POOL_name parameter, 19-35

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

BYTES column

CACHE hint, 7-30

cache hit ratios

increasing, 19-31

cardinality, 12-21

CARDINALITY column

ALTER SYSTEM statement, 24-7

Cartesian products, 4-48

CATPARR.SQL script, 19-31

CATPERF.SQL file, 19-38

chained rows, 20-31

channel bandwidth, 16-6

CHECKPOINT clause

checkpoints

choosing checkpoint frequency, 20-40

CHOOSE hint, 4-11, 7-8

client/server applications, 3-11, 18-5

CLUSTER hint, 7-11

cluster joins, 4-55

clusters, 12-25

hash

scans of, 4-22, 4-36, 4-38

index

scans of, 4-39

joins and, 4-36, 4-38, 4-55

scans of, 4-22, 4-36

hash, 4-36, 4-38

joins, 4-38

collections, 14-4, 14-23

columns

pseudocolumns

ROWNUM, 4-46, 4-77, 4-88

selectivity, 8-2

complex view merging, 4-78

to index, 12-4

COMPATIBLE parameter

and parallel query, 7-24

composite indexes, 12-5

composite partitions

examples of, 5-16

CONNECT BY clause

consistent gets statistic, 19-28, 21-4, 21-21

connection manager, 22-17

connection pooling, 21-8

consistency

read, 18-10

consistent mode

TKPROF, 6-13

constants

comparisons and, 4-65

evaluation of expressions, 4-65

contention

redo allocation latch, 21-19

redo copy latches, 21-19

rollback segments, 21-3

tuning, 21-1

tuning resource, 2-13

context area, 2-12

context switching, 18-6

COST column

cost-based optimization, 4-12

procedures for plan stability, 10-8

statistics, 4-10, 8-2

upgrading to, 10-9

checking utilization, 18-4

count column

SQL trace, 6-12

CPU

column

SQL trace, 6-12

detecting problems, 18-4

system architecture, 18-13

tuning, 18-1

utilization, 9-15

CPU Statistics data view, 14-12

CPU Statistics for Parse/Execute/Fetch drilldown data view, 14-19

CPU_COUNT initialization parameter, 24-17

CREATE CLUSTER statement, 12-27

CREATE INDEX statement

examples, 20-38

NOSORT clause, 20-38

CREATE OUTLINE statement, 10-5

CREATE TABLE statement

STORAGE clause, 20-23

TABLESPACE clause, 20-23

CREATE TABLESPACE statement, 20-23

CREATE_BITMAP_AREA_SIZE parameter, 12-16, 12-19

CREATE_STORED_OUTLINES parameter, 10-4

cross joins, 4-48

current column

SQL trace, 6-13

current mode

TKPROF, 6-13

CURSOR_NUM column

TKPROF_TABLE table, 6-18

CURSOR_SHARING parameter, 19-18

CURSOR_SPACE_FOR_TIME parameter

setting, 19-19

data

comparative, 11-5

design tuning, 2-8

sources for tuning, 11-2

volume, 11-2

data blocks, 20-10

data cache, 23-2

data dictionary, 2-12, 11-3, 19-22

statistics in, 4-10, 8-13

views used in optimization, 8-13

data views in Oracle Trace, 14-6

data warehousing

dimensions, 4-62

database administrator (DBA), 1-8

database

buffers, 19-31

Database Connection event, 14-5

Database Resource Manager, 18-4, 18-7, 23-4, 23-5

database writer process (DBWn)

tuning, 18-10

databases

distributed

statement optimization on, 4-94

DATAFILE clause, 20-23

datafiles

placement on disk, 20-19

datatypes

user-defined

statistics, 4-33

DATE_OF_INSERT column

TKPROF_TABLE table, 6-18

db block gets statistic, 19-28, 21-4, 21-21

DB_BLOCK_BUFFERS parameter, 19-31, 19-36, 20-45

DB_BLOCK_LRU_LATCHES parameter, 19-36, 19-40

DB_BLOCK_SIZE parameter

tuning backups, 20-63

DB_FILE_DIRECT_IO_COUNT

parameter, 20-63

DB_FILE_MULTIBLOCK_READ_COUNT parameter, 4-26, 4-29, 20-38

cost-based optimization, 4-59

DB_WRITER_PROCESSES initialization parameter, 20-43, 20-44

DBA_INDEXES view, 12-17

DBA_OBJECTS view, 19-34

DBMS_APPLICATION_INFO package, 3-7

DBMS_SHARED_POOL package, 13-4, 19-13, 19-26

DBMS_STATS package, 8-5, 8-6

creating histograms, 8-18

DBMSPOOL.SQL script, 13-4, 19-13

decision support, 3-4

systems (DSS), 1-2

tuning, 9-15

with OLTP, 3-6

decomposition of SQL statements, 9-32

default cache, 19-33

demand rate, 1-5

DEPTH column

TKPROF_TABLE table, 6-18

design dictionary, 11-5

detail report in Oracle Trace, 14-6

details property sheet in Oracle Trace, 14-15

DETERMINISTIC functions, 4-70

deterministic functions, 4-70

device bandwidth, 16-6

evaluating, 20-11

device latency, 16-6

diagnosing tuning problems, 16-1

dictionary-mapped tablespaces, 20-29

dimensions

star joins, 4-62

disabled constraints, 12-11

discrete transactions

disk column

Disk Reads data view, 14-9

Disk Reads/Execution Ratio data view, 14-9

Disk Reads/Logical Reads Ratio data view, 14-10

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

DISKRATIO parameter

to distribute backup I/O, 20-61

disks

contention, 20-18, 20-19

distributing I/O, 20-19

I/O requirements, 20-4

layout options, 20-10

monitoring OS file activity, 20-15

number required, 20-4

placement of datafiles, 20-19

placement of redo logs, 20-19

reducing contention, 20-18

speed characteristics, 20-3

testing performance, 20-5

dispatcher processes (Dnnn), 21-8

DISTINCT operator

optimizing views, 4-78

statement optimization on, 4-94

distributed query, 9-30, 9-40

distributed transactions

distributed statements, 4-48

optimizing, 4-94

sample table scan not supported, 4-21

distributing I/O, 20-19, 20-23

distribution

hints for, 7-26

DISTRIBUTION column

DIUTIL package, 13-4

domain indexes

and EXPLAIN PLAN, 5-22

drilldown data views in Oracle Trace, 14-17

using, 12-24

Basic Statistics for Parse/Execute/Fetch, 14-19

CPU Statistics for Parse/Execute/Fetch, 14-19

Parse Statistics, 14-19

Row Statistics for Execute/Fetch, 14-20

duration events in Oracle Trace, 14-5

dynamic extension, 20-27

avoiding, 20-29

dynamic performance views

enabling statistics, 6-4

for tuning, 15-1

elapsed column

SQL trace, 6-12

enabled constraints, 12-11

enforced constraints, 12-11

errors

during discrete transactions, 17-3

events in Oracle Trace, 14-5

examples

ALTER SESSION statement, 6-5

CREATE INDEX statement, 20-38

DATAFILE clause, 20-23

discrete transactions, 17-4

execution plan, 9-3

EXPLAIN PLAN output, 6-15, 9-3

full table scan, 9-3

indexed query, 9-4

NOSORT clause, 20-38

SET TRANSACTION statement, 20-30

SQL trace facility output, 6-15

STORAGE clause, 20-23

table striping, 20-23

TABLESPACE clause, 20-23

executable code as data source, 11-4

Execute Elapsed Time data view, 14-11

execution plan

accessing views, 4-80, 4-82, 4-84

complex statements, 4-75

compound queries, 4-91, 4-92, 4-93

examples, 4-75, 6-7, 9-3

execution sequence of, 4-7

overview of, 4-5

plan stability, 10-2

preserving with plan stability, 10-2

TKPROF, 6-7, 6-10

viewing, 4-3

expectations for tuning, 1-9

EXPLAIN PLAN statement

Export utility

copying statistics, 8-2

user-defined selectivity, 4-33

extents

unlimited, 20-28

fact tables

star joins, 4-62

fast full index scans, 4-23, 12-8

FAST_START_IO_TARGET initialization parameter

controlling checkpoints with, 20-40

recovery time and the, 24-5

FAST_START_PARALLEL_ROLLBACK initialization parameter, 24-17

fast-start checkpoints

controlling checkpoints, 20-40

FAST_START_IO_TARGET initialization parameter, 24-5

LOG_CHECKPOINT_INTERVAL initialization parameter, 24-5

LOG_CHECKPOINT_TIMEOUT initialization parameter, 24-4

fast-start on-demand rollback, 24-16

fast-start parallel rollback, 24-16

Fetch Elapsed Time data view, 14-11

file storage

designing, 20-5

FILESPERSET parameter

tuning backups, 20-63

FIRST_ROWS hint, 4-11, 7-7

FORMAT statement

in Oracle Trace, 14-21

formatter tables

in Oracle Trace, 14-5

free lists

adding, 21-21

contention, 21-20

reducing contention, 21-21

FULL hint, 7-10, 12-7

full index scans, 4-23

full partition-wise joins, 5-20

full table scans, 4-21, 4-46, 9-3

multiblock reads, 4-26

rule-based optimizer, 4-46

selectivity and, 4-25

function-based indexes, 12-12

functions

PL/SQL

DETERMINISTIC, 4-70

deterministic, 4-70

SQL

optimizing view queries, 4-84

user-defined

GATHER_ INDEX_STATS procedure

GATHER_DATABASE_STATS procedure

GATHER_SCHEMA_STATS procedure

GATHER_TABLE_STATS procedure

in V$ROWCACHE table, 19-22

GETMISSES column

GETS column

in V$ROWCACHE table, 19-22

global hints, 7-37

goals for tuning, 1-9, 2-14

GROUP BY clause

NOSORT clause, 20-39

optimizing views, 4-78

hash

scans of, 4-22, 4-36, 4-38

hash areas, 2-12

hash clusters

HASH hint, 7-11

hash join, 4-53

HASH_AREA_SIZE parameter, 4-54

HASH_MULTIBLOCK_IO_COUNT parameter, 4-54

index join, 4-24

hash partitions, 5-14

examples of, 5-14

HASH_AJ hint, 4-61, 7-22, 7-23

HASH_AREA_SIZE parameter, 4-29, 4-54

HASH_JOIN_ENABLED parameter, 4-29, 4-53

HASH_MULTIBLOCK_IO_COUNT parameter, 4-30, 4-54

HASH_SJ hint, 4-62, 7-23

hashing, 12-26

HASHKEYS parameter

CREATE CLUSTER statement, 12-27

HIGH_VALUE statistics, 4-26

hints, 7-2

access methods, 7-9

ALL_ROWS hint, 7-6

AND_EQUAL hint, 7-16, 12-7

as used in outlines, 10-3

CACHE hint, 7-30

cannot override sample access path, 4-25

CHOOSE hint, 7-8

CLUSTER hint, 7-11

degree of parallelism, 7-24

extensible optimization, 4-33

FIRST_ROWS hint, 7-7

FULL hint, 7-10, 12-7

INDEX hint, 7-12, 7-19, 12-7

INDEX_ASC hint, 7-13

INDEX_DESC hint, 7-14, 7-15

INDEX_FFS, 4-23

INDEX_FFS hint, 7-15

INDEX_JOIN, 4-24

join operations, 7-19

LEADING hint, 7-22

MERGE_AJ and HASH_AJ, 4-61

MERGE_AJ hint, 7-22

MERGE_SJ and HASH_SJ, 4-62

NO_PUSH_PRED hint, 7-34

NO_UNNEST hint, 7-33

NOCACHE hint, 7-31

NOPARALLEL hint, 7-26

NOREWRITE hint, 7-18

optimization approach and goal, 7-6

ORDERED, 4-59

ORDERED hint, 7-18

overriding optimizer choice, 4-25

overriding OPTIMIZER_MODE and OPTIMIZER_GOAL, 4-11

PARALLEL hint, 7-25

parallel query option, 7-24

PQ_DISTRIBUTE hint, 7-26

USE_CONCAT hint, 7-16

number of buckets, 8-19

HOLD_CURSOR clause, 19-10

ID column

INDEX hint, 7-12, 12-7, 12-17

Import utility

copying statistics, 8-2

IN operator, 4-66

merging views, 4-79

IN subquery, 4-78

index joins, 4-24

INDEX_ASC hint, 7-13

INDEX_COMBINE hint, 12-7, 12-17

INDEX_DESC hint, 7-14, 7-15

INDEX_FFS hint, 4-23, 7-15, 12-9

INDEX_JOIN hint, 4-24

indexes

avoiding the use of, 12-7

bitmap, 12-13, 12-14, 12-16, 12-19

choosing columns for, 12-4

cluster

domain indexes

enforcing uniqueness, 12-10

ensuring the use of, 12-6

example, 9-4

fast full scan, 12-8

fast full scans of, 4-23

function-based, 12-12

index joins, 4-24

modifying values of, 12-5

non-unique, 12-10

optimization and, 4-71

placement on disk, 20-21

unbounded range, 4-42

selectivity of, 12-4

statement conversion and, 4-71

statistics, gathering, 8-6

unique scans, 4-22

when to create, 12-2

initialization parameters

IN-lists, 7-13, 7-17

INSERT statement

append, 7-28

internal write batch size, 20-45

INTERSECT operator

compound queries, 4-48

example, 4-93

intra transaction recovery, 24-17

I/O

analyzing needs, 20-2, 20-3

balancing, 20-22

distributing, 20-19, 20-23

insufficient, 16-6

multiple buffer pools, 19-33

parallel execution, 9-15

Statistics for Parse/Execute/Fetch view, 14-19

testing disk performance, 20-5

tuning, 2-12, 20-2

isolation level

of transactions, 17-6

joins

keep cache, 19-33

keys

searches, 4-36

large pool, 20-64

LARGE_POOL_SIZE parameter, 20-64

latches

contention, 2-13, 18-12

redo allocation latch, 21-16

redo copy latches, 21-16

LEADING hint, 7-22

least recently used list (LRU), 18-10

library cache, 2-12

memory allocation, 19-16

tuning, 19-14

LIKE, 4-66

load balancing, 20-22

lock contention, 2-13

log, 21-16

log buffer tuning, 2-12, 19-7

log writer process (LGWR) tuning, 20-19, 20-41

LOG_BUFFER parameter, 19-6, 20-41

setting, 19-8

LOG_CHECKPOINT_INTERVAL initialization parameter, 20-40

recovery time, 24-5

LOG_CHECKPOINT_TIMEOUT initialization parameter, 20-40

recovery time, 24-4

LOG_SIMULTANEOUS_COPIES parameter, 21-19

LOG_SMALL_ENTRY_MAX_SIZE parameter, 21-19

Logical Reads data view, 14-9

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

logical structure of database, 2-9

long waits

definition of, 20-57

lookup tables

LOW_VALUE statistics, 4-26

LRU

aging policy, 19-33

latch, 19-35, 19-36, 19-41

latch contention, 19-41, 21-19

Management Information Base (MIB), 11-5

massively parallel system, 9-15

max session memory statistic, 19-24

MAX_DUMP_FILE_SIZE

SQL Trace parameter, 6-4

MAX_DUMP_FILE_SIZE initialization parameter, 6-4

MAXOPENCURSORS clause, 19-10

MAXOPENFILES parameter

tuning backups, 20-63

memory

insufficient, 16-5

reducing usage, 19-42

memory allocation

shared SQL areas, 19-16

MERGE_AJ hint, 4-61, 7-22, 7-23

MERGE_SJ hint, 4-62, 7-23

merging complex views, 4-78

merging views into statements, 4-76

method

MIB, 11-5

MINUS operator

compound queries, 4-48

MTS_DISPATCHERS parameter, 21-8

mirroring

redo logs, 20-20

monitoring, 11-5

MTS_MAX_DISPATCHERS parameter, 21-8

MTS_MAX_SERVERS parameter, 21-11

multiblock reads, 20-28

multiple buffer pools, 19-32, 19-33, 19-36

multi-purpose applications, 3-6

multi-threaded server

context area size, 2-12

performance issues, 21-5

reducing contention, 21-5

tuning, 21-5

tuning memory, 19-22

multi-tier systems, 3-9, 18-15

NAMESPACE column

V$LIBRARYCACHE table, 19-14

nested loops joins, 4-50

cost-based optimization, 4-59

Net8 Assistant, 22-17

network

array interface, 22-16

bandwidth, 16-6

constraints, 16-6

detecting performance problems, 22-9

prestarting processes, 22-6

problem solving, 22-11

Session Data Unit, 22-16

tuning, 22-1

NLS_SORT parameter

ORDER BY access path, 4-45

NO_EXPAND hint, 7-17

NO_INDEX hint, 7-15, 12-7

NO_MERGE hint, 7-32

NO_PUSH_PRED hint, 7-34

non-equijoins

NOPARALLEL hint, 7-26

NOPARALLEL_INDEX hint, 7-30

NOREWRITE hint, 7-18

NOSORT clause, 20-38, 20-39

NOT, 4-68

NOT IN subquery, 4-61

NT performance, 23-6

nulls

converting to values

optimization, 4-86

non-null values for, 4-86

NUM_DISTINCT column

USER_TAB_COLUMNS view, 4-26

NUM_ROWS column

USER_TABLES view, 4-26

Number of Rows Processed data view, 14-12

OBJECT_INSTANCE column

OBJECT_NAME column

OBJECT_NODE column

OBJECT_OWNER column

OBJECT_TYPE column

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

with decision support, 3-6

OPEN_CURSORS parameter

allocating more private SQL areas, 19-10

increasing cursors per session, 19-16

operating system

data cache, 23-2

monitoring disk I/O, 20-15

monitoring tools, 11-3

tuning, 2-13, 16-7, 19-4

OPERATION column

PLAN_TABLE table, 5-4, 5-9

OPTIMAL storage parameter, 20-30

optimization

choosing the approach, 4-10

conversion of expressions and predicates, 4-65

cost-based, 4-12, 4-59

choosing an access path, 4-25

examples of, 4-26

remote databases and, 4-94

distributed SQL statements, 4-94

described, 4-4

DISTINCT, 4-78

extensible optimizer, 4-32

GROUP BY views, 4-78

hints, 4-11, 4-23, 4-24

manual, 4-11

merging complex views, 4-78

merging views into statements, 4-76

non-null values for nulls, 4-86

operations performed, 4-48

rule-based, 4-34, 4-60

selectivity of queries and, 4-25

select-project-join views, 4-76

semi-joins, 4-61

statistics, 4-10, 8-2

types of SQL statements, 4-47

transitivity and, 4-69

without merging, 4-88

optimizer, 4-4

plan stability, 10-2

OPTIMIZER column

PLAN_TABLE, 5-5

OPTIMIZER_FEATURES_ENABLE parameter, 4-23, 4-24, 4-78, 4-87

OPTIMIZER_FEATURES_ENABLED parameter, 4-29

OPTIMIZER_GOAL clause, 4-11

OPTIMIZER_INDEX_CACHING, 4-31

OPTIMIZER_INDEX_COST_ADJ parameter, 4-30

OPTIMIZER_MODE, 4-10, 4-20

hints affecting, 4-11

OPTIMIZER_MODE initialization parameter, 4-12, 4-29, 4-34, 7-6

OPTIMIZER_PERCENT_PARALLEL initialization parameter, 4-9

OPTIMIZER_PERCENT_PARALLEL parameter, 4-9, 4-29

OPTIONS column

Oracle Enterprise Manager, 11-8

Oracle Expert, 2-1, 11-13

Oracle Forms, 6-5

control of parsing and private SQL areas, 19-11

synchronization points, 2-8

CPU, 18-17

tuning, 11-14

Oracle Parallel Server Management, 11-14

Oracle Performance Manager, 11-11

Oracle Server

client/server configuration, 3-11

configurations, 3-7

events, 14-5

Oracle Trace, 14-1, 19-40

accessing collected data, 14-5

binary files, 14-5

collection results, 14-27

collections, 14-4, 14-23

command-line interface, 14-20

data views, 14-6

deleting files, 14-22

details property sheet, 14-15

drilldown data views, 14-17, 14-19

Basic Statistics for Parse/Execute/Fetch view, 14-19

CPU Statistics for Parse/Execute/Fetch view, 14-19

Parse Statistics view, 14-19

Row Statistics for Execute/Fetch view, 14-20

duration events, 14-5

events, 14-5

FORMAT statement, 14-21

formatter tables, 14-5

formatting data, 14-27

Oracle Trace Data Viewer, 14-6

parameters, 14-22

point events, 14-5

predefined data views, 14-6

reporting utility, 14-6, 14-28

SQL statement property sheet, 14-15

START statement, 14-21

STOP statement, 14-21

stored procedures, 14-25

using to collect workload data, 14-3

viewing data, 14-13

Oracle Trace Data Viewer, 14-6

Oracle Trace Manager, 14-4, 14-23

used for formatting collections, 14-5

ORACLE_TRACE_COLLECTION_NAME parameter, 14-22, 14-23

ORACLE_TRACE_COLLECTION_PATH parameter, 14-22

ORACLE_TRACE_COLLECTION_SIZE parameter, 14-22

ORACLE_TRACE_ENABLE parameter, 14-22, 14-23

ORACLE_TRACE_FACILITY_NAME parameter, 14-22, 14-23

ORACLE_TRACE_FACILITY_PATH parameter, 14-22

ORDERED hint, 4-59, 7-18

ORDERED_PREDICATES hint, 7-35

OTHER column

OTHER_TAG column

non-null values for nulls, 4-86

outer joins

defined, 4-47

outlines

CREATE OUTLINE statement, 10-5

creating and using, 10-4

execution plans and plan stability, 10-2

hints, 10-3

matching with SQL statements, 10-3

moving tables, 10-7

storage requirements, 10-4

using, 10-5

using to move to the cost-based optimizer, 10-8

viewing data for, 10-6

overloaded disks, 20-19

packages

DBMS_APPLICATION_INFO package, 3-7

DBMS_SHARED_POOL package, 13-4

DBMS_TRANSACTION package, 17-4

DIUTIL package, 13-4

registering with the database, 11-8

STANDARD package, 13-4

PARALLEL clause

RECOVER statement, 24-15

parallel execution, 3-5

hints, 7-25

query servers, 21-15

tuning query servers, 21-15

PARALLEL hint, 7-25

parallel joins

and PQ_DISTRIBUTE hint, 7-26

parallel recovery, 24-15

PARALLEL_MAX_SERVERS initialization parameter, 24-15

PARALLEL_MAX_SERVERS parameter, 24-15

parameter files, 11-4

PARENT_ID column

Parse Elapsed Time data view, 14-11

Parse Statistics drilldown data view, 14-19

Parse/Execution Ratio data view, 14-10

parsing

Oracle Forms, 19-11

Oracle precompilers, 19-10

reducing unnecessary calls, 19-10

partition views, 9-35

PARTITION_ID column

PARTITION_START column

PARTITION_STOP column

PARTITION_VIEW_ENABLED parameter, 9-36

partitioned objects

and EXPLAIN PLAN statement, 5-14

partitioning

examples of composite, 5-16

examples of, 5-14

hash, 5-14

range, 5-14

start and stop columns, 5-15

partitions

elimination, 9-35

statistics, 8-4

partition-wise joins

full, 5-20

full, and EXPLAIN PLAN output, 5-20

partial, and EXPLAIN PLAN output, 5-19

PCTFREE parameter, 2-12, 20-31

PCTINCREASE parameter, 20-37

and SQL.BSQ file, 20-34

PCTUSED parameter, 2-12, 20-32

performance

client/server applications, 3-11

decision support applications, 3-4

different types of applications, 3-2

monitoring registered applications, 11-8

evaluating, 1-10

key factors, 16-3

mainframe, 23-6

NT, 23-6

OLTP applications, 3-2

viewing execution plans, 4-3

UNIX-based systems, 23-6

Performance Manager, 11-11

Performance Monitor

NT, 18-5

PHYRDS column

V$FILESTAT table, 20-17

physical reads statistic, 19-28

PHYWRTS column

V$FILESTAT table, 20-17

ping UNIX command, 11-3

pinging, 2-13

PINS column

V$LIBRARYCACHE table, 19-15

plan

accessing views, 4-80, 4-82, 4-84

complex statements, 4-75

compound queries, 4-91, 4-92, 4-93

preserving execution plans, 10-2

procedures for the cost-based optimizer, 10-8

use of hints, 10-2

PLAN_TABLE table

PL/SQL

deterministic functions, 4-70

package, 11-7

tuning PL/SQL areas, 19-8

point events in Oracle Trace, 14-5

POOL attribute, 21-8

POSITION column

PRE_PAGE_SGA parameter, 19-5

PQ_DISTRIBUTE hint, 7-26

precompilers

control of parsing and private SQL areas, 19-10

predicates

optimizing view queries, 4-76

pushing into a view, 4-79, 4-84

PRIMARY KEY constraint, 12-10

primary keys

optimization, 4-75

searches, 4-37

private SQL areas, 19-10

PRIVATE_SGA variable, 19-24

proactive tuning, 2-2

procedures

deterministic functions, 4-70

process

dispatcher process configuration, 21-8

processing, distributed, 3-11

pseudocolumns

ROWNUM

cannot use indexes, 4-46

optimizing view queries, 4-77, 4-88

PUSH_JOIN_PRED hint, 4-87

PUSH_PRED hint, 7-34

queries

avoiding the use of indexes, 12-7

compound

defined, 4-48

optimization of, 4-91

ORs converted to, 4-71

defined, 4-47

distributed, 9-30, 9-40

ensuring the use of indexes, 12-6

optimizing IN subquery, 4-78

optimizing view queries, 4-76

SAMPLE clause

cost-based optimization, 4-19

query column

query server process

range

read/write operations, 20-5

range partitions, 5-14

examples of, 5-14

raw device, 23-2

reactive tuning, 2-3

read consistency, 18-10

REBUILD statement, 12-9

record keeping, 2-15

RECOVER statement

PARALLEL clause, 24-15

recovery

parallel

intra transaction recovery, 24-17

parallel processes for, 24-15

PARALLEL_MAX_SERVERS initialization parameter, 24-15

setting number of processes to use, 24-15

RECOVERY_PARALLELISM initialization parameter, 24-15

recursive calls, 6-13, 20-27

recursive SQL, 13-1

RECYCLE cache, 19-33

redo allocation latch, 21-16, 21-19

REDO BUFFER ALLOCATION RETRIES statistic, 19-7

redo copy latches, 21-16, 21-19

choosing how many, 21-16

redo logs

buffer tuning, 19-6

mirroring, 20-20

placement on disk, 20-19

reducing

buffer cache misses, 19-31

contention

dispatchers, 21-6

OS processes, 23-3

query servers, 21-15

redo log buffer latches, 21-16

shared servers, 21-9

data dictionary cache misses, 19-22

paging and swapping, 19-4

rollback segment contention, 21-4

unnecessary parse calls, 19-10

registering applications with database, 11-8

RELEASE_CURSOR clause, 19-10

RELOADS column

V$LIBRARYCACHE table, 19-15

REMARKS column

remote SQL statement, 9-30

Re-Parse Frequency data view, 14-10

resource

adding, 1-4

tuning contention, 2-13

response time, 1-2, 1-3, 4-8

cost-based approach, 4-10

optimizing, 4-9, 7-7

REWRITE hint, 7-17

RMAN

tuning for backups, 20-61

roles in tuning, 1-7

rollback segments, 18-10

assigning to transactions, 20-30

choosing how many, 21-4

contention, 21-3

creating, 21-4

detecting dynamic extension, 20-27

dynamic extension, 20-30

rollbacks

fast-start on-demand, 24-16

fast-start parallel, 24-16

round-robin

Row Statistics for Execute/Fetch drilldown data views, 14-20

row sampling, 8-3

row sources, 4-6

ROWID hint, 7-11

rowids

mapping to bitmaps, 12-18

table access by, 4-21

ROWNUM pseudocolumn

cannot use indexes, 4-46

optimizing view queries, 4-77, 4-88

rows

row sources, 4-6

rowids used to locate, 4-21, 4-35

rows column, SQL trace, 6-13

Rows Fetched/Fetch Count Ratio data view, 14-12

Rows Sorted data view, 14-12

RowSource event, 14-5

RULE hint, 7-8

OPTIMIZER_MODE and, 4-11

rule-based optimization, 4-34

SAMPLE BLOCK clause, 4-21

access path, 4-21

hints cannot override, 4-25

SAMPLE clause, 4-21

access path, 4-21

hints cannot override, 4-25

cost-based optimization, 4-19

sample table scans, 4-21

hints cannot override, 4-25

sar UNIX command, 18-5

scalability, 18-11

scans, 4-21

schemas

star schemas, 4-62

SEARCH_COLUMNS column

cost-based optimization, 4-19

segments, 20-26

SELECT statement

SAMPLE clause, 4-21

access path, 4-21, 4-25

user-defined selectivity, 4-33

selectivity of queries, 4-25

selectivity, index, 12-4

select-project-join views, 4-76

semi-joins, 4-61

sequence cache, 2-12

sequential reads, 20-5

sequential writes, 20-5

serializable transactions, 17-6

service time, 1-2, 1-3

Session Data Unit (SDU), 22-16

session memory statistic, 19-24

SESSION_CACHED_CURSORS parameter, 19-20

SET TRANSACTION statement, 20-30

keeping objects pinned in, 13-4

tuning, 19-13, 19-25

shared SQL areas

keeping in the shared pool, 13-4

memory allocation, 19-16

similar SQL statements, 13-2

statements considered, 13-1

SHARED_POOL_RESERVED_SIZE parameter, 19-27

SHARED_POOL_SIZE parameter, 19-22, 19-27

allocating library cache, 19-16

tuning the shared pool, 19-24

short waits

definition of, 20-57

SHOW SGA statement, 19-5

Simple Network Management Protocol (SNMP), 11-5

single tier, 18-14

SNMP, 11-5

SOME, 4-67

sort areas

memory allocation, 20-35

process local area, 2-12

SORT_AREA_RETAINED_SIZE parameter, 19-42, 20-37

SORT_AREA_SIZE parameter, 4-29, 12-16, 19-41

cost-based optimization and, 4-59

tuning sorts, 20-36

SORT_MULTIBLOCK_READ_COUNT parameter, 20-38

sort-merge joins, 4-51

access path, 4-43

cost-based optimization, 4-59

example, 4-43

sorts

(disk) statistic, 20-35

(memory) statistic, 20-35

avoiding on index creation, 20-38

tuning, 20-35

Sorts in Memory data view, 14-12

Sorts on Disk data view, 14-12

source data for tuning, 11-2

spin count, 18-12

SPINCOUNT parameter, 21-2

SQL

functions

optimizing view queries, 4-84

types of statements in

optimizing, 4-47

SQL area tuning, 19-8

SQL Parse event, 14-5

SQL statement property sheet in Oracle Trace, 14-15

SQL statements

SQL trace facility, 6-2, 6-6, 11-6, 19-9, 19-40

example of output, 6-15

output, 6-12

parse calls, 19-9

statement truncation, 6-14

steps to follow, 6-3

trace files, 6-4, 11-3

SQL*Plus script, 11-7

SQL_STATEMENT column

TKPROF_TABLE, 6-18

SQL_TRACE parameter, 6-5

SQL.BSQ file, 20-33

STANDARD package, 13-4

STAR hint, 7-19

star joins, 4-62

star query, 4-62

star transformation, 7-34

STAR_TRANSFORMATION hint, 7-34

STAR_TRANSFORMATION_ENABLED parameter, 7-35

start columns

in partitioning and EXPLAIN PLAN statement, 5-15

START statement in Oracle Trace, 14-21

STATEMENT_ID column

consistent gets, 19-28, 21-4, 21-21

statistics, 15-2

current value, 15-4

db block gets, 19-28, 21-4

estimated

block sampling, 8-3

row sampling, 8-3

exporting and importing, 8-2

from B*-tree or bitmap index, 8-6

from ANALYZE, 8-4

gathering with DBMS_STATS package, 8-6

generating, 8-3

generating and managing with DBMS_STATS, 8-5

generating for cost-based optimization, 8-3

HIGH_VALUE and LOW_VALUE, 4-26

max session memory, 19-24

optimizer goal, 4-11

optimizer mode, 4-10

optimizer use of, 4-10, 4-12, 8-2

partitions and subpartitions, 8-4

physical reads, 19-28

query servers, 21-15

rate of change, 15-4

shared server processes, 19-7, 21-9

session memory, 19-24

sorts (disk), 20-35

sorts (memory), 20-35

undo block, 21-3

STATSPACK package, 11-7, 18-2

stop columns

in partitioning and EXPLAIN PLAN statement, 5-15

STOP statement in Oracle Trace, 14-21

storage

file, 20-5

STORAGE clause

CREATE TABLE statement, 20-23

examples, 20-23

modifying parameters, 20-33

modifying SQL.BSQ file, 20-33

OPTIMAL parameter, 20-30

stored outlines

creating and using, 10-4

execution plans and plan stability, 10-2

hints, 10-3

matching with SQL statements, 10-3

moving tables, 10-7

storage requirements, 10-4

using, 10-5

viewing data for, 10-6

stored procedures

in Oracle Trace, 14-25

registering with the database, 11-8

striping, 20-21, 20-22

examples, 20-23

manual, 20-23

subpartitions

statistics, 8-4

subqueries

converting to joins, 4-74

NOT IN, 4-61

optimizing IN subquery, 4-78

subquery unnesting, 9-10

switching processes, 18-6

symmetric multiprocessor, 9-15

System Global Area tuning, 19-4

system-specific Oracle documentation

software constraints, 16-7

tables

dimensions

fact tables

formatter in Oracle Trace, 14-5

lookup tables, 4-62

placement on disk, 20-21

striping examples, 20-23

TABLESPACE clause, 20-23

CREATE TABLE statement, 20-23

tablespaces

dictionary-mapped, 20-29

temporary, 20-37

TCP.NODELAY parameter, 22-17

TEMPORARY keyword, 20-37

temporary LOBs, 19-31

temporary tablespaces

optimizing sort, 20-37

cost-based approach, 4-10

optimizing, 4-9, 7-6

tiers, 18-14

TIMED_STATISTICS initialization parameter, 6-4, 23-2

SQL Trace, 6-4

TIMESTAMP column

TKPROF program, 6-3, 6-6, 19-40

editing the output SQL script, 6-17

example of output, 6-15

generating the output SQL script, 6-16

introduction, 11-7

syntax, 6-8

using the EXPLAIN PLAN statement, 6-10

TKPROF_TABLE, 6-18

querying, 6-17

Total Elapsed Time data view, 14-11

Trace, Oracle, 14-1

transaction processing monitor, 18-15, 18-17

transactions

assigning rollback segments, 20-30

discrete, 17-2

serializable, 17-6

transmission time, 16-7

Transparent Gateway, 9-40

triggers

in tuning OLTP applications, 9-16

tuning

access path, 2-11

and design, 2-10

application design, 2-9

business rule, 2-7

client/server applications, 3-11

contention, 21-1

CPU, 18-1

data design, 2-8

data sources, 11-2

database logical structure, 2-9

decision support systems, 3-4

diagnosing problems, 16-1

memory allocation, 2-11, 19-2, 19-42

logical structure, 12-3

method, 2-1

monitoring registered applications, 11-8

multi-threaded server, 21-5

OLTP applications, 3-2

operating system, 2-13, 16-7, 19-4

SQL and PL/SQL areas, 19-8

parallel execution, 3-5

personnel, 1-7

proactive, 2-2

production systems, 2-4

SQL, 2-10

System Global Area (SGA), 19-4

two-tier, 18-14

undo block statistic, 21-3

UNION ALL operator

examples, 4-72, 4-74, 4-91

transforming OR into, 4-71

UNION ALL view, 9-36

UNION operator

compound queries, 4-48

examples, 4-79, 4-92

UNIX system performance, 23-6

UNIQUE constraint, 12-10

unique keys

unlimited extents, 20-28

UNNEST hint, 7-32

UNNEST_SUBQUERY parameter, 7-32, 9-10

upgrade

to the cost-based optimizer, 10-9

USE_CONCAT hint, 7-16

USE_MERGE hint, 7-21

USE_NL hint, 7-20

USE_STORED_OUTLINES parameter, 10-5

USER_DUMP_DEST initialization parameter, 6-4

USER_DUMP_DEST parameter

SQL Trace parameter, 6-4

USER_ID column

TKPROF_TABLE, 6-18

USER_INDEXES view, 12-17

USER_OULTINE_HINTS view

stored outline hints, 10-6

USER_OUTLINES view

stored outlines, 10-6

USER_TAB_COL_STATISTICS view, 4-26

USER_TAB_COLUMNS view, 4-26

USER_TABLES view, 4-26