How to disable flush of ASH data to AWR?

MMON process will periodically flush ASH data into AWR tables.


Oracle introduced WF enqueue which is used to serialize the flushing of snapshots.



If for any reason ( space issue, bugs, hanging etc..) you need to disable flushing the run time statistics for

particular table than following procedure needs to be done.



First, locate the exact AWR Table Info (KEW layer):



SQL> select table_id_kewrtb, table_name_kewrtb from x$kewrtb order by 1



TABLE_ID_KEWRTB TABLE_NAME_KEWRTB

————— —————————————————————-

0 WRM$_DATABASE_INSTANCE

1 WRM$_SNAPSHOT

2 WRM$_BASELINE

3 WRM$_WR_CONTROL



—-



TABLE_ID_KEWRTB TABLE_NAME_KEWRTB

————— —————————————————————-

99 WRH$_RSRC_PLAN

100 WRM$_BASELINE_DETAILS

101 WRM$_BASELINE_TEMPLATE

102 WRH$_CLUSTER_INTERCON

103 WRH$_MEM_DYNAMIC_COMP

104 WRH$_IC_CLIENT_STATS

105 WRH$_IC_DEVICE_STATS

106 WRH$_INTERCONNECT_PINGS



107 rows selected.



1st option :



SQL> alter system set “_awr_disabled_flush_tables”=’’;



e.g.



alter system set “_awr_disabled_flush_tables”=’WRH$_INTERCONNECT_PINGS,WRH$_RSRC_PLAN’;



System altered.



2nd option:



SQL> alter session set events ‘immediate trace name awr_flush_table_off level 106′;

SQL> alter session set events ‘immediate trace name awr_flush_table_off level 99′



If you decide to turn on flushing statistics than



SQL> alter session set events ‘immediate trace name awr_flush_table_on level 106′;

SQL> alter session set events ‘immediate trace name awr_flush_table_on level 99′;