 |
|
|
dba_hist_sysstat system statistics The AWR stores history for a large number of instance cumulative statistics. These statistics are generally available through the v$sysstat dynamic view. The AWR stores snapshots for this view in the dba_hist_sysstat view. The following sections provide more details on these system statistics AWR views. dba_hist_sysstat The dba_hist_sysstat view contains a history for system statistics from the v$sysstat view. Statistic names can be retrieved from the dba_hist_statname view where more than 300 statistics are available.
SQL> desc DBA_HIST_SYSSTAT
Name Null? Type ----------------- -------- ------------ SNAP_ID NUMBER DBID NUMBER INSTANCE_NUMBER NUMBER STAT_ID NUMBER STAT_NAME VARCHAR2(64) VALUE NUMBER
System statistics for a particular snapshot interval can be viewed using the sys_stat_int_10g.sql query.
sys_stat_int_10g.sql
select e.stat_name "Statistic Name" , e.value - b.value "Total" , round((e.value - b.value)/ ( select avg( extract( day from (e1.end_interval_time-b1.end_interval_time) )*24*60*60+ extract( hour from (e1.end_interval_time-b1.end_interval_time) )*60*60+ extract( minute from (e1.end_interval_time-b1.end_interval_time) )*60+ extract( second from (e1.end_interval_time-b1.end_interval_time)) ) from dba_hist_snapshot b1 ,dba_hist_snapshot e1 where b1.snap_id = b.snap_id and e1.snap_id = e.snap_id and b1.dbid = b.dbid and e1.dbid = e.dbid and b1.instance_number = b.instance_number and e1.instance_number = e.instance_number and b1.startup_time = e1.startup_time and b1.end_interval_time < e1.end_interval_time ),2) "Per Second" from dba_hist_sysstat b , dba_hist_sysstat e where b.snap_id = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid = &pDbId and e.dbid = &pDbId and b.instance_number = &pInstNum and e.instance_number = &pInstNum and b.stat_id = e.stat_id and e.stat_name not in ( 'logons current' , 'opened cursors current' , 'workarea memory allocated' ) and e.value >= b.value and e.value > 0 order by 1 asc
The query output will look like:
SQL> @Sys_stat_int_10g.sql
Statistic Name Total Per Second -------------------------------------- ---------- ---------- CPU used by this session 4,307 1 CPU used when call started 4,307 1 CR blocks created 200 0 DB time 959,909 115 DBWR checkpoint buffers written 3,228 0 DBWR checkpoints 9 0 DBWR object drop buffers written 75 0 DBWR tablespace checkpoint buffers written 71 0 DBWR transaction table writes 92 0 DBWR undo block writes 822 0 IMU CR rollbacks 20 0 IMU Flushes 103 0 IMU Redo allocation size 761,060 92 IMU commits 383 0 IMU contention 0 0 IMU ktichg flush 4 0 IMU pool not allocated 1,702 0 IMU undo allocation size 1,772,624 213
The sys_stat_int_10g.sql script allows users to easily identify all instance activity statistics for a particular snapshot interval in two representations: cumulative and per second.
The Ion tool also has a report named Instance Activity Statistics that is based on dba_hist_sysstat view.
|
|
|
