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 







AWR Warehouse tips

 Oracle Database Tips by Donald BurlesonJuly 23, 2015

Question:  I need to understand the OEM new feature for the AWR warehouse.  How does this AWR warehouse GUI out-perform manual AWR warehouse methods?

Answer:  One of the shortcomings or AWR is that by default it only keeps a short duration of data (8 days).  In order to allow predictive modeling and analytics of Oracle performance, you need to increase the retention period for AWR data and know what tables to keep and which AWR tables to truncate.  The AWR warehouse appears to be an extra-cost feature of Oracle OEM, and it requires the purchase of the diagnostic and tuning management packs for all the AWR warehouse features

Thee AWR warehouse consolidates AWR data from multiple database "targets".  target is identified by the target_guid column in the mgmt$target_properties table, just as with targets in the EM12c repository, making loads and reporting simplified.  Here is a script:

   t.host_name as host,
   ip.property_value IP,
   t.target_name as name,
   decode(t.type_qualifier4,' ','Normal',t.type_qualifier4) as type,
   dbv.property_value as version,
   port.property_value port,
   SID.property_value SID,
   logmode.property_value as "Log Mode",
   oh.property_value as "Oracle Home"
   mgmt$target t,
   ( select
        mgmt$target_properties p
    where p.property_name='DBVersion') dbv,
   ( select
        mgmt$target_properties p
        p.property_name='Port') port,
   ( select
        mgmt$target_properties p
        p.property_name='SID') sid,
    ( select
        mgmt$target_properties p
        p.property_name='log_archive_mode') logmode,
    ( select
        mgmt$target_properties p
        p.property_name='OracleHome') oh,
        tp.target_name as host_name,
        mgmt$target_properties tp
        tp.target_type='host' and tp.property_name='IP_address') ip
order by 1,3

 Here is what the AWR data consolidation feature of AWR Warehouse repository looks like:

Source: dbakevlar

The OEM console for the AWR warehouse allows you to manage data from many databases.

Source: dbakevlar

Now that we see the architecture and concepts behind the AWR warehouse, let's look at the foundation of the AWR data for modeling and decision support activities.

The basis for AWR Warehouse

In a superb paper titled METRIC BASELINES: DETECTING AND EXPLAINING PERFORMANCE EVENTS IN EM 10GR2, Presented at the RMOUG 2005 Training Days in Denver, John Beresniewicz of Oracle Corporation gives us a great preview into the new predictive modeling tools in Oracle 10g release 2 (10.2). Beresniewicz told me that the use of "baselines to capture and adapt thresholds to expected time-dependent workload variations" is a core feature of Oracle:

The metric baselines introduced in Enterprise Manager 10gR2 statistically characterize specific system metrics over time periods matched to system usage patterns. These statistical profiles of expected metric behavior are used to implement adaptive alert thresholds that can signal administrators when statistically unusual metric events occur.

Assuming that systems are normally stable and performance problems rare, it is reasonable to expect that actual performance events will be highly correlated with observed unusual values in some metric or other. Thus it is hoped that baseline-driven adaptive thresholds will both reduce configuration overhead for administrators and more reliably signal real problems than fixed alert thresholds.

The idea is simple. Because Automated Workload Repository (AWR, or STATSPACK in 8i and ) keeps a historical performance record, Oracle now knows when a scheduled task is going to cause a resource shortage, before it happens!

Using the AWR as a warehouse, we can analyze statistically valid trends and repeating patterns. Many databases have batch job schedules, implemented via crontabs, dbms_job or dbms_scheduler, and this valuable data can be used to:

  • Automatically morph the Oracle instance to anticipate a daily of weekly batch job. The most common example of this is a database that runs in OLTP mode during the day (first_rows) and switches to decision support at night (all_rows).

  • Predict the reduction in logical I/O and physical I/O from reorganizing a table or index

  • Use linear regression to predict a future time when Oracle will exhaust server resources RAM, CPU)

Beresniewicz also shows the value of signature analysis in an AWR warehouse where you average metric values by day-of-the-week and hour-of-the-day, and use the valid trends to create a predictive model for proactive DBA changes:

The baseline period can be divided into sub-intervals of time over which statistical aggregates are computed. These subintervals, or time partitions are intended to allow baselines to capture and adapt thresholds to expected time-dependent workload variations.

OEM AWR Repository allows for simple time partitioning that can capture common daily or weekly usage/workload cycles. The daily options are:

By hour of day: aggregate each hour separately, strong variation across hours

By day and night: aggregate the hours of 7am-7pm as day and 7pm-7am as night

By all hours: aggregate all hours together, no strong daily cycle

The weekly time partitioning options are:

By day of week: aggregate days separately, strong variation across days

By weekday and weekend: aggregate Mon-Fri together and Sat-Sun together

By all days: aggregate all days together, no strong weekly cycle

Time partitioning is fully specified in EM 10gR2 by selecting both daily and weekly partitioning options.

For example, consider an operational system serving many online users working similar daily schedules and running batch jobs in the evening.

In this exciting paper we see that Oracle is planning to adopt the same kind of predictive model that I have espoused for many years. The basic idea is that historical data will provide valid predictive information that can be used by Oracle to fix a resource issue BEFORE it cripples your performance.  Predictive modeling is widely used in the credit card industry: , and the same principles are being used by the AWR warehouse to predict trends within Oracle system performance.

AWR warehouse Sizing

Setting an appropriate retention interval for your AWR warehouse is critical for proper data retention, especially for predictive modeling. You can adjust the AWR retention period according to your analysis needs.

Beware that you may want to selectively truncate AWR tables while leaving the "major" statistics for long-term trend analysis.

These periodic truncates are for the SQL tables, so I disable AWR data purging and manually truncate tables such as the dba_hist_sqlstat tables. See how to periodically truncate the SQL tables from AWR.

In this example the retention period is specified as 3 years (1,576,800 minutes) and the interval between each snapshot is 60 minutes.

execute dbms_workload_repository.modify_snapshot_settings (

interval => 60,

retention => 1576800);

Also see my related notes on dba_hist_wr_control.

Oracle has published suggested storage requirements for AWR based on the activity and complexity of your 10g database:

Active Sessions number of
CPU's (num_cpus)
number of
schema objects
space required (gig)
Small 10 2 500 2
Medium 20 8 5,000 8
Large 100 32 50,000 20

See these notes on setting a retention period for an AWR warehouse.

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