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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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.

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

 

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

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.

prompt

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

select

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

value

from
see code depot for full script

dba_hist_sysstat

natural join

dba_hist_snapshot

where

stat_name = '&stat_name'

and

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

SNAP_TIME VALUE

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

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"
from
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster