|
|
| |
 |
|
Oracle Tips by Burleson |
Trend Identification with the AWR
Once the creation of simple dba_hist queries has been mastered,
the next step is to move on to examine trend identification with
the AWR views. The Oracle professional knows that aggregating
important performance metrics over time (day-of-the-week and
hour-of-the-day) allows them to see performance signatures that
may otherwise remain hidden. These signatures are extremely
important for proactive tuning because they show regularly
occurring changes in processing demands. This knowledge allows
for the anticipation of upcoming changes in order to reconfigure
Oracle just-in-time to meet the changes.
The following is a simple example. This example utilizes a
report called rpt_sysstat_hr_10g.sql that will show the
signature for any Oracle system statistic, averaged by hour of
the day.
rpt_sysstat_hr_10g.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
prompt
prompt
prompt This will query the dba_hist_sysstat view to
prompt display average values by hour of the day
prompt
set pages 999
break on snap_time skip 2
accept stat_name char prompt 'Enter Statistics Name: ';
col snap_time format a19
col avg_value format 999,999,999
select
to_char(begin_interval_time,'hh24') snap_time,
avg(value) avg_value
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = '&stat_name'
group by
to_char(begin_interval_time,'hh24')
order by
to_char(begin_interval_time,'hh24')
;
|
The above book excerpt is from:
 |
|
Oracle Silver Bullets
Real-world Oracle performance Secrets
Donald K. Burleson
ISBN 0-9759135-2-2
Retail Price $27.95
/ £10.95
Order now and
get immediate online access to the
code depot!
Only
$19.95
(30% off)
 |
http://www.rampant-books.com/book_2005_1_silver_bullet.htm |
|
|
