■ Monitoring Instance Activity ■ Monitoring Host Activity
■ Determining the Cause of Spikes in Database Activity ■ Customizing the Database Performance page
Monitoring User Activity
As described in Chapter 2, "Oracle Database Performance Method", database time (DB time) is an indicator of the total database instance workload. The average active sessions for a time period equals the total database time of all user sessions during the period divided by the elapsed time (wall-clock time) for the period.
The Average Active Sessions chart on the Performance page shows the average active sessions for CPU usage and wait classes in the time period. By following the
performance method explained in Chapter 2, you can drill down from the chart to identify the causes of instance-related performance issues and resolve them.
To monitor user activity:
1. Access the Database Home page.
See "Accessing the Database Home Page" on page 1-3 for more information.
2. From the Performance menu, select Performance Home.
If the Database Login page appears, then log in as a user with administrator privileges. The Performance page appears.
3. Locate any spikes or other areas of interest in the Average Active Sessions stacked area chart.
Figure 4–1 shows an example of the Average Active Sessions chart on the Performance page.
Figure 4–1 Average Active Sessions Chart
Each color-filled area on the stacked area chart shows the average active sessions for the specified event at the specified time. In the chart, the average active sessions amount for each event is stacked upon the one below it. The events appear on the chart in the order shown in the legend, with CPU starting at zero on the y-axis and the other events stacked in ascending order, from CPU Wait to Other. The wait classes show how much database activity is consumed by waiting for a resource such as disk I/O.
For example, in Figure 4–1 at approximately 1:26 p.m., five events consume database time in the sampled time interval: CPU, CPU Wait, Scheduler, Concurrency, and Other. The combined average active sessions total is 2.1. The average active sessions value for CPU is about 1.52, for CPU Wait it is about .38, for Scheduler it is about .03, for Concurrency it is about .16, and for Other it is about .01.
The CPU Cores line at 2 on the y-axis indicates the number of CPUs on the host system. When the CPU value reaches the CPU Cores line, the database instance is consuming 100 percent of CPU time on the system.
In Figure 4–1, the chart shows that most of the activity occurred from 1:20 to 1:28 and from about 1:53 to 2:20. Most of the activity between 1:20 and 1:28 was CPU usage, as was most of the activity after about 1:59. Wait class activity spiked a few times between 1:56 and 2:12, and consumed a significant amount of database time from about 1:52 to about 2:08.
4. To identify each wait class, move your cursor over the area in the Average Active Sessions chart that corresponds to the class.
The corresponding wait class is highlighted in the chart legend.
5. Click the largest area of color on the chart or the corresponding wait class in the legend to drill down to the wait class with the most average active sessions. If you click CPU or CPU Wait, then the Active Sessions Working: CPU + CPU Wait page appears. If you click a different wait class, such as User I/O, then an Active
Sessions Waiting page for that wait class appears. Figure 4–2 shows the Active Sessions Working: CPU + CPU Wait page.
Figure 4–2 Active Sessions Working Page
The Active Sessions Working page shows a 1-hour timeline. Details for each wait class are shown in 5-minute intervals under Detail for Selected 5 Minute Interval. You can view the details of wait classes in different dimensions by proceeding to one of the following sections:
■ "Monitoring Top SQL" on page 4-4 ■ "Monitoring Top Sessions" on page 4-4 ■ "Monitoring Top Services" on page 4-5 ■ "Monitoring Top Modules" on page 4-6 ■ "Monitoring Top Actions" on page 4-7 ■ "Monitoring Top Clients" on page 4-8 ■ "Monitoring Top PL/SQL" on page 4-8 ■ "Monitoring Top Files" on page 4-9 ■ "Monitoring Top Objects" on page 4-9
6. To change the selected time interval, drag the shaded area on the chart to a different interval.
The information contained in the Detail for Selected 5 Minute Interval section is automatically updated to display the selected time period.
In the example shown in Figure 4–2, the 5 -minute interval from 9:59 to 10:04 is selected for the CPU and CPU wait class.
7. If you discover a performance problem, then you can attempt to resolve it in real time. On the Performance page, do one of the following:
■ Below the Average Active Sessions chart, click the snapshot corresponding to
the time when the performance problem occurred to run ADDM for this time period.
For information about ADDM analysis, see "Reviewing the Automatic Database Diagnostic Monitor Analysis" on page 3-7.
■ Click Run ADDM Now to create a snapshot manually.
For information about creating snapshots manually, see "Creating Snapshots"
on page 3-5. For information about running ADDM manually, see "Manually Running ADDM to Analyze Current Database Performance" on page 7-1.
■ Click Run ASH Report to create an Active Session History (ASH) report to
analyze transient, short-lived performance problems.
For information about ASH reports, see "Active Session History Reports" on page 8-3.