|
|
AWR Retention period sizing guidelines
Oracle Database Tips by Donald Burleson |
For complete details on managing AWR retention and
flushing, see
Oracle Tuning: The Definitive Reference.
Also see my notes on
removing
AWR and
disabling AWR data purging.
Out of the box, the AWR automatically captures
performance metrics every hour. Some internal database operations,
such as the Undo Advisor, use AWR data. The Diagnostics Pack does
not have to be licensed to collect the metrics or use this advisor.
The Diagnostics Pack will have to be licensed to run reports against
the AWR, query any dba_hist_* views, use ASH, leverage ADDM, or use
the Performance page in Oracle Enterprise Manager, which will be
discussed in the next chapter. Using the Diagnostics Pack in
Enterprise Manager is often the biggest time saver. The
Automated Workload Repository contains performance metrics gathered
once per hour by default and stores the results in the sysaux
tablespace. With Oracle RAC databases, the only difference is that
the number of instances will linearly scale the AWR data volume. By
default, AWR will keep eight days data in the repository. Oracle
includes a nice script, $ORACLE_HOME/rdbms/admin/awrinfo.sql, which
can help you keep track of the AWR repository sizing. The
output of a sample awrinfo.sql script will be discussed in this
section. The first portion of the script output shows some
introductory information. Notice the snapshot interval and
retention. Oddly, this script is warning that there is a non-default
AWR setting but this database is out-of-the-box with no custom AWR
configuration. ~~~~~~~~~~~~~~~
AWR INFO Report ~~~~~~~~~~~~~~~ Report generated at
11:44:21 on Sep 16, 2015 ( Tuesday ) in Timezone -06:00
Warning: Non Default AWR Setting!
----------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days
DB_ID DB_NAME
HOST_PLATFORM
INST STARTUP_TIME ------------ -------
-------------------------- ----- ----------------- * 2793090278
ORCL host01 - Linux x86 64-bit
1 10:12:20 (08/28)
Setting an appropriate retention
interval for your AWR 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 |
Monthly
space required (gig) |
Small |
10 |
2 |
500 |
2 |
Medium |
20 |
8 |
5,000 |
8 |
Large |
100 |
32 |
50,000 |
20 |
Here, we choose out approximate database size
and see estimated space for the AWR dba_hist tables. Oracle
provides the SYSAUX sizing utility called utlsyxsz.sql (in
$ORACLE_HOME/rdbms/admin) for estimating your AWR retention
based on disk space consumption.
AWR also supports the creation of "AWR
baselines", which are a "named" series of AWR snapshots that are
immune from being purged by the AWR retention mechanism. the
create_baseline procedure allows you to specify a start_snapshot and
end_snapshot and name the baseline:
DBMS_WORKLOAD_REPOSITORY.create_baseline (1900, 2000, 'EOY_PEAK_LOADS');
Note: You must purchase the
extra cost Oracle performance pack and Oracle diagnostic packs to use
AWR. Else, you can use the free STATSPACK tables which provide almost
the same functionality.
The dbms_workload_repository package can
be used by Oracle DBAs to configure AWR settings such as snapshot
interval, flushing and data retention.
The dbms_workload_repository
.modify_snapshot_settings procedure is used to configure AWR data
retention in the database. The retention interval determines the
length of time that data will be preserved in the AWR. The default
setting for retention is 10,080 minutes (one week).
desc dbms_workload_repository
PROCEDURE
MODIFY_SNAPSHOT_SETTINGS Argument Name Type
In/Out Default? ------------------------------ ----------- ------
-------- RETENTION NUMBER IN DEFAULT
INTERVAL NUMBER IN DEFAULT
DBID NUMBER IN DEFAULT
The range of valid values for this parameter ranges
from ten minutes to 52,560,000 minutes or 100 years.
The dbms_workload_repository package
contains the global variables of min_retention and
max_retention. These variables set the lower and upper limits
for the retention parameter.
If a zero value is specified for retention,
snapshots will be stored for an unlimited amount of time. The
following awr_settings.sql script can be used to check the current
settings for the AWR interval and retention settings:
awr_settings.sql
select
extract( day from snap_interval) *24*60+ extract( hour
from snap_interval) *60+ extract( minute from
snap_interval ) "Snapshot Interval", extract( day from
retention) *24*60+ extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval" from
dba_hist_wr_control;
The script returns the current AWR interval values
in minutes:
Snapshot Interval Retention Interval
----------------- ------------------ 60
10080
AWR flushing and the MMON background process
The MMON Oracle background process is
responsible for periodically flushing the oldest AWR tables, using a
LIFO queue method. Here, we see the flush_level for an AWR
installation:
SQL> desc dbms_workload_repository
PROCEDURE
CREATE_SNAPSHOT Argument Name
Type In/Out Default? ------------------------------
----------------------- ------ --------
FLUSH_LEVEL VARCHAR2 IN DEFAULT
FUNCTION CREATE_SNAPSHOT RETURNS NUMBER Argument
Name Type In/Out Default? ------------------------------
----------------------- ------ -------- FLUSH_LEVEL
VARCHAR2 IN DEFAULT
The only parameter listed in the procedures is
the flush_level , which can have either the default value of
TYPICAL or a value of ALL. When the statistics level is set to
ALL, the AWR gathers the maximum amount of performance data.
The MMON background process is responsible for
removing old historical data from the AWR. The amount of retention
time after which data will be removed from database is determined by
the retention setting. However, data can be cleared from the AWR
tables by using the dbms_workload_repository.drop_snapshot_range
procedure. The starting and ending snapshots for the history to be
removed from the AWR will need to be set to run the following script:
desc dbms_workload_repository
PROCEDURE
DROP_SNAPSHOT_RANGE Argument Name
Type In/Out Default? ------------------------------
-------------------- ------ -------- LOW_SNAP_ID
NUMBER IN HIGH_SNAP_ID
NUMBER IN DBID
NUMBER IN DEFAULT
Disabling and enabling automatic AWR
flushing
You can disable the AWR automatic data
flushing mechanism at either the system level or disable flushing for
individual tables.
For system-wide disable of AWR table flushing
you can use these commands but beware that the AWR tablespace will
continue to grow ad-infinitum:
alter session set events 'immediate trace name
awr_flush_table_off level 99′ alter session set events 'immediate
trace name awr_flush_table_off level 106′;
To return to the default of weekly flushing
you can issue these commands:
alter session set events 'immediate trace name
awr_flush_table_on level 99′; alter session set events 'immediate
trace name awr_flush_table_on level 106′;
If you need to disable flushing the run time
statistics for an AWR workload table, you can get the underlying WRH
tables with this query:
select table_id_kewrtb, table_name_kewrtb
from x$kewrtb order by table_id_kewrtb;
Once you identify a specific table to disable
flushing, you can use an ALTER SYSTEM command:
alter system set
"_awr_disabled_flush_tables"=' WRH$_IC_CLIENT_STATS';
|