Database Reference
In-Depth Information
lag(snap_id) over (order by dbid, instance_number, snap_id) AS start_snap_id,
snap_id AS end_snap_id,
lag(instance_number) over (order by dbid, instance_number, snap_id)
AS start_inst_nr, instance_number AS end_inst_nr,
lag(snap_time) over (order by dbid, instance_number, snap_id)
AS start_snap_time, snap_time AS end_snap_time,
lag(startup_time) over (order by dbid, instance_number, snap_id)
AS start_startup_time, startup_time AS end_startup_time
FROM perfstat.stats$snapshot
) iv
WHERE iv.start_snap_id IS NOT NULL
AND iv.start_dbid=iv.end_dbid
AND iv.start_inst_nr=iv.end_inst_nr
AND iv.start_startup_time=iv.end_startup_time;
Following is a query that accesses the view
SP_VALID_INTERVALS
:
SELECT start_snap_id, end_snap_id, start_inst_nr, start_snap_time,
trunc((end_snap_time-start_snap_time)*86400) AS interval
FROM site_sys.sp_valid_intervals;
START_SNAP_ID END_SNAP_ID START_INST_NR START_SNAP_TIME INTERVAL
------------- ----------- ------------- ------------------- ----------
87 88 1 15.08.2007 06:06:04 1898
88 89 1 15.08.2007 06:37:42 2986
90 91 1 15.08.2007 09:35:21 1323
High CPU Usage
High CPU usage might be an indication of a performance problem, such that snapshot intervals
exhibiting high CPU usage may warrant further investigation. CPU usage is usually expressed as a
percentage of available CPU resources. Applied to Statspack, this means that we need to look
at CPU utilization during consecutive snapshot intervals. The algorithm for computing CPU
usage from Statspack repository tables is as follows:
1.
Get the CPU consumption (in centiseconds) during the snapshot interval by subtracting
the value captured by the start snapshot from the value captured by the end snapshot.
This value is part of each Statspack report and is calculated by the
STATSPACK
package.
CPU consumption since instance startup is represented by the statistic “CPU used by
this session” in
STATS$SYSSTAT
.
2.
Divide the value by 100 for conversion from centiseconds to seconds.
3.
Divide the CPU consumption by the snapshot interval in seconds. The length of the
interval may be derived from
STATS$SNAPSHOT
using the analytic function
LAG
.
Divide the result by the number of CPUs (parameter
CPU_COUNT
) captured at the beginning
of the snapshot interval (
STATS$PARAMETER
) to get average CPU utilization as a percentage
of CPU capacity. The CPU capacity of a system is one second of CPU time per CPU
per second.
4.
