Wednesday, 27 April 2011

Disabling ADDM in 10g and 11g

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

No comments: