Oracle Advanced Performance Tuning Scripts

Wait Event Scripts

response_time_breakdown.sql

This script can be used to focus tuning attention on the most important issues. It reports a breakdown of total foreground response time into four major categories: CPU usage, disk I/O, resource waits, and routine latencies. These categories are broken down further into sub-categories, and the component wait events are shown.

The 8.1.6 version of the script uses the ratio_to_report analytic function to calculate percentages. The 8.1.5 version can be used if percentages are not required. The 8.1.5 version of the script should work on Oracle8 also, but has not yet been tested.

View sample listing

Download script for: 8.1.5, 8.1.6+

session_times.sql

When a user reports an episode of poor performance, this script can be used to determine whether their session has been working (say on an inefficient query) or waiting for resources, and if so, which resources they have been waiting for. Idle waits are not excluded. To select a session before running this script, please see the first three scripts on the Session Scripts page.

View sample listing

Download script for: 7.3, 8.0 or 8.1

resource_waiters.sql

If there has been extensive waiting for a particular type of resource, then this script can be used to determine which extant sessions have contributed to or been affected by the problem.

Download script for: 7.3, 8.0 or 8.1

trace_waits.sql

This script is one of our favorites. It finds the top N sessions that have been affected by a particular type of resource wait, and enables event 10046, level 8 in those sessions for the specified period. This event captures every wait event and its wait parameters, and writes the information to the process trace file together with the normal sql_trace output. The wait parameters can then be analyzed to understand exactly what is causing the performance problem.

View sample listing

Download script for: 7.3, 8.0 or 8.1.6.2+ Warning: Do not use this script between 8.1.5 and 8.1.6.1!

waiters.sql

During short-term performance problems, a standard diagnostic procedure is to count the number of session in V$SESSION_WAIT that are waiting (or have recently waited) for each non-idle type of wait event. However, it is difficult to query this view quickly enough to profile most such problems. This script addresses this difficulty by selecting the information 10 times in very quick succession within a single query. It still misses a lot, but is quick enough to identify some interesting patterns.

View sample listing

Download script for: 7.3, 8.0 or 8.1

resource_waits.sql

Resource waits can often be (nearly) eliminated by database server level tuning. This script shows the major resource types waited for over the life of the instance, in order of severity. It can be used to focus reactive tuning.

The average waiting time shown is the average waiting time for each logical wait. A logical wait may be composed of multiple distinct waits, all but the last of which timed out.

View sample listing

Download script for: 7.3, 8.0 or 8.1

routine_waits.sql

The average waiting time for routine waits, such as I/O waits, can also be improved by database server level or operating system level tuning. This script reports the average waiting time for these routine waits.

Download script for: 7.3, 8.0 or 8.1

system_times.sql

This script reports the total waiting time for both routine and resource waits, together with the CPU time used. The relative contribution of each type of wait to overall response times, and the potential benefit of tuning actions, can be estimated from this information.

View sample listing

Download script for: 7.3, 8.0 or 8.1


Ixora Pty Ltd.   All rights reserved.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home