Oracle DBA – A lifelong learning experience

Disabling ADDM in 10g and 11g

Posted by John Hallas on January 17, 2009

You may not be licensed to use ADDM and want to stop automatic statistics collection, which is enabled by default.

The method alters from 10g to 11g


Use the package to modify the snapshot interval to 0

Execute dbms_workload_repository.modify_snapshot_settings
(retention=>20160, interval=> 0);


The initialization parameter should be set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable automatic database diagnostic monitoring. Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables many Oracle Database features, including ADDM, and is strongly discouraged, but seems to be the only way to disable the automatic monitoring.

Tuning relies on having a diagnostic license which explains why the options are NONE, DIAGNOSTIC or DIAGNOSTIC+TUNING


3 Responses to “Disabling ADDM in 10g and 11g”

  1. there is way you can do that , that is set _addm_auto_enable to false

  2. Agree with Siva
    By doing this you disable the entire AWR infrastructure.
    To disable only ADDM just run the folllowing online:
    ALTER SYSTEM set “_addm_auto_enable”=false;

    — Kirill Loifman,

    • John Hallas said

      Thanks chaps. I did write this post in 2009 so I am not sure if the option was available then or not but thanks for clarifying.

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 )

Google+ photo

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

%d bloggers like this: