In a recent Automatic Workload Repository (AWR) report for your database, you notice a high number of buffer busy waits. The database consists of locally managed tablespaces with free list managed…

Question

Options

• ADecreasing PCTUSED
• BDecreasing PCTFREE
• CIncreasing the number of DBWN process
• DUsing Automatic Segment Space Management (ASSM)
• EIncreasing db_buffer_cache based on t he V$DB_CACHE_ADVICE recommendation

• A
  6% (4)
• B
  11% (7)
• C
  3% (2)
• D
  56% (35)
• E
  23% (14)

Explanation

Buffer busy waits on data blocks in freelist-managed segments happen because multiple sessions compete to insert into the same blocks - only one block is "current" on the freelist at a time, creating a hot-block bottleneck. Switching to ASSM (D) replaces the single freelist with bitmap blocks, allowing concurrent sessions to use different data blocks simultaneously, which directly eliminates the contention.

Why the others are wrong:

• A (Decrease PCTUSED): PCTUSED controls when a block is returned to the freelist; lowering it reduces block reuse but doesn't fix concurrent insert contention.
• B (Decrease PCTFREE): PCTFREE reserves space for row updates; changing it doesn't address multi-session block contention.
• C (More DBWN processes): DBWN writes dirty buffers to disk - useful for write I/O throughput, but irrelevant to hot-block read/insert contention.
• E (Increase db_buffer_cache): This helps when waits stem from insufficient cache (too many physical reads), but the question states contention is the cause, not cache size.

Memory tip: Freelist = one door for everyone (bottleneck); ASSM = many doors (bitmaps). When you see "freelist + buffer busy waits on data blocks," the answer is almost always ASSM.

No community discussion yet for this question.