 |
|
Oracle AWR Exception Reporting
Oracle Tips by Burleson Consulting |
AWR Report
Exception Reporting with the AWR
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.
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.
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
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.
The next section provides a more powerful exception report that
compares system-wide values to individual snapshots.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
