Question 1
AdvancedTuning the Buffer Cache · Diagnosing and resolving performance problems related to the buffer cache
A financial services application is experiencing severe performance degradation during its nightly batch processing. An AWR report for the period shows the top wait event is 'latch: cache buffers chains', with a high number of consistent gets. The primary table involved in the batch job is frequently accessed via a non-selective index. Which action is the most direct and effective way to mitigate this specific latch contention?
Answer and explanation
Correct answer: C
'latch: cache buffers chains' contention often occurs when multiple sessions are trying to access blocks protected by the same latch, a situation commonly caused by 'hot blocks'. This is frequently a result of inefficient SQL, such as repeatedly scanning a large number of blocks through a non-selective index. The most effective solution is to fix the root cause: the SQL statement. Tuning the SQL to use a more selective index or a full table scan (if appropriate) will reduce the logical I/O and the contention on the same set of blocks. Increasing the buffer cache might slightly alleviate the symptoms but doesn't solve the underlying SQL inefficiency. Reducing block size is a major architectural change and not a direct solution. Increasing _DB_BLOCK_LRU_LATCHES is a deprecated action and not recommended.
