Chapter 11. Responding to the Diagnosis

---

To improve Oracle performance, you must of course understand the technology of each response time component that contributes significantly to your targeted user action's response time. The place you should begin your research is the Oracle Database Concepts guide at http://technet.oracle.com. The response time components that show up in your resource profiles associate directly to instrumented Oracle kernel actions described in the Concepts guide. For example, it describes how the Oracle LGWR process copies content from the redo log buffer to an online redo log file. An Oracle kernel process accounts for the time it spends waiting on LGWR to perform this particular action with the wait event called log file sync .

There are lots of other such events. The number of wait events inside the Oracle kernel has grown with each new release, as shown in Table 11-1. Thankfully, it is not important for you to know a lot of details about every Oracle wait event. You usually don't need the gory details in your brain for any more than a couple of wait events at a time ”the ones that are dominating your targeted user action at the moment. This is excellent news, because some of the events require some study time to understand. Rather than learn and try to retain a lot of details about dozens of events, I think it is more important to focus on the following:

• Know how to target the events that are important to you right now. I describe how to do this in Part I.

• Retain a general knowledge about the meanings of just a few response time components that will occur frequently on your system, including:

  • CPU service

  • unaccounted-for

  • SQL*Net message from client

  • The various read events

  • The one or two other events that occur frequently on your system

  You can acquire this knowledge by studying this book, the Oracle Database Concepts guide, and the response time behavior of targeted user actions on your own system.

• Know where to find the details about a response time component when you need them. My favorite tools for finding wait event information are:

  • Oracle database product documentation at http://technet.oracle.com

  • Oracle MetaLink support bulletins and bug reports at http://metalink.oracle.com

  • Anjo Kolk and Shari Yamaguchi's YAPP paper [Kolk and Yamaguchi (1999) ]

  • Steve Adams's bumblebee book [Adams (1999)], and his web site at http://www.ixora.com.au

  • The Google search engine at http://www.google.com, which helps me find wait event documentation throughout the Internet

Table 11-1. The number of Oracle wait events grows with each Oracle release (source: select count(*) from v$event_name)

Oracle release	Number of wait events
7.3.4	106
8.1.7	215
9.0.1	287
9.2.0	361
10.0.1	500 (est.)

---

Top