Index

registering with the database, 4-7, 26-2

OLTP, 5-2

parallel query, 5-5

parallel server, 5-9

ARCH process, 18-13

ARCH process, multiple, 19-43

architecture and CPU, 13-10

array interface, 16-3

asynchronous I/O, 19-21

asynchronous operation, 19-21

asynchronous readahead, 19-32

audit trail, 4-4

B

B*-tree index, 10-15, 10-19

backup

data warehouse, 6-8

disk mirroring, 19-30

BACKUP_DISK_IO_SLAVES parameter, 19-21

BACKUP_TAPE_IO_SLAVES parameter, 19-21

bandwidth, 19-2

BEGIN_DISCRETE_TRANSACTION procedure, 11-2, 11-4

benefit of tuning, 2-3

bind variables, 14-16

BITMAP CONVERSION row source, 10-19

bitmap index, 6-7, 10-13, 10-18

creating, 10-16

inlist iterator, 23-12

maintenance, 10-15

size, 10-20

storage considerations, 10-14

when to use, 10-13

BITMAP keyword, 10-16

BITMAP_MERGE_AREA_SIZE parameter, 8-8, 10-15, 10-18

block contention, 2-12

bottlenecks

adding buffers, 14-32

memory allocation, 14-29

partitioning, 14-39

performance statistics, 14-29

reducing buffers, 14-32

reducing cache misses, 14-29

buffer pool

multiple, 14-37, 14-38

recycle cache, 14-37

syntax, 14-40

BUFFER_POOL clause, 14-40

BUFFER_POOL_name parameter, 14-39

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

C

CACHE hint, 8-32

cardinality, 10-20

CATPARR.SQL script, 14-29

CATPERF.SQL file, 14-42

chained rows, 15-32

channel bandwidth, 3-6

checkpoints

choosing checkpoint frequency, 15-42

current write batch size, 15-44

performance, 15-41

redo log maintenance, 15-42

tuning, 15-41

CHOOSE hint, 8-16

CKPT process, 15-43

client/server applications, 5-9, 13-5

CLUSTER hint, 8-18

clusters, 10-24

columns, to index, 10-5

COMPATIBLE parameter, 10-16, 19-36

and parallel query, 19-18

COMPLEX_VIEW_MERGING parameter, 8-7, 8-9, 8-33

composite indexes, 10-6

COMPUTE option, 19-45

CONNECT BY, 23-14

Connection Manager, 16-4

connection pooling, 18-9

consistency, read, 13-8

consistent gets statistic, 14-26, 14-30, 14-34, 18-5, 18-18

consistent mode, TKPROF, 24-14

contention

redo allocation latch, 18-16

redo copy latches, 18-16

rollback segments, 18-4

tuning, 18-1

tuning resource, 2-12

context area, 2-11

context switching, 13-5

cost-based optimization, 6-7, 8-2, 20-24

parallel query, 20-24

COUNT column, 14-29, 14-33

count column, SQL trace, 24-14

CPU

checking utilization, 13-4

detecting problems, 13-4

insufficient, 3-5

system architecture, 13-10

tuning, 13-1

utilization, 13-2, 19-2

CPU bound operations, 19-32

cpu column, SQL trace, 24-14

CREATE CLUSTER command, 10-26

CREATE INDEX command, 20-21

examples, 15-39

NOSORT option, 15-39

CREATE TABLE AS SELECT, 6-3, 20-19, 21-5

CREATE TABLE command

STORAGE clause, 15-24

TABLESPACE clause, 15-24

CREATE TABLESPACE command, 15-24

CREATE TABLESPACE statement, 15-24

CREATE_BITMAP_AREA_SIZE parameter, 10-15, 10-18

current column, SQL trace, 24-14

current mode, TKPROF, 24-14

CURSOR_NUM column

data dictionary cache, 2-11, 14-20

CURSOR_SPACE_FOR_TIME parameter

setting, 14-17

D

data

comparative, 4-5

sources for tuning, 4-2

volume, 4-2

data block size, 15-15

data cache, 17-2

data design

tuning, 2-8

data dictionary, 4-3

data warehouse

backup, 6-8

Oracle Parallel Server, 6-5

parallel aware optimizer, 6-6

parallel index creation, 6-3

parallel load, 6-4

partition, 6-4

partitioned table, 19-31

recovery, 6-8

star schema, 6-7

database administrator (DBA), 1-8

database buffers, 14-32

database layout, 19-22

database writer process (DBWn)

behavior on checkpoints, 15-41

tuning, 13-8, 19-44

DATAFILE clause, 15-24

datafile placement on disk, 15-21

DATE_OF_INSERT column

db block gets statistic, 14-26, 14-30, 18-5, 18-18

DB BLOCK GETS, 14-34

DB_BLOCK_BUFFERS parameter, 14-29, 14-32, 14-40, 15-44

DB_BLOCK_CHECKPOINT_BATCH parameter, 15-44

DB_BLOCK_LRU_EXTENDED_STATISTICS parameter, 14-30

DB_BLOCK_LRU_LATCHES parameter, 14-40, 14-45

DB_BLOCK_LRU_STATISTICS parameter, 14-33

DB_BLOCK_SIZE parameter

and parallel query, 19-19

DB_FILE_MULTIBLOCK_READ_COUNT parameter, 8-7, 15-38, 19-19

DBA locking, 20-13

DBA_DATA_FILES view, 21-10

DBA_EXTENTS view, 21-10

DBA_HISTOGRAMS, 8-4

DBA_INDEXES view, 10-16

DBA_OBJECTS view, 14-38

DBA_TAB_COLUMNS, 8-4

DBMS_APPLICATION_INFO package, 26-2, 26-4

DBMS_SHARED_POOL package, 12-4, 14-12, 14-23

DBMS_SYSTEM package, 24-6

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure, 24-6

DBMS_UTILITY.ANALYZE_PART_OBJECT, 19-45

DBMSPOOL.SQL script, 12-4, 14-12

DBMSUTL.SQL, 26-2

DBWR_IO_SLAVES parameter, 19-21

decision support, 5-4

processes, 20-3

query characteristics, 19-4

systems (DSS), 1-2

tuning, 19-2

with OLTP, 5-6

decomposition of SQL statements, 9-4

default cache, 14-37

demand rate, 1-5, 1-6

DEPTH column

diagnosing tuning problems, 3-1

design dictionary, 4-4

designing and tuning, 2-10

device bandwidth, 3-6

evaluating, 15-16

device latency, 3-6

dimension table, 6-7

direct-load insert, 19-44, 20-29

external fragmentation, 20-13

disabled constraint, 10-11

discrete transactions

example, 11-4

processing, 11-3, 11-4

when to use, 11-2

disk affinity

and parallel query, 20-15

disabling with MPP, 19-26

with MPP, 19-41

disk column, SQL trace, 24-14

DISK_ASYNCH_IO parameter, 19-21

disks

contention, 15-21

distributing I/O, 15-21

I/O requirements, 15-4

layout options, 15-15

monitoring OS file activity, 15-17

number required, 15-4

placement of datafiles, 15-21

placement of redo log files, 15-21

reducing contention, 15-21

speed characteristics, 15-4

testing performance, 15-6

dispatcher processes (Dnnn), 18-8

distributed query, 9-1, 9-12

distributing I/O, 15-21, 15-24

DIUTIL package, 12-5

DML_LOCKS parameter, 19-14, 19-15

DSS memory, 19-3

dynamic extension, 15-27

dynamic performance views

enabling statistics, 24-4

for tuning, 22-1

parallel operations, 21-10

E

elapsed column, SQL trace, 24-14

enabled constraint, 10-11

enforced constraint, 10-11

ENQUEUE_RESOURCES parameter, 19-14, 19-15

Enterprise Manager, 4-7

equijoin, 7-9

errors

common tuning, 2-15

during discrete transactions, 11-3

ESTIMATE option, 19-45

examples

ALTER SESSION command, 24-5

ANALYZE command, 8-5

CREATE INDEX command, 15-39

CREATE TABLE command, 15-24

CREATE TABLESPACE command, 15-24

DATAFILE clause, 15-24

discrete transactions, 11-4

execution plan, 7-7

EXPLAIN PLAN output, 7-7, 23-13, 24-17

full table scan, 7-8

indexed query, 7-8

NOSORT option, 15-39

SET TRANSACTION command, 15-30

SQL trace facility output, 24-17

STORAGE clause, 15-24

table striping, 15-24

TABLESPACE clause, 15-24

executable code as data source, 4-4

execution plans, 23-2

examples, 7-7, 24-8

parallel operations, 21-5

TKPROF, 24-8, 24-10

EXISTS subquery, 19-17

expectations for tuning, 1-9

Expert, Oracle, 4-12

EXPLAIN PLAN command

examples of output, 7-7, 23-13, 24-17

introduction, 4-6

invoking with the TKPROF program, 24-10

parallel query, 21-4

PLAN_TABLE, 23-3

query parallelization, 21-8

SQL decomposition, 9-7

extents

size, 19-35

temporary, 19-40

unlimited, 15-29

F

fact table, 6-7

failover, 6-5

FAST FULL SCAN, 6-3, 10-9

FAST_FULL_SCAN_ENABLED parameter, 10-9

file storage, designing, 15-5

FIRST_ROWS hint, 8-15, 19-5

fragmentation, external, 20-13

free lists

adding, 18-18

contention, 18-17

reducing contention, 18-18

FREELISTS, 19-43

FULL hint, 8-17, 10-8

full table scan, 7-8

G

GC_FILES_TO_LOCKS parameter, 20-13

GC_ROLLBACK_LOCKS parameter, 20-14

GC_ROLLBACK_SEGMENTS parameter, 20-14

GETMISSES, V$ROWCACHE table, 14-20

GETS, V$ROWCACHE table, 14-20

global dynamic performance view, 21-10

global index, 19-42

goals for tuning, 1-9, 2-13

GROUP BY

decreasing demand for, 20-7

example, 21-9

NOSORT, 15-39

GV$ views, querying, 19-7

GV$CURRENT_BUCKET view, 14-29

GV$FILESTAT view, 21-10

GV$RECENT_BUCKET view, 14-29

H

hash area, 2-11, 20-3

HASH hint, 8-18

hash join, 19-4, 20-3

HASH parameter

CREATE CLUSTER command, 10-26

HASH_AJ hint, 8-19, 19-16, 19-17

HASH_AREA_SIZE parameter

and parallel execution, 19-4

example, 20-7

relationship to memory, 20-6

HASH_JOIN_ENABLED parameter, 8-8

HASH_MULTIBLOCK_IO_COUNT parameter, 8-8, 19-19

HASH_SJ hint, 8-19, 8-22

hashing, 10-25

HASHKEYS parameter

CREATE CLUSTER command, 10-26

hints, 8-11

access methods, 8-17

ALL_ROWS, 8-14

AND_EQUAL, 8-23, 10-8

CACHE, 8-32

CLUSTER, 8-18

degree of parallelism, 8-28

HASH, 8-18

INDEX, 8-19, 8-25, 10-8

INDEX_ASC, 8-21

INDEX_DESC, 8-21

INDEX_FFS, 8-22

join operations, 8-25

MERGE_AJ, 8-22

NO_MERGE, 8-33

NOCACHE, 8-32

NOPARALLEL hint, 8-29

optimization approach and goal, 8-14

ORDERED, 8-24, 8-25

PARALLEL hint, 8-28

parallel query option, 8-28

PUSH_SUBQ, 8-35

ROWID, 8-18

RULE, 8-16

STAR, 8-25

histogram

number of buckets, 8-4

viewing, 8-4

HOLD_CURSOR, 14-10

I

I/O

analyzing needs, 15-2, 15-3

asynchronous, 19-21

balancing, 15-23

distributing, 15-21, 15-24

insufficient, 3-6

multiple buffer pools, 14-38

parallel execution, 19-2

striping to avoid bottleneck, 19-24

testing disk performance, 15-6

tuning, 2-12, 15-2

ID column

INDEX hint, 8-19, 10-8, 10-16

index join, 20-7

INDEX_ASC hint, 8-21

INDEX_COMBINE hint, 10-16

INDEX_DESC hint, 8-21

INDEX_FFS hint, 6-3, 8-22, 10-9

indexes

avoiding the use of, 10-8

bitmap, 6-7, 10-13, 10-16, 10-18

choosing columns for, 10-5

composite, 10-6

creating in parallel, 20-20

design, 2-9

enforcing uniqueness, 10-11

ensuring the use of, 10-7

example, 7-8

fast full scan, 6-3, 10-9

global, 19-42

local, 19-42

modifying values of, 10-5

non-unique, 10-11

parallel, 6-3

parallel creation, 20-20, 20-21

parallel local, 20-20

placement on disk, 15-22

rebuilding, 10-10

recreating, 10-10

selectivity of, 10-5

STORAGE clause, 20-21

when to create, 10-3

INDX column, 14-29, 14-33

INITIAL extent size, 19-35, 20-13

initialization parameters

DISCRETE_TRANSACTIONS_ENABLED, 11-3

for parallel execution, 19-3

MAX_DUMP_FILE_SIZE, 24-4

OPTIMIZER_MODE, 8-10, 8-14

PRE_PAGE_SGA, 14-5

SESSION_CACHED_CURSORS, 14-18

SORT_DIRECT_WRITES, 15-40

SORT_WRITE_BUFFER_SIZE, 15-40

SORT_WRITE_BUFFERS, 15-40

SQL_TRACE, 24-6

TIMED_STATISTICS, 24-4

USER_DUMP_DEST, 24-4

inlists, 8-20, 8-23

INSERT functionality, 20-25

INSERT, append, 8-29

integrity constraint, 10-12

internal write batch size, 15-44

ISOLATION LEVEL, 11-6

K

keep cache, 14-37

KEEP procedure, 12-7

L

large pool, 15-48

LARGE_POOL_MIN_ALLOC, 15-48

LARGE_POOL_SIZE, 15-48

latches

contention, 2-12, 13-9

redo allocation latch, 18-13

redo copy latches, 18-13

least recently used list (LRU), 13-8

LGWR_IO_SLAVES parameter, 19-21

library cache, 2-11

memory allocation, 14-15

tuning, 14-13

listening queue, 16-3

load balancing, 6-5, 15-23

load, parallel, 6-4, 19-38

local index, 19-42

local striping, 19-26

lock contention, 2-12

log, 18-12

log buffer tuning, 2-11, 14-7

log switches, 15-42

log writer process (LGWR) tuning, 15-21, 15-43

LOG_BUFFER parameter, 14-7, 15-43

and parallel execution, 19-13

setting, 18-13

LOG_CHECKPOINT_INTERVAL parameter, 15-42

LOG_CHECKPOINT_TIMEOUT parameter, 15-42

LOG_SIMULTANEOUS_COPIES parameter, 18-14, 18-16

LOG_SMALL_ENTRY_MAX_SIZE parameter, 18-13, 18-16

LOGGING option, 19-44

logical structure of database, 2-9

LRU

aging policy, 14-37

latch, 14-39, 14-40, 14-45

latch contention, 14-45, 18-16

M

Managment Information Base (MIB), 4-5

massively parallel system, 19-2

max session memory statistic, 14-21

MAX_DUMP_FILE_SIZE, 24-4

MAXEXTENTS keyword, 19-36, 20-13

MAXOPENCURSORS, 14-10

media recovery, 19-40

memory

configure at 2 levels, 19-3

insufficient, 3-5

process classification, 20-3

reducing usage, 14-47

memory allocation

shared SQL areas, 14-15

sort areas, 15-36

tuning, 14-2, 14-46

users, 14-6

memory/user/server relationship, 20-2

MERGE hint, 8-9, 8-33

MERGE_AJ hint, 8-22, 19-16, 19-17

method

tuning, 2-1

MIB, 4-5

mirroring

redo log files, 15-22

monitoring the system, 4-5

MPP

disk affinity, 19-26

MTS_DISPATCHERS parameter, 18-8, 18-9

MTS_MAX_DISPATCHERS parameter, 18-8

MTS_MAX_SERVERS parameter, 18-10

multi-block reads, 15-28

MULTIBLOCK_READ_COUNT parameter, 19-35

multiple archiver processes, 19-43

multiple buffer pools, 14-37, 14-38, 14-40

multi-purpose applications, 5-6

multi-threaded server, 20-3

context area size, 2-11

reducing contention, 18-6

shared pool and, 14-20

tuning, 18-6

multi-tier systems, 13-11

N

NAMESPACE column

V$LIBRARYCACHE table, 14-13

nested loop join, 19-32, 20-3

nested query, 20-18

network

array interface, 16-3

bandwidth, 3-7

constraints, 3-7

detecting performance problems, 16-2

prestarting processes, 16-3

problem solving, 16-2

Session Data Unit, 16-3

tuning, 16-1

NEXT extent, 20-13

NO_MERGE hint, 8-9, 8-33

NO_PUSH_JOIN_PRED hin, 8-9, 8-34

NOAPPEND hint, 8-30, 20-26

NOARCHIVELOG mode, 19-44

NOCACHE hint, 8-32

NOLOGGING option, 19-41, 19-44, 20-19, 20-20, 20-26

NOPARALLEL attribute, 20-17

NOPARALLEL hint, 8-29

NOPARALLEL_INDEX hint, 8-31

NOSORT option, 15-39

NOT IN operator, 19-16

NT performance, 17-6

O

OBJECT_INSTANCE column

OBJECT_NAME column

OBJECT_NODE column, 21-9

OBJECT_OWNER column

