Donald K. Burleson Oracle Tips

Oracle Bottleneck Analysis

How does one correctly practice bottleneck or wait-based analysis? First, it is imperative that the timed_statistics initialization parameter be set to TRUE, if the Oracle wait events are to be examined. By default, this parameter is set to FALSE, which disallows the collection of wait times for each wait event defined in the Oracle engine.

For one to really understand the impact of wait events on database performance, the DBA needs to not only discover what the database is or has been waiting on but the durations of the waits. Having both allows a complete picture to be formed regarding the magnitude of wait-initiated performance degradations.

Almost all Oracle experts now agree that collecting time statistics adds little, if anything, to database overhead, so setting timed_statistics to TRUE should not be a worry. The parameter can be dynamically altered at both the system and session levels, so the database does not have to be shutdown and then restarted for the change to take effect. A simple alter system set timed_statistics= true should do the trick.

The next prerequisite to using bottleneck analysis is that certain wait events should be filtered out of any metrics used to diagnose performance bottlenecks. For example, Oracle will record a wait statistic that represents how long a particular user sits at their SQL*Plus prompt between each issued database request.

Such a statistic provides no real value to a DBA who is trying to figure out where a database bottleneck exists. Any SQL scripts that are used to collect database wait statistics should exclude such events. A listing of these Oracle events (normally dubbed “idle” events) to eliminate includes:

• lock element cleanup
• pmon timer
• rdbms ipc message
• smon timer
• SQL*Net message from client
• SQL*Net break/reset to client
• SQL*Net message to client
• SQL*Net more data to client
• dispatcher timer
• Null event
• parallel query dequeue wait
• parallel query idle wait - Slaves
• pipe get
• PL/SQL lock timer
• slave wait
• virtual circuit status
 

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:

http://www.rampant-books.com/book_2003_1_perf.htm