Oracle Scratchpad

November 24, 2006

Disabling AWR

Filed under: Infrastructure,Oracle,Performance — Jonathan Lewis @ 7:22 am BST Nov 24,2006

I see that in a comment on  the AWR Dilemma that Alex Gorbachev has pointed out the existence of the parameter _awr_restrict_mode  as a possible attack on disabling AWR.

For a few more options to disable the monitoring overheads of 10g when you aren’t licensed to use the diagnostic and performance packs, you might also want to investigate the effect of the following parameters – quoted here with their descriptions from x$ksppi.

_addm_auto_enable           governs whether ADDM gets run automatically after every AWR snapshot 
_ash_enable                 To enable or disable Active Session sampling and flushing 
_ash_disk_write_enable      To enable or disable Active Session History flushing 
_swrf_mmon_flush            Enable/disable SWRF MMON FLushing 
_swrf_mmon_metrics          Enable/disable SWRF MMON Metrics Collection 

There are several more related parameters but the selection above appears to enough to eliminate ASH, AWR, and ADDM.

As always, you need to remember that you should not set hidden parameters on production systems without approval from Oracle support. Note especially, I have only run a couple of small tests on the effects of these parameters – so I haven’t looked for any side-effects to changing their defaults.


  1. Oracle Support suggesting us to recreate the AWR using the below steps since our SYSAUX tablespace is keep growing:

    alter system set sga_target=0 scope=spfile;
    alter system set statistics_level = basic scope=both;
    alter system set cluster_database=false;

    shutdown immediate

    startup restrict
    alter system flush shared_pool;

    Did anybody tried this before?

    Appriciate any feedback.


    Comment by Padhu — July 7, 2009 @ 4:42 pm BST Jul 7,2009 | Reply

    • Padhu,

      I’ve not tried it – but if Oracle support is telling you to do it we can only assume that it is considered to be safe.
      However, you might want to run a query against dba_extents to check what other objects might be in the tablespace. Presumably they are also telling you to do this so that you can shrink the data files after rebuilding the AWR – but if there is something higher up the tablespace then you won’t be able to shrink the AWR. (And the AWR is larger partitioned tables anyway which drop and create partitions, so will be reusing cleared space over time anyway).

      Check the results of this (potentially very slow) query:

             /*+ rule */
             file_id, block_id, segment_name
             tablespace_name = 'SYSAUX'
      order by
             file_id, block_id

      Another idea, anyway – one of the biggest objects I have seen in SYSAUX is the optimizer histogram histogram table: wri$_optstat_histgrm_history (I think I’ve mentioned it on the blog or on the OTN forum somewhere). It’s possible that your problem lies with (a) having too many histograms generated by the automatic stats collection, and (b) having a long stats history retention.

      Comment by Jonathan Lewis — July 9, 2009 @ 6:54 pm BST Jul 9,2009 | Reply

  2. Padhu,

    You can check the V$SYSAUX_OCCUPANTS View to find out who/what is occupying space in SYSAUX.


    Comment by John Kanagaraj — July 10, 2009 @ 12:45 am BST Jul 10,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

You are commenting using your 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

Blog at