Top wait events in Oracle and the suggestions for DBA

Top Oracle Wait events and the suggestion to DBAs

==================================================================

Wait Event / Suggestions

************************************************************************************

db file sequential read

􀂃 tune indexing

􀂃 tune SQL

􀂃 tune disks

􀂃 increase buffer cache

db file scattered read

􀂃 add indexes

􀂃 tune SQL

􀂃 tune disks

􀂃 refresh statistics

􀂃 create materialized view

direct path read / direct path read temp

􀂃 review P1 (file id), P2 (starting dba), P3 (blocks)

􀂃 if reading temporary data

o increase pga_aggregate_target

(workarea_size_policy=AUTO)

o increase sort_area_size

(workarea_size_policy<>AUTO)

􀂃 if reading application data

o could be related to parallel query

o ensure DISK_ASYNC_IO = TRUE

􀂃 cache temporary datafiles at O/S level

global cache cr request

􀂃 RAC event similar to buffer busy waits

􀂃 tune SQL to request less data

􀂃 tune network latency between RAC nodes

􀂃 localize data access

buffer busy waits / read by other session

􀂃 tune SQL

􀂃 tune indexing

􀂃 we often see this event along with full table scans

􀂃 review P1 (file id), P2 (block id) for hot blocks

􀂃 if the SQL is inserting data, consider increasing

FREELISTS and/or INITRANS

􀂃 if the waits are on segment header blocks, consider

increasing extent sizes

SQL*Net more data from dblink

􀂃 may not be a problem

􀂃 reduce amount of data transferred across dblink

􀂃 tune network between databases

log file sync

􀂃 tune applications to commit less often

􀂃 tune disks where redo logs exist

􀂃 try using nologging / unrecoverable options

􀂃 log buffer could be too large

direct path write / direct path write temp

􀂃 review P1 (file id), P2 (starting dba), P3 (blocks)

􀂃 similar approaches as “direct path read”

􀂃 could be related to direct path loads

library cache lock

􀂃 need to find the session holding the lock

􀂃 look for DML manipulating an object being accessed

􀂃 if the session is trying to recompile PL/SQL, look for

other sessions executing the code

􀂃 Review Metalink Note: 122793.1 for other ideas

SQL*Net more data to client

􀂃 may not be a problem

􀂃 reduce amount of data being returned by query

􀂃 tune network access between client and database

db file parallel read / db file parallel write

􀂃 tune SQL

􀂃 tune indexing

􀂃 tune disk I/O

􀂃 increase buffer cache

library cache pin

􀂃 if many sessions are waiting, tune shared pool

􀂃 if few sessions are waiting, lock is session specific

o find the blocking sessions

o review Metalink Note: 115656.1 for ideas

log buffer space

􀂃 increase LOG_BUFFER parameter

􀂃 move log files to faster disks

􀂃 tune application

o use NOLOGGING

o look for poor behavior that updates an

entire row when only a few columns change

enq: TX - row lock contention (10g) / enqueue (9i)

􀂃 multiple sessions are updating same row

cache buffers chains

􀂃 typically caused by hot blocks

􀂃 distribute data activity