OBJECT_TYPE column

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

online redo log, 15-42

processes, 20-3

with decision support, 5-6

OPEN_CURSORS parameter

allocating more private SQL areas, 14-9

increasing cursors per session, 14-15

operating system

data cache, 17-2

monitoring disk I/O, 15-17

monitoring tools, 4-3

striping, 19-24, 19-25

tuning, 2-12, 3-7, 14-4

OPERATION column

PLAN_TABLE, 23-4, 23-7

OPTIMAL storage parameter, 15-31

optimization

choosing an approach and goal for, 8-2

OPTIMIZER column

OPTIMIZER_FEATURES_ENABLED parameter, 8-7

OPTIMIZER_MODE, 6-7, 8-3, 8-6, 8-7, 8-10, 8-14, 20-24

OPTIMIZER_PERCENT_PARALLEL parameter, 6-6, 8-7, 19-5, 21-4

OPTIMIZER_SEARCH_LIMIT parameter, 8-8

OPTIONS column

control of parsing and private SQL areas, 14-10

Oracle Expert, 2-1, 4-12

Oracle Forms, 24-5

Oracle Network Manager, 16-3

Oracle Parallel Server, 5-9, 6-5

CPU, 13-13

disk affinity, 20-15

parallel load, 19-39

parallel query, 19-11, 20-13

ST enqueue, 20-12

synchronization points, 2-8

Oracle Parallel Server Management (OPSM), 4-13

Oracle Performance Manager, 4-8

Oracle Precompilers

control of parsing and private SQL areas, 14-10

Oracle Server

client/server configuration, 5-9

configurations, 5-7

Oracle striping, 19-26

Oracle Tablespace Manager, 4-11

Oracle TopSessions, 4-9

Oracle Trace, 4-10, 14-43, 25-1

command line interface, 25-7

detail report, 25-9

formatting data, 25-10

parameters, 25-4

Oracle Trace Manager, 25-4

ORACLE_TRACE_COLLECTION_NAME parameter, 25-5

ORACLE_TRACE_ENABLE parameter, 25-4

ORACLE_TRACE_FACILITY_NAME parameter, 25-5

ORDER BY, 23-14

decreasing demand for, 20-7

order, preserving, 23-14

ORDERED hint, 8-24

OTHER column

oversubscribing resources, 20-5, 20-9

OTHER_TAG column, 21-8

overhead, process, 20-3

overloaded disks, 15-21

P

packages

DBMS_APPLICATION_INFO, 26-2, 26-4

DBMS_SHARED_POOL, 12-4

DBMS_TRANSACTION, 11-4

DIUTIL, 12-5

registering with the database, 4-7, 26-2

STANDARD, 12-5

page table, 13-4

paging, 3-5, 13-5, 20-5, 21-5, 21-14

library cache, 14-15

rate, 19-4

reducing, 14-4

SGA, 14-46

subsystem, 20-5

parallel aware optimizer, 6-6

PARALLEL clause, 20-25, 20-26

PARALLEL CREATE INDEX statement, 19-13

PARALLEL CREATE TABLE AS SELECT, 6-3

external fragmentation, 20-13

resources required, 19-13

parallel Data Manipulation Lanugage, 20-29

parallel execution

introduction, 19-2

resource parameters, 19-3

tuning parallel servers, 21-11

tuning physical database layout, 19-22

parallel execution plan, 21-5

PARALLEL hint, 8-28, 20-17, 20-25, 21-4

parallel index, 20-21

creation, 6-3

parallel load, 6-4

example, 19-38

Oracle Parallel Server, 19-39

using, 19-33

parallel query, 5-5

adjusting workload, 20-8

cost-based optimization, 20-24

detecting performance problems, 21-1

hints, 8-28

I/O parameters, 19-19

index creation, 20-20

maximum processes, 20-2

parallel server, 20-13

parameters enabling new features, 19-16

process classification, 19-23, 19-26, 19-41, 20-4

query servers, 18-11

rewriting SQL, 20-18

solving problems, 20-17

space management, 20-12

tuning, 19-1 to ??, 20-1 to 20-24

tuning query servers, 18-11

understanding performance issues, 20-2

parallel server, 5-9

disk affinity, 20-15

parallel query tuning, 20-13

parallel server tuning, 4-13

PARALLEL_ADAPTIVE_MULTI_USER parameter, 19-9, 19-32

PARALLEL_BROADCAST_ENABLE parameter, 19-18

PARALLEL_EXECUTION_MESSAGE_SIZE parameter, 19-20

PARALLEL_MAX_SERVERS parameter, 19-6, 19-8, 19-10, 20-6

and parallel query, 19-6

and SHARED_POOL_SIZE, 19-10

PARALLEL_MIN_PERCENT parameter, 19-6

PARALLEL_MIN_SERVERS parameter, 19-8, 19-9

PARALLEL_TRANSACTION_RESOURCE_TIMEOUT parameter, 20-16

parallelism

degree on parallel server, 19-11

degree, overriding, 20-17

degree, with parallel query, 19-32

PARALLEL-TO-PARALLEL keyword, 21-9

parameter file, 4-4

PARENT_ID column

reducing unnecessary calls, 14-9

parsing, 13-7

Oracle Forms, 14-10

Oracle Precompilers, 14-10

partition elimination, 9-8

partition view, 6-4, 9-8

PARTITION_VIEW_ENABLED parameter, 9-8

partitioned table, 6-4

data warehouse, 19-31

example, 19-36

parallel grouping, 21-9

PCM lock, 20-13

PCTFREE, 2-12, 15-34

PCTINCREASE parameter, 15-37

and SQL.BSQ file, 15-34

PCTUSED, 2-12, 15-34

performance

client/server applications, 5-9

decision support applications, 5-4

different types of applications, 5-2

monitoring registered applications, 4-7, 26-2

evaluating, 1-10

key factors, 3-4

mainframe, 17-6

NT, 17-6

OLTP applications, 5-2

Parallel Server, 5-9

UNIX-based systems, 17-5

Performance Manager, 4-8

Performance Monitor, NT, 13-4

PHYRDS column

V$FILESTAT table, 15-19

physical database layout, 19-22

PHYSICAL READ, 14-34

physical reads statistic, 14-26, 14-30

PHYWRTS column

V$FILESTAT table, 15-19

ping UNIX command, 4-3

pinging, 2-12

PINS column

V$LIBRARYCACHE table, 14-14

PL/SQL

package, 4-6

tuning PL/SQL areas, 14-7

PLAN_TABLE table

ID column, 23-5

OBJECT_INSTANCE column, 23-4

OBJECT_NAME column, 23-4

OBJECT_NODE column, 23-4

OBJECT_OWNER column, 23-4

OBJECT_TYPE column, 23-5

OPERATION column, 23-4

OPTIMIZER column, 23-5

OPTIONS column, 23-4

OTHER column, 23-5

PARENT_ID column, 23-5

POSITION column, 23-5

REMARKS column, 23-4

SEARCH_COLUMNS column, 23-5

STATEMENT_ID column, 23-4

structure, 23-3

TIMESTAMP column, 23-4

POOL attribute, 18-9

POSITION column

PRE_PAGE_SGA parameter, 14-5

PRIMARY KEY constraint, 10-11, 10-12, 20-21

private SQL areas, 14-9

proactive tuning, 2-2

process

classes of parallel query, 19-23, 19-26, 19-41, 20-4

dispatcher process configuration, 18-8

DSS, 20-3

maximum number, 3-7, 20-2

maximum number for parallel query, 20-2

OLTP, 20-3

overhead, 20-3

prestarting, 16-3

scheduling, 13-5

process priority, 17-3

process scheduler, 17-3

processing, distributed, 5-9

PRVTPOOL.PLB, 12-4

PUSH_JOIN_PRED hint, 8-9, 8-34

PUSH_JOIN_PREDICATE parameter, 8-7, 8-9

Q

queries

avoiding the use of indexes, 10-8

distributed, 9-1

ensuring the use of indexes, 10-7

query column, SQL trace, 24-14

query plan, 23-2

query server process

tuning, 18-11, 21-11

query, distributed, 9-12

R

RAID, 15-26, 19-30, 19-40

read consistency, 13-8

read/write operations, 15-6

REBUILD, 10-10

record keeping, 2-14

recovery

data warehouse, 6-8

effect of checkpoints, 15-41

media, with striping, 19-30

recursive calls, 15-27, 24-15

recursive SQL, 12-2

recycle cache, 14-37

redo allocation latch, 18-13, 18-16

REDO BUFFER ALLOCATION RETRIES, 18-12

redo copy latches, 18-13, 18-16

choosing how many, 18-14

redo log buffer tuning, 14-7

redo log files

mirroring, 15-22

placement on disk, 15-21

tuning checkpoints, 15-42

reducing

contention

dispatchers, 18-6

OS processes, 17-3

query servers, 18-12

redo log buffer latches, 18-12

shared servers, 18-9

data dictionary cache misses, 14-20

library cache misses, 14-15

paging and swapping, 14-4

rollback segment contention, 18-5

unnecessary parse calls, 14-9

reducing buffer cache misses, 14-29

registering applications with database, 4-7, 26-2

regression, 21-4, 21-5

RELEASE_CURSOR, 14-10

RELOADS column

V$LIBRARYCACHE table, 14-14

REMARKS column

rollback segments, 13-8, 19-13

remote SQL statement, 9-2

reparsing, 13-7

resource

adding, 1-4

oversubscribing, 20-5

oversubscription, 20-9

parallel query usage, 19-3

tuning contention, 2-12

response time, 1-2, 1-3

optimizing, 8-6, 8-15

roles in tuning, 1-8

assigning to transactions, 15-30

choosing how many, 18-5

contention, 18-4

creating, 18-5

detecting dynamic extension, 15-27

dynamic extension, 15-30

ROLLBACK_SEGMENTS parameter, 19-13

ROWID hint, 8-18

rows column, SQL trace, 24-14

RULE hint, 8-16, 20-24

rule-based optimization, 8-10

S

sar UNIX command, 13-4, 21-14

scalability, 6-5, 13-9

scalable operations, 21-7

SEARCH_COLUMN column

serializable transactions, 11-6

segments, 15-26

selectivity, index, 10-5

semi-join, 19-17

sequence cache, 2-11

sequential reads, 15-6

sequential writes, 15-6

server/memory/user relationship, 20-2

service time, 1-2, 1-3

Session Data Unit (SDU), 16-3

session memory statistic, 14-21

SESSION_CACHED_CURSORS parameter, 13-7, 14-18

SET TRANSACTION command, 15-30

keeping objects pinned in, 12-4

tuning, 14-11, 14-22

shared SQL areas

finding large areas, 12-6

identical SQL statements, 12-3

keeping in the shared pool, 12-4

memory allocation, 14-15

statements considered, 12-2

SHARED_POOL_RESERVED_MIN_ALLOC parameter, 14-25

SHARED_POOL_RESERVED_SIZE parameter, 14-24

SHARED_POOL_SIZE parameter, 14-20, 14-25

allocating library cache, 14-15

and parallel query, 19-10

on parallel server, 19-11

tuning the shared pool, 14-20

SHOW SGA command, 14-5

Simple Network Management Protocol (SNMP), 4-5

single tier, 13-11

SIZES procedure, 12-6

skew, workload, 21-6

SNMP, 4-5

sort areas

memory allocation, 15-36

process local area, 2-11

sort merge join, 20-3

SORT_AREA_RETAINED_SIZE parameter, 14-46, 15-37

SORT_AREA_SIZE parameter, 10-15, 14-46

and parallel execution, 19-12

tuning sorts, 15-37

SORT_DIRECT_WRITES parameter, 8-8, 15-40, 19-20

SORT_READ_FAC parameter, 15-38, 19-20

SORT_WRITE_BUFFER_SIZE parameter, 8-8

SORT_WRITE_BUFFERS, 15-40

sorts

avoiding on index creation, 15-39

tuning, 15-35

sorts (disk) statistic, 15-36

sorts (memory) statistic, 15-36

source data for tuning, 4-2

space management, 19-40

parallel query, 20-12

reducing transactions, 20-12

spin count, 13-9

SPINCOUNT parameter, 13-9, 18-2

SQL area tuning, 14-7

SQL Loader, 19-33

SQL statements

avoiding the use of indexes, 10-8

decomposition, 9-4

ensuring the use of indexes, 10-7

inefficient, 13-8

modifying indexed data, 10-5

recursive, 12-2

reparsing, 13-7

tuning, 2-10

SQL trace facility, 4-6, 14-8, 14-43, 24-2, 24-7

enabling, 24-5

example of output, 24-17

output, 24-14

parse calls, 14-8

statement truncation, 24-16

steps to follow, 24-3

SQL_STATEMENT column

SQL_TRACE parameter, 24-6

SQL.BSQ file, 15-34

SQLUTLCHAIN.SQL, 4-6

ST enqueue, 20-12

STANDARD package, 12-5

STAR hint, 8-25

star query, 6-7

star schema, 6-7

star transformation, 6-8, 8-35

STAR_TRANSFORMATION hint, 6-8, 8-35

STAR_TRANSFORMATION_ENABLED parameter, 6-8, 8-35

STATEMENT_ID column

consistent gets, 14-26, 18-5, 18-18

statistics, 21-5, 22-2

computing, 19-46

current value, 22-4

db block gets, 14-26, 18-5

dispatcher processes, 18-6

enabling collection, 14-30

estimating, 19-46

generating, 8-4

max session memory, 14-21

operating system, 21-14

physical reads, 14-26

query servers, 18-11

rate of change, 22-5

session memory, 14-21

shared server processes, 18-9, 18-12

sorts (disk), 15-36

sorts (memory), 15-36

undo block, 18-4

STORAGE clause

CREATE TABLE command, 15-24

examples, 15-24

modifying parameters, 15-34

modifying SQL.BSQ, 15-34

OPTIMAL, 15-31

parallel query, 20-21

storage, file, 15-5

stored procedures

BEGIN_DISCRETE_TRANSACTION, 11-3

KEEP, 12-7

READ_MODULE, 26-7

registering with the database, 4-7, 26-2

SET_ACTION, 26-4

SET_CLIENT_INFO, 26-5

SET_MODULE, 26-3

SIZES, 12-6

UNKEEP, 12-7

striping, 15-23, 19-24

and disk affinity, 20-15

media recovery, 19-30

operating system, 19-25

operating system software, 15-25

Oracle, 19-26

temporary tablespace, 19-40

subquery, correlated, 20-18

swapping, 3-5, 13-4, 13-5

library cache, 14-15

reducing, 14-4

SGA, 14-46

switching processes, 13-5

symmetric multiprocessor, 19-2

System Global Area tuning, 14-5

system-specific Oracle documentation

software constraints, 3-7

SPIN_COUNT parameter, 13-9

USE_ASYNC_IO, 19-21

T

table queue, 21-9, 21-11

tables

placement on disk, 15-22

striping examples, 15-24

tablespace

creating, example, 19-34

dedicated temporary, 19-40

temporary, 15-38

TABLESPACE clause, 15-24

CREATE TABLE command, 15-24

Tablespace Manager, 4-11

TAPE_ASYNCH_IO parameter, 19-21

TCP.NODELAY option, 16-4

temporary extent, 19-40

TEMPORARY keyword, 15-38

temporary tablespace

optimizing sort, 15-38

size, 19-40

optimizing, 8-6, 8-14

tiers, 13-11

TIMED_STATISTICS parameter, 21-12, 24-4

TIMESTAMP column

TKPROF program, 14-43, 14-44, 24-3, 24-7

editing the output SQL script, 24-18

example of output, 24-17

generating the output SQL script, 24-18

introduction, 4-6

syntax, 24-9

using the EXPLAIN PLAN command, 24-10

tool, in-house performance, 4-13

querying, 24-19

TopSessions, 4-9

Trace, Oracle, 4-10, 25-1

transaction processing monitor, 13-12

transactions

assigning rollback segments, 15-30

discrete, 11-2

rate, 20-12

serializable, 11-6

TRANSACTIONS parameter, 19-13

transmission time, 3-7

Transparent Gateway, 9-13

tuning

access path, 2-10

and design, 2-10

application design, 2-9

business rule, 2-7

checkpoints, 15-41

client/server applications, 5-9

contention, 18-1

CPU, 13-1

data design, 2-8

data sources, 4-2

database logical structure, 2-9

decision support systems, 5-4

diagnosing problems, 3-1

memory allocation, 2-11, 14-2, 14-46

logical structure, 10-3

method, 2-1

monitoring registered applications, 4-7, 26-2

multi-threaded server, 18-6

OLTP applications, 5-2

operating system, 2-12, 3-7, 14-4

parallel execution, 19-22

production systems, 2-4

query servers, 18-11, 21-11

reactive, 2-3

shared pool, 14-11, 14-20

sorts, 15-35

SQL, 2-10

SQL and PL/SQL areas, 14-7

System Global Area (SGA), 14-5

two-phase commit, 19-13

two-tier, 13-11

U

undo block statistic, 18-4

UNION ALL view, 9-8

UNIQUE constraint, 10-11, 10-12, 20-21

UNIQUE index, 10-16

uniqueness, 10-11

UNIX system performance, 17-5

UNKEEP procedure, 12-7

unlimited extents, 15-29

USE_CONCAT hint, 8-23

USE_MERGE hint, 8-26

USE_NL hint, 8-25

user memory allocation, 14-7

user/server/memory relationship, 20-2

USER_DUMP_DEST, 24-4

USER_HISTOGRAMS, 8-4

USER_ID column