Oracle wait problems and potential solutions

Wait Problem Potential Fix Sequential Read Indicates many index reads—tune the code (especially joins) Scattered Read Indicates many full table scans—tune the code; cache small tables Free Buffer Increase the DB_CACHE_SIZE; shorten the checkpoint; tune
the code Buffer Busy Segment header—add freelists or freelist groups Buffer Busy Data block—separate “hot” data; use reverse key indexes; use
smaller blocks; increase initrans (debatable); reduce block
popularity; make I/O faster Buffer Busy Undo header—add rollback segments or areas Buffer Busy Undo block—commit more; larger rollback segments or areas Latch Free Investigate the detail (a listing later in this chapter includes fixes) Enqueue – ST Use LMTs or preallocate large extents Enqueue – HW Preallocate extents above the high water mark Enqueue – TX4 Increase initrans or use a smaller block size on the table or index Enqueue – TX6 Fix the code that is making the block unsharable (use v$lock to find) Enqueue – TM Index foreign keys; check application locking of tables Log Buffer Space Increase the log buffer; use faster disks for the redo logs Log File Switch Archive destination slow or full; add more or larger redo logs Log file sync Commit more records at a time; use faster redo log disks; use
raw devices Write complete waits Add database writers; checkpoint more often; buffer cache too small