For complete detauls on managing AWR retention and flushing, see
the book
"Oracle Tuning: The Definitive Reference".
Question: : I want to flush or
change the retention for my AWR data. How can I flush my AWR
data? Is there a process for adjusting AWR retention?
Answer:
Follow the link for complete details on
flushing and adjusting the retention of AWR table data.
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 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';
