True or False – Buffer Busy Waits

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:

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.

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.

The English answers that you provided are very clear. However, I think that the English questions might be causing you a little bit of trouble. For example, for question #1, article #1 shows 2,154 in the Waits column for the “CPU Time” with an elapsed time of 934 seconds. I agree that the answer should be False, but for a different reason. Statspack reports do not (can not) show the number of waits on the CPU, so the answer to the question must be false.

For question #6, “and then” means that you would perform all three steps. That significant phrase may not have translated well for you.

February 2008 is the month when the first article was last updated, and that is why I selected that date. If I remember correctly, Oracle 10.2.0.2 was released in April 2006 for some operating system platforms, and that version was likely made available on other platforms before that date. I assume that the article was updated for Oracle 10g R2, or possibly Oracle 11g R1 – which was released in January or February 2008 for some operating system platforms.

1. Sorry I made a blunder on CPU time. I’m used with AWR&10g and there, rightly, CPU time waits column does not exists.
My answer is false because the event “buffer busy waits” does not appear in the “top 5 timed events”.

6. Yes, I missed “and then”. It is meaningless ending with the last step.

“February 2008 is the month when the first article was last updated”:
I had guessed… you kidding Don.

I hope that the questions in this blog series do not appear on a real Oracle test – I would have a concern that the test answer sheet would be wrong depending on who wrote the test answer sheet. People who read the Oracle documentation will probably answer the questions differently from people who search the Internet for answers. And for people who read books… I guess that the answer depends on the person’s background.

Regarding question #4, the question did not state whether or not we should look at the Top 5 Timed Events at the top of article #1, or if in general it is the case that seeing ’db file sequential read’ waits and ‘db file scattered read’ waits in the top 5 indicates that the database instance is I/O bound. The question probably should have been a little more clear.

If we take question #4 to mean in general, does the appearance of ’db file sequential read’ waits and ‘db file scattered read’ waits in the top 5 indicate that the database instance is I/O bound – that statement must be false. Somewhere in the documentation it states that it is completely normal for these two wait events to appear in the top 5 list. When those wait events appear in the top 5 list it is reasonable to assume that there is probably no I/O bottleneck – that the database instance is just performing its normal work. *Something* must appear in the top 5 wait events – what should take the place of these two wait events? See this blog article:https://hoopercharles.wordpress.com/2010/02/24/eliminating-2-wait-events-from-the-top-5-how-many-remain/

If we look at the specific top 5 list in article #1, there is something seriously wrong. The average time for a single block read is 2.75 seconds and the average multiblock read time is 0.127 seconds. I don’t know if this database instance is I/O bound, or if some of the data files are stored on floppy disks. In this case the answer is probably True for the reason that you stated.

Based on the only “top 5 events” is not easy to answer with true or false. From a similar situation, if there is a problem of slowness, I would have thought of an excess of PIOs due to a small buffer cache. If the buffer cache size is good, typically go up the CPU time and buffer chain latch waits appear.
Block contention is quite rare in recent Oracle versions.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: