Tag Archives: direct path read

Performance suggestion on Wait Event in Oracle

Performance Suggestion on Wait Event Present in Oracle

Wait Event Wait Class Suggestions
db file sequential read User I/O -tune indexing
-tune SQL
-tune disks
-increase buffer cache
db file scattered read User I/O -add indexes
-tune SQL
-tune disks
-refresh statistics
-create materialized view
direct path read/direct path read temp User I/O -review P1 (file id), P2 (starting dba), P3 (blocks)
-if reading temporary data
o increase pga_aggregate_target
o increase sort_area_size
-if reading application data
o could be related to parallel query
-cache temporary datafiles at O/S level
global cache cr request Cluster -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 Concurrency -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 Network -may not be a problem
-reduce amount of data transferred across dblink
-tune network between databases
log file sync Commit -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 User I/O -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 Concurrency -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 Network -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 User I/O -tune SQL
-tune indexing
-tune disk I/O
-increase buffer cache
library cache pin Concurrency -if many sessions are waiting, tune shared pool
-if few sessions are waiting, lock is session specific
-find the blocking sessions
-review Metalink Note: 115656.1 for ideas
log buffer space Configuration -increase LOG_BUFFER parameter
-move log files to faster disks
-tune application
-look for poor behavior that updates an entire row when only a few columns change
enq: TX – row lock contention Application -multiple sessions are updating same row
cache buffers chains Concurrency -typically caused by hot blocks
-distribute data activity