Oracle7 Server Tuning

Library

Product

Contents

Index

Output Table Columns

ABORTED_REQUEST_THRESHOLD procedure, 13-24

access paths

bounded range on indexed columns, A-29

cluster join, A-26

composite index, A-27

full table scan, A-33

hash cluster key, A-26

indexed cluster key, A-27

MAX or MIN of indexed column, A-32

optimization, A-23

ORDER BY on indexed column, A-33

single row by cluster join, A-24

single row by hash cluster key (with unique key), A-24

single row by ROWID, A-23

single row by unique or primary key, A-25

single-column index, A-28

sort-merge join, A-31

tuning, 2-10

unbounded range on indexed columns, A-30

adding

dispatcher processes, 16-8

aggregate, 18-27, 18-50

alert log, 4-4

ALL, A-3

ALL_HISTOGRAMS, 7-4

ALL_INDEXES view, 8-21

ALL_ROWS hint, 7-12, A-23

ALL_TAB_COLUMNS, 7-4

allocation, of memory, 13-2

ALTER SESSION command

examples, 21-5

SET SESSION_CACHED_CURSORS, 13-19

ALWAYS_ANTI_JOIN parameter, 8-5

and parallel query, 18-9

analysis dictionary, 4-5

ANALYZE command, 6-5, 14-31, 18-24, 18-52–18-53

COMPUTE option, 18-24

ESTIMATE option, 18-24

examples, 7-6

analyzing data, 18-24

AND_EQUAL hint, 7-23, 8-11

anti-join, 18-9

how to use, 8-5

when to use, 8-5

ANY, A-3

application designer, 1-8

application developer, 1-8

applications

client/server, 5-9

decision support, 5-4, 18-2, C-4

design tuning, 2-8

distributed databases, 5-7

OLTP, 5-2

parallel query, 5-6

parallel server, 5-9

registering with the database, 4-8, 23-2

architecture

and CPU, 12-10

array interface

network tuning, 15-3

array processing, B-7

assigning

rollback segments to transactions, 14-30

ASYNC_WRITE parameter

and parallel query, 18-13

asynchronous operation, 18-14

asynchronous readahead, 18-18

audit trail, 4-4

backup, 18-16

data warehouse, 6-8

BEGIN_DISCRETE_TRANSACTION procedure, 9-2

benefit of tuning, 2-3

BETWEEN, A-4

bind variables, B-7

optimization, A-35

shared SQL areas, 13-17

bitmap index, 6-4, 8-14, 8-16, 8-18, 8-21–8-22

block

size, 14-15

block contention, 2-11

bottlenecks

disk I/O, 14-20

memory, 13-2

buffer cache, 2-10

memory allocation, 13-31

processes which use, 18-12

reducing cache misses, 13-31

tuning, 13-27

buffers

determining number to add, 13-34

when to reduce number of, 13-34

business rule, 1-8, 2-3

tuning, 2-7

BUSY column

V$DISPATCHER table, 16-6

CACHE hint, 7-29

CATPARR.SQL script, 13-30

chained rows, 14-30

channel bandwidth, 3-5

check constraint, 11-5, 11-8

CHECK CONSTRAINTS41, 18-11

Checkpoint process (CKPT)

behavior on checkpoints, 14-42

enabling, 14-42

CHECKPOINT_PROCESS parameter

setting, 14-42

checkpoints

choosing checkpoint frequency, 14-41

current write batch size, 14-44

effect on recovery time performance, 14-41

effect on runtime performance, 14-41

redo log maintenance, 14-41

signalling DBWR to write, 14-41

tuning, 14-41

CHOOSE hint, 7-14, A-23

CKPT process, 14-43

client/server

round trip, 12-5

client/server applications, 5-9

CLUSTER hint, 7-16

cluster joins, A-40

clusters

hash, A-24

searches, A-26

how to use, 8-2

index

searches on, A-27

tradeoffs, 8-2

columns

choosing for indexes, 8-8

COMPATIBLE parameter, 8-21, 18-19

and parallel query, 18-10

composite indexes, 8-9

COMPUTE option, 18-24

consistency

read, 12-8

consistent gets statistic, 13-28, 16-5, 16-19

calculating hit ratio, 13-33, 13-36

consistent mode

number of buffers retrieved, 21-13

contention

disk access, 14-20

free lists, 16-18

memory, 13-2

memory access, 16-1

redo allocation latch, 16-16

redo copy latches, 16-16

rollback segments, 16-4

tuning, 16-1

tuning resource, 2-11

context area, 2-10

context switching, 12-5

cost-based optimization, 6-4, 7-2

parallel query, 18-53

using hints with, 18-53

COUNT column

X$KCBCBH table, 13-35

X$KCBRBH table, 13-32

count column

SQL trace facility output, 21-13

CPU

checking utilization, 12-4

detecting problems, 12-4

insufficient, 3-4

solving problems, 12-4

system architecture, 12-10

tuning, 12-1

underutilized, 18-2

utilization, 12-2

CPU bound operations, 18-18

cpu column

SQL trace facility output, 21-13

CREATE CLUSTER command, 8-4

CREATE INDEX command, 18-52

examples, 14-38

NOSORT option, 14-38

CREATE TABLE AS SELECT, 18-41, 18-50–18-51

parallel, 6-6

CREATE TABLE command

examples, 14-24

parallelism, C-4

STORAGE clause, 14-24

TABLESPACE clause, 14-24

CREATE TABLESPACE command

DATAFILE clause, 14-24

examples, 14-24

current column

SQL trace facility output, 21-13

current mode

number of buffers retrieved, 21-13

CURSOR_NUM column

TKPROF_TABLE, 21-18

CURSOR_SPACE_FOR_TIME parameter

setting, 13-18

cursors

creating, B-4

data

comparative, 4-5

sources for tuning, 4-2

volume, 4-2

data block

size, 14-15

data cache

operating system, 17-2

data definition statements (DDL)

processing, B-8

data design

tuning, 2-8

data dictionary, 4-3

data dictionary cache, 2-10

reducing cache misses, 13-21

data manipulation statements (DML)

processing, B-4

data warehouse

ANALYZE command, 6-5

backup, 6-8

bitmap index, 6-4

fast full index scan, 6-6

features, 6-1, 6-3

introduction, 6-2

Oracle Parallel Server, 6-7

parallel aware optimizer, 6-5

parallel CREATE TABLE AS SELECT, 6-6

parallel index creation, 6-3

parallel load, 6-3

parallel query, 6-4

partition, 6-6

partition view, 6-6, 18-17

recovery, 6-8

star schema, 6-4

database

tuning logical structure, 8-7

database administrator (DBA), 1-8

database layout

tuning for parallel query, 18-14

Database Writer process (DBWR)

behavior on checkpoints, 14-41

datafile

adding in parallel, 18-20

placement on disk, 14-21

DATAFILE clause

CREATE TABLESPACE command, 14-24

examples, 14-24

DATE_OF_INSERT column

TKPROF_TABLE, 21-18

db block gets statistic, 13-28, 16-5, 16-19

calculating hit ratio, 13-33, 13-36

DB_BLOCK_BUFFERS parameter

and internal write batch size, 14-44

reducing buffer cache misses, 13-31

removing unneeded buffers, 13-34

DB_BLOCK_CHECKPOINT_BATCH parameter

and internal write batch size, 14-44

DB_BLOCK_LRU_EXTENDED_STATISTICS parameter, 13-31

setting, 13-32

DB_BLOCK_LRU_STATISTICS parameter, 13-31

setting, 13-35

DB_BLOCK_SIZE parameter

and parallel query, 18-12

DB_FILE_MULTIBLOCK_READ_COUNT parameter, 14-37

and parallel query, 18-12

cost-based optimization, A-44

DBA locking, 18-35

DBA_DATA_FILES view, 18-46

DBA_EXTENTS view, 18-46

DBA_HISTOGRAMS, 7-4

DBA_INDEXES view, 8-21

DBA_TAB_COLUMNS, 7-4

DBMS_APPLICATION_INFO package, 23-3, 23-5

DBMS_SHARED_POOL package, 10-4, 13-24

DBMS_SYSTEM package, 21-5

DBMS_SYSTEM.SET_SQL_TRACE_ IN_SESSION procedure, 21-5

DBMSPOOL.SQL script, 10-4

DBMSUTL.SQL, 23-3

DBWR

tuning, 12-8

decision support, 5-4, C-4

processes, 18-27

query characteristics, 18-3

response time, 1-2

tuning, 18-2

with OLTP, 5-5

define phase of query processing, B-6

degree of parallelism, C-4, C-10

between operations, C-9

EXPLAIN PLAN command, 18-43

hints, C-11

setting, C-10

demand rate, 1-5–1-6

DEPTH column

TKPROF_TABLE, 21-18

describe phase of query processing, B-6

design dictionary, 4-5

designing and tuning, 2-9

device bandwidth, 3-5

evaluating, 14-16

device latency, 3-5

diagnosing tuning problems, 3-1

dictionary

analysis and design, 4-5

dimension table, 6-4

discrete transactions

errors, 9-3

example, 9-5

processing, 9-3

usage notes, 9-4

when to use, 9-2

disk affinity

and parallel query, 18-37

disabling with MPP, 18-15

with MPP, 18-23

disk column

SQL trace facility output, 21-13

disk speed characteristics, 14-3

disks

avoiding contention, 14-21

contention, 14-20

distributing I/O, 14-20

I/O requirements, 14-4

layout options, 14-15

monitoring OS file activity, 14-17

number required, 14-4

placement of datafiles, 14-21

placement of redo log files, 14-21

testing performance, 14-6

dispatcher processes (Dnnn)

adding, 16-8

distributed databases, 5-7

distributed processing environment

data manipulation statements, B-4

distributing I/O, 14-20, 14-24

DIUTIL package, 10-4

DSS memory, 18-4

dynamic extension, 14-27

dynamic performance view

parallel query, 18-46

dynamic performance views

enabling statistics, 21-4

for tuning, 19-1

elapsed column

SQL trace facility output, 21-13

enabling

Checkpoint process (CKPT), 14-42

SQL trace facility, 21-5

Enterprise Manager, 4-9

errors

common tuning, 2-14

during discrete transactions, 9-3

ESTIMATE option, 18-24

examples

ALTER SESSION command, 21-5

ANALYZE command, 7-6

CREATE INDEX command, 14-38

CREATE TABLE command, 14-24

CREATE TABLESPACE command, 14-24

DATAFILE clause, 14-24

discrete transactions, 9-5

execution plan, 7-30

EXPLAIN PLAN output, 7-30, 18-43, 20-8, 21-15

full table scan, 7-30

indexed query, 7-31

NOSORT option, 14-38

SET TRANSACTION command, 14-30

SQL trace facility output, 21-15

STORAGE clause, 14-24

table striping, 14-24

TABLESPACE clause, 14-24

executable code as data source, 4-4

execution plan

parallel query, 18-41

execution plans, 20-2

examples, 7-30, 21-8, A-11

TKPROF, 21-8–21-9

expectations for tuning, 1-10

Expert, Oracle, 4-15

EXPLAIN PLAN command, 20-3

about, 18-43

examples of output, 7-30, 18-43, 21-15

exapmles of output, 20-8

invoking with the TKPROF program, 21-9

parallel query, 18-40

PLAN_TABLE, 20-3

query parallelization, 18-43

EXPLAIN PLAN statement, 4-7

extension

generates recursive calls, 14-27

extent

temporary, 18-22

unlimited, 14-28

extent size, 18-19

fact table, 6-4

failover, 6-7

fast full index scan, 6-6

FAST FULL SCAN, 8-12

fetching rows in a query, B-8

file storage

designing, 14-5

FIRST_ROWS hint, 7-13, A-23

free lists

adding, 16-19

contention, 16-18

reducing contention, 16-19

FULL hint, 7-15, 8-11

full table scans, A-33, C-3

example, 7-30

parallel query, C-3

GC_FILES_TO_LOCKS parameter, 18-35

GC_ROLLBACK_LOCKS parameter, 18-35

GC_ROLLBACK_SEGMENTS parameter, 18-35

GETMISSES column

V$ROWCACHE table, 13-20–13-21

GETS column

V$LATCH table, 16-14

V$ROWCACHE table, 13-20–13-21

goals for tuning, 1-9, 2-12

GROUP BY

decreasing demand for, 18-30

example, 18-45

GROUP BY NOSORT, 14-39

hash area, 2-10, 18-27

HASH hint, 7-16

hash join, 18-27

disabling, 18-29

performance, 18-4

HASH parameter

CREATE CLUSTER command, 8-4

HASH_AJ hint, 7-17, 8-5

HASH_AREA_SIZE parameter

and parallel query, 18-4

example, 18-30

relationship to memory, 18-29

HASH_JOIN_ENABLED parameter, 18-29

and parallel query, 18-10

HASH_MULTIBLOCK_IO_COUNT parameter

and parallel query, 18-12

hashing

how to use, 8-3

HASHKEYS parameter

CREATE CLUSTER command, 8-4

hints, 7-10

access methods, 7-15

ALL_ROWS, 7-12

AND_EQUAL, 7-23, 8-11

CACHE, 7-29

CLUSTER, 7-16

degree of parallelism, 7-27, C-11

FIRST_ROWS, 7-13

FULL, 7-15, 8-11

HASH, 7-16

HASH_AJ, 7-17

how to use, 7-10

INDEX, 7-17, 7-24, 8-11, A-46

INDEX_ASC, 7-18

INDEX_DESC, 7-19

INDEX_FFS, 7-23, 8-12

join operations, 7-25

MERGE_AJ, 7-23

NO_MERGE, 7-26

NOCACHE, 7-29

NOPARALLEL hint, 7-28

optimization approach and goal, 7-12

ORDERED, 7-24, A-45–A-46

PARALLEL hint, 7-27

parallel query option, 7-27

PUSH_SUBQ, 7-29

ROWID, 7-16

RULE, 7-14

STAR, 7-24, A-46

USE_CONCAT, 7-23

USE_MERGE, 7-26

USE_NL, 7-25

with cost-based optimization, 18-53

histogram

creating, 7-3

number of buckets, 7-4

viewing, 7-4

HOLD_CURSOR, 13-10

I/O

analyzing needs, 14-2–14-3

balancing, 14-23

distributing, 14-20, 14-24

insufficient, 3-5

striping to avoid bottleneck, 18-15

testing disk performance, 14-6

tuning, 2-11, 14-2

I/O bound operation, 18-18

ID column

PLAN_TABLE table, 20-4

IDLE column

V$DISPATCHER table, 16-6

IN, A-2

INDEX hint, 7-17, 8-11–8-12, 8-21

index join

decreasing demand for, 18-30

INDEX_ASC hint, 7-18

INDEX_COMBINE hint, 8-21

INDEX_DESC hint, 7-19

INDEX_FFS hint, 6-6, 7-23, 8-12

indexes

avoiding the use of, 8-11

bitmap, 6-4, 8-14, 8-16, 8-18, 8-21–8-22

choosing columns for, 8-8

composite, 8-9

searches on, A-27

creating in parallel, 18-52

creation in parallel, 18-23

design, 2-9

ensuring the use of, 8-10

example, 7-31

FAST FULL SCAN, 8-12

modifying values of, 8-9

optimization and, A-6

options for creating, 18-23

parallel, 6-3

parallel creation, 18-52

partition view, 18-23

placement on disk, 14-22

recreating, 8-13

searches on, A-28

using MAX or MIN, A-32

using ORDER BY, A-33

selectivity of, 8-8

statement conversion and, A-6

STORAGE clause with parallel query option, 18-52

when to create, 8-6

INDX column

X$KCBCBH table, 13-35

X$KCBRBH table, 13-32

INITIAL extent size, 18-19

INITIAL parameter, 18-34

initialization parameters

DISCRETE_TRANSACTIONS_ENABLED, 9-3

for parallel query, 18-3

MAX_DUMP_FILE_SIZE, 21-4

OPTIMIZER_MODE, 7-9, 7-12, A-21

PRE_PAGE_SGA, 13-5

SESSION_CACHED_CURSORS, 13-19

SORT_DIRECT_WRITES, 14-39

SORT_WRITE_BUFFER_SIZE, 14-39

SORT_WRITE_BUFFERS, 14-39

SQL_TRACE, 21-6

TIMED_STATISTICS, 21-4

USER_DUMP_DEST, 21-4

instances

limiting for parallel queries, C-12

internal write batch size, 14-44

inter-operator parallelism, C-9

intra-operator parallelism, C-9

ISOLATION LEVEL, 9-6

joins

cluster, A-24, A-26, A-40

convert to subqueries, A-10

execution plans and, A-37

nested loops, A-37, A-44

optimization of, A-43

parallel query, C-7

sort-merge, A-39, A-44

sort-merge searches, A-31

KEEP procedure, 10-6

keys, A-24

latch contention, 2-11

latches

contention, 12-9

redo allocation latch, 16-13

redo copy latches, 16-13

least recently used list (LRU), 12-8

LGWR

