Oracle8i Tuning
Release 8.1.5

A67775-01

Library

Product

Contents

Prev

Index

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z 


A

ABORTED_REQUEST_THRESHOLD procedure, 19-24
access path, 2-10
adaptive multi-user
algorithm for, 26-7
definition, 26-7
aggregate computability check, 31-17
aggregates, 27-18, 28-6, 29-10, 31-23
alert log, 12-3
ALL_HISTOGRAMS view, 7-6
ALL_INDEXES view, 6-16
ALL_OBJECTS view, 19-32
ALL_ROWS hint, 7-38
ALL_TAB_COLUMNS view, 7-5, 7-6
allocation
multi-threaded server, 19-21
of memory, 19-1
ALTER INDEX REBUILD statement, 6-9
ALTER MATERIALIZED VIEW statement, 29-4
enabling query rewrite, 31-4
ALTER SESSION statement
examples, 14-5
SET SESSION_CACHED_CURSORS, 19-18
ALTER SYSTEM DISCONNECT SESSION, 25-22
ALTER SYSTEM statement
CHECKPOINT option, 25-6
MTS_DISPATCHERS parameter, 21-8
ALTER TABLE statement
NOLOGGING option, 27-23
ALWAYS_ANTI_JOIN parameter, 7-22, 7-56
analysis dictionary, 12-4
ANALYZE statement, 11-4, 20-31, 27-20, 27-27, 32-16
analyzing data
for parallel processing, 26-78
AND_EQUAL hint, 6-7, 7-49
APPEND hint, 7-61, 27-22
application design, 2-9
application designer, 1-8
application developer, 1-8
application failover, 25-17, 25-19
applications
client/server, 3-9
decision support, 3-3, 26-3
distributed databases, 3-7
OLTP, 3-1
parallel query, 3-4
parallel server, 3-8
registering with the database, 5-1, 12-7
ARCH process, 21-16
multiple, 26-75
architecture and CPU, 18-10
array interface, 22-2
asynchronous I/O, 26-29
attributes, 30-4
audit trail, 12-4
AUTOEXTEND, 20-28
Average Elapsed Time data view, 15-10

B

B*-tree index, 6-15, 6-19
backups
data warehouse, 11-9
disk mirroring, 26-44
tuning, 20-52
bandwidth, 26-3
Basic Statistics for Parse/Execute/Fetch drilldown data view, 15-18
BEGIN_DISCRETE_TRANSACTION procedure, 9-1, 9-3
benefit of tuning, 2-3
binary files
formatting using Oracle Trace, 15-5
bind variables, 19-16
bitmap
mapping to ROWIDs, 6-17
BITMAP CONVERSION row source, 6-20
bitmap index, 6-13, 6-18, 11-7
creating, 6-16
inlist iterator, 13-23
maintenance, 6-15
size, 6-20
storage considerations, 6-14
when to use, 6-13
BITMAP keyword, 6-16
BITMAP_MERGE_AREA_SIZE parameter, 6-15, 6-19, 7-22
block contention, 2-11
block size, 20-15
bottlenecks
disk I/O, 20-20
memory, 19-1
broadcast
distribution value, 13-8
buffer cache, 2-10
memory allocation, 19-29
partitioning, 19-33
reducing buffers, 19-29
reducing cache misses, 19-29
tuning, 19-25
buffer get, 4-5
buffer not pinned statistics, 19-27
buffer pinned statistics, 19-27
buffer pool
default cache, 19-31
keep cache, 19-32
multiple, 19-31, 19-32
RECYCLE cache, 19-31
syntax, 19-35
BUFFER_POOL clause, 19-35
BUFFER_POOL_name parameter, 19-34
build methods, 29-5
business rule, 1-8, 2-3, 2-7

C

CACHE hint, 7-63
cache hit ratios
increasing, 19-29
callback
failover, 25-23
cardinality, 6-20
CATPARR.SQL script, 19-29
CATPERF.SQL file, 19-37
chained rows, 20-30
channel bandwidth, 17-4
CHECKPOINT option
ALTER SYSTEM statement, 25-6
checkpoints
choosing checkpoint frequency, 20-39
performance, 20-39
redo log maintenance, 20-39
tuning, 20-38
CHOOSE hint, 7-40
CKPT process, 20-41
client/server applications, 3-9, 18-4
CLUSTER hint, 7-43
clusters, 6-24
collections in Oracle Trace, 15-4, 15-22
columns, to index, 6-4
common joins, 31-9
COMPATIBLE parameter, 6-16, 26-66
and parallel query, 7-57
complete refresh, 32-4
composite indexes, 6-5
composite partitioning, 26-46
examples of, 13-17
performance considerations, 26-49
concurrent users
increasing the number of, 26-10
CONNECT BY, 13-26
connection load balancing
multi-threaded server, 23-5
connection manager, 22-4
in the multi-threaded server, 23-3
connection multiplexing
with the multi-threaded server, 23-3
connection pooling, 21-9
with the multi-threaded server, 23-3
connection-to-dispatcher
recommended ratio, 23-3
consistency, read, 18-7
consistent gets statistic, 19-26, 21-4, 21-22
consistent mode, TKPROF, 14-13
constraints, 6-10, 30-10, 31-22
contention
disk access, 20-20
free lists, 21-21
memory, 19-1
memory access, 21-1
redo allocation latch, 21-19
redo copy latches, 21-19
rollback segments, 21-3
tuning, 21-1
tuning resource, 2-11
context area, 2-10
context switching, 18-4
cost-based optimization, 7-3, 11-8, 27-27
parallel execution, 27-27
procedures for Plan Stability, 7-32
upgrading to, 7-34
cost-based rewrite, 31-2
count column, SQL trace, 14-13
CPU
checking utilization, 18-3
detecting problems, 18-3
system architecture, 18-10
tuning, 18-1
utilization, 18-1, 26-3
cpu column, SQL trace, 14-13
CPU Statistics data view, 15-11
CPU Statistics for Parse/Execute/Fetch drilldown data view, 15-18
CPU_COUNT initialization parameter, 25-16
CREATE CLUSTER statement, 6-26
CREATE DIMENSION statement, 30-3
CREATE INDEX statement, 27-21
examples, 20-37
NOSORT option, 20-37
CREATE MATERIALIZED VIEW statement, 29-4
enabling query rewrite, 31-4
CREATE OUTLINE statement, 7-28
CREATE SNAPSHOT statement, 29-2
CREATE TABLE AS SELECT, 11-3, 27-18, 27-19, 27-31
CREATE TABLE statement
STORAGE clause, 20-24
TABLESPACE clause, 20-24
CREATE TABLESPACE statement, 20-24
CREATE_BITMAP_AREA_SIZE parameter, 6-15, 6-18
CREATE_STORED_OUTLINES parameter, 7-28
current column, SQL trace, 14-13
current mode, TKPROF, 14-13
CURSOR_NUM column
TKPROF_TABLE, 14-19
CURSOR_SPACE_FOR_TIME parameter
setting, 19-17

D

data
comparative, 12-5
design, tuning, 2-7
partitioning, 26-45
sources for tuning, 12-1
volume, 12-2
data block size, 20-15
data cache, 24-2
data dictionary, 12-2
data dictionary cache, 2-10, 19-20
Data Mart Builder, 28-9
Data Mart Designer, 28-9
data marts, 28-1
data sufficiency check, 31-13
data views in Oracle Trace, 15-6
Average Elapsed Time, 15-10
CPU Statistics, 15-11
Disk Reads, 15-9
Disk Reads/Execution Ratio, 15-9
Disk Reads/Logical Reads Ratio, 15-9
Disk Reads/Rows Fetched Ratio, 15-9
Execute Elapsed Time, 15-10
Fetch Elapsed Time, 15-11
Logical Reads, 15-9
Logical Reads/Rows Fetched Ratio, 15-9
Number of Rows Processed, 15-11
Parse Elapsed Time, 15-10
Parse/Execution Ratio, 15-10
Re-Parse Frequency, 15-10
Rows Fetched/Fetch Count Ratio, 15-11
Rows Sorted, 15-11
Sorts in Memory, 15-11
Sorts on Disk, 15-11
Total Elapsed Time, 15-10
Waits by Average Wait Time, 15-12
Waits by Event Frequency, 15-12
Waits by Total Wait Time, 15-12
data warehouse, 28-1
ANALYZE statement, 11-4
backup, 11-9
bitmap index, 11-7
data mart, 28-1
dimension tables
(lookup tables), 28-5
fact tables (detail tables), 28-6
features, 11-1
hierarchies
rolling up and drilling down, 30-2
introduction, 11-1
Oracle Parallel Server, 11-6
parallel aware optimizer, 11-6
parallel index creation, 11-3
parallel load, 11-4
partition, 11-4
partitioned table, 26-47
recovery, 11-9
star schema, 11-8
database
buffers, 19-29
layout, 26-36
staging database, 28-1
statistics, gathering, 7-16
database administrator (DBA), 1-8
Database Connection event, 15-5
database writer process (DBWn)
behavior on checkpoints, 20-39
tuning, 18-8, 26-76
DATAFILE clause, 20-24
datafile placement on disk, 20-21
DATE_OF_INSERT column
TKPROF_TABLE, 14-18
db block gets statistic, 19-26, 21-4, 21-22
DB_BLOCK_BUFFERS parameter, 19-29, 19-34, 20-43
DB_BLOCK_LRU_LATCHES parameter, 19-34, 19-40
DB_BLOCK_MAX_DIRTY_TARGET
parameter, 20-41
DB_BLOCK_SIZE parameter
and parallel query, 26-28
tuning backups, 20-53
DB_FILE_DIRECT_IO_COUNT
parameter, 20-53
DB_FILE_MULTIBLOCK_READ_COUNT parameter, 7-21, 20-37, 26-28
DBA locking, 27-13
DBA_DATA_FILES view, 26-79
DBA_EXTENTS view, 26-79
DBA_HISTOGRAMS view, 7-6
DBA_INDEXES view, 6-16
DBA_OBJECTS view, 19-32
DBA_TAB_COLUMNS view, 7-5, 7-6
DBMS_APPLICATION_INFO package, 5-2, 5-3
DBMS_MVIEW package, 32-6
DBMS_MVIEW.REFRESH procedure, 32-3
DBMS_MVIEW.REFRESH_ALL_MVIEWS procedure, 32-3
DBMS_MVIEW.REFRESH_DEPENDENT procedure, 32-3
DBMS_OLAP package, 29-22, 32-15, 32-17
DBMS_OLAP.RECOMMEND_MV procedure, 29-22
DBMS_SHARED_POOL package, 10-3, 19-12, 19-24
DBMS_STATISTICS package, 31-3
DBMS_STATS package, 7-9, 32-16
DBMSPOOL.SQL script, 10-3, 19-12
DBMSUTL.SQL, 5-2
decision support, 3-3
processes, 27-3
query characteristics, 26-20
systems (DSS), 1-2
tuning, 26-3
with OLTP, 3-5
decision support systems (DSS)
dimensions, 30-1
decomposition of SQL statements, 8-3
default cache, 19-31
degree of parallelism
and adaptive multi-user, 26-7
setting, 26-7
demand rate, 1-5
dependencies
dimensions, 29-21
materialized views, 29-21
DEPTH column
TKPROF_TABLE, 14-19
design dictionary, 12-4
designing and tuning, 2-10
detail report in Oracle Trace, 15-6
detail tables, 28-6
same as fact tables, 28-6
details property sheet in Oracle Trace, 15-14
device bandwidth, 17-4
evaluating, 20-16
device latency, 17-5
diagnosing tuning problems, 17-1
dictionary-mapped tablespaces, 20-28
dimension tables, 11-8, 28-6
(lookup tables), 28-5
normalized, 30-7
dimensions, 30-1, 30-10, 31-22
altering, 30-11
creating, 30-3
dependencies, 29-21
dimension tables (lookup tables), 28-5
dropping, 30-12
hierarchies
rolling up and drilling down, 30-2
in a decision support system, 30-1
using, 30-1
validating, 30-10
direct-load insert, 26-76
external fragmentation, 27-13
disabled constraint, 6-10
Discoverer, 28-9
discrete transactions
example, 9-4
processing, 9-2, 9-3
when to use, 9-1
disk affinity
and parallel query, 27-16
disabling with MPP, 26-39
with MPP, 26-71
disk column, SQL trace, 14-13
Disk Reads data view, 15-9
Disk Reads/Execution Ratio data view, 15-9
Disk Reads/Logical Reads Ratio data view, 15-9
Disk Reads/Rows Fetched Ratio data view, 15-9
DISK_ASYNCH_IO parameter, 26-29
DISKRATIO
to distribute backup I/O, 20-52
disks
contention, 20-20, 20-21
distributing I/O, 20-20
I/O requirements, 20-4
layout options, 20-15
monitoring OS file activity, 20-17
number required, 20-4
placement of datafiles, 20-21
placement of redo log files, 20-21
reducing contention, 20-20
speed characteristics, 20-3
testing performance, 20-5
dispatcher processes (Dnnn), 21-9
dispatcher-to-connection
recommended ratio, 23-3
distributed databases, 3-7
distributed query, 8-1, 8-10
distributing I/O, 20-20, 20-24
distribution
hints for, 7-58
DIUTIL package, 10-4
DML_LOCKS parameter, 26-26, 26-28
DOMAIN INDEX
and EXPLAIN PLAN, 13-24
domain indexes
using, 6-23
drilldown data views in Oracle Trace, 15-16
Basic Statistics for Parse/Execute/Fetch, 15-18
CPU Statistics for Parse/Execute/Fetch, 15-18
Parse Statistics, 15-18
Row Statistics for Execute/Fetch, 15-19
drilling down, 30-2
hierarchies, 30-2
DROP MATERIALIZED VIEW statement, 29-4
prebuilt tables, 29-17
DROP_BY_CAT
procedure of OUTLN_PKG, 7-30
DROP_UNUSED
procedure of OUTLN_PKG, 7-30
DSS database
dimensions, 30-1
DSS memory, 26-20
duration events in Oracle Trace, 15-5
dynamic extension, 20-26
avoiding, 20-28
dynamic performance views
enabling statistics, 14-4
for tuning, 16-1

E

elapsed column, SQL trace, 14-13
enabled constraint, 6-10
enforced constraint, 6-10
ENFORCED mode, 31-19
ENQUEUE_RESOURCES parameter, 26-26, 26-28
Enterprise Manager, 12-7
equijoin, 4-9
errors
common tuning, 2-14
during discrete transactions, 9-3
estimating materialized view size, 32-20
EVALUATE_UTILIZATION_W package, 32-21
events in Oracle Trace, 15-5
examples
ALTER SESSION statement, 14-5
CREATE INDEX statement, 20-37
DATAFILE clause, 20-24
discrete transactions, 9-4
execution plan, 4-7
EXPLAIN PLAN output, 4-7, 13-25, 14-15
full table scan, 4-8
indexed query, 4-8
NOSORT option, 20-37
SET TRANSACTION statement, 20-29
SQL trace facility output, 14-15
STORAGE clause, 20-24
table striping, 20-24
TABLESPACE clause, 20-24
executable code as data source, 12-4
Execute Elapsed Time data view, 15-10
execution plans, 13-1
examples, 4-7, 14-6
parallel operations, 27-30
Plan Stability, 7-25
preserving with Plan Stability, 7-25
TKPROF, 14-7, 14-10
expectations for tuning, 1-9
EXPLAIN PLAN statement, 31-20
and DOMAIN INDEX, 13-24
and full partition-wise joins, 13-21
and partial partition-wise joins, 13-19
and partitioned objects, 13-13
examples of output, 4-7, 13-25, 14-15
formatting output for, 13-24
introduction, 12-6
invoking with the TKPROF program, 14-10
parallel query, 27-30
PLAN_TABLE, 13-2
query parallelization, 26-73
restrictions, 13-28
SQL decomposition, 8-6
extents
size, 26-66
temporary, 26-70
unlimited, 20-28

F

fact tables, 11-8, 28-6
same as detail tables, 28-6
facts, 30-1
failover, 11-6, 25-17
application, 25-19
BASIC, 25-21
callback, 25-23
METHOD, 25-21
PRECONNECT, 25-22
restrictions, 25-24
tuning, 25-23
TYPE, 25-21
FAST FULL SCAN, 11-3
fast full scan, 6-8
fast refresh, 32-5
FAST_START_IO_TARGET initialization parameter
controlling checkpoints with, 20-40
recovery time and the, 25-4
FAST_START_PARALLEL_ROLLBACK initialization parameter, 25-16
FAST_START_PARALLEL_ROLLBACK parameter
recovery parameter, 26-25
fast-start checkpoints
controlling checkpoints, 20-40
FAST_START_IO_TARGET initialization parameter, 25-4
LOG_CHECKPOINT_INTERVAL initialization parameter, 25-4
LOG_CHECKPOINT_TIMEOUT initialization parameter, 25-3
fast-start on-demand rollback, 25-15
fast-start parallel rollback, 25-15
Fetch Elapsed Time data view, 15-11
file storage, designing, 20-5
FILESPERSET parameter
tuning backups, 20-53
FIRST_ROWS hint, 7-39, 26-24
FORMAT statement in Oracle Trace, 15-19, 15-20
formatter tables in Oracle Trace, 15-5
formatting
EXPLAIN PLAN output, 13-24
fragmentation, external, 27-13
free lists
adding, 21-22
contention, 21-21
reducing contention, 21-22
FREELISTS parameter, 26-75
FULL hint, 6-7, 7-42
full partition-wise joins, 13-21, 26-52
full table scan, 4-8
function-based indexes, 6-12

G

GATHER_ INDEX_STATS
procedure of DBMS_STATS, 7-9
GATHER_DATABASE_STATS
procedure of DBMS_STATS, 7-9
GATHER_SCHEMA_STATS
procedure of DBMS_STATS, 7-9
GATHER_TABLE_STATS
procedure of DBMS_STATS, 7-9
GC_FILES_TO_LOCKS parameter, 27-13
GC_ROLLBACK_LOCKS parameter, 27-14
GC_ROLLBACK_SEGMENTS parameter, 27-14
GETMISSES, V$ROWCACHE table, 19-20
GETS, V$ROWCACHE table, 19-20
global index, 26-74
goals for tuning, 1-9, 2-12
GROUP BY
decreasing demand for, 27-7
NOSORT, 20-38
grouping
compatibility check, 31-16
conditions, 31-23
GV$FILESTAT view, 26-78

H

handle
user, 25-23
hash
distribution value, 13-8
hash area, 2-11, 27-3
HASH hint, 7-44
hash join, 26-22, 27-3
hash partitioning, 13-14, 26-46
examples of, 13-15
HASH_AJ hint, 7-44, 7-55, 7-56
HASH_AREA_SIZE parameter, 7-21, 26-21
and parallel execution, 26-21
example, 27-7
relationship to memory, 27-7
HASH_JOIN_ENABLED parameter, 7-22
HASH_MULTIBLOCK_IO_COUNT parameter, 7-22, 26-29
HASH_SJ hint, 7-44, 7-49, 7-56
hashed partitions
performance considerations, 26-48
hashing, 6-25
HASHKEYS parameter
CREATE CLUSTER statement, 6-26
hierarchies, 30-2
multiple, 30-6
rolling up and drilling down, 30-2
hints, 7-36
access methods, 7-41
ALL_ROWS, 7-38
AND_EQUAL, 6-7, 7-49
as used in outlines, 7-26
CACHE, 7-63
CLUSTER, 7-43
degree of parallelism, 7-57
FIRST_ROWS, 7-39
FULL, 6-7, 7-42
HASH, 7-44
HASH_AJ, 7-44, 7-49, 7-55
HASH_SJ, 7-56
how to use, 7-36
INDEX, 6-7, 7-44, 7-52
INDEX_ASC, 7-46
INDEX_DESC, 7-46, 7-47
INDEX_FFS, 7-48
join operations, 7-52
MERGE_AJ, 7-49, 7-55
MERGE_SJ, 7-56
NO_MERGE, 7-65
NOCACHE, 7-64
NOPARALLEL hint, 7-58
optimization approach and goal, 7-38
ORDERED, 7-51, 7-52
PARALLEL hint, 7-57
parallel query option, 7-57
PQ_DISTRIBUTE, 7-58
PUSH_SUBQ, 7-66
query rewrite, 31-4, 31-21
ROWID, 7-43
RULE, 7-41
STAR, 7-52
USE_CONCAT, 7-50
USE_MERGE, 7-54
USE_NL, 7-53
histogram
creating, 7-5
number of buckets, 7-6
viewing, 7-6
HOLD_CURSOR, 19-10

I

ID column
PLAN_TABLE table, 13-4
in avoiding dynamic extension, 20-28
INDEX hint, 6-7, 6-16, 7-44
index join, 27-7
INDEX_ASC hint, 7-46
INDEX_COMBINE hint, 6-7, 6-16
INDEX_DESC hint, 7-46, 7-47
INDEX_FFS hint, 6-9, 7-48, 11-3
indexes
avoiding the use of, 6-7
bitmap, 6-13, 6-16, 6-18, 11-7
choosing columns for, 6-4
composite, 6-5
creating in parallel, 27-20
design, 2-9
domain, using, 6-23
enforcing uniqueness, 6-10
ensuring the use of, 6-6
example, 4-8
fast full scan, 6-8, 11-3
function-based, 6-12
global, 26-74
local, 26-74
modifying values of, 6-5
non-unique, 6-10
parallel, 11-3
parallel creation, 27-20, 27-21
parallel local, 27-20
partitioning, 26-46
placement on disk, 20-22
rebuilding, 6-9
recreating, 6-9
selectivity of, 6-4
statistics, gathering, 7-9
STORAGE clause, 27-21
when to create, 6-2
INITIAL extent size, 26-66, 27-13
initialization parameters
COMPATIBLE, 28-10, 31-4
CPU_COUNT, 25-16
FAST_START_PARALLEL_ROLLBACK, 25-16
JOB_QUEUE_INTERVAL, 28-10, 32-6
JOB_QUEUE_PROCESSES, 28-10, 32-6
LOG_CHECKPOINT_INTERVAL, 25-4
LOG_CHECKPOINT_TIMEOUT, 25-3
MAX_DUMP_FILE_SIZE, 14-4
OPTIMIZER_MODE, 7-24, 7-38, 28-10, 28-11, 31-4, 32-13
OPTIMIZER_PERCENT_PARALLEL, 28-11, 32-14
ORACLE_TRACE_COLLECTION_SIZE, 28-11
ORACLE_TRACE_ENABLE, 28-11
ORACLE_TRACE_FACILITY_NAME, 28-11
ORACLE_TRACE_FACILITY_PATH, 28-11
PARALLEL_MAX_SERVERS, 25-14, 28-11, 32-13
PRE_PAGE_SGA, 19-5
QUERY_REWRITE_ENABLED, 28-10, 31-4
QUERY_REWRITE_INTEGRITY, 28-10
RECOVERY_PARALLELISM, 25-14
SESSION_CACHED_CURSORS, 19-18
SORT_AREA_SIZE, 28-11, 32-13
SQL_TRACE, 14-5
TIMED_STATISTICS, 14-4
USER_DUMP_DEST, 14-4
UTL_FILE_DIR, 28-10, 32-6
initialization parameters in Oracle Trace, 15-21
inlists, 7-46, 7-50
INSERT
append, 7-61
functionality, 27-22
internal write batch size, 20-43
intra transaction recovery, 25-16
I/O
analyzing needs, 20-2
asynchronous, 26-29
balancing, 20-22
distributing, 20-20, 20-24
insufficient, 17-5
multiple buffer pools, 19-32
parallel execution, 26-3
Statistics for Parse/Execute/Fetch, 15-18
striping to avoid bottleneck, 26-37
testing disk performance, 20-5
tuning, 2-11, 20-2
ISOLATION LEVEL, 9-5

J

join compatibility, 31-9
joins, 29-10
full partition-wise, 26-52
parallel, and PQ_DISTRIBUTE hint, 7-58
partial partition-wise, 26-57
partition-wise, 26-52
partition-wise, examples of full, 13-21
partition-wise, examples of partial, 13-19
partition-wise, full, 13-21

K

keep cache, 19-32
KEEP procedure, 10-5
keys, 28-6

L

large pool, 20-54
LARGE_POOL_SIZE, 20-54
LARGE_POOL_SIZE parameter, 26-12
latches
contention, 2-12, 18-9
redo allocation latch, 21-16
redo copy latches, 21-16
least recently used list (LRU), 18-8
library cache, 2-10
memory allocation, 19-15
tuning, 19-13
listening queue, 22-3
load
parallel, 11-4, 26-68
load balancing, 11-6, 20-22
multi-threaded server, 23-5
LOBS
temporary, 19-30
local index, 26-74
local striping, 26-40
lock contention, 2-11
log, 21-15
log buffer tuning, 2-10, 19-7
log writer process (LGWR) tuning, 20-21, 20-40
LOG_BUFFER parameter, 19-6, 20-40
and parallel execution, 26-26
setting, 21-16
LOG_CHECKPOINT_INTERVAL initialization parameter, 20-39
recovery time, 25-4
LOG_CHECKPOINT_TIMEOUT initialization parameter, 20-39
recovery time, 25-3
LOG_SIMULTANEOUS_COPIES parameter, 21-19
LOG_SMALL_ENTRY_MAX_SIZE parameter, 21-19
LOGGING option, 26-76
Logical Reads data view, 15-9
Logical Reads/Rows Fetched Ratio data view, 15-9
logical structure of database, 2-9
long waits
definition, 20-47
lookup tables, 28-5
(dimension tables), 28-5
LRU
aging policy, 19-31
latch, 19-34, 19-40
latch contention, 19-40, 21-20

M

Management Information Base (MIB), 12-5
manual refresh, 32-6
manual striping, 26-39
massively parallel system, 26-3
materialized views, 11-2
build methods, 29-5
containing only joins, 29-13
creating, 29-3
delta joins, 31-12
dependencies, 29-21
dropping, 29-17, 29-23
estimating size, 32-20
naming, 29-4
partitioning, 29-18
prebuilt, 29-4
query rewrite
hints, 31-4, 31-21
initialization parameters, 31-4
matching join graphs, 29-6
privileges, 31-4
refresh dependent, 32-8
refresh options, 29-7
refreshing, 29-8
refreshing all, 32-8
registration, 29-16
restrictions, 29-6
rewrites
enabling, 31-4
schema design guidelines, 28-7
security, 29-22
selecting views to create, 29-22
single table aggregate, 29-12
uses for, 28-2
max session memory statistic, 19-22
MAX_DUMP_FILE_SIZE, 14-4
SQL Trace parameter, 14-4
MAXEXTENTS keyword, 26-66, 27-13
MAXOPENCURSORS, 19-10
MAXOPENFILES parameter
tuning backups, 20-53
media recovery, 26-70
memory
configure at 2 levels, 26-20
insufficient, 17-4
process classification, 27-3
reducing usage, 19-42
tuning, 2-10
virtual, 26-20
memory allocation
and MTS, 19-21
buffer cache, 19-29
importance, 19-1
library cache, 19-15
shared SQL areas, 19-15
sort areas, 20-34
tuning, 19-2, 19-41
users, 19-6
memory/user/server relationship, 27-2
MERGE hint, 7-64
MERGE_AJ hint, 7-49, 7-55, 7-56
MERGE_SJ hint, 7-56
message rate, 17-5
method
applying, 2-12
tuning, 2-1
tuning steps, 2-4
MIB, 12-5
migrated rows, 20-30
mirroring
disks, 26-44
redo log files, 20-21
monitoring, 12-5
parallel processing, 26-78
MPP
disk affinity, 26-39
MTS
large pool, 19-21
shared pool, 19-21
MTS_DISPATCHERS parameter, 21-8, 21-9
MTS_MAX_DISPATCHERS parameter, 21-8, 23-3
MTS_MAX_SERVERS parameter, 21-12, 23-4
MTS_SERVERS parameter, 23-4
setting, 21-13
multi-block reads, 20-27
MULTIBLOCK_READ_COUNT parameter, 26-66
multiple archiver processes, 26-75
multiple buffer pools, 19-31, 19-32, 19-35
multiple hierarchies, 30-6
multiple user handles, 25-23
multiplexing
with multi-threaded server, 23-1
multi-purpose applications, 3-5
multi-threaded server, 27-3
benefits, 23-1
configuring dispatchers, 23-3
connection pooling, 23-3
context area size, 2-10
definition, 23-1
large pool, 19-21
performance issues, 23-8
reducing contention, 21-5
setting up, 23-1
shared pool, 19-21
shared pool and, 19-21
tuning, 21-5, 23-1
tuning memory, 23-6
views with statistics for, 23-7

multi-threaded server
connection multiplexing, 23-3
multi-tier systems, 18-12

N

NAMESPACE column
V$LIBRARYCACHE table, 19-13
nested loop join, 27-3
nested query, 27-18
network
array interface, 22-2
bandwidth, 17-5
constraints, 17-5
detecting performance problems, 22-1
prestarting processes, 22-2
problem solving, 22-2
Session Data Unit, 22-3, 23-5
tuning, 22-1
NEXT extent, 27-13
NO_EXPAND hint, 7-50
NO_INDEX hint, 7-48
NO_MERGE hint, 7-65
NO_PUSH_JOIN_PRED hint, 7-66
NOAPPEND hint, 7-62, 27-23
NOARCHIVELOG mode, 26-77
NOCACHE hint, 7-64
NOLOGGING option, 26-71, 26-76, 27-19, 27-21, 27-23
NOPARALLEL attribute, 27-17
NOPARALLEL hint, 7-58
NOPARALLEL_INDEX hint, 7-63
NOREWRITE hint, 7-51, 31-4, 31-21
NOSORT option, 20-37, 20-38
NT performance, 24-4
Number of Rows Processed data view, 15-11

O

OBJECT_INSTANCE column
PLAN_TABLE table, 13-4
OBJECT_NAME column
PLAN_TABLE table, 13-4
OBJECT_NODE column
PLAN_TABLE table, 13-4
OBJECT_OWNER column
PLAN_TABLE table, 13-4
OBJECT_TYPE column
PLAN_TABLE table, 13-4
../../server.815/a67846/toc.htmStmtFetch, 25-24
../../server.815/a67846/toc.htmTransRollback, 25-24
online transaction processing (OLTP), 1-2, 3-1
processes, 27-3
with decision support, 3-5
OPEN_CURSORS parameter
allocating more private SQL areas, 19-9
increasing cursors per session, 19-15
operating system
data cache, 24-2
monitoring disk I/O, 20-17
monitoring tools, 12-3
striping, 26-38
tuning, 2-12, 17-6, 19-4
OPERATION column
PLAN_TABLE, 13-4, 13-9
OPTIMAL storage parameter, 20-30
optimization
choosing an approach and goal for, 7-3
cost-based, 7-3
parallel aware, 11-6
query rewrite
enabling, 31-4
hints, 31-4, 31-21
matching join graphs, 29-6
query rewrites
privileges, 31-4
rule-based, 7-24
optimizer, 31-2
Plan Stability, 7-25
OPTIMIZER column
PLAN_TABLE, 13-4
OPTIMIZER_FEATURES_ENABLED parameter, 7-21
OPTIMIZER_INDEX_CACHING
for index optimization, 7-22
OPTIMIZER_INDEX_COST_ADJ
for index optimization, 7-22
OPTIMIZER_MODE, 7-3, 7-4, 7-21, 7-24, 7-38, 11-8, 27-27
OPTIMIZER_PERCENT_PARALLEL parameter, 7-21, 11-7, 26-23, 27-30
OPTIMIZER_SEARCH_LIMIT parameter, 7-22
OPTIONS column
PLAN_TABLE table, 13-4
Oracle Designer, 28-9
Oracle Expert, 2-1, 12-11
Oracle Express Objects, 28-9
Oracle Forms, 14-5
control of parsing and private SQL areas, 19-10
Oracle Network Manager, 22-3
Oracle Parallel Server, 3-8, 11-6
CPU, 18-13
disk affinity, 27-16
parallel execution, 27-13
parallel load, 26-69
ST enqueue, 27-12
synchronization points, 2-8
Oracle Parallel Server Management (OPSM), 12-12
Oracle Performance Manager, 12-10
Oracle Precompilers
control of parsing and private SQL areas, 19-10
Oracle Sales Analyzer, 28-9
Oracle Server
client/server configuration, 3-9
configurations, 3-6
events, 15-5
Oracle striping, 26-39
Oracle Trace, 15-1, 19-39, 32-16
accessing collected data, 15-5
binary files, 15-5
collection results, 15-25
collections, 15-4, 15-22
command-line interface, 15-19
data views, 15-6
Average Elapsed Time, 15-10
CPU Statistics, 15-11
Disk Reads, 15-9
Disk Reads/Execution Ratio, 15-9
Disk Reads/Logical Reads Ratio, 15-9
Disk Reads/Rows Fetched Ratio, 15-9
Execute Elapsed Time, 15-10
Fetch Elapsed Time, 15-11
Logical Reads, 15-9
Logical Reads/Rows Fetched Ratio, 15-9
Number of Rows Processed, 15-11
Parse Elapsed Time, 15-10
Parse/Execution Ratio, 15-10
Re-Parse Frequency, 15-10
Rows Fetched/Fetch Count Ratio, 15-11
Rows Sorted, 15-11
Sorts in Memory, 15-11
Sorts on Disk, 15-11
Total Elapsed Time, 15-10
Waits by Average Wait Time, 15-12
Waits by Event Frequency, 15-12
Waits by Total Wait Time, 15-12
deleting files, 15-21
details property sheet, 15-14
drilldown data views, 15-16, 15-18
Basic Statistics for Parse/Execute/Fetch, 15-18
CPU Statistics for Parse/Execute/Fetch, 15-18
Parse Statistics, 15-18
Row Statistics for Execute/Fetch, 15-19
duration events, 15-5
events, 15-5
FORMAT statement, 15-19, 15-20
formatter tables, 15-5
formatting data, 15-25
Oracle Trace Data Viewer, 15-6
parameters, 15-21
point events, 15-5
predefined data views, 15-6
reporting utility, 15-6, 15-26
SQL statement property sheet, 15-14
START statement, 15-19, 15-20
STOP statement, 15-19, 15-20
stored procedures, 15-24
using to collect workload data, 15-3
viewing data, 15-12
Oracle Trace Data Viewer, 15-6
Oracle Trace Manager, 15-4, 15-22
used for formatting collections, 15-5
ORACLE_TRACE_COLLECTION_NAME parameter, 15-21, 15-22
ORACLE_TRACE_COLLECTION_PATH parameter, 15-21
ORACLE_TRACE_COLLECTION_SIZE parameter, 15-21
ORACLE_TRACE_ENABLE parameter, 15-21, 15-22
ORACLE_TRACE_FACILITY_NAME parameter, 15-21, 15-22
ORACLE_TRACE_FACILITY_PATH parameter, 15-21
order
preserving, 13-26
ORDER BY, 13-26
decreasing demand for, 27-7
ORDERED hint, 7-51
ORDERED_PREDICATES hint, 7-67
OTHER column
PLAN_TABLE table, 13-5
outer joins, 31-22
outlines
assigning category names to, 7-28
CREATE OUTLINE statement, 7-28
creating and using, 7-28
creating, using, 7-28
dropping, 7-30
dropping unused, 7-30
execution plans and Plan Stability, 7-25
hints, 7-26
managing with OUTLN_PKG, 7-30
matching with SQL statements, 7-27
reassigning categories, 7-31
storage requirements, 7-27
tables, moving, 7-31
using, 7-28
using to move to the cost-based optimizer, 7-32
viewing data for, 7-29
OUTLN_PKG
package to manage outlines, 7-30
overhead, process, 27-3
overloaded disks, 20-20
oversubscribing resources, 27-5, 27-9

P

packages
DBMS_APPLICATION_INFO, 5-2, 5-3
DBMS_SHARED_POOL, 10-3
DBMS_TRANSACTION, 9-4
DIUTIL, 10-4
OUTLN_PKG, 7-30
registering with the database, 5-1, 12-7
STANDARD, 10-4
page table, 18-4
paging, 17-4, 18-4, 26-84, 27-5, 27-31
library cache, 19-15
rate, 26-20
reducing, 19-4
SGA, 19-41
subsystem, 27-5
parallel aware optimizer, 11-6
PARALLEL clause, 27-22, 27-23
RECOVER statement, 25-14
PARALLEL CREATE INDEX statement, 26-25
PARALLEL CREATE TABLE AS SELECT, 11-3
external fragmentation, 27-13
resources required, 26-25
parallel execution, 3-4
adjusting workload, 27-8
cost-based optimization, 27-27
detecting performance problems, 27-1
hints, 7-57
index creation, 27-20
introduction, 26-2
I/O parameters, 26-28
maximum processes, 27-2
parallel server, 27-13
plans, 27-30
process classification, 26-37, 26-39, 26-72, 27-4
query servers, 21-14
resource parameters, 26-20
rewriting SQL, 27-18
solving problems, 27-17
space management, 27-12
tuning, 26-1
tuning physical database layout, 26-36
tuning query servers, 21-14
understanding performance issues, 27-2
PARALLEL hint, 7-57, 27-17, 27-22, 27-30
parallel index, 27-21
creation, 11-3
parallel joins
and PQ_DISTRIBUTE hint, 7-58
parallel load, 11-4
example, 26-68
Oracle Parallel Server, 26-69
using, 26-63
parallel partition-wise joins
performance considerations, 26-61
parallel recovery, 25-14
parallel server, 3-8
disk affinity, 27-16
parallel execution tuning, 27-13
tuning, 12-12
PARALLEL_ADAPTIVE_MULTI_USER parameter, 26-7, 26-32
PARALLEL_AUTOMATIC_TUNING parameter, 26-4
PARALLEL_BROADCAST_ENABLE parameter, 26-24
PARALLEL_EXECUTION_MESSAGE_SIZE parameter, 26-23
PARALLEL_MAX_SERVERS initialization parameter, 25-14, 26-10
PARALLEL_MAX_SERVERS parameter, 25-14, 26-9, 26-11, 27-6
and parallel execution, 26-9
PARALLEL_MIN_PERCENT parameter, 26-10, 26-19
PARALLEL_MIN_SERVERS parameter, 26-11
PARALLEL_SERVER_INSTANCES
and parallel execution, 26-19
PARALLEL_THREADS_PER_CPU parameter, 26-4, 26-8
parallelism
degree, overriding, 27-17
enabing for tables and queries, 26-8
setting degree of, 26-7
parameter file, 12-4
PARENT_ID column
PLAN_TABLE table, 13-4
Parse Elapsed Time data view, 15-10
Parse Statistics drilldown data view, 15-18
Parse/Execution Ratio data view, 15-10
parsing, 18-6
Oracle Forms, 19-10
Oracle Precompilers, 19-10
reducing unnecessary calls, 19-9
partial partition-wise joins, 26-57
partition
pruning, 26-51
partition elimination, 8-6
partition view, 8-6, 11-4
PARTITION_VIEW_ENABLED parameter, 8-7
partitioned objects
and EXPLAIN PLAN, 13-13
partitioned table, 11-4
data warehouse, 26-47
example, 26-66
partitioning
by hash, 13-14
by range, 13-13
composite, 26-46
data, 26-45
distribution value, 13-8
examples of, 13-15
examples of composite, 13-17
hash, 26-46
indexes, 26-46
range, 26-46
start and stop columns, 13-15
partition-wise joins, 26-52
benefits of, 26-60
full, 13-21
full, and EXPLAIN PLAN output, 13-21
parallel, performance considerations for, 26-61
partial, 26-57
partial, and EXPLAIN PLAN output, 13-19
PCM lock, 27-13
PCTFREE, 2-11, 20-31
PCTINCREASE parameter, 20-36
and SQL.BSQ file, 20-33
PCTUSED, 2-11, 20-31
performance
client/server applications, 3-9
decision support applications, 3-3
different types of applications, 3-1
distributed databases, 3-7
evaluating, 1-10
key factors, 17-2
mainframe, 24-4
monitoring registered applications, 5-1, 12-7
NT, 24-4
OLTP applications, 3-1
Parallel Server, 3-8
UNIX-based systems, 24-4
Performance Manager, 12-10
Performance Monitor, NT, 18-4
PHYRDS column
V$FILESTAT table, 20-19
physical database layout, 26-36
physical reads statistic, 19-26
PHYWRTS column
V$FILESTAT table, 20-19
ping UNIX command, 12-3
pinging, 2-12
PINS column
V$LIBRARYCACHE table, 19-14
Plan Stability, 7-25
limitations of, 7-25
preserving execution plans, 7-25
procedures for the cost-based optimizer, 7-32
use of hints, 7-25
PLAN_TABLE table
ID column, 13-4
OBJECT_INSTANCE column, 13-4
OBJECT_NAME column, 13-4
OBJECT_NODE column, 13-4
OBJECT_OWNER column, 13-4
OBJECT_TYPE column, 13-4
OPERATION column, 13-4
OPTIMIZER column, 13-4
OPTIONS column, 13-4
OTHER column, 13-5
PARENT_ID column, 13-4
POSITION column, 13-5
REMARKS column, 13-3
SEARCH_COLUMNS column, 13-4
STATEMENT_ID column, 13-3
structure, 13-2
TIMESTAMP column, 13-3
PL/SQL
package, 12-7
tuning PL/SQL areas, 19-7
point events in Oracle Trace, 15-5
POOL attribute, 21-9
POSITION column
PLAN_TABLE table, 13-5
POST_TRANSACTION option, 25-22
PQ_DISTRIBUTE hint, 7-58
PRE_PAGE_SGA parameter, 19-5
prebuilt materialized view, 29-4
PRIMARY KEY constraint, 6-10, 27-21
private SQL areas, 19-9
PRIVATE_SGA variable, 23-6
proactive tuning, 2-1
process
and memory contention in parallel processing, 26-10
classes of parallel execution, 26-37, 26-39, 26-72, 27-4
dispatcher process configuration, 21-9
DSS, 27-3
maximum number, 17-6, 27-2
maximum number for parallel query, 27-2
OLTP, 27-3
overhead, 27-3
prestarting, 22-2
priority, 24-2
scheduler, 24-2
scheduling, 18-4
processing, distributed, 3-9
pruning
partition, 26-51
using DATE columns, 26-52
PRVTPOOL.PLB, 10-4
PUSH_JOIN_PRED hint, 7-65

Q

queries
avoiding the use of indexes, 6-7
distributed, 8-1, 8-10
enabling parallelism for, 26-8
ensuring the use of indexes, 6-6
query column, SQL trace, 14-13
query delta joins, 31-11
query plan, 13-1
query rewrite
correctness, 31-18
enabling, 31-3, 31-4
hints, 31-4, 31-21
initialization parameters, 31-4
matching join graphs, 29-6
methods, 31-6
privileges, 31-4
query server process
tuning, 21-14

R

RAID, 20-25, 26-45, 26-70
random reads, 20-5
random writes, 20-5
range
distribution value, 13-8
range partitioning, 13-13, 26-46
examples of, 13-15
performance considerations, 26-47
raw device, 24-2
reactive tuning, 2-3
read consistency, 18-7
READ_CLIENT_INFO
procedure in DBMS_APPLICATION_INFO, 5-2
READ_MODULE
procedure in DBMS_APPLICATION_INFO, 5-2
read/write operations, 20-5
REBUILD, 6-9
RECOMMEND_MV function, 32-17
RECOMMEND_MV_W function, 32-17
record keeping, 2-13
RECOVER statement
PARALLEL clause, 25-14
recovery
data warehouse, 11-9
effect of checkpoints, 20-39
media, with striping, 26-44
parallel
intra transaction recovery, 25-16
parallel processes for, 25-14
PARALLEL_MAX_SERVERS initialization parameter, 25-14
setting number of processes to use, 25-14
RECOVERY_PARALLELISM initialization parameter, 25-14
recursive calls, 14-14, 20-26
recursive SQL, 10-1
RECYCLE cache, 19-31
redo allocation latch, 21-16, 21-19
REDO BUFFER ALLOCATION RETRIES, 21-15
redo copy latches, 21-17, 21-19
choosing how many, 21-17
redo log buffer tuning, 19-6
redo log files
mirroring, 20-21
placement on disk, 20-21
reducing
buffer cache misses, 19-29
contention
dispatchers, 21-6
OS processes, 24-2
query servers, 21-15
redo log buffer latches, 21-15
shared servers, 21-10
data dictionary cache misses, 19-20
library cache misses, 19-15
paging and swapping, 19-4
rollback segment contention, 21-4
unnecessary parse calls, 19-9
reference tables, 28-5
refresh options, 29-7
registering applications with database, 5-1, 12-7
regression, 27-29, 27-31
Relational Access Manager, 28-9
RELEASE_CURSOR, 19-10
RELOADS column
V$LIBRARYCACHE table, 19-14
REMARKS column
PLAN_TABLE table, 13-3
remote SQL statement, 8-1
Re-Parse Frequency data view, 15-10
reparsing, 18-6
resource
adding, 1-4
consumption, parameters affecting, 26-20
consumption, parameters affecting parallel DML/DDL, 26-25
limiting for users, 26-10
limits, 26-9
oversubscribing, 27-5
oversubscription, 27-9
parallel query usage, 26-20
tuning contention, 2-11
response time, 1-2, 1-3
optimizing, 7-4, 7-39
REWRITE hint, 7-50, 31-4, 31-21
rewrites
hints, 31-21
initialization parameters, 31-4
privileges, 31-4
query optimization
hints, 31-4, 31-21
matching join graphs, 29-6
RMAN
tuning for backups, 20-52
roles in tuning, 1-7
rollback segments, 18-8, 26-25
assigning to transactions, 20-29
choosing how many, 21-4
contention, 21-3
creating, 21-4
detecting dynamic extension, 20-26
dynamic extension, 20-29
ROLLBACK_SEGMENTS parameter, 26-25
rollbacks
fast-start on-demand, 25-15
fast-start parallel, 25-15
rolling up a hierarchy, 30-2
round-robin
distribution value, 13-8
Row Statistics for Execute/Fetch drilldown data views, 15-19
ROWID hint, 7-43
ROWIDs
mapping to bitmaps, 6-17
rows column, SQL trace, 14-13
Rows Fetched/Fetch Count Ratio data view, 15-11
Rows Sorted data view, 15-11
RowSource event, 15-5
RULE hint, 7-41, 27-27
rule-based optimization, 7-24

