Oraclue

Oracle internals, debugging and undocumented features

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”='<table_name>’;

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’;

Advertisements

2 responses to “How to disable flush of ASH data to AWR?

  1. Anand October 26, 2010 at 12:13 am

    Really interesting stuff !!!!!

    How to go about learning oracle internals?

    Regards,
    Anand

  2. oraclue October 28, 2010 at 10:09 am

    Hi Anand,

    Long time ago I started by reading:
    http://www.ixora.com.au/

    and Steve’s book Oracle8i Internal Services for Waits, Latches, Locks, and Memory

    There are plenty of resources on internet.

    On My Oracle Support look at bugs description.Lot of good information.

    Also Tanel has good stuff on his blog and site..

    Use oradebug , bbed and hex editors to dump various structures

    http://www.evdbt.com/Oradebug_Modrakovic.pdf

    Miladin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: