Eliminating 2 Wait Events from the Top 5, How Many Remain?

24 02 2010

February 24, 2010

A recent question on the OTN forums forced me to stop and think for a moment.  If you eliminate two of the most commonly found wait events from the top five wait events:

  • How many wait events will appear in the top five wait events for a selected time period (hint – something has to appear in the top five)?
  • When the two most commonly found wait events are eliminated from the top five, does that necessarily mean that the database performance is better, or could the performance be worse?

The question from the OTN forum is as follows:

I have a query regarding the 2 events:
1. db file scattered read
2. db file sequential read

If the above two events are in top 5 events then what could be the reason for this and how to resolve the issue?

The question asked is a good question, but something is missing.  Similar questions have appeared on various forums in the past, so this blog article is not specifically addressed to this particular OTN posting.  The original poster excluded a couple of potentially useful pieces of information:

  • What Oracle release and operating system platform is in use?
  • How many seconds of wait time were reported in each of the wait events?
  • What was the total duration of the statistics collection period?
  • Were there any other, potentially more significant wait events in the top five list?
  • How many users were connected to the database?
  • What was the average wait duration for each of the events, and how many waits?
  • Were any business critical processes operating at a slower than expected speed?

There will always be five wait events in a top 5 list, so if the original poster was able to somehow eliminate those datafile type access waits events from the top 5 list, which wait events should take the place of the db file scattered read and db file sequential read waits?  Would it be better to see a different set of wait events, maybe pipe get and inactive session followed closely by cleanup of aborted processes? (I think that there might be a play on words and their definitions here – SHUTDOWN IMMEDIATE; is one certain way to decrease the frequency of datafile type access waits.)