S

sar UNIX command, 18-4, 26-83
scalability, 11-6, 18-8
scalable operations, 26-72
schema design guidelines
materialized views, 28-7
schema statistics
gathering, 7-14
SEARCH_COLUMN column
PLAN_TABLE table, 13-4
segments, 20-26
selectivity, index, 6-4
sequence cache, 2-10
sequential reads, 20-5
sequential writes, 20-5
serializable transactions, 9-5
server/memory/user relationship, 27-2
service time, 1-2, 1-3
Session Data Unit (SDU), 22-3, 23-5
session memory statistic, 19-22
SESSION_CACHED_CURSORS parameter, 18-7, 19-18
SET TRANSACTION statement, 20-29
SET_ACTION
procedure in DBMS_APPLICATION_INFO, 5-2
SET_CLIENT_INFO
procedure in DBMS_APPLICATION_INFO, 5-2
SET_MODULE
procedure in DBMS_APPLICATION_INFO, 5-2
SGA size, 19-7, 26-20
SGA statistics, 16-2
shared pool, 2-10
contention, 2-11
keeping objects pinned in, 10-3
multi-threaded server and, 19-21
tuning, 19-10, 19-23
shared servers
configuring, 23-4
processes, 21-13
shared SQL areas
finding large areas, 10-5
identical SQL statements, 10-2
keeping in the shared pool, 10-3
memory allocation, 19-15
statements considered, 10-1
SHARED_POOL_RESERVED_SIZE parameter, 19-24, 19-25
SHARED_POOL_SIZE parameter, 19-20, 19-25
allocating library cache, 19-15
tuning the shared pool, 19-21
short waits
definition, 20-47
SHOW SGA statement, 19-6
Simple Network Management Protocol (SNMP), 12-5
single table aggregate requirements, 29-13
single tier, 18-11
SIZES procedure, 10-5
skew
workload, 27-32
SNMP, 12-5
sort areas
memory allocation, 20-34
process local area, 2-11
SORT_AREA_RETAINED_SIZE parameter, 19-41, 20-36
SORT_AREA_SIZE parameter, 6-15, 7-21, 19-41, 26-22
and parallel execution, 26-22
tuning sorts, 20-35
SORT_MULTIBLOCK_READ_COUNT parameter, 20-37, 26-29
sorts
(disk) statistic, 20-34
(memory) statistic, 20-34
avoiding on index creation, 20-37
tuning, 20-34
Sorts in Memory data view, 15-11
Sorts on Disk data view, 15-11
source data for tuning, 12-1
space management, 26-70
parallel execution, 27-12
reducing transactions, 27-12
spin count, 18-9
SPINCOUNT parameter, 21-2
SQL area tuning, 19-7
SQL Loader, 26-63
SQL Parse event, 15-5
SQL statement property sheet in Oracle Trace, 15-14
SQL statements
avoiding the use of indexes, 6-7
decomposition, 8-3
ensuring the use of indexes, 6-6
inefficient, 18-7
matching with outlines, 7-27
modifying indexed data, 6-5
recursive, 10-1
reparsing, 18-6
tuning, 2-10
SQL text match, 31-7, 31-22
SQL trace facility, 12-6, 14-2, 14-6, 19-8, 19-39
enabling, 14-4
example of output, 14-15
output, 14-12
parse calls, 19-8
statement truncation, 14-14
steps to follow, 14-3
trace file, 12-3
trace files, 14-4
SQL*Plus script, 12-7
SQL_STATEMENT column
TKPROF_TABLE, 14-18
SQL_TRACE parameter, 14-5
SQL.BSQ file, 20-33
SQLUTLCHAIN.SQL, 12-7
ST enqueue, 27-12
staging database, 28-1
staging file, 28-1
STALE_TOLERATED mode, 31-19
STANDARD package, 10-4
STAR hint, 7-52
star query, 11-8
star schema, 11-8
star transformation, 7-66, 11-8
STAR_TRANSFORMATION hint, 7-66, 11-8
STAR_TRANSFORMATION_ENABLED parameter, 7-67, 11-8
start columns
in partitioning and EXPLAIN PLAN, 13-15
START statement in Oracle Trace, 15-19, 15-20
STATEMENT_ID column
PLAN_TABLE table, 13-3
statistics, 16-2, 27-31, 31-23
consistent gets, 19-26, 21-4, 21-22
current value, 16-4
db block gets, 19-26, 21-4
dispatcher processes, 21-7
gathering with DBMS_STATS, 7-9
generating, 7-7
generating for cost-based optimization, 7-7
max session memory, 19-22
operating system, 26-83
physical reads, 19-26
query servers, 21-14
rate of change, 16-5
session memory, 19-22
shared server processes, 21-10, 21-15
sorts (disk), 20-34
sorts (memory), 20-34
undo block, 21-3
when to generate, 7-24
stop columns
in partitioning and EXPLAIN PLAN, 13-15
STOP statement in Oracle Trace, 15-19, 15-20
storage
file, 20-5
STORAGE clause
CREATE TABLE statement, 20-24
examples, 20-24
modifying parameters, 20-33
modifying SQL.BSQ, 20-33
OPTIMAL, 20-30
parallel query, 27-21
stored outlines
assigning category names to, 7-28
creating, using, 7-28
dropping, 7-30
dropping unused, 7-30
execution plans and Plan Stability, 7-25
hints, 7-26
managing with OUTLN_PKG, 7-30
matching with SQL statements, 7-27
reassigning categories, 7-31
storage requirements, 7-27
tables, moving, 7-31
using, 7-28
viewing data for, 7-29
stored procedures
in Oracle Trace, 15-24
KEEP, 10-5
READ_MODULE, 5-5
registering with the database, 5-1, 12-7
SET_ACTION, 5-3
SET_CLIENT_INFO, 5-4
SET_MODULE, 5-2
SIZES, 10-5
striping, 20-22, 26-38
analyzing, 26-41
and disk affinity, 27-16
example, 26-63
examples, 20-24
local, 26-40
manual, 20-23, 26-38, 26-39
media recovery, 26-44
operating system, 26-38
operating system software, 20-25
Oracle, 26-39
temporary tablespace, 26-70
subquery
correlated, 27-18
summary advisor, 32-14
summary management, 28-3
swapping, 17-4, 18-4
library cache, 19-15
reducing, 19-4
SGA, 19-41
switching processes, 18-4
symmetric multiprocessor, 26-3
System Global Area tuning, 19-5
system-specific Oracle documentation
software constraints, 17-6
USE_ASYNC_IO, 26-30

T

table queue, 26-80
table statistics
gathering, 7-11
tables
detail tables, 28-6
dimension tables (lookup tables), 28-5
enabling parallelism for, 26-8
fact tables, 28-6
formatter, in Oracle Trace, 15-5
placement on disk, 20-22
striping examples, 20-24
tablespace
creating, example, 26-64
dedicated temporary, 26-70
dictionary-mapped, 20-28
temporary, 20-36
TABLESPACE clause, 20-24
CREATE TABLE statement, 20-24
TAPE_ASYNCH_IO parameter, 26-29
TCP.NODELAY option, 22-3
temporary extent, 26-70
TEMPORARY keyword, 20-36
temporary LOBS, 19-30
temporary tablespace
optimizing sort, 20-36
size, 26-70
striping, 26-70
testing, 2-13
thrashing, 18-4
thread, 24-2
throughput, 1-3
optimizing, 7-4, 7-38
tiers, 18-11
TIMED_STATISTICS parameter, 14-4, 24-1, 26-79
SQL Trace, 14-4
TIMESTAMP column
PLAN_TABLE table, 13-3
TKPROF program, 14-2, 14-6, 19-39
editing the output SQL script, 14-17
example of output, 14-15
generating the output SQL script, 14-17
introduction, 12-6
syntax, 14-7
using the EXPLAIN PLAN statement, 14-10
TKPROF_TABLE, 14-18
querying, 14-17
TNSNAMES.ORA, 25-21
Total Elapsed Time data view, 15-10
Trace, Oracle, 15-1
transaction processing monitor, 18-12, 18-13
TRANSACTIONAL option
SHUTDOWN, 25-22
transactions
assigning rollback segments, 20-29
discrete, 9-1
rate, 27-12
serializable, 9-5
TRANSACTIONS parameter, 26-25
transmission time, 17-5
transparent application failover, 25-19
Transparent Gateway, 8-11
triggers
in tuning OLTP applications, 4-4
TRUSTED mode, 31-19
tuning
access path, 2-10
and design, 2-10
application design, 2-9
business rule, 2-7
checkpoints, 20-38
client/server applications, 3-9
contention, 21-1
CPU, 18-1
data design, 2-7
data sources, 12-1
database logical structure, 2-9
decision support systems, 3-3
diagnosing problems, 17-1
distributed databases, 3-7
expectations, 1-9
factors, 17-1
goals, 1-9, 2-12
I/O, 2-11, 20-2
large pool for MTS, 19-21
library cache, 19-13
logical structure, 6-3
memory allocation, 2-10, 19-2, 19-41
method, 2-1
monitoring registered applications, 5-1, 12-7
multi-threaded server, 21-5
OLTP applications, 3-1
operating system, 2-12, 17-6, 19-4
parallel execution, 3-4, 26-36
parallel server, 3-8
personnel, 1-7
proactive, 2-1
production systems, 2-4
query servers, 21-14
reactive, 2-3
shared pool, 19-10, 19-21
shared pool for MTS, 19-21
sorts, 20-34
SQL, 2-10
SQL and PL/SQL areas, 19-7
System Global Area (SGA), 19-5
two-phase commit, 26-25
two-tier, 18-11

U

undo block statistic, 21-3
UNION ALL view, 8-7
UNIQUE constraint, 6-10, 27-21
UNIQUE index, 6-16
uniqueness, 6-10
UNIX system performance, 24-4
unlimited extents, 20-28
unusable, 32-4
update frequency, 32-2
update window, 32-2
UPDATE_BY_CAT
procedure of OUTLN_PKG, 7-30, 7-31
upgrading
to the cost-based optimizer, 7-34
USE_CONCAT hint, 7-50
USE_MERGE hint, 7-54
USE_NL hint, 7-53
USE_STORED_OUTLINES parameter, 7-28
user
handles, 25-23
memory allocation, 19-7
user resources
limiting, 26-10
USER_DUMP_DEST, 14-4
SQL Trace parameter, 14-4
USER_HISTOGRAMS view, 7-6
USER_ID column
TKPROF_TABLE, 14-19
USER_INDEXES view, 6-16
USER_OULTINE_HINTS
view for stored outline hints, 7-29
USER_OUTLINES
view for stored outlines, 7-29
USER_TAB_COLUMNS view, 7-5, 7-6
user/server/memory relationship, 27-2
UTLBSTAT.SQL, 12-7
UTLCHAIN.SQL, 20-31
UTLDTREE.SQL, 12-7
UTLESTAT.SQ, 12-7
UTLLOCKT.SQ, 12-7
UTLXPLAN.SQL, 13-2

V

V$ dynamic performance views, 12-5
V$BACKUP_ASYNC_IO
for tuning backups, 20-46
view, description, 20-47
V$BACKUP_SYNC_IO
for tuning backups, 20-46
view, description, 20-47
V$BH view, 19-29
V$BUFFER_POOL_STATISTICS view, 19-38, 19-40
V$DATAFILE view, 20-19
V$DISPATCHER view, 21-6
V$FAST_START_SERVERS view, 11-10, 25-16
V$FAST_START_TRANSACTIONS view, 11-11, 25-16
V$FILESTAT view
and parallel query, 26-79
disk I/O, 20-19
PHYRDS column, 20-19
PHYWRTS column, 20-19
V$FIXED_TABLE view, 16-2
V$INSTANCE view, 16-2
V$LATCH view, 16-2, 21-2, 21-17
V$LATCH_CHILDREN view, 19-40
V$LATCH_MISSES view, 18-9
V$LIBRARYCACHE view, 16-2
NAMESPACE column, 19-13
PINS column, 19-14
RELOADS column, 19-14
V$LOCK view, 16-3
V$MYSTAT view, 16-3
V$PARAMETER view, 26-80
V$PQ_SESSTAT view, 26-79, 27-31
V$PQ_SYSSTAT view, 27-31
V$PQ_TQSTAT view, 26-80, 27-32
V$PROCESS, 16-3
V$PX_PROCESS view, 26-78
V$PX_SESSION view, 26-78
V$PX_SESSTAT view, 26-78
V$QUEUE view, 21-7, 21-10
V$RESOURCE_LIMIT view, 21-2
V$ROLLSTAT view, 16-2
V$ROWCACHE view, 16-2
GETMISSES column, 19-20
GETS column, 19-20
performance statistics, 19-19
using, 19-19
V$SESSION view, 16-3, 25-22
application registration, 5-1, 12-7
V$SESSION_EVENT view, 16-3
network information, 22-1
V$SESSION_WAIT view, 16-3, 19-39, 21-2
network information, 22-1
V$SESSTAT view, 16-3, 18-5, 26-80, 26-83
network information, 22-2
using, 19-22
V$SGA view, 16-2
V$SGASTAT view, 16-2
V$SHARED_POOL_RESERVED view, 19-25
V$SORT_SEGMENT view, 27-12
V$SORT_USAGE view, 4-5, 16-2
V$SQLAREA, 16-2, 18-7
application registration, 5-1, 5-5, 12-7
resource-intensive statements, 4-5
V$SQLTEXT view, 16-2
V$SYSSTAT view, 16-2, 18-5, 18-6, 26-75, 26-80
detecting dynamic extension, 20-27
examining recursive calls, 20-27
redo buffer allocation, 21-15
redo buffer allocation retries, 26-26
tuning sorts, 20-34
using, 19-26
V$SYSTEM_EVENT view, 16-2, 18-9, 21-1, 21-2
V$WAITSTAT view, 16-2, 21-2
reducing free list contention, 21-21
rollback segment contention, 21-3
views
instance level, 16-2
materialized views
dependencies, 29-21
multi-threaded server, 23-7
tuning, 16-1
USER_OUTLINE_HINTS, 7-29
USER_OUTLINES, 7-29
V$FAST_START_SERVERS, 25-16
V$FAST_START_TRANSACTIONS, 25-16
virtual memory, 26-20
vmstat UNIX command, 18-4, 26-83

W

wait time, 1-3, 1-4, 27-5
Waits by Average Wait Time data view, 15-12
Waits by Event Frequency data view, 15-12
Waits by Total Wait Time data view, 15-12
warehouse, 28-1
advisor, 32-2
refresh, 29-7, 32-2
refresh, tips, 32-9
workload, 1-6, 18-1
adjusting, 27-8
exceeding, 27-5
skew, 27-32
write batch size, 20-43


Prev
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents