Setting initialization parameters incorrectly often results in various types of performance issues that show up as general “waits” or “latch waits” in a STATSPACK report. The following tables identify some waits and latch waits and their potential fixes.
| Wait Problem | Potential Fix |
| Free buffer | Increase the DB_CACHE_SIZE; shorten the checkpoint; tune the code. |
| Buffer busy | Segment header: Add freelists or freelist groups or use ASSM. |
| Buffer busy | Data block: separate hot data; use reverse key indexes; small block sizes. |
| Buffer busy | Data block: increase initrans and/or maxtrans. |
| Buffer busy | UNDO header: use automatic undo management. |
| Buffer busy | UNDO block: commit more; use automatic undo management. |
| Latch free | Investigate the detail. |
| Log buffer space | Increase the log buffer; use faster disks for the redo logs. |
| Scattered read | Indicates many full table scans: tune the code; cache small tables. |
| Sequential read | Indicates many index reads: tune the code (especially joins). |
| Write complete waits | Adds database writers; checkpoint more often; buffer cache too small. |
Some latch problems have often been bug related in the past, so make sure you check Metalink for issues related to latches. Any of the latches that have a hit ratio less than 99 percent should be investigated.
| Latch Problem | Potential Fix |
| Library cache | Use bind variables; adjust the SHARED_POOL_SIZE. |
| Shared pool | Use bind variables; adjust the SHARED_POOL_SIZE. |
| Row cache objects | Increase the shared pool. This is not a common problem. |
| Cache buffers chain | Shouldn’t be a problem in 11g. If you get this latch wait, it means you need to reduce logical I/O rates by tuning and minimizing the I/O requirements of the SQL involved. High I/O rates could be a sign of a hot block (meaning a block highly accessed). Cache buffer lru chain latch contention can be resolved by increasing the size of the buffer cache and thereby reducing the rate at which new blocks are introduced into the buffer cache. Multiple buffer pools, if it’s found they are needed, can help reduce contention. You can also create additional cache buffer lru chain latches by adjusting the configuration parameter DB_BLOCK_LRU_LATCHES. You may be able to reduce the load on the cache buffer chain latches by increasing the configuration parameter. _DB_BLOCK_HASH_BUCKETS may need to be increased or set to a prime number, but this should not be needed in 11g. Keep in mind there are now in memory updates (IMU) to consider as well. |
Leave a Reply