Database Reference
In-Depth Information
If you don't have access to Enterprise Manager, you can use the
host_load_hist.sql
script to display
information that is equivalent to Figure
4-15
. Note that the script takes no parameter as input. Because the metrics it
displays should be available for one hour, it simply displays all the available data. The following example is an excerpt
from its output:
SQL> @host_load_hist.sql
BEGIN_TIME DURATION DB_FG_CPU DB_BG_CPU NON_DB_CPU OS_LOAD NUM_CPU
---------- -------- --------- --------- ---------- ------- -------
14:05:00 60.10 1.71 0.03 0.03 4.09 8
14:06:00 60.08 1.62 0.03 0.04 4.13 8
14:07:00 59.10 1.89 0.03 0.04 4.96 8
14:08:00 60.11 1.93 0.03 0.03 5.29 8
14:09:00 60.09 1.73 0.03 0.59 4.60 8
14:10:00 60.10 1.57 0.02 3.64 7.50 8
14:11:00 60.16 1.15 0.02 6.60 11.82 8
14:12:00 60.11 1.21 0.02 6.60 13.77 8
14:13:00 60.28 1.17 0.02 6.62 15.30 8
14:14:00 59.24 1.19 0.02 6.55 14.06 8
14:15:00 60.09 1.59 0.04 0.18 9.19 8
14:16:00 60.09 1.77 0.03 0.03 7.88 8
14:17:00 60.09 1.72 0.03 0.04 5.45 8
14:18:00 60.11 1.87 0.03 0.03 5.28 8
14:19:00 60.09 1.77 0.03 0.03 5.54 8
14:20:00 60.08 1.72 0.03 0.04 4.83 8
System Level Analysis
If you continue the analysis at the system level, you should do it from the Top Activity page. Note that I don't advise to
use the Performance Home page, specifically the Average Active Sessions chart, because the drill-down functionalities
on that page lead to a fast identification of a performance problem only when most of the response time is consumed
by a single wait class.
■
If you have access to aSh analytics, you should use it instead of using the Top activity page. In some situations,
the greater flexibility in selecting the period to analyze, add filters, and aggregate data according a multitude of
dimensions allows you to carry out a more straightforward and detailed analysis.
Tip
The Top Activity page displays either real-time or historical data. Because this chapter focuses on analyzing
performance problems while they're happening (or just after they took place), it goes without saying that you have to
use the real-time data. In this mode, data for the last hour should be available. Note that the real-time data displayed
by the Top Activity page is taken from the
v$active_session_history
view.
Three sets of data are provided by the Top Activity page:
•
An activity chart (Figure
4-16
) displays data for the last hour. Specifically, it shows the DB time
broken up into CPU utilization as well as wait classes.