tuning I/O, 14-43

library cache, 2-10

memory allocation, 13-16

tuning, 13-14

LIKE, A-2

listener load balancing, 15-3

load

parallel, 6-3, 18-21

load balancing, 6-7, 14-23

lock contention, 2-11

log, 16-12

log buffer, 2-10

tuning, 13-7

log switches

tuning checkpoints, 14-42

Log Writer process (LGWR)

tuning, 14-21

LOG_BUFFER parameter, 13-7, 14-43

setting, 16-13

LOG_CHECKPOINT_INTERVAL parameter

guidelines, 14-42

LOG_CHECKPOINT_TIMEOUT parameter

guidelines, 14-42

LOG_SIMULTANEOUS_COPIES parameter, 16-16

setting, 16-14

LOG_SMALL_ENTRY_MAX_SIZE parameter

setting, 16-13, 16-16

logical structure of database, 2-9, 8-7

LRU latch, 16-17

Managment Information Base (MIB), 4-6

massively parallel system

parallel query benefits, 18-2

MAX operator, A-32

max session memory statistic, 13-22

MAX_DUMP_FILE_SIZE, 21-4

MAXEXTENTS keyword, 18-19

MAXOPENCURSORS, 13-10

media recovery, 18-23

temporary tablespace, 18-22

memory

insufficient, 3-4

process classification, 18-27

reducing usage, 13-39

tuning, 2-10

virtual, 18-4

memory allocation

buffer cache, 13-31

importance, 13-2

library cache, 13-16

shared SQL areas, 13-16

sort areas, 14-34

tuning, 13-2, 13-38

users, 13-6

memory/user/server relationship, 18-26

MERGE_AJ hint, 7-23, 8-5

message rate, 3-6

method

applying, 2-12

tuning, 2-1

tuning steps, 2-5

MIB, 4-6

migrated rows, 14-30

MIN operator, A-32

mirroring, 18-16, 18-23

redo log files, 14-21

MISSES column

V$LATCH table, 16-14

monitoring the system, 4-6

MPP

disk affinity, 18-15

MTS_MAX_DISPATCHERS parameter

tuning dispatchers, 16-8

MTS_MAX_SERVERS parameter

tuning servers, 16-10

mulit-tier, 12-11

multi-block reads, 14-28

MULTIBLOCK_READ_COUNT parameter, 18-19

multi-purpose applications, 5-5

multi-threaded serve, 2-10

multi-threaded server, 18-27

reducing contention, 16-6

shared pool and, 13-22

tuning, 16-6

NAMESPACE column

V$LIBRARYCACHE table, 13-14

nested loop join, 18-18, 18-27

nested loops joins, A-37

cost-based optimization, A-44

nested query, 18-50

network

array interface, 15-3

bandwidth, 3-6

constraints, 3-6

detecting performance problems, 15-2

listener load balancing, 15-3

out-of-band breaks, 15-3

prestarting processes, 15-4

problem solving, 15-3

Session Data Unit, 15-4

tuning, 15-1

NEXT extent size, 18-19

NEXT parameter, 18-34

NLS_SORT parameter

ORDER BY access path, A-33

NO_MERGE hint, 7-26

NOCACHE hint, 7-29

NONUNIQUE index, 8-21

NOPARALLEL hint, 7-28

NOSORT option, 14-38

choosing when to use, 14-38

CREATE INDEX command, 14-38

examples, 14-38

GROUP BY, 14-39

performance benefits, 14-38

NOT IN operator, 18-9

NT performance, 17-6

OBJECT_INSTANCE column

PLAN_TABLE table, 20-4

OBJECT_NAME column

PLAN_TABLE table, 20-4

OBJECT_NODE column, 18-45

PLAN_TABLE table, 20-4

OBJECT_OWNER column

PLAN_TABLE table, 20-4

OBJECT_TYPE column

PLAN_TABLE table, 20-4

OLTP

processes, 18-27

online redo log

increasing size, 14-42

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

with decision support, 5-5

OPEN_CURSORS parameter

allocating more private SQL areas, 13-10

increasing cursors per session, 13-16

operating system

data cache, 17-2

monitoring disk I/O, 14-17

monitoring tools, 4-3

striping, 18-15

tuning, 2-11, 3-6, 13-4

OPERATION column

PLAN_TABLE table, 20-4

values, 20-6

operators

MAX, A-32

MIN, A-32

OPTIMAL storage parameter, 14-29

optimization

choosing an approach and goal for, 7-2

choosing the approach, A-21

conversion of expressions and predicates, A-2

cost-based, A-44

when to use, 7-2

hints, A-23

manual, A-23

rule-based, A-43

when to use, 7-9

transitivity and, A-4

optimizer

parallel aware, 6-5

parallel queries, C-6

OPTIMIZER column

PLAN_TABLE, 20-4

OPTIMIZER_GOAL option, A-22

ALTER SESSION command, 7-6

of ALTER SESSION command, 7-3

OPTIMIZER_MODE, 6-4, 7-3, 7-9, 7-12, A-21

hints affecting, A-23

OPTIMIZER_PERCENT_PARALLEL parameter, 6-5, 18-40

and parallel query, 18-5

OPTIONS column

PLAN_TABLE table, 20-4

Oracle

striping, 18-16

Oracle Call Interface (OCI)

bind variables, B-7

control of parsing and private SQL areas, 13-11

Oracle Expert, 4-15

Oracle Forms, 21-5

control of parsing and private SQL areas, 13-11

Oracle Network Manager, 15-4

Oracle Parallel Server, 5-9, 6-7

CPU, 12-12

disk affinity, 18-37

parallel query, 18-8, 18-35

partition layout, 18-18

synchronization points, 2-8

Oracle Performance Manager, 4-10

Oracle Precompilers

bind variables, B-7

control of parsing and private SQL areas, 13-10

Oracle Server

client/server configuration, 5-9

configurations, 5-7

SQL processing, B-4

Oracle Tablespace Manager, 4-14

Oracle TopSessions, 4-11

Oracle Trace, 4-12, 22-1

command line interface, 22-6

detail report, 22-7

formatting data, 22-8

parameters, 22-3

ORACLE_TRACE_COLLECTION_NAME parameter, 22-9

ORACLE_TRACE_COLLECTION_PATH parameter, 22-9

ORACLE_TRACE_COLLECTION_SIZE parameter, 22-9

ORACLE_TRACE_ENABLE parameter, 22-4, 22-10

ORACLE_TRACE_FACILITY_NAME parameter, 22-10

ORACLE_TRACE_FACILITY_PATH parameter, 22-10

ORDER BY

decreasing demand for, 18-30

ORDERED hint, 7-24, A-45

OTHER column

PLAN_TABLE table, 20-4

OTHER_TAG column, 18-43–18-44

out-of-band break, 15-3

overhead

process, 18-27

overloaded disks, 14-20

oversubscribing resources, 18-28, 18-32

packages

DBMS_APPLICATION_INFO, 23-3, 23-5

DBMS_SHARED_POOL, 10-4

DBMS_TRANSACTION, 9-5

DIUTIL, 10-4

registering with the database, 4-8, 23-2

STANDARD, 10-4

page table, 12-4

paging, 3-4, 12-5, 18-28, 18-41, 18-48

library cache, 13-16

reducing, 13-4

SGA, 13-38

paging rate, 18-4

paging subsystem, 18-28

parallel aware optimizer, 6-5

parallel CREATE TABLE AS SELECT, 6-6

PARALLEL hint, 7-27, 18-40

parallel index

creation, 6-3

parallel index creation, 18-23

parallel load, 6-3

example, 18-21

using, 18-19

parallel query, 5-6, 6-4

adjusting workload, 18-31

benefits, 18-2

cost-based optimization, 18-53

degree of parallelism, C-10

detecting performance problems, 18-38

execution plan, 18-41

EXPLAIN PLAN command, 18-43

full table scans, C-3

hints, 7-27

I/O parameters, 18-12

index creation, 18-52

inter-operator parallelism, C-9

intra-operator parallelism, C-9

maximum processes, 18-26

multi-threaded server, C-4

number of server processes, C-14

operations in execution plan, C-7

optimizer, C-6

overriding degree of parallelism, 18-49

parallel operations, C-7

parallel server, 18-35

parallel server and, C-1, C-12

parameters enabling new features, 18-9

process classification, 18-27

processing, C-2

query coordinator, C-3

query servers, C-14

resource parameters, 18-3

rewriting SQL, 18-50

solving problems, 18-49

space management, 18-34

summary or rollup tables, C-4

tuning, 18-1, 18-54

tuning physical database layout, 18-14

understanding performance issues, 18-26

parallel query option

query servers, 16-11

tuning query servers, 16-11, 18-47

parallel server, 5-9

disk affinity, 18-37

parallel query, C-1, C-12

parallel query tuning, 18-35

PARALLEL_MAX_SERVERS parameter, 18-6–18-7, 18-29, C-14

and parallel query, 18-6

and SHARED_POOL_SIZE, 18-8

PARALLEL_MIN_PERCENT parameter, 18-7

PARALLEL_MIN_SERVERS parameter, 18-7, C-14

and parallel query, 18-7

PARALLEL_SERVER_IDLE_TIME parameter, C-14

PARALLEL_TO_PARALLEL keyword, 18-44

parallelism

degree on parallel server, 18-9

degree, overriding, 18-49

degree, with parallel query, 18-18

PARALLEL-TO-PARALLEL keyword, 18-45

PARAMETER column

V$ROWCACHE tabe, 13-20

parameter file, 4-4

PARENT_ID column

PLAN_TABLE table, 20-4

parsing, 12-7, B-5

Oracle Call Interface (OCI), 13-11

Oracle Forms, 13-11

Oracle Precompilers, 13-10

reducing unnecessary calls, 13-10

SQL statements, B-5

partition elimination, 11-4

partition table, 18-11

partition view, 18-17

analyzing, 18-24

check constraint, 11-5

creating underlying tables, 11-7

data warehouse, 18-17

defining, 11-5

example, 11-7, 18-20

guidelines, 6-6, 11-2

indexing, 18-23

parallel grouping, 18-45

parallelism, 11-4

WHERE clause, 11-6

PARTITION_VIEW_ENABLED parameter, 11-3, 11-7

and parallel query, 18-11

partitioning

defining criteria, 18-21

with Oracle Parallel Server, 18-18

partitioning data, 18-17

PCM lock

and parallel query, 18-35

PCTFREE, 2-11, 14-32

PCTINCREASE parameter, 14-36, 18-34

and SQL.BSQ file, 14-33

PCTUSED, 2-11, 14-32

performance

client/server applications, 5-9

decision support applications, 5-4

different types of applications, 5-2

distributed databases, 5-7

evaluating, 1-10

mainframe, 17-6

monitoring registered applications, 4-8, 23-2

NT, 17-6

OLTP applications, 5-2

Parallel Server, 5-9

UNIX-based systems, 17-5

performance factor

key to, 3-3

Performance Manager, 4-10

Performance Monitor, NT, 12-4

PHYRDS column

V$FILESTAT table, 14-19

physical database layout

parallel query, 18-14

physical reads statistic, 13-28

calculating hit ratio, 13-33, 13-36

PHYWRTS column

V$FILESTAT table, 14-19

ping UNIX command, 4-3

pinging, 2-11

PINS column

V$LIBRARYCACHE table, 13-15

PL/SQL

package, 4-8

tuning PL/SQL areas, 13-8

PLAN_TABLE table

ID column, 20-4

OBJECT_INSTANCE column, 20-4

OBJECT_NAME column, 20-4

OBJECT_NODE column, 20-4

OBJECT_OWNER column, 20-4

OBJECT_TYPE column, 20-4

OPERATION column, 20-4

OPTIMIZER column, 20-4

OPTIONS column, 20-4

OTHER column, 20-4

PARENT_ID column, 20-4

POSITION column, 20-4

REMARKS column, 20-4

SEARCH_COLUMNS column, 20-4

STATEMENT_ID column, 20-4

structure, 20-3

TIMESTAMP column, 20-4

POSITION column

PLAN_TABLE table, 20-4

PRE_PAGE_SGA parameter, 13-5

PRIMARY KEY constraint, 18-53

primary keys

optimization, A-10

searches, A-25

private SQL areas

reuse by multiple SQL statements, 13-10

proactive tuning, 2-2

process

classes of parallel query, 18-27

DSS, 18-27

maximum number, 18-26

maximum number for parallel query, 18-26

maximum number of, 3-6

OLTP, 18-27

overhead, 18-27

prestarting, 15-4

scheduling, 12-5

switching, 12-5

process priority, 17-3

process scheduler, 17-3

PROCESSES parameter

increasing for CKPT, 14-42

processing

distributed, 5-9, B-6

queries, B-6

queries

ad hoc, C-4

avoiding the use of indexes, 8-11

compound

optimization of, A-47

ORs converted to, A-6

define phase, B-6

describe phase, B-6

ensuring the use of indexes, 8-10

fetching rows, B-6

parallel processing, C-2

processing, B-6

query column

SQL trace facility output, 21-13

query coordinator, C-3

query plan, 20-2

query server process, C-3

about, C-3, C-14

tuning, 16-11, 18-47

RAID, 14-26, 18-16, 18-22

random reads, 14-6

random writes, 14-6

raw device, 17-3

reactive tuning, 2-3

read consistency, 12-8

read/write operations, 14-6

record keeping, 2-13

recovery

data warehouse, 6-8

effect of checkpoints, 14-41

media, with striping, 18-16

recursive calls, 14-27

detected by the SQL trace facility, 21-13

dynamic extension, 14-27

statistic, 14-27

recursive SQL, 10-2

redo allocation latch, 16-13

contention, 16-16

redo copy latches, 16-13

choosing how many, 16-14, 16-16

contention, 16-16

creating more, 16-16

redo log buffer

tuning, 13-7

redo log files

mirroring, 14-21

placement on disk, 14-21

tuning checkpoints, 14-42

redo log space requests statistic, 16-12

reducing

buffer cache misses, 13-31

contention

dispatchers, 16-6

OS processes, 17-3

query servers, 16-12

redo log buffer latches, 16-12

shared servers, 16-9

data dictionary cache misses, 13-21

disk contention, 14-20

library cache misses, 13-15

number of database buffers, 13-34

paging and swapping, 13-4

query execution time, C-2

rollback segment contention, 16-5

unnecessary parse calls, 13-10

registering applications with database, 4-8, 23-2

regression, 18-40–18-41

RELEASE_CURSOR, 13-10

RELOADS column

V$LIBRARYCACHE table, 13-15

REMARKS column

PLAN_TABLE table, 20-4

reparsing, 12-7

resource

oversubscribing, 18-28

oversubscription, 18-32

parallel query usage, 18-3

tuning contention, 2-11

resources, 1-5

response time, 1-2–1-3

cost-based approach, A-21

optimizing, 7-6, 7-13

roles in tuning, 1-8

rollback segments, 12-8

assigning to transactions, 14-30

choosing how many, 16-5

contention, 16-4

creating, 16-5

detecting dynamic extension, 14-27

dynamic extension, 14-29

round trip

client/server, 12-5

ROWID hint, 7-16

rows

fetched, B-6

ROWIDs used to locate, A-23

rows column

SQL trace facility output, 21-13

RULE hint, 7-14

OPTIMIZER_MODE and, A-23

rule-based optimization, 7-9

sar UNIX command, 12-4, 18-48

scalability, 6-7, 12-9, 18-53

operations, 18-43

scans

bounded range on indexed columns, A-29

cluster, A-26

FAST FULL SCAN, 8-12

full table, A-33

parallel query, C-3

hash, A-26

index, A-28, A-33

unbounded range on indexed columns, A-30

scheduling processes, 12-5

SEARCH_COLUMN column

PLAN_TABLE table, 20-4

segments, 14-27

selectivity

indexes, 8-8

sequence cache, 2-10

sequential reads, 14-6

sequential writes, 14-6

serializable transactions, 9-6

Server Manager

monitor screens, 4-6

SHOW SGA command, 13-6

server/memory/user relationship, 18-26

service time, 1-2–1-3

Session Data Unit (SDU), 15-4

session memory statistic, 13-22

SESSION_CACHED_CURSORS parameter, 12-7, 13-19

SET TRANSACTION command

assigning transactions to rollback segments, 14-29

examples, 14-30

SGA size, 13-7, 18-4

SGA statistics, 19-2

shared pool, 2-10

contention, 2-11

keeping objects pinned in, 10-4

tuning, 13-12, 13-24

shared SQL areas

finding large areas, 10-5

identical SQL statements, 10-2

keeping in the shared pool, 10-4

memory allocation, 13-16

statements considered, 10-2

SHARED_POOL_RESERVED_MIN_ALLOC parameter, 13-24, 13-26

SHARED_POOL_RESERVED_SIZE parameter, 13-24–13-25

SHARED_POOL_SIZE parameter, 13-26

allocating library cache, 13-16

and parallel query, 18-8

on parallel server, 18-8

reducing dictioanry cache misses, 13-21

tuning the shared pool, 13-22

SHOW SGA command, 13-6

Simple Network Management Protocol (SNMP), 4-6

single tier, 12-10

SIZES procedure, 10-5

skew, workload, 18-42

SLEEPS column

V$LATCH table, 16-14

SNMP, 4-6

SOME, A-3

sort areas, 2-10

memory allocation, 14-34

sort merge join, 18-27

SORT_AREA_RETAINED_SIZE parameter, 13-38

tuning sorts, 14-36

SORT_AREA_SIZE parameter, 8-20, 13-38

and parallel query, 18-9

cost-based optimization and, A-44

tuning sorts, 14-35

SORT_DIRECT_WRITES parameter, 14-39

and parallel query, 18-12

parallel query, 18-53

SORT_READ_FAC parameter, 14-37

and parallel query, 18-13

SORT_WRITE_BUFFERS, 14-39

sort-merge joins, A-39

cost-based optimization, A-44

sorts

avoiding on index creation, 14-38

parallel query, C-7

tuning, 14-34

sorts (disk) statistic, 14-35

sorts (memory) statistic, 14-35

source

data for tuning, 4-2

space management, 18-22

parallel query, 18-34

reducing transactions, 18-34

speed

disk, 14-3

spin count, 12-9

SPIN_COUNT parameter, 12-9

SPINCOUNT, 16-2

SQL areas

tuning, 13-8

SQL Loader, 18-19

SQL statement

inefficient, 12-7

reparsing, 12-7

SQL statements

array processing, B-7

avoiding the use of indexes, 8-11

binding variables, B-7

converting

examples of, A-6

creating cursors, B-4

ensuring the use of indexes, 8-10

execution, B-7

modifying indexed data, 8-9

optimization of complex, A-10

parallel query, C-2

parallelizing, C-6

parsing, B-5

recursive, 10-2

OPTIMIZER_GOAL does not affect, A-22

tuning, 2-9

SQL trace facility, 4-7, 21-2, 21-7

enabling, 21-5

example of output, 21-15

output, 21-12

parse calls, 13-8

steps to follow, 21-3

trace file, 4-3

trace files, 21-4

SQL*Plus script, 4-8

SQL.BSQ file, 14-33

SQL_STATEMENT column

TKPROF_TABLE, 21-18

SQL_TRACE parameter, 21-6

ST enqueue, 18-34

STANDARD package, 10-4

STAR hint, 7-24, A-46

star query, 6-4

extended star schemas, A-46

tuning, A-45–A-46

star schema, 6-4

STATEMENT_ID column

PLAN_TABLE table, 20-4

statistics, 19-2

computing, 18-25

consistent gets, 13-28, 16-5, 16-19

current value, 19-4

db block gets, 13-28, 16-5

dispatcher processes, 16-6

enabling collection, 13-32

estimating, 18-25

generating, 7-5

max session memory, 13-22

operating system, 18-48

physical reads, 13-28

query servers, 16-11

rate of change, 19-5

recursive calls, 14-27

redo log space requests, 16-12

session memory, 13-22

shared server processes, 16-9, 16-12

sorts (disk), 14-35

sorts (memory), 14-35

undo block, 16-4

STORAGE clause

CREATE TABLE command, 14-24

examples, 14-24

modifying parameters, 14-33

modifying SQL.BSQ, 14-33

OPTIMAL, 14-29

parallel query option, 18-52, C-5

storage, file, 14-5

stored procedures

BEGIN_DISCRETE_TRANSACTION, 9-3

KEEP, 10-6

READ_MODULE, 23-8

registering with the database, 4-8, 23-2

SET_ACTION, 23-5

SET_CLIENT_INFO, 23-6

SET_MODULE, 23-4

SIZES, 10-5

UNKEEP, 10-6

striping, 14-23, 18-15

and disk affinity, 18-37

example, 18-19

examples, 14-24

manual, 14-24, 18-15

media recovery, 18-16

operating system, 18-15

operating system software, 14-26

Oracle, 18-16

temporary tablespace, 18-22

subqueries

converting to joins, A-10

subquery, correlated, 18-50

swapping, 3-4, 12-4–12-5

library cache, 13-16

reducing, 13-4

SGA, 13-38

switching processes, 12-5

symmetric multiprocessor

parallel query benefits, 18-2

System Global Area (SGA)

tuning, 13-5

system-specific Oracle documentation

software constraints, 3-6

SPIN_COUNT parameter, 12-9

USE_ASYNC_IO, 18-14

table queue, 18-45, 18-47

tables

parallel creation, C-4

placement on disk, 14-22

STORAGE clause with parallel query option, C-5

striping examples, 14-24

summary or rollup, C-4

tablespace

creating, example, 18-19

dedicated temporary, 18-22

temporary, 14-37

TABLESPACE clause

CREATE TABLE command, 14-24

examples, 14-24

Tablespace Manager, 4-14

temporary extent, 18-22

TEMPORARY keyword, 14-37

temporary tablespace, 18-22

optimizing sort, 14-37

striping, 18-22

test

repeatable, 2-12

thrashing, 12-5

thread, 17-3

throughput, 1-3

cost-based approach, A-21

optimizing, 7-6, 7-12

tier

multi, 12-11

single, 12-10

two-tier, 12-10

TIMED_STATISTICS, 21-4

TIMED_STATISTICS parameter, 18-48

TIMESTAMP column

PLAN_TABLE table, 20-4

TKPROF program, 21-3, 21-7

command line parameters, 21-9

editing the output SQL script, 21-16

example of output, 21-15

generating the output SQL script, 21-16

introduction, 4-7

syntax, 21-8

using the EXPLAIN PLAN command, 21-9

TKPROF_TABLE

columns of, 21-18

querying, 21-17

tool

in-house performance, 4-16

TopSessions, 4-11

TOTALQ column

V$QUEUE table, 16-7, 16-9

trace facility, 13-8

Trace, Oracle, 4-12, 22-1

transaction processing monitor, 12-11–12-12

transaction rate, 18-34

transactions

assigning rollback segments, 14-30

discrete, 9-2

serializable, 9-6

transmission time, 3-6

tuning

access path, 2-10

and design, 2-9

application design, 2-8

business rule, 2-7

checkpoints, 14-41

client/server applications, 5-9

contention, 16-1

CPU, 12-1

data design, 2-8

data sources, 4-2

database logical structure, 2-9

decision support systems, 5-4

diagnosing problems, 3-1

distributed databases, 5-7

expectations, 1-10

factors, 3-2

goals, 1-9, 2-12

I/O, 2-11, 14-2

library cache, 13-14

logical structure of database, 8-7

memory allocation, 2-10, 13-2, 13-38

method, 2-1

monitoring registered applications, 4-8, 23-2

multi-threaded server, 16-6

OLTP applications, 5-2

operating system, 2-11, 3-6, 13-4

parallel query, 5-6

parallel server, 5-9

personnel, 1-8

proactive, 2-2

query servers, 16-11, 18-47

reactive, 2-3

shared pool, 13-12, 13-22

sorts, 14-34

SQL, 2-9

SQL and PL/SQL areas, 13-8

System Global Area (SGA), 13-5

two-tier, 12-10

undo block statistic, 16-4

UNION ALL view, 18-11, 18-17

UNIQUE key constraint, 18-53

unique keys

optimization, A-10

searches, A-25

UNIQUENESS column, 8-21

UNIX-based system

performance, 17-5

UNKEEP procedure, 10-6

unlimited extents, 14-28

UNRECOVERABLE option, 18-23, 18-51–18-52

USE_ASYNC_IO parameter

and parallel query, 18-13

USE_CONCAT hint, 7-23

USE_MERGE hint, 7-26

USE_NL hint, 7-25

user/server/memory relationship, 18-26

USER_DUMP_DEST, 21-4

USER_HISTOGRAMS, 7-4

USER_ID column

TKPROF_TABLE, 21-18

USER_INDEXES view, 8-21

USER_TAB_COLUMNS, 7-4

users

memory allocation, 13-8

UTLBSTAT.SQL, 4-8

UTLCHAIN.SQL, 14-31

UTLESTAT.SQ, 4-8

UTLXPLAN.SQL, 20-3

V$ dynamic performance views, 4-6

V$BH view, 13-30

V$DATAFILE view

disk I/O, 14-19

V$DISPATCHER view

using, 16-6

V$FILESTAT view

and parallel query, 18-46

disk I/O, 14-19

PHYRDS column, 14-19

PHYWRTS column, 14-19

V$FIXED_TABLE, 19-2

V$INSTANCE, 19-2

V$LATCH view, 16-2, 19-2

GETS column, 16-14

MISSES column, 16-14

SLEEPS column, 16-14

using, 16-14

V$LATCH_MISSES, 12-9

V$LIBRARYCACHE view, 19-2

NAMESPACE column, 13-14

PINS column, 13-15

RELOADS column, 13-15

using, 13-14

V$LOCK, 19-3

V$MYSTAT, 19-3

V$PARAMETER view

and parallel query, 18-46

V$PQ_SESSTAT view, 18-46

V$PQ_SLAVE view, 18-46

V$PQ_SYSSTAT view, 18-47

V$PQ_TQSTAT view, 18-42, 18-47

V$PROCESS, 19-3

V$QUEUE view

examining wait times, 16-7

identifying contention, 16-9

V$ROLLSTAT, 19-2

V$ROWCACHE view, 19-2

data dictionary cache performance statistics, 13-20

GETMISSES column, 13-20–13-21

GETS column, 13-20–13-21

PARAMETER column, 13-20

using, 13-20

V$SESSION, 19-3

application registration, 4-8, 23-2

V$SESSION_EVENT view, 19-3

network information, 15-2

V$SESSION_WAIT view, 16-2, 19-3

network information, 15-2

V$SESSTAT view, 12-6, 18-48, 19-3

network information, 15-2

using, 13-22

V$SGA, 19-2

V$SGASTAT, 19-2

V$SHARED_POOL_RESERVED view, 13-26

V$SORT_SEGMENT view, 18-34

V$SQLAREA, 12-7, 19-2

application registration, 4-8, 23-2, 23-7

V$SQLTEXT, 19-2

V$SYSSTAT, 12-7

V$SYSSTAT view, 12-6, 19-2

detecting dynamic extension, 14-27

examining recursive calls, 14-27

redo buffer space, 16-12

tuning sorts, 14-35

using, 13-28

V$SYSTEM_EVENT view, 12-9, 16-2, 19-2

V$WAITSTAT view, 16-2, 19-2

reducing free list contention, 16-18

rollback segment contention, 16-4

V733_PLANS_ENABLED parameter, 14-39

view

optimization, A-12

views

instance level, 19-2

tuning, 19-1

virtual memory, 18-4

virtual tables

X$KCBCBH table, 13-35

X$KCBRBH table, 13-32

vmstat UNIX command, 12-4, 18-48

WAIT column

V$QUEUE table, 16-7, 16-9

wait time, 1-3–1-4, 18-28

WHERE clause, 11-6

workload, 1-7, 12-2

adjusting, 18-31

exceeding, 18-28

skew, 18-42

write batch size, internal, 14-44

X$KCBCBH table

buffer cache performance statistics, 13-35

COUNT column, 13-35

enabling use, 13-35

INDX column, 13-35

X$KCBRBH table

buffer cache performance statistics, 13-32

COUNT column, 13-32

enabling use, 13-32

INDX column, 13-32

Prev

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index