Index

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

A

Active Session History, 5.1.2
report
activity over time, 9.3.8
load profile, 9.3.2
top events, 9.3.1
top files, 9.3.7
top Java, 9.3.5
top latches, 9.3.7
top objects, 9.3.7
top PL/SQL, 9.3.4
top sessions, 9.3.6
Top SQL, 9.3.3
using, 9.3
reports, 9.2
allocation of memory, 11.1
applications
deploying, 2.7
design principles, 2.5
development trends, 2.5.7
implementing, 2.5.6
Automatic Database Diagnostic Monitor
actions and rationales of recommendations, 7.1.4
analysis results example, 7.1.5
and DB time, 7.1.1
CONTROL_MANAGEMENT_PACK_ACCESS parameter, 7.2
DBIO_EXPECTED, 7.2
example report, 7.1.5
findings, 7.1.4
overview, 7.1
results, 7.1.4
setups, 7.2
STATISTICS_LEVEL parameter, 7.2
types of problems considered, 7.1.1
types of recommendations, 7.1.4
automatic database diagnostic monitoring, 1.2.1
automatic segment-space management, 4.1.4.1, 10.3.2.2.1, 17.2.6.2
Automatic shared memory management, 12.1
automatic SQL tuning, 1.2.1
automatic undo management, 4.1.2, 4.1.2
Automatic Workload Repository, 1.2.1
compare periods report
about, 8.1
advisory statistics, 8.3.2.8, 8.3.2.16
details, 8.3.2
dictionary cache statistics, 8.3.2.14
instance activity statistics, 8.3.2.6
I/O statistics, 8.3.2.7
latch statistics, 8.3.2.11
library cache statistics, 8.3.2.15
operating system statistics, 8.3.2.2
segment statistics, 8.3.2.12, 8.3.2.13
service statistics, 8.3.2.4
SQL statistics, 8.3.2.5
summary, 8.3.1
supplemental information, 8.3.3
time model statistics, 8.3.2.1
undo statistics, 8.3.2.10
using, 8.3
wait events, 8.3.2.3
wait statistics, 8.3.2.9
configuring, 6.2.1
DBMS_WORKLOAD_REPOSITORY package, 6.2.2.1, 6.2.3.1, 6.2.4.1
default settings, 6.1.4
factors affecting space usage, 6.1.4
managing with APIs, 6.2.2.1, 6.2.3.1, 6.2.4.1
minimizing space usage, 6.1.4
overview, 6.1.1
recommendations for retention period, 6.1.4
reports, 6.3.2.1
retention period, 6.1.4
settings in DBA_HIST_WR_CONTROL view, 6.2.2.4.1
space usage, 6.1.4
statistics collected, 6.1.1
turning off automatic snapshot collection, 6.1.4
unusual percentages in reports, 6.3
views for accessing data, 6.2.6
awrrpt.sql
Automatic Workload Repository report, 6.3.2.1

B

baselines, 1.1.2.2, 6.1.3
performance, 6.1
benchmarking workloads, 2.6.2.2
big bang rollout strategy, 2.7.1
bitmap indexes, 2.5.3.2.2
block cleanout, 10.2.4.2
block size
choosing, 17.2.6
optimal, 17.2.6
bottlenecks
elimination, 1.1.2.4.2
fixing, 3.1
identifying, 3.1
memory, 11.1
resource, 10.3.19.1.2
B-tree indexes, 2.5.3.2.1
buffer busy wait events, 10.2.3, 10.3.2
actions, 10.3.2.2
buffer cache
contention, 10.3.3, 10.3.4, 10.3.11.2
hit ratio, 13.2.2
buffer pools
default cache, 13.3.1.1
KEEP, 13.3.6
KEEP cache, 13.3.1.1
multiple, 13.3
RECYCLE cache, 13.3.1.1
buffer waits
about, 8.3.2.9.1
business logic, 2.4.1.2.2, 2.5.6

C

chained rows, 10.2.4.3
classes
wait events, 5.1.3, 10.1.3.2
client/server applications, 18.4.1.2
column order
indexes, 2.5.3.5
COMPATIBLE initialization parameter, 4.1.1
components
hardware, 2.4.1.1
software, 2.4.1.2
conceptual modeling, 3.1.2
consistency
read, 10.2.4.2
consistent gets from cache statistic, 13.2.2
contention
library cache latch, 10.3.11.3
memory, 10, 11.1
shared pool, 10.3.11.3
tuning, 10
wait events, 10.3.11
context switches, 18.4.1.4.2
CONTROL_FILES initialization parameter, 4.1.1
CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter
enabling automatic database diagnostic monitoring, 7.2
CPUs, 2.4.1.1.1
statistics, 10.1.2.1.3
utilization, 18.4.1
CREATE INDEX statement
PARALLEL clause, 4.2.3
CURSOR_SHARING initialization parameter, 14.2.1
CURSOR_SPACE_FOR_TIME initialization parameter, 14.3.2
cursors
accessing, 14.2.6
sharing, 14.2.6

D

data
and transactions, 2.4.1.2.4
cache, 18.1.1
gathering, 5.1
modeling, 2.5.2
queries, 2.4.2
searches, 2.4.2
database caching modes
configuring, 13.5
default database caching mode, 13.5.1
determining which mode to use, 13.5.3
force full database caching mode
about, 13.5.2
verifying, 13.5.4
database monitoring, 1.2.1
diagnostic, 7.1
database performance
comparing, 8.1
degradation over time, 8.1
Database Resource Manager, 10.1.2.1.3, 18.1.3, 18.1.3, 18.4.2
database tuning
performance degradation over time, 8.1
transient performance problems, 9.1
databases
diagnosing and monitoring, 7.1
size, 2.4.2
statistics, 5.1
db block gets from cache statistic, 13.2.2
db file scattered read wait events, 10.2.3, 10.3.3, 10.3.3
actions, 10.3.3.1, 10.3.4.1
db file sequential read wait events, 10.2.3, 10.3.3, 10.3.4
actions, 10.3.4.1
DB time
metric, 7.1.1
statistic, 5.1.1
DB_BLOCK_SIZE initialization parameter, 4.1.1, 17.2.1.2
DB_CACHE_ADVICE parameter, 13.2.4
DB_CACHE_SIZE initialization parameter, 13.3
DB_DOMAIN initialization parameter, 4.1.1
DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 10.3.3, 17.2.1.1, 17.2.1.2, 17.2.1.3
DB_KEEP_CACHE_SIZE
initialization parameter, 13.3.6
DB_NAME initialization parameter, 4.1.1
DB_nK_CACHE_SIZE initialization parameter, 13.2.4
DB_RECYCLE_CACHE_SIZE
initialization parameter, 13.3.7
DB_WRITER_PROCESSES initialization parameter, 10.3.9.3.1
DBA_HIST views, 6.2.6
DBA_HIST_WR_CONTROL view
Automatic Workload Repository settings, 6.2.2.4.1
DBA_OBJECTS view, 13.3.5.2
DBIO_EXPECTED parameter, 7.2
DBMS_ADDM package
Automatic Database Diagnostic Monitor, 7.3
DBMS_ADVISOR package
setting DBIO_EXPECTED, 7.2
setups for ADDM, 7.2, 7.2
DBMS_RESULT_CACHE, 15.2.1.2
DBMS_SHARED_POOL package
managing the shared pool, 14.3.5
DBMS_WORKLOAD_REPOSITORY package
managing the Automatic Workload Repository, 6.2.2.1, 6.2.3.1, 6.2.4.1
debugging designs, 2.6.4
default cache, 13.3.1.1
deploying applications, 2.7
design principles, 2.5
designs
debugging, 2.6.4
testing, 2.6.4
validating, 2.6.4
development environments, 2.5.6
diagnostic monitoring, 1.2.1, 7.1
introduction, 7.1
dictionary cache, 8.3.2.14
direct path
read events, 10.3.5
read events actions, 10.3.5.2
read events causes, 10.3.5.1
wait events, 10.3.6
write events actions, 10.3.6.2
write events causes, 10.3.6.1
disks
monitoring operating system file activity, 10.1.2.2

E

emergencies
performance, 3.2
Emergency Performance Method, 3.2.1
End to End Application Tracing
action and module names, 2.5.6
enqueue
about, 8.3.2.9.2
enqueue wait events, 10.2.3, 10.3.7
actions, 10.3.7.2
statistics, 10.1.3.4.5
estimating workloads, 2.6.2
benchmarking, 2.6.2.2
extrapolating, 2.6.2.1
EXTENT MANAGEMENT LOCAL
creating temporary tablespaces, 4.1.4.2
extrapolating workloads, 2.6.2.1

F

FAST_START_MTTR_TARGET
and tuning instance recovery, 10.4.3
Fast-Start checkpointing architecture, 10.4.2
Fast-Start Fault Recovery, 10.4, 10.4.2
FILESYSTEMIO_OPTIONS initialization parameter, 18.1.1.2
free buffer wait events, 10.2.3, 10.3.9
free lists, 10.3.2.2.1
full table scans, 10.3.5.2.2
function-based indexes, 2.5.3.2.3

H

hard parsing, 2.5.5
hardware
components, 2.4.1.1
limitations of components, 2.3.3
sizing of components, 2.3.3
HOLD_CURSOR clause, 14.2.6.2
hours of service, 2.4.2
HW enqueue
contention, 10.3.7.2.2

I

idle wait events, 10.3.10
SQL*Net message from client, 10.3.19.1
implementing business logic, 2.4.1.2.2
indexes
adding columns, 2.5.3.1
appending columns, 2.5.3.1
bitmap, 2.5.3.2.2
B-tree, 2.5.3.2.1
column order, 2.5.3.5
costs, 2.5.3.3
creating, 4.2.3
design, 2.5.3
function-based, 2.5.3.2.3
partitioned, 2.5.3.2.4
placement on disk, 17.2.2
reducing I/O, 2.5.3.5
reverse key, 2.5.3.2.5
selectivity, 2.5.3.5
sequences in, 2.5.3.4
serializing in, 2.5.3.4
initialization parameters
CONTROL_FILES, 4.1.1
DB_BLOCK_SIZE, 4.1.1
DB_DOMAIN, 4.1.1
DB_NAME, 4.1.1
OPEN_CURSORS, 4.1.1
PGA_AGGREGATE_TARGET, 4.2.3.1
PROCESSES, 4.1.1
SESSIONS, 4.1.1
STREAMS_POOL_SIZE, 4.1.1, 12.2
In-Memory Column Store
about, 12.4.1
configuring, 12.4
estimating required size, 12.4.3
performance benefits, 12.4.2
sizing, 12.4.4
In-Memory Column Store (IM column store), Preface
instance activity
comparing, 8.3.2.6
instance caging, 18.4
instance configuration
initialization files, 4.1.1
performance considerations, 4.1
instance recovery
Fast-Start Fault Recovery, 10.4.2
performance tuning, 10.4
Internet scalability, 2.3.2
I/O
and SQL statements, 10.3.3.4
contention, 10.1.2.2.1, 10.1.3.2, 10.3.3.2, 10.3.15
excessive I/O waits, 10.3.3.2
monitoring, 10.1.2.2
objects causing I/O waits, 10.3.3.5

K

KEEP buffer pool, 13.3.6
KEEP cache, 13.3.1.1

L

LARGE_POOL_SIZE initialization parameter, 14.4.3
latch contention
library cache latches, 10.2.1.2
shared pool latches, 10.2.1.2
latch free wait events, 10.2.3
actions, 10.3.11.1
latch wait events, 10.3.11
latches, 9.3.7.3
tuning, 1.1.2.3, 10.3.11.3
library cache, 8.3.2.15
latch contention, 10.3.11.3
latch wait events, 10.3.11.1
lock, 10.3.14
pin, 10.3.13
linear scalability, 2.3.3
locks and lock holders
finding, 10.3.7.1
log buffer
space wait events, 10.2.3, 10.3.15
log file
parallel write wait events, 10.3.12
switch wait events, 10.3.16
sync wait events, 10.2.3, 10.3.17
log writer processes
tuning, 17.2.3.2
LOG_BUFFER initialization parameter, 13.4.1
LRU
aging policy, 13.3
latch contention, 10.3.11.3.4

M

managing the user interface, 2.4.1.2.1
max session memory statistic, 14.4.3
MAX_DISPATCHERS initialization parameter, 4.3.1.1
MAXOPENCURSORS clause, 14.2.6.2
memory
hardware component, 2.4.1.1.2
PGA statistics, 8.3.2.8
statistics, 8.3.2.16
memory allocation
importance, 11.1
tuning, 12.2.5
metrics, 6.1
migrated rows, 10.2.4.3
mirroring
redo logs, 17.2.3.3
modeling
conceptual, 3.1.2
data, 2.5.2
workloads, 2.6.3
monitoring
diagnostic, 1.2.1
multiple buffer pools, 13.3

N

NAMESPACE column
V$LIBRARYCACHE view, 14.3.1.1.1
network
hardware component, 2.4.1.1.4
speed, 2.4.2
network communication wait events, 10.3.19
db file scattered read wait events, 10.3.3
db file sequential read wait events, 10.3.3, 10.3.4
SQL*Net message from Dblink, 10.3.19.2
SQL*Net more data to client, 10.3.19.3

O

object-orientation, 2.5.7
OPEN_CURSORS initialization parameter, 4.1.1
operating system
data cache, 18.1.1
monitoring disk I/O, 10.1.2.2
optimization
described
optimizer
introduction, 1.1.3.1
query, 1.1.3.1
Oracle CPU statistics, 10.1.2.1.3
Oracle Enterprise Manager
advisors, 1.2.1
Performance page, 1.2.1
Oracle Forms
control of parsing and private SQL areas, 14.2.6.5
Oracle Managed Files, 17.2.5
tuning, 17.2.5
Oracle Orion
calibration tool parameters, 17.4.4
command-line options, 17.4.4
Oracle performance improvement method, 3.1
steps, 3.1.1

P

package
DBMS_RESULT_CACHE, 15.2.1.2
page table, 18.4.1.1.2
paging, 18.4.1.2
reducing, 12.2.4.1
PARALLEL clause
CREATE INDEX statement, 4.2.3
parameter
RESULT_CACHE_MAX_SIZE, 15.2.1.1
RESULT_CACHE_MODE, 15.2.3
parameters
initialization, 8.3.3.1
parsing
hard, 2.5.5
Oracle Forms, 14.2.6.5
Oracle precompilers, 14.2.6.2
reducing unnecessary calls, 14.2.6
soft, 2.5.5
partitioned indexes, 2.5.3.2.4
PCTFREE parameter, 4.2, 10.2.4.3
PCTUSED parameter, 10.2.4.3, 10.2.4.3
performance
emergencies, 3.2
improvement method, 3.1
improvement method steps, 3.1.1
mainframe, 18.2.3
monitoring memory on Windows, 18.4.1.1.1
tools for diagnosing and tuning, 1.2
UNIX-based systems, 18.2.1
Windows, 18.2.2
Performance Hub active reports
about, 6.4.1
generating, 6.4, 6.4.3
performance problems
transient, 9.1
performance tuning
Fast-Start Fault Recovery, 10.4
instance recovery, 10.4
FAST_START_MTTR_TARGET, 10.4.2
setting FAST_START_MTTR_TARGET, 10.4.3
using V$INSTANCE_RECOVERY, 10.4.2.3
PGA_AGGREGATE_TARGET initialization parameter, 4.1.1, 4.2.3.1, 16.2.1, 18.1.2.2
physical reads from cache statistic, 13.2.2
PRIVATE_SGA variable, 14.4.4
proactive monitoring, 1.1.2.4.1
processes
scheduling, 18.4.1.4.1
PROCESSES initialization parameter, 4.1.1
program global area (PGA)
direct path read, 10.3.5
direct path write, 10.3.6
shared servers, 14.4.1
programming languages, 2.5.6

Q

queries
data, 2.4.2
query optimizer, 1.1.3.1
See optimizer

R

rdbms ipc reply wait events, 10.3.18
read consistency, 10.2.4.2
read wait events
direct path, 10.3.5
scattered, 10.3.3
RECYCLE cache, 13.3.1.1
REDO BUFFER ALLOCATION RETRIES statistic, 13.4.2
redo logs, 4.1.3
buffer size, 10.3.15
mirroring, 17.2.3.3
placement on disk, 17.2.3.2
sizing, 4.1.3
space requests, 10.2.4.1
reducing
contention with dispatchers, 4.3.1.1
paging and swapping, 12.2.4.1
RELEASE_CURSOR clause, 14.2.6.2
resources
allocation, 2.4.1.2.3, 2.5.6
bottlenecks, 10.3.19.1.2
wait events, 10.3.4
response time, 2.4.2
reverse key indexes, 2.5.3.2.5
rollout strategies
big bang approach, 2.7.1
trickle approach, 2.7.1
row cache objects, 10.3.11.3.6

S

sar UNIX command, 18.4.1.1.1
scalability, 2.3.1
factors preventing, 2.3.3, 2.3.3
Internet, 2.3.2
linear, 2.3.3
scattered read wait events, 10.3.3
actions, 10.3.3.1
segment-level statistics, 10.1.3.5
selectivity
ordering columns in an index, 2.5.3.5
sequential read wait events
actions, 10.3.4.1
service hours, 2.4.2
session memory statistic, 14.4.3
SESSIONS initialization parameter, 4.1.1
SGA_TARGET initialization parameter, 4.1.1
and Automatic Shared Memory Management, 12.1.2.1
automatic memory management, 12.1
shared pool contention, 10.3.11.3
shared server
performance issues, 4.3
reducing contention, 4.3
tuning, 4.3
tuning memory, 14.4.1
SHARED_POOL_SIZE initialization parameter, 14.3.1.4, 14.3.1.5
SHOW SGA statement, 12.2.4.2.1
sizing redo logs, 4.1.3
snapshots
about, 6.1.2
soft parsing, 2.5.5
software
components, 2.4.1.2
sort areas
tuning, 16.1.1
SQL statements
waiting for I/O, 10.3.3.4
SQL Tuning Advisor, 1.2.1
SQL*Net
message from client idle events, 10.3.19.1
message from dblink wait events, 10.3.19.2
more data to client wait events, 10.3.19.3
ST enqueue
contention, 10.3.7.2.1
statistics
and STATISTICS_LEVEL initialization parameter, 1.2
baselines, 6.1
consistent gets from cache, 13.2.2
databases, 5.1
db block gets from cache, 13.2.2
dictionary cache, 8.3.2.14
gathering, 5.1
instance activity, 8.3.2.6
I/O, 8.3.2.7
latch, 8.3.2.11
library cache, 8.3.2.15
max session memory, 14.4.3
memory, 8.3.2.16
operating system
comparing, 8.3.2.2
PGA memory, 8.3.2.8
physical reads from cache, 13.2.2
segment, 8.3.2.12, 8.3.2.13
segment-level, 10.1.3.5
service, 8.3.2.4
session memory, 14.4.3
shared server processes, 4.3.2
SQL, 8.3.2.5
time model, 5.1.1, 8.3.2.1
undo, 8.3.2.10
waits, 8.3.2.9
STATISTICS_LEVEL initialization parameter, 5.2.2, 10.1.3.1
and Automatic Workload Repository, 6.2.1
enabling automatic database diagnostic monitoring, 7.2
settings for statistic gathering, 1.2
STREAMS_POOL_SIZE initialization parameter, 4.1.1, 12.2
striping
manual, 17.2.2
swapping, 18.4.1.1.1, 18.4.1.2
reducing, 12.2.4.1
switching processes, 18.4.1.4.1
system architecture, 2.4
configuration, 2.4.2
hardware components, 2.4.1.1
CPUs, 2.4.1.1.1
I/O subsystems, 2.4.1.1.3
memory, 2.4.1.1.2
networks, 2.4.1.1.4
software components, 2.4.1.2
data and transactions, 2.4.1.2.4
implementing business logic, 2.4.1.2.2
managing the user interface, 2.4.1.2.1
user requests and resource allocation, 2.4.1.2.3
System Global Area tuning, 12.2.4.2

T

tables
creating, 4.2
design, 2.5.3
full scans, 10.3.5.2.2
placement on disk, 17.2.2
setting storage options, 4.2
tablespaces, 4.1.4
creating, 4.1.4, 4.1.4.2
temporary, 4.1.4, 4.1.4.2
temporary tablespaces, 4.1.4
creating, 4.1.4.2
testing designs, 2.6.4
thrashing, 18.4.1.2
time model statistics, 5.1.1
comparing, 8.3.2.1
TM enqueue contention, 10.3.7.2.3
tools for performance tuning, 1.2
Top Java
Active Session History report, 9.3.5
top PL/SQL
Active Session History report, 9.3.4
Top Sessions
Active Session History report, 9.3.6
Top SQL
Active Session History report, 9.3.3
transactions and data, 2.4.1.2.4
trickle rollout strategy, 2.7.1
tuning
and bottleneck elimination, 1.1.2.4.2
and proactive monitoring, 1.1.2.4.1
latches, 1.1.2.3, 10.3.11.3
resource contention, 10
shared server, 4.3
sorts, 16.1.1
System Global Area (SGA), 12.2.4.2
TX enqueue contention, 10.3.7.2.4

U

undo management, automatic mode, 4.1.2
UNDO TABLESPACE clause, 4.1.2
UNDO_MANAGEMENT initialization parameter, 4.1.1, 4.1.2
UNDO_TABLESPACE initialization parameter, 4.1.1
UNIX system performance, 18.2.1
user global area (UGA)
shared servers, 4.3, 14.4.1
user requests, 2.4.1.2.3
users
interaction method, 2.4.2
interfaces, 2.5.6
location, 2.4.2
network speed, 2.4.2
number of, 2.4.2
requests, 2.5.6
response time, 2.4.2
UTLCHN1.SQL script, 10.2.4.3

V

V$ACTIVE_SESSION_HISTORY view, 5.1.2, 10.1.3.3
V$BH view, 13.3.5
V$BUFFER_POOL_STATISTICS view, 13.3.4
V$DB_CACHE_ADVICE view, 13.2.1, 13.2.2, 13.2.4, 13.2.5, 13.3.3
V$EVENT_HISTOGRAM view, 10.1.3.3
V$FILE_HISTOGRAM view, 10.1.3.3
V$JAVA_LIBRARY_CACHE_MEMORY view, 14.3.1.2.3
V$JAVA_POOL_ADVICE view, 14.3.1.2.3
V$LIBRARY_CACHE_MEMORY view, 14.3.1.2.2
V$LIBRARYCACHE view
NAMESPACE column, 14.3.1.1.1
V$QUEUE view, 4.3.2
V$ROWCACHE view
performance statistics, 14.3.1.3
V$RSRC_CONSUMER_GROUP view, 10.1.2.1.3
V$SESS_TIME_MODEL view, 5.1.1, 10.1.3.3
V$SESSION view, 10.1.3.3, 10.1.3.4, 10.3
V$SESSION_EVENT view, 10.1.3.3, 10.3
V$SESSION_WAIT view, 10.1.3.3, 10.3
V$SESSION_WAIT_CLASS view, 10.1.3.3
V$SESSION_WAIT_HISTORY view, 10.1.3.3, 10.1.3.3, 10.3
V$SESSTAT view, 10.1.2.1.3, 14.4.3
V$SHARED_POOL_ADVICE view, 14.3.1.2.1
V$SHARED_POOL_RESERVED view, 14.3.6.1
V$SYS_TIME_MODEL view, 5.1.1, 10.1.3.3
V$SYSSTAT view
redo buffer allocation, 13.4.2
using, 13.2.2
V$SYSTEM_EVENT view, 10.1.3.3, 10.3
V$SYSTEM_WAIT_CLASS view, 10.1.3.3
V$TEMP_HISTOGRAM view, 10.1.3.3
V$UNDOSTAT view, 4.1.2
V$WAITSTAT view, 10.1.3.4
validating designs, 2.6.4
views, 2.5.4
DBA_HIST, 6.2.6
vmstat UNIX command, 18.4.1.1.1

W

wait events, 5.1.3
buffer busy waits, 10.3.2
classes, 5.1.3, 10.1.3.2
comparing, 8.3.2.3
contention wait events, 10.3.11
direct path, 10.3.6
enqueue, 10.3.7
free buffer waits, 10.3.9
idle wait events, 10.3.10
latch, 10.3.11
library cache latch, 10.3.11.1
log buffer space, 10.3.15
log file parallel write, 10.3.12
log file switch, 10.3.16
log file sync, 10.3.17
network communication wait events, 10.3.19
rdbms ipc reply, 10.3.18
resource wait events, 10.3.4
Windows performance, 18.2.2
workloads, 2.6.2, 2.6.2.1, 2.6.2.2, 2.6.3, 2.6.4