Enable and disable Diagnostic and tuning pack in Oracle

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

Leave a Reply

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

WordPress.com Logo

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

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