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 segments.
On further investigation, you find that buffer busy waits is caused by contention on data blocks.
Which option would you consider first to decrease the wait event immediately?
A.
Decreasing PCTUSED
B.
Decreasing PCTFREE
C.
Increasing the number of DBWN process
D.
Using Automatic Segment Space Management (ASSM)
E.
Increasing db_buffer_cache based on the V$DB_CACHE_ADVICE recommendation
Explanation:
*Automatic segment space management (ASSM) is a simpler and more efficient
way of managing space within a segment. It completely eliminates any need to specify and tune
thepctused,freelists, andfreelist groupsstorage parameters for schema objects created in the
tablespace. If any of these attributes are specified, they are ignored.
*Oracle introduced Automatic Segment Storage Management (ASSM) as a replacement for
traditional freelists management which used one-way linked-lists to manage free blocks with tables
and indexes. ASSM is commonly called “bitmap freelists” because that is how Oracle implement
the internal data structures for free block management.
Note:
* Buffer busy waits are most commonly associated with segment header contention onside the
data buffer pool (db_cache_size, etc.).
*The most common remedies for high buffer busy waits include database writer (DBWR)
contention tuning, adding freelists (or ASSM), and adding missing indexes.
why not C?
Notice that question asks implement immediately…ASSM is simpler and more efficient way.
D
D = Using Automatic Segment Space Management (ASSM)
D – OK
The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes.
http://www.dba-oracle.com/m_buffer_busy_waits.htm
D
D
C – question asks to decrease wait event IMMEDIATELY – to change to ASSM you have to create new tablespaces with ASSM and migrate data there – very long process. On the other hand, you can increase the number of DBWN processes very quickly – increase DB_WRITER_PROCESSES parameter and bounce the database
How number off DBWR can decrease wait event immediately?
I think A – oK. Decreasing PCTUSED will make the table less dense .
Decreasing PCTUSED will be effective only for new tables; for existing tables you would have to rebuild them (for example with ALTER TABLE MOVE). Hard to say it’s IMMEDIATE, in my opinion…
AutoCorrect: increasing number of DBWR would potentially decrease free buffer waits, not buffer busy waits. For buffer busy waits, if they are caused by concurrent reads and buffer hits % is low, then increasing buffer cache could help (so it’s D). For writes, ASSM would of course help, but for me converting to ASSM is rather not immediate…
Which option would you CONSIDER FIRST to decrease the wait event IMMEDIATELY?
IMMEDIATELY is the Key Word.
Among proposals which one is dynamic ?
Answer E: Increasing db_buffer_cache, but I would have say db_cache_size instead…
Could it lower waits for accessing block if cache is bigger : yes
Because server processes do not have to read block from datafiles if cache is bigger.
A,B are block reshaping (not dynamic) , D (ASSM) is about block management in a segment (not dynamic imply moving segments into ASSM tablespace)
C is about DBWR, it is the other way around from cache to datafiles, no link with wait for sessions, assumption is that we focus on foreground waits and not on background waits.
Even though increasing the cache may not help in all cases, it should be considered as an immediate test.