Oracle Scratchpad

April 11, 2007

AWR – again

Filed under: Infrastructure — Jonathan Lewis @ 10:34 pm BST Apr 11,2007

Some time ago I wrote about the problems of disabling the automatic workload repository (AWR). In one of the comments, John Boogaard asked the question: “Can you just run  script:  ?/rdbms/admin/catnoawr.sql instead ?”

I finally upgraded my test machine to – and the answer to this remarkably simple question is yes.

The script calls procedure dbms_swrf_internal.remove_wr_control, which deletes a row relating to your database from the wrm$_wr_control table, and then drops all the AWR tables.

If you change your mind, by the way, there is no equivalent catawr.sql script. There are four different scripts related to building the stuff you need for AWR, but you are probably better off calling catsvrm.sql – which rebuilds all the “manageability” bits of 10g, and deals with the complicated interdependencies between objects and routines used by the various bits of AWR, ASH (active session history) and the rest.


  1. Stupid question, where did you find the document stating that this script catsvrm.sql rebuilds all the manageability bits.

    I’ve been looking for this for a while.

    Comment by RobH — April 13, 2007 @ 5:12 pm BST Apr 13,2007 | Reply

  2. RobH, it may have been a bit naive of me, but I just read the header to the script itself:

    Rem      catsvrm.sql - Catalog script for Server Manageability
    Rem      Runs all scripts for Server Manageability

    Bits like this aren’t guaranteed to be 100% true, of course; and I don’t always trust such comments, but when the code is still quite new the comments are fairly likely to be true.

    Comment by Jonathan Lewis — April 14, 2007 @ 4:58 pm BST Apr 14,2007 | Reply

  3. Right, but did you just grep all the files in rdbms/admin looking for ADDM or AWR? I didn’t see anything in the oracle docs and very few in metalink that refer to this script.

    I ask because recently I ran into a problem on AIX of not being able to compile some of the AWR procs (DBMS_SWRF_REPORT_INTERNAL). I hoped I could drop and recreate but I couldn’t find the information to properly to it. In the end it was a ulimit issue, but I was just wondering how you knew that script rebuilds the AWR procs and views.

    Comment by RobH — April 17, 2007 @ 6:49 pm BST Apr 17,2007 | Reply

  4. Metalink note 436386.1 documents now an official way to disable AWR.
    See as well

    Comment by Martin — February 24, 2008 @ 3:06 pm GMT Feb 24,2008 | Reply

  5. I am here redirected from google so forgive me if the answer is obvious, but…
    Why would you like to disable AWR?
    Juan Martín.

    Comment by Juan Martin Guillen — August 18, 2010 @ 11:09 pm BST Aug 18,2010 | Reply

  6. You might wish to disable (or really drop and recreate) the AWR repository as a quick means of removing mountains of old snapshots which have been accumulated by problems around the mmon process not being given long enough to complete its nightly work

    Comment by John Hallas — April 9, 2014 @ 11:33 am BST Apr 9,2014 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: