Database Reference
In-Depth Information
Let's look at an example. We will use the excerpts from the following Statspack report to
provide sample figures for the calculation. The relevant figures are reproduced in bold font.
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------
Begin Snap: 90 15-Aug-07 09:35:21 215 11.9
End Snap: 91 15-Aug-07 09:57:24 177 10.6
Elapsed: 22.05 (mins)
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 82,337 62.2 23.0
The value of the parameter CPU_COUNT is captured, but not printed at the end of the
Statspack report, since it has a default value, so we need to retrieve the value from
STATS$PARAMETER .
SQL> SELECT value FROM stats$parameter WHERE name='cpu_count' and snap_id=90;
VALUE
-----
4
If we translate the preceding algorithm into a formula, it becomes this:
CPU consumption during snapshot interval (s) 100
CPU usage (%)
=
-----------------------------------------------------------------------------------------------------------------------------
snapshot interval (s) CPU_COUNT
Using the sample figures yields this:
823.37 100
1323 
------------------------------
=
15.56 %
The following query automates this calculation (script snap_by_cpu_util.sql ):
SELECT i.start_snap_id, i.end_snap_id,
i.start_snap_time, i.end_snap_time,
(i.end_snap_time - i.start_snap_time) * 86400 AS interval,
round(((s2.value - s1.value)/ 100 / ((i.end_snap_time - i.start_snap_time) * 86400)
/ p.value) * 100,2) AS cpu_utilization
FROM site_sys.sp_valid_intervals i, stats$sysstat s1,
stats$sysstat s2, stats$parameter p
WHERE i.start_snap_id=s1.snap_id
AND i.end_snap_id=s2.snap_id
AND s1.name='CPU used by this session'
AND s1.name=s2.name
AND p.snap_id=i.start_snap_id
AND p.name='cpu_count'
ORDER BY cpu_utilization DESC;
Search WWH ::




Custom Search