Enable and disable Diagnostic and tunning pack in Oracle
ADDM reports require separate license for configuration the diagnostic and diagnostic & tuning pack.
It cover in two packs: Oracle Diagnostics Pack & Oracle Tunning Pack.
Oracle Diagnostic pack includes AWR, ADDM, and so on.
Oracle Tunning pack includes SQL Tuning Advisor, SQLAccess Advisor, and so on.
In Oracle EE edition, Parameter CONTROL_MANAGEMENT_PACK_ACCESS is enabled by default.
Automatic database diagnostic monitoring is enabled by default.
you can control the ADDM and AWR reports with the following parameters
CONTROL_MANAGEMENT_PACK_ACCESS
STATISTICS_LEVEL
Check the status of Diagnostic pack
SQL> show parameter control_management
NAME TYPE VALUE ------------------------------- -------- ------ control_management_pack_access string NONE
Enable Diagnostic Pack in Oracle
-- Set the value CONTROL_MANAGEMENT_PACK_ACCESS = { DIAGNOSTIC | DIAGNOSTIC+TUNING }
-- Enable diagnostic pack only
ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS = "DIAGNOSTIC";
-- Enable diagnostic and tuning pack
ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS= "DIAGNOSTIC+TUNING" ;
Disable the Diagnostic pack
ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS = "NONE";
Statistics_level is used to collect statistics of Database and operating system.
It has following three values:
STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }
TYPICAL is a collection of all major statistics required for database (default).
ALL more detailed information need then we use ALL
BASIC stop all AWR, ADDM , advisory so we not set it.
SQL> show parameter statistics_level
NAME TYPE VALUE ------------------ -------- -------- statistics_level string TYPICAL