Database Reference
In-Depth Information
excerpts of an Oracle10
g
Statspack report follow. Figures required for the calculation of relative
DB time are in bold font.
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 83 06-Sep-07 17:04:06 24 3.3
End Snap: 84 06-Sep-07 17:09:54 24 3.3
Elapsed:
5.80
(mins)
Time Model System Stats DB/Inst: TEN/TEN1 Snaps: 83-84
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 319.3 100.0
PL/SQL execution elapsed time 316.7 99.2
DB CPU 301.4 94.4
…
DB time
319.3
Expressed as a formula, relative DB time is as follows:
relative DB time (s) =
DB time (s)
snapshot interval (s)
Using the sample figures yields this:
319.3
5.8 60
------------------
=
. 2
The query, which automates the calculation, is once again based on the view
SP_VALID_INTERVALS
(file
snap_by_db_time.sql
).
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) / 1000000 /* convert from microsec to sec */
/ ((i.end_snap_time - i.start_snap_time) * 86400 ), 2)
/* normalize by snapshot interval */
AS db_time_per_sec
FROM site_sys.sp_valid_intervals i, stats$sys_time_model s1,
stats$sys_time_model s2, stats$time_model_statname n
WHERE i.start_snap_id=s1.snap_id
AND i.end_snap_id=s2.snap_id
AND n.stat_name='DB time'
AND s1.stat_id=n.stat_id
AND s2.stat_id=n.stat_id
ORDER BY db_time_per_sec DESC;
Start End
