Session 4: Using Workload Performance Analysis in Performance Analyst for Oracle
Go Up to Performance Analyst for Oracle Tutorial
Key ratios give you a global perspective on database activity. Bottleneck analysis gives you insight into things that are holding up user activity and throughput. But another technique is necessary if a database professional is to really get a handle on what’s occurring inside a badly performing database.
Workload analysis involves the investigation of two critical areas of a database’s performance:
- Session resource consumption and activity
- SQL execution
Without looking at these two key performance categories, you can miss a lot of what could be responsible for perceived performance problems. When performance on a database takes a sudden nosedive, it is not uncommon to find one or two sessions that are causing the bulk of the workload. Finding these processes can be easily accomplished by viewing session metadata coupled with resource consumption and statistical execution statistics.
Pinpointing Top Resource Consumers in Performance Analyst for Oracle
Performance Analyst identifies top resource consumers in a couple of different ways. On the main home page, Performance Analyst highlights the top resource sessions across physical and logical I/O, memory usage, and CPU consumption.
The percentage used across all statistical categories are displayed so you can immediately pinpoint a session that is using all or most of a particular resource. The top resource sessions are also displayed on the memory, I/O, and other home pages as well with a listing for the top sessions for that category. For example, the top memory users appear on the memory home page.
Getting Workload Details Using Performance Analyst for Oracle
Performance Analyst offers several details on top resource sessions in addition to the summaries provided on each home page. A detailed and flexible top sessions view lets you view a summary of all the major resource sessions on the database as well as every critical detail regarding each session connected to the database.
Further drill downs are available that highlights specific details regarding each session’s activities and submitted SQL. To see detailed information regarding a session identified in one of Performance Analyst’s Workload Analysis sections, do the following:
- In a Workload Analysis display, position the pointer over one of the processes.
- Double-click the process or right-click, and then select Details.
If you want like to see all key workload analysis metadata for sessions, do the following:
- On the toolbar, click the Top Sessions button.
- Or right-click anywhere in Performance Analyst, point to Users, and then click Session Waits.
Proceed to Session 5: Using SQL Performance Analysis in Performance Analyst for Oracle