Session 3: Using Bottleneck/wait-based Performance Analysis in Performance Analyst for Oracle

Go Up to Performance Analyst for Oracle Tutorial

When an Oracle database is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting may mean nothing in the overall scheme of things, or it can be an indicator that a database bottleneck exists. Bottleneck analysis is a valid method of measuring performance because it helps you track where a database and user sessions have been spending their time. If latch contention or heavy table-scan activity has been dragging a database’s performance down, you can use bottleneck analysis to confirm the actual root cause.

Note: For Performance Analyst to show wait times for Oracle wait events, the database configuration parameter TIMED_STATISTICS must be set to TRUE.

Performance Analyst uses two broad areas of metrics to form its bottleneck analysis. The first is the Oracle wait event interface, which displays where the database and user sessions have been bottlenecked. But, when using bottleneck analysis you can not rely only on the information contained in the wait event views that Oracle provides. For example, an object may attempt to extend into another extent of space in a tablespace and yet, be denied if no such free space exists. Such a failure will not be reflected in any wait event, but still represents a very real bottleneck to the database. For this reason, Performance Analyst also shows non-wait event based bottlenecks that represent a very real threat to a database’s availability and performance.

Performance Analyst prominently displays bottleneck analysis on every performance home page. The Main home page, displays a summary of all key bottlenecks threatening the database. You can also see specific bottlenecks for memory, I/O, space, objects, and users by navigating to each home page and viewing the Bottleneck Analysis section for that home page. For example, to see bottlenecks that specifically relate to I/O, do the following

1. On the toolbar, click the I/O button.
2. Locate the Bottleneck Analysis section at the lower-left portion of the screen

   The I/O bottleneck analysis section displays the top database and log-related Oracle wait events (ranking them by wait time and not actual wait count), along with the most active database files.

Obtaining Detail on System Bottlenecks Using Performance Analyst for Oracle

You can easily obtain more information regarding global bottlenecks that are plaguing a database by drilling down into a particular bottleneck metric. For example, if you would like to see more data regarding the current top system bottlenecks, do the following:

1. Open the Main home page.
2. Double-click the Top System Bottlenecks area.

   Performance Analyst opens the System Waits view.

3. Or right-click anywhere in Performance Analyst, point to Users, and then click System Waits.

   Performance Analyst opens the System Waits view.

The System Waits view displays critical details regarding all the wait events that Oracle has recorded. You can easily see what events that occurred the most often as well as the events that have accumulated the most wait time.

Viewing Detail for Session Bottlenecks in Performance Analyst for Oracle

Not only can you obtain detail information for system bottlenecks, but you can also use Performance Analyst to get drill down information regarding session-level bottlenecks. There are several layers to session-level details you can view.

To see more information regarding session-level waits, do the following:

1. Open the Main home page.
2. Double-click the Top Session Bottlenecks area.

   Performance Analyst opens the Session Waits drill down view.

3. Or right-click anywhere in Performance Analyst, point to Users, and then click Session Waits.

   Performance Analyst opens the Session Waits drill down view.

When you are in the Session Waits drill down view, to drill further into a session’s details, do the following:

1. In the Historical Waits or Current Waits grid, select a session that appears to be experiencing critical waits.
2. Double-click the session or right-click, and then select Details.

   Performance Analyst opens a drill down view that shows key statistics for the selected session, including its current work activities, wait events, and SQL statements.

Proceed to Session 4: Using Workload Performance Analysis in Performance Analyst for Oracle