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 (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