Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







dba_hist_sysstat tips

Oracle Database Tips by Donald BurlesonJuly 15, 2013


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.

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.


Name Null? Type
----------------- -------- ------------
SNAP_ID           NUMBER
DBID              NUMBER
STAT_ID           NUMBER
STAT_NAME         VARCHAR2(64)
VALUE             NUMBER


At the highest level, exception reporting involved adding a WHERE clause to a data dictionary query to eliminate values that fall beneath a pre-defined threshold. For a simple example, this can be done quite easily with a generic script to read dba_hist_sysstat.


AWR uses a "Top N" method which defaults to collect the Top-30 SQL statements for each SQL category (statistics_level=typical). If you set statistics_level = all, AWR will collect the top 100 SQL statements.


The following simple script called rpt_sysstat_10g.sql displays a time-series exception report for any statistic in dba_hist_sysstat. The script accepts the statistics number and the value threshold for the exception report.


< rpt_sysstat_10g.sql


prompt This will query the dba_hist_sysstat view to display all values

prompt that exceed the value specified in

prompt the "where" clause of the query.


set pages 999

break on snap_time skip 2

accept stat_name char prompt 'Enter Statistic Name: ';

accept stat_value number prompt 'Enter Statistics Threshold value: ';

col snap_time format a19

col value format 999,999,999


to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,


see code depot for full script


natural join



stat_name = '&stat_name'


value > &stat_value

order by

to_char(begin_interval_time,'yyyy-mm-dd hh24:mi')



Notice that this simple script will prompt you for the statistic name and threshold value; allowing ad-hoc AWR queries:


SQL> @rpt_sysatst

This will query the dba_hist_sysstat view to display all values

that exceed the value specified in

the "where" clause of the query.

Enter Statistic Name: physical writes

Enter Statistics Threshold value: 200000


------------------- ------------

2004-02-21 08:00 200,395

2004-02-27 08:00 342,231

2004-02-29 08:00 476,386

2004-03-01 08:00 277,282

2004-03-02 08:00 252,396

2004-03-04 09:00 203,407


The listing above indicates a repeating trend where physical writes seem to be high at 8:00 AM on certain days. This powerful script will allow the DBA to quickly extract exception conditions from any instance-wide Oracle metric and see its behavior over time.

System statistics for a particular snapshot interval can be viewed using the query below:

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"
see code depot for full script
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 buffer 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.

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster