April 19, 2010
An earlier blog article provided a couple of links that I found to be helpful when encountering buffer busy waits. There are certainly other helpful web pages within reach of the nearest search engine. I thought for today’s True or False quiz I would reference some of the pages found by the Google search engine.
Please read the following articles, and try to answer the questions that follow with either a True or False answer, followed by justification for your answer:
- http://dba-oracle.com/art_builder_bbw.htm (updated February 2008)
1. Referencing the Top 5 Timed Events at the top of article #1, a Statspack report that indicates 2,154 waits for the CPU is a possible sign of a problem with buffer busy waits.
2. Referencing the Top 5 Timed Events at the top of article #1, a Statspack report that indicates 2,598 waits on the ‘db file sequential read’ wait event and 25,519 waits on the ‘db file scattered read’ wait event provides significant evidence of buffer busy waits when 7,146 seconds and 3,246 seconds, respectively, were reported for those wait events.
3. I/O bound Oracle database instances frequently experience buffer busy waits.
4. ‘db file sequential read’ waits and ‘db file scattered read’ waits appearing in the top 5 list in a Statspack report indicate that the database instance is I/O bound.
5. Eliminating buffer busy waits in the database instance will also reduce the I/O load in the instance.
6. To reduce the frequency of buffer busy waits, missing indexes should be created, the freelists for the affected tables and indexes should be increased, and then those objects should be moved to an ASSM tablespace.
7. Referencing article #1, assume that you query V$SYSTEM_EVENT and find that there were a total of 636,528 buffer busy waits. The reported number of waits on this event indicate that there were 636,528 distinct waits for a block, and this number of buffer busy waits is an indication of a severe problem.
8. As of February 2008, a block that is in the process of being read into the buffer cache will trigger a buffer busy wait in another session if that other session requires access to that same block.
9. As of February 2008, the P3 column of V$SESSION_WAIT indicates the reason code for a buffer busy wait, with a value of 0 indicating that a block is in the process of being read into the buffer cache.
10. Freelist groups should be used to reduce segment header contention.
Tip: Think carefully about the questions – some of the questions might not have obvious answers.