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
Idle Event	Ignore it

更多推荐