Oracle7 Tuning, release 7.3.3 Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents



Go to previous file in sequence

Index



A

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

B

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

C

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

D

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

E

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

F

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

G

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

H

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

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

J

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

K

KEEP procedure, 10-6
keys, A-24

L

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

M

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

N

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

O

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

P

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

Q

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

R

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

S

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

T

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

U

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

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

W

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

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


Go to previous file in sequence
Prev
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents