Disable Oracle Diagnostic Pack + Tuning Pack?


I see very often at customers that they use a Diagnostic or Tuning Pack on Standard Edition (note this is completely disallowed in this Edition) or Enterprise Edition. And this often happens, without any knowing of the DBA. Therefore I would like to show some small assistance.

One at the beginning, in Standard Edition or Standard Edition One there are no management packs. These can be purchased only with the Enterprise Edition, and they are also not a part of the Oracle Personal Edition.

First of all, is the Diagnostic Pack used?

col name format A30
col detected format 9999
col samples format 9999
col used format A5
col interval format 9999999

SELECT name,
       detected_usages detected,
                 total_samples   samples,
                 currently_used  used,
                 to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
                 sample_interval interval
FROM dba_feature_usage_statistics
WHERE name = 'Automatic Workload Repository';
NAME                               DETECTED SAMPLES USED  LAST_SAMPLE    INTERVAL
 ------------------------------    -------- ------- ----- --------------   --------
 Automatic Workload Repository     15       196     FALSE 06042011:02:38   604800

This output shows us that the AWR has 15 uses and saved 196 samples. Do not misleading interpret the USED or last sample date column. The point is, that there is a use.

You can also easily change the query to see the use of the tuning packs.

col name format A31
col detected format 9999
col samples format 9999
col used format A5
col interval format 9999999

SELECT name,       
       detected_usages detected,
       total_samples   samples,
       currently_used  used,
       to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
       sample_interval interval
  FROM dba_feature_usage_statistics
 WHERE name = 'Automatic Workload Repository'     OR  name like 'SQL%';
NAME                            DETECTED SAMPLES USED  LAST_SAMPLE    INTERVAL
------------------------------- -------- ------- ----- -------------- --------
Automatic Workload Repository          0      19 FALSE 03162013:01:49    604800
SQL Access Advisor                     0      19 FALSE 03162013:01:49    604800
SQL Monitoring and Tuning pages        0      19 FALSE 03162013:01:49    604800
SQL Performance Analyzer               0      19 FALSE 03162013:01:49    604800
SQL Plan Management                   19      19 TRUE  03162013:01:49    604800
SQL Profile                           19      19 TRUE  03162013:01:49    604800
SQL Repair Advisor                     0      19 FALSE 03162013:01:49    604800
SQL Tuning Advisor                     1      19 FALSE 03162013:01:49    604800
SQL Tuning Set (system)               19      19 TRUE  03162013:01:49    604800
SQL Tuning Set (user)                 19      19 TRUE  03162013:01:49    604800
SQL Workload Manager                   0      19 FALSE 03162013:01:49    604800

An overview of all the options can be found here for Oracle 11.2.

Please remember that this is a rough check here. Especially when Tuning Pack is in use, it is not quite so easy to find.

There is a suspicion of an unintended use? What we should consider now? This depends on the database version.

11G Database

From Oracle 11G onwards we have „control_management_pack_access“  database parameters. The pitfall is that this parameter has a default value of „DIAGNOSTIC + TUNING“ in a 11GR2 Enterprise Edition. So many of my Customers have buy a Oracle Enterprise Edition but did not buy any management break. But also check this parameter on a Standard Edition, as it is often accidentally activated with the dbconsole.

Checking the Parameter:

[oracle@dbm diagpack]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 17 12:38:53 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter control_management_pack_access
NAME                                   TYPE        VALUE
------------------------------------   ----------- ------------------------------
control_management_pack_access         string      DIAGNOSTIC+TUNING

If a pack is available, so it is best to change these parameters in accordance with its license.

The possible values are:

  • NONE – disable all management packs.
  • DIAGNOSTIC – Nur DIAGNOSTIC Pack available.
  • DIAGNOSTIC+TUNING – DIAGNOSTIC and TUNING Pack available.

The value can be changed at runtime.

SQL> ALTER SYSTEM SET control_management_pack_access=NONE;

10G Databases

In this case, it is more complicated, since there is no available parameter that controls the use. I advise anyone to start a dbconsole to examine it. In the setup, there is a point „Management Pack Access“ which indicates the activated licenses in the dbconsole. Another option is to set the database parameter „STATISTICS_LEVEL“ to Basic, but this has a greater impact on the system. As a last resort I recommend a visit to MOS Note: 436386.1 . Here you will find an additional database package which you can import to disable the AWR.

For more Information refer MOS Note 1909073.1 Disabling and Uninstalling AWR

As always, I welcome comments

Advertisements

5 Gedanken zu “Disable Oracle Diagnostic Pack + Tuning Pack?

  1. Hi Peter,
    Thanks for the note. BTW the note indicated here is pointing to a 11g related document. Can you share the steps for disabling diagnostics in 10g. Can we run this procedure to disable the diagnostics pack
    Execute dbms_workload_repository.modify_snapshot_settings(retention=>20160, interval=> 0);

    • Hello,
      Oracle has changed the note and deleted the 10g relevant Information.
      Your solution is also a technical way but i believe you are searching for MOS „Note 1909073.1 Disabling and Uninstalling AWR“.

      Hope this helps

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s