Tuning advice on Wait Event present in Oracle
List the various wait events present in Oracle with wait class defined and suggestion on tunning the wait events
| 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 (workarea_size_policy=AUTO) o increase sort_area_size -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 | 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 -use NOLOGGING -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 |
Pingback: Understanding wait event helps during performance analysis | Smart way of Technology