Exception reporting with dba_hist_filestatxs

Time-series exception reporting is extremely useful for detecting those times when Oracle Database 10g is experiencing stress.  Many Oracle professionals will schedule these types of exception reports for automatic e-mailing every day.

This completes the review of trend identification. The next section will examine a more sophisticated type of report wherein it is possible to identify repeating trends within data.

Trend Identification with the AWR

With the examination of dba_hist queries complete, the next topic to explore is trend identification with the AWR views.  The senior Oracle professional knows that aggregating important Oracle performance metrics over time (day-of-the-week and hour-of-the-day) allows them to see hidden “signatures.”  These signatures are extremely important for proactive tuning because they show regularly occurring changes in processing demands.  This knowledge allows the DBA to anticipate upcoming changes and reconfigure Oracle just in time to meet the changes.

For example, listed below is a report called report_sysstat_hour.sql that will show the “signature” for any Oracle system statistic, averaged by hour of the day.

            report_sysstat_hour.sql

-- *************************************************
-- Copyright © 2004 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
-- *************************************************