April 19, 2010
(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
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)
- rafioracledba.blogspot.com/2010/03/buffer-busy-waits-and-remedies.html
- yanggq.itpub.net/post/1594/111558
- remote-dba.net/oracle_10g_tuning/t_buffer_busy_waits.htm
- http://www.jlcomp.demon.co.uk/bbw.html
- http://books.google.com/books?id=bxHDtttb0ZAC&pg=PA284
- http://books.google.com/books?id=tdRes4IdLiIC&pg=PA1176
- http://books.google.com/books?id=TmPoYfpeJAUC&pg=PA434
- http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA539
The quiz:
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.
Charles Hooper's Oracle Notes 
There are many questions! At first try to answer some, it’s particularly difficult to justify the answer: I speak english very badly, so I tend to write as little as possible.
What happened in February 2008? You are joking, are you?
1. False. Where is it the wait event “buffer busy waits”?
CPU time=2,154 is the time spent by the CPU, is not a wait event.
2. False. There is only evidence of many PIOs
3. False. Wait a moment for the explanation…
4. True. IO waits are about 70% of DB time
5. False. Wait a moment for the explanation…
6. True. They are all measures that tend to reduce block contention.
7. False. Time is important, not the number
8. False. The wait event as of 10g is “read by another session”
9. False. As of 10g V$SESSION_WAIT.P3 is not reason code anymore
10. True. Freelist groups exist for this
Roberto,
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.
My English is clear? Thanks to Google Translate!
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.
Roberto, thank you for participating.
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.
#1: It is an indication of tampering with the StatsPack report output and therefore no indication of anything else (that does #2, too).