ASH Data and How Sizing an AWR Warehouse Can be Mitigated

When sizing the AWR Warehouse, one of my requirements is to have certain reports for a sampling of databases that will source the AWR Warehouse. This report provides me the right information to create the correct sizing requirements vs. any assumptions done with other choices. The report is the General AWR Information report and can be found in the $ORACLE_HOME/rdbms/admin directory.  It provides the following information:

• Time of Report
• AWR Retention and interval settings, including if non-default setting.
• Schema percentage of space used in the SYSAUX tablespace.
• Objects and space consumption in the SYSAUX tablespace, including breakdown of the AWR space usage by type.
• Automated tasks scheduled.
• ASH Usage Info
• Foreground vs. Background processing info.

I’ve now run into a few projects where questions were raised on why so much space was required and there is sometimes an issue with the data being retained in the AWR that must be identified or addressed that will impact the long term size demand on the warehouse before the data is brought over from the source database, (target) via the ETL to the AWR Warehouse.  Knowing how to identify this is crucial, but many folks aren’t looking at AWR data space usage regularly, so how would you know?  Well, that’s what this post is about and hopefully will save me time with how much is on my plate these days… 🙂

The General AWR Information Report

To run the AWR Info report, log into the host of the database in question as a user that has rights to AWR reports.  From the directory you would like to have the report housed, run the following:

SQL> $ORACLE_HOME/rdbms/admin/awrinfo.sql;

Once the report is generated, simply open it in a text editor on the host or FTP it to your workstation.

We’ll start with what a standard AWR Info report with “normal” space usage looks like and use it as a baseline.  This will help you, as a DBA understand what is common for AWR space consumption.

Our Standard retention and interval is 8 days and 60 minute intervals on snapshots and the space usage for the SYSAUX tablespace looks like this:

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
| 
| Total SYSAUX size 2,993.9 MB ( 74% of 4,046.0 MB MAX with AUTOEXTEND OFF ) 
| 
| Schema SYS occupies 2,750.9 MB ( 91.9% ) 
| Schema XDB occupies 67.3 MB ( 2.2% ) 
| Schema AUDSYS occupies 65.4 MB ( 2.2% ) 
| Schema MDSYS occupies 61.7 MB ( 2.1% ) 
| Schema ORDDATA occupies 16.1 MB ( 0.5% ) 
| Schema SYSTEM occupies 15.7 MB ( 0.5% ) 
| Schema WMSYS occupies 7.1 MB ( 0.2% ) 
| Schema EXFSYS occupies 3.7 MB ( 0.1% ) 
| Schema CTXSYS occupies 3.7 MB ( 0.1% )

Non Default AWR Settings

If the settings have been changed from the default, the AWR info report will display the settings and let you know they aren’t the default. Space consumption will change vs. what you see in our first example, too.

Warning: Non Default AWR Setting!
——————————————————————————–
Snapshot interval is 30 minutes and Retention is 8 days    (5.2 GB, vs. 3GB for a 60 min. interval.)

OR

Snapshot interval is 60 minutes and Retention is 42 days  (10.5 GB)

With an increase in interval or retention, an increase in space consumption will result, but it won’t be 1:1.  There are two features that impact what space is required-  rollup, which saves space, then partitioning, that requires a bit more, so approximately 70% increase on average with the interval to every 30 minutes.

When an increase in retention of AWR data is implemented, then you should calculate about 2.5G of data for each one week of AWR data retained, at 1hr interval on snapshots, (this depends on version of the database, too.  AWR in 10g is much smaller than 11g, which is also smaller than 12c…)

AWR Components

We also need to look at the consumption used by a standard AWR schema breakdown to understand WHAT components are using the space:

**********************************
(3a) Space usage by AWR components (per database)
**********************************

COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX% 
--------- --------- ------ ------------ ---------- ----------- ---------------- 
FIXED 1,559.1 63.3 7,750 181.6 1,271.5 45% : 55% 
EVENTS 489.9 19.9 2,435 57.1 399.5 40% : 60% 
SQL 238.3 9.7 1,184 27.8 194.3 64% : 36% 
SPACE 111.1 4.5 552 12.9 90.6 63% : 37% 
ASH 35.3 1.4 175 4.1 28.7 83% : 17% 
SQLPLAN 11.0 0.4 55 1.3 9.0 64% : 36% 
SQLTEXT 0.9 0.0 5 0.1 0.8 87% : 13% 
SQLBIND 0.6 0.0 3 0.1 0.5 50% : 50% 
RAC 0.6 0.0 3 0.1 0.5 50% : 50%

Note that fixed objects are at the top of the list, followed by events, SQL, space and then, ASH.  This is how the flow of greatest to least should commonly be displayed.

Now lets looks at an AWR Info report where the data consumption is experiencing an issue:

********************************** 
(3a) Space usage by AWR components (per database) 
********************************** 
 
COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX% 
--------- --------- ------ ------------ ---------- ----------- ---------------- 
ASH 2,410.3 42.5 1,494 70.0 490.2 89% : 11% 
FIXED 2,149.7 37.9 1,332 62.5 437.2 48% : 52% 
EVENTS 489.7 8.6 304 14.2 99.6 43% : 57% 
SPACE 224.4 4.0 139 6.5 45.6 58% : 42% 
SQL 160.6 2.8 100 4.7 32.7 55% : 45% 
SQLPLAN 82.0 1.4 51 2.4 16.7 67% : 33% 
RAC 58.3 1.0 36 1.7 11.8 70% : 30% 
SQLTEXT 7.3 0.1 5 0.2 1.5 96% : 4% 
SQLBIND 6.0 0.1 4 0.2 1.2 33% : 67%

Note that the ASH data is the first component listed and the size is extensively larger than the FIXED, EVENTS, etc.  There are numerous reasons for this to have occurred, so we’ll investigate what could have caused the increase in space consumption, as over time, the extended retention into the AWR Warehouse will consume more space on the destination side, increasing requirements for the AWR Warehouse.

Why Is ASH Using So Much?

First, we’ll check to see what the minimum and maximum snap_id’s from both the dba_hist_snapshot in comparison to the AWR:

select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
       15027        15189

select min(snap_id),MAX(snap_id) from WRH$_ACTIVE_SESSION_HISTORY;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
           1        15189

As you can see, the AWR contains ASH data from the first snap_id when the dba_hist snapshot shows that only data from 15027 on should exist.

We’ll next check for orphaned rows of ASH data in the AWR:

SELECT COUNT(*) FROM wrh$_active_session_history a
  WHERE NOT EXISTS
  (SELECT 1
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );

If this exists, follow the steps from Oracle to Manually Purge the Optimizer Statistics & AWR Snaphots to Reduce Space Usage of SYSAUX Tablespace (Doc ID 1965061.1) to split the partitions and purge the data manually from the AWR from the SOURCE DATABASE, (target) to address before the ETL extracts and load the data to the AWR Warehouse.

ASH Parameters

The next reason for the extensive ASH data in the AWR could result in a change to the parameters involving how ASH data is written to the AWR.  I’ve only recently heard that some shops are doing this as a way to “audit” the SQL happening in their databases.  I have to admit, I would prefer to see DBAs use auditing features vs. use ASH samples to track this, but it is happening and they should expect the following:

1.  Extensive space usage by the AWR

2.  Inaccurate results in ASH and AWR reports due to Oracle expecting only 1:10 samples existing in the AWR and having 10:10 will impact the results.

The parameters controlling this feature are underscore parameters and should only be changed under the guidance of Oracle.

_ash_sampling_interval = 100  The interval that ASH samples, lessened, causing samples to be created more often than the default of 1000.

_ash_sample_all = TRUE  True results in samples of even inactive sessions to be created, increasing the amount of ASH data by 10X or more.

_ash_disk_filter_ratio = 1 Would result in ASH writing all samples to the AWR instead of 1:10.

Once you’ve addressed any issues in storage of the AWR and loaded all snapshots to your new AWR Warehouse, also remember to “dial down” the retention in the source database to a the default of 8 days, (or something close) and shrink the SYSAUX tablespace to reallocate the space back to the database, having no longer need of the space it once consumed.

The AWR Warehouse does require considerable storage for the performance data housed within the Automatic Workload Repository, but with the right process to inspect what is kept in your AWR before building out your environment, you can avoid having to allocate more storage than you really need to.