Examine the details of the Top 5 Timed Events in the following Automatic Workloads Repository (AWR) report: What are three possible causes for the latch-related wait events?

Question

Exhibit

Options

• AThe size of the shared pool is too small.
• BCursors are not being shared.
• CA large number COMMITS are being performed.
• DThere are frequent logons and logoffs.
• EThe buffers are being read into the buffer cache, but some other session is changing the buffers.

• A
  69% (18)
• C
  19% (5)
• E
  12% (3)

Explanation

Latch-related wait events in Oracle (such as 'latch: library cache' or 'latch: shared pool') indicate contention for latches, which are lightweight serialization mechanisms protecting internal memory structures. Three root causes are: (A) A small shared pool forces Oracle to constantly age out and reload parsed SQL, increasing library cache latch contention as sessions compete to parse. (B) When cursors are not shared-typically because SQL uses literals instead of bind variables-Oracle performs repeated hard parses, each requiring exclusive access to library cache latches and creating heavy contention. (D) Frequent logons and logoffs generate bursts of activity in the shared pool (session allocation, authentication lookups, cursor creation), all of which require latches. Option C (COMMITs) primarily drives 'log file sync' waits, not latch waits. Option E describes 'buffer busy waits', a separate wait class unrelated to latches.

No community discussion yet for this question.