Oracle Management Packs

There has been quite a bit of confusion about the licensing of Oracle Management Packs and about their functionality. Here are a couple of links to the Oracle Docs which describe what’s not allowed without licensing these packs.

10gR2 Management Pack Licensing

11gR1 Management Pack Licensing

A couple of things to be aware of.

  1. They are only available with Enterprise Edition
  2. The Tuning Pack depends on the Diagnostic Pack 

11gR1 includes a new parameter (CONTROL_MANAGEMENT_PACK_ACCESS) which controls access to features based on the Diagnostic and Tuning Packs at the database level. The valid values for this parameter are NONE, DIAGNOSTIC, and DIAGNOSTIC+TUNING. Setting this parameter to NONE disables several features at the database level including AWR (which is the only feature I’ll discuss in this post). By the way, the default is TUNING+DIAGNOSTICS, so AWR is still enabled by default. Also, note that snapshots are still created, even if the parameter is set to NONE, but they don’t contain any data. So they are not just limiting access to the reporting capability, but also disabling the collection of data. Here’s a quick example on a 11.1.0.7 database:

 

SQL> @test_cmpa
SQL> --
SQL> -- Test setting control_management_pack_access=NONE
SQL> --
SQL> select name,value from v$parameter where name like '%pack%';
 
NAME                                                                             VALUE
-------------------------------------------------------------------------------- ------------------------------
control_management_pack_access                                                   DIAGNOSTIC+TUNING
 
SQL> select snap_id, begin_interval_time, snap_level, snap_flag from dba_hist_snapshot
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);
 
   SNAP_ID BEGIN_INTERVAL_TIME       SNAP_LEVEL  SNAP_FLAG
---------- ------------------------- ---------- ----------
      3826 27-OCT-08 08.12.32.405 PM          1          1
 
SQL> select count(*) from dba_hist_sysstat
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);
 
  COUNT(*)
----------
       500
 
SQL> alter system set control_management_pack_access=NONE;
 
System altered.
 
SQL> select name,value from v$parameter where name like '%pack%';
 
NAME                                                                             VALUE
-------------------------------------------------------------------------------- ------------------------------
control_management_pack_access                                                   NONE
 
SQL> @awr_snap
SQL> exec dbms_workload_repository.create_snapshot();
 
PL/SQL procedure successfully completed.
 
SQL> select snap_id, begin_interval_time, snap_level, snap_flag from dba_hist_snapshot
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);
 
   SNAP_ID BEGIN_INTERVAL_TIME       SNAP_LEVEL  SNAP_FLAG
---------- ------------------------- ---------- ----------
      3827 27-OCT-08 08.13.10.553 PM          1          5
 
SQL> select count(*) from dba_hist_sysstat
  2    where snap_id = (select max(snap_id) from dba_hist_snapshot);
 
  COUNT(*)
----------
         0

So it seems clear that the setting affects not only reporting but also collecting since no records were created in the DBA_HIST_SYSSTATS view. You may have also noticed the SNAPSHOT_FLAG column which is new in 11g. This column is used for a couple of purposes. First, it indicates whether the snapshot was generated manually using the dbms_workload_repository.create_snapshot procedure or automatically by the standard scheduled AWR snapshot process. I’m not sure why that is important, but hey, what do I know. I guess someone at Oracle decided that checking on manually created snapshots is a way to track AWR usage. Here’s an excerpt from the DBMS_FEATURE_REGISTER_ALLFEAT package which is used to monitor usage of “special” features.

  /**************************************
   * Automatic Workload Repository (AWR)
   **************************************/
 
  declare
    DBFUS_AWR_STR CONSTANT VARCHAR2(1000) :=
      'select count(*), NULL, NULL ' ||
        'from wrm$_snapshot ' ||
        'where dbid = (select dbid from v$database) ' ||
          'and status = 0 ' ||
          'and bitand(snap_flag, 1) = 1 ' ||
          'and end_interval_time > ' || DBFUS_LAST_SAMPLE_DATE_STR;
 
  begin
    dbms_feature_usage.register_db_feature
     ('Automatic Workload Repository',
      dbms_feature_usage.DBU_INST_ALWAYS_INSTALLED,
      NULL,
      dbms_feature_usage.DBU_DETECT_BY_SQL,
      DBFUS_AWR_STR,
      'A manual Automatic Workload Repository (AWR) snapshot was taken ' ||
      'in the last sample period.');
  end;

By the way, there are a couple of views that will allow you to see how your database is spying on you (DBA_FEATURE_USAGE_STATISTICS and DBA_HIGH_WATER_MARK_STATISTICS). Here’s a couple of scripts to look at them, feature_usage.sql and feature_hwm_stats.sql. Note that these views exist in 10gR2 as well, so these scripts will work in a 10g environment as well.

SQL> @feature_usage
SQL> set lines 132
SQL> set pages 999
SQL> break on version skip 1
SQL> select version, name, detected_usages, currently_used, first_usage_date, last_usage_date
  2  from DBA_FEATURE_USAGE_STATISTICS
  3  where detected_usages > 0
  4  order by 1, 2
  5  /
 
VERSION           NAME                                                             DETECTED_USAGES CURRE FIRST_USA LAST_USAG
----------------- ---------------------------------------------------------------- --------------- ----- --------- ---------
11.1.0.6.0        Audit Options                                                                 24 TRUE  14-AUG-07 10-OCT-08
                  Automatic Maintenance - Optimizer Statistics Gathering                        24 TRUE  14-AUG-07 10-OCT-08
                  Automatic Maintenance - SQL Tuning Advisor                                    24 TRUE  14-AUG-07 10-OCT-08
                  Automatic Maintenance - Space Advisor                                         24 TRUE  14-AUG-07 10-OCT-08
                  Automatic Memory Tuning                                                       24 TRUE  14-AUG-07 10-OCT-08
                  Automatic SQL Tuning Advisor                                                  20 TRUE  21-AUG-07 10-OCT-08
                  Automatic Segment Advisor                                                     21 TRUE  21-AUG-07 10-OCT-08
                  Automatic Segment Space Management (system)                                   24 TRUE  14-AUG-07 10-OCT-08
                  Automatic Undo Management                                                     24 TRUE  14-AUG-07 10-OCT-08
                  Automatic Workload Repository                                                  4 TRUE  14-AUG-08 10-OCT-08
                  Character Set                                                                 24 TRUE  14-AUG-07 10-OCT-08
                  DICOM                                                                          1 FALSE 14-AUG-07 14-AUG-07
                  EM Database Control                                                            2 FALSE 14-AUG-07 21-AUG-07
                  Extensibility                                                                 24 TRUE  14-AUG-07 10-OCT-08
                  LOB                                                                           24 TRUE  14-AUG-07 10-OCT-08
                  Locally Managed Tablespaces (system)                                          24 TRUE  14-AUG-07 10-OCT-08
                  Locally Managed Tablespaces (user)                                            24 TRUE  14-AUG-07 10-OCT-08
                  Materialized Views (User)                                                     24 TRUE  14-AUG-07 10-OCT-08
                  Object                                                                        24 TRUE  14-AUG-07 10-OCT-08
                  Oracle Text                                                                   24 TRUE  14-AUG-07 10-OCT-08
                  Parallel SQL DDL Execution                                                     3 FALSE 14-AUG-07 28-AUG-07
                  Parallel SQL Query Execution                                                   6 TRUE  28-AUG-08 10-OCT-08
                  Partitioning (system)                                                         24 TRUE  14-AUG-07 10-OCT-08
                  Partitioning (user)                                                           24 TRUE  14-AUG-07 10-OCT-08
                  Recovery Area                                                                 24 TRUE  14-AUG-07 10-OCT-08
                  Resource Manager                                                              18 TRUE  21-AUG-07 10-OCT-08
                  Result Cache                                                                   1 TRUE  10-OCT-08 10-OCT-08
                  SQL Plan Management                                                            1 TRUE  10-OCT-08 10-OCT-08
                  SQL Tuning Advisor                                                             1 FALSE 28-AUG-08 28-AUG-08
                  Segment Advisor                                                               23 TRUE  21-AUG-07 10-OCT-08
                  Server Parameter File                                                         14 TRUE  14-AUG-07 10-OCT-08
                  Services                                                                      24 TRUE  14-AUG-07 10-OCT-08
                  XDB                                                                           24 TRUE  14-AUG-07 10-OCT-08
 
11.1.0.7.0        Audit Options                                                                  2 TRUE  17-OCT-08 24-OCT-08
                  Automatic Maintenance - Optimizer Statistics Gathering                         2 TRUE  17-OCT-08 24-OCT-08
                  Automatic Maintenance - SQL Tuning Advisor                                     2 TRUE  17-OCT-08 24-OCT-08
                  Automatic Maintenance - Space Advisor                                          2 TRUE  17-OCT-08 24-OCT-08
                  Automatic Memory Tuning                                                        2 TRUE  17-OCT-08 24-OCT-08
                  Automatic SQL Tuning Advisor                                                   2 TRUE  17-OCT-08 24-OCT-08
                  Automatic Segment Advisor                                                      2 TRUE  17-OCT-08 24-OCT-08
                  Automatic Segment Space Management (system)                                    2 TRUE  17-OCT-08 24-OCT-08
                  Automatic Undo Management                                                      2 TRUE  17-OCT-08 24-OCT-08
                  Automatic Workload Repository                                                  1 FALSE 17-OCT-08 17-OCT-08
                  Character Set                                                                  2 TRUE  17-OCT-08 24-OCT-08
                  Extensibility                                                                  2 TRUE  17-OCT-08 24-OCT-08
                  LOB                                                                            2 TRUE  17-OCT-08 24-OCT-08
                  Locally Managed Tablespaces (system)                                           2 TRUE  17-OCT-08 24-OCT-08
                  Locally Managed Tablespaces (user)                                             2 TRUE  17-OCT-08 24-OCT-08
                  Logfile Multiplexing                                                           2 TRUE  17-OCT-08 24-OCT-08
                  Materialized Views (User)                                                      2 TRUE  17-OCT-08 24-OCT-08
                  Object                                                                         2 TRUE  17-OCT-08 24-OCT-08
                  Oracle Text                                                                    2 TRUE  17-OCT-08 24-OCT-08
                  Parallel SQL DDL Execution                                                     2 TRUE  17-OCT-08 24-OCT-08
                  Partitioning (system)                                                          2 TRUE  17-OCT-08 24-OCT-08
                  Partitioning (user)                                                            2 TRUE  17-OCT-08 24-OCT-08
                  RMAN - Disk Backup                                                             2 TRUE  17-OCT-08 24-OCT-08
                  Recovery Area                                                                  2 TRUE  17-OCT-08 24-OCT-08
                  Recovery Manager (RMAN)                                                        2 TRUE  17-OCT-08 24-OCT-08
                  Resource Manager                                                               1 TRUE  24-OCT-08 24-OCT-08
                  Result Cache                                                                   1 TRUE  24-OCT-08 24-OCT-08
                  SQL Plan Management                                                            2 TRUE  17-OCT-08 24-OCT-08
                  Segment Advisor                                                                2 TRUE  17-OCT-08 24-OCT-08
                  Server Parameter File                                                          2 TRUE  17-OCT-08 24-OCT-08
                  Services                                                                       2 TRUE  17-OCT-08 24-OCT-08
                  XDB                                                                            2 TRUE  17-OCT-08 24-OCT-08
 
65 rows selected.
 
SQL> @feature_hwm_stats
SQL> set lines 155
SQL> col description for a60
SQL> col name for a30
SQL> break on version skip 1
SQL> select version, name, highwater, last_value, description from dba_high_water_mark_statistics
  2  order by  version, name
  3  /
 
VERSION           NAME                            HIGHWATER LAST_VALUE DESCRIPTION
----------------- ------------------------------ ---------- ---------- ------------------------------------------------------------
11.1.0.6.0        ACTIVE_SESSIONS                4.36474119   .0221502 Maximum Number of Active Sessions seen in the system
                  CPU_COUNT                              16         16 Maximum Number of CPUs
                  DATAFILES                               5          5 Maximum Number of Datafiles
                  DB_SIZE                        4797759488 4797759488 Maximum Size of the Database (Bytes)
                  INSTANCES                               1          1 Oracle Database instances
                  PART_INDEXES                            2          2 Maximum Number of Partitions belonging to an User Index
                  PART_TABLES                             2          2 Maximum Number of Partitions belonging to an User Table
                  QUERY_LENGTH                         1904       1371 Maximum Query Length
                  SEGMENT_SIZE                   1484783616 1484783616 Size of Largest Segment (Bytes)
                  SESSIONS                               70         33 Maximum Number of Concurrent Sessions seen in the database
                  SQL_NCHAR_COLUMNS                      39         39 Maximum Number of SQL NCHAR Columns
                  TABLESPACES                             6          6 Maximum Number of Tablespaces
                  USER_INDEXES                         2797       2797 Number of User Indexes
                  USER_MV                                 1          1 Maximum Number of Materialized Views (User)
                  USER_TABLES                          1502       1502 Number of User Tables
 
11.1.0.7.0        ACTIVE_SESSIONS                5.97538533 5.44210966 Maximum Number of Active Sessions seen in the system
                  CPU_COUNT                              16         16 Maximum Number of CPUs
                  DATAFILES                               5          5 Maximum Number of Datafiles
                  DB_SIZE                        5697044480 5697044480 Maximum Size of the Database (Bytes)
                  INSTANCES                               1          1 Oracle Database instances
                  PART_INDEXES                            2          2 Maximum Number of Partitions belonging to an User Index
                  PART_TABLES                             2          2 Maximum Number of Partitions belonging to an User Table
                  QUERY_LENGTH                       392780     392780 Maximum Query Length
                  SEGMENT_SIZE                   1484783616 1484783616 Size of Largest Segment (Bytes)
                  SESSIONS                               66         66 Maximum Number of Concurrent Sessions seen in the database
                  SQL_NCHAR_COLUMNS                      39         39 Maximum Number of SQL NCHAR Columns
                  TABLESPACES                             6          6 Maximum Number of Tablespaces
                  USER_INDEXES                         2855       2855 Number of User Indexes
                  USER_MV                                 1          1 Maximum Number of Materialized Views (User)
                  USER_TABLES                          1530       1530 Number of User Tables
 
30 rows selected.

But I digress, remember the SNAP_FLAG field that I said was used for a couple of purposes. Well the second thing it’s used for is as an indicator of whether there is any data associated with a snapshot (i.e. if the CONTROL_MANAGEMENT_PACK_ACCESS parameter was set to NONE when the snapshot was generated). So after a little playing around here’s a table of possible values:

I’m pretty sure about the Snap_Mode (i.e. whether it’s automatic or manual) because of the code snippet we saw in the usage monitoring package (DBMS_FEATURE_REGISTER_ALLFEAT). The Data_Collection part is just a guess based on my observation (and very little observation actually, so there may well be other possibilities), but it appears that the 3rd bit (4) is on (1xx) when data has not been gathered (i.e. when CONTROL_MANAGEMENT_PACK_ACCESS=NONE). I have no idea what the second bit does by the way. So here’s a little script to look at the snapshots using what we’ve learned thus far (it shows the number of SQL statements on which data has been collected as a verification of whether data has been collected for this snapshot or not):

SQL> @awr_snaps2
SQL> col snap_mode for a10
SQL> col awr_mode for a10
SQL> select a.snap_id, snap_flag,
  2  decode(bitand(snap_flag,1),1,'Manual','Automatic') Snap_Mode,
  3  decode(bitand(snap_flag,4),4,'Off','On') Data_collection,
  4  count(sql_id)
  5  from dba_hist_snapshot a, dba_hist_sqlstat b
  6  where a.snap_id = b.snap_id(+)
  7  and begin_interval_time > sysdate-1
  8  group by a.snap_id, snap_flag
  9  order by 1
 10  /
 
   SNAP_ID  SNAP_FLAG SNAP_MODE  DATA_COLLECTION COUNT(SQL_ID)
---------- ---------- ---------- --------------- -------------
      3770          0 Automatic  ON                         90
      3771          0 Automatic  ON                         85
      3772          0 Automatic  ON                         83
      3773          0 Automatic  ON                         86
      3774          0 Automatic  ON                         89
      3775          5 Manual     OFF                         0
      3776          5 Manual     OFF                         0
      3777          1 Manual     ON                         93
      3778          1 Manual     ON                         96
      3779          1 Manual     ON                         99
      3780          5 Manual     OFF                         0
      3781          5 Manual     OFF                         0
      3782          5 Manual     OFF                         0
      3783          5 Manual     OFF                         0
      3784          1 Manual     ON                         98
      3785          5 Manual     OFF                         0
      3786          1 Manual     ON                         88
      3787          1 Manual     ON                         68
      3788          1 Manual     ON                         99
      3789          1 Manual     ON                         74
      3790          0 Automatic  ON                        109
      3791          4 Automatic  OFF                         0
      3792          5 Manual     OFF                         0
      3793          1 Manual     ON                        100
      3794          1 Manual     ON                         72
      3795          1 Manual     ON                         72
      3796          5 Manual     OFF                         0
      3797          1 Manual     ON                         77
      3798          1 Manual     ON                         71
      3799          1 Manual     ON                         71
      3800          5 Manual     OFF                         0
      3801          1 Manual     ON                         68
      3802          1 Manual     ON                         72
      3803          1 Manual     ON                         73
      3804          5 Manual     OFF                         0
      3805          1 Manual     ON                         78
      3806          1 Manual     ON                         73
      3807          1 Manual     ON                         72
      3808          5 Manual     OFF                         0
      3809          1 Manual     ON                         79
      3810          1 Manual     ON                         69
      3811          0 Automatic  ON                         82
      3812          0 Automatic  ON                         86
      3813          0 Automatic  ON                         88
      3814          4 Automatic  OFF                         0
 
45 rows selected.

So what’s this good for. Well not that much probably, but at least we’ll be able to easily see what we can and can’t access with or without the DIAGNOSTICS and TUNING packs. And it’s nice to know that, if were’ not licensed to use AWR, we can turn off the collection of the data easily. (No sense spending CPU cycles collecting data we’re not licensed to look at)

6 Comments

  1. [...] Pack, despite the fact the there is no easy way to disable the data collection. I posted earlier on Oracle Management Packs and associated licensing here, by the [...]

  2. [...] Jonathan Lewis reminded me on my last post that using SQL Profiles (because they are part of the SQL Tuning Advisor) requires a license for Oracle Tuning Pack (which requires a license for the Diagnostics Pack). He also mentioned that Baselines did not require any additional license (at least creating and using Baselines on SQL statements). It’s been a while since I worked on a database that didn’t have both Packs, but Frankly I wasn’t sure I had a good handle of what was allowed and what wasn’t so I thought it might be worthwhile to check. There is an easy way to check by the way. I did a post a while back on Tuning Pack and Diagnostic Pack license requirements for running AWR and how to check what was allowed and what wasn’t using the CONTROL_MANAGEMENT_PACK_ACCESS parameter. Here’s a link to the post: Oracle Management Packs [...]

  3. vijay says:

    how to identify oracle management packs are installed?

  4. osborne says:

    As far as I know, management packs are always installed. Changing the setting of the CONTROL_MANAGEMENT_PACK_ACCESS parameter turns their usage on and off.

  5. Robkol says:

    Hi,

    I have collected management pack feature usage information from ~500 databases (of which one is already version 12.1) from dba_feature_usage_statistics. I searched for those entries where upper(dba_feature_usage_statistics.name) like ‘%PACK%’.
    Here is the resulting list group by database version:

    Change Management Pack 10.1.0.4.0
    Change Management Pack 10.2.0.4.0
    Change Management Pack 11.1.0.6.0
    Data Masking Pack 10.1.0.4.0
    Data Masking Pack 10.2.0.4.0
    Data Masking Pack 11.1.0.6.0
    EM AS Provisioning and Patch Automation Pack 10.1.0.4.0
    EM AS Provisioning and Patch Automation Pack 10.2.0.4.0
    EM AS Provisioning and Patch Automation Pack 11.1.0.6.0
    EM Config Management Pack 10.1.0.4.0
    EM Config Management Pack 10.2.0.4.0
    EM Config Management Pack 11.1.0.6.0
    EM Database Provisioning and Patch Automation Pack 10.1.0.4.0
    EM Database Provisioning and Patch Automation Pack 10.2.0.4.0
    EM Database Provisioning and Patch Automation Pack 11.1.0.6.0
    EM Standalone Provisioning and Patch Automation Pack 10.1.0.4.0
    EM Standalone Provisioning and Patch Automation Pack 10.2.0.4.0
    EM Standalone Provisioning and Patch Automation Pack 11.1.0.6.0

    To me it seems that these dba_feature_usage_statistics (let’s call it dfus) entries were removed at a certain moment.
    We simply don’t have them in 10.1.0.5.0, 10.2.0.5.0, 11.1.0.7.0, 11.2.x, 12.x

    Can you explain why these dfus entries disappeared?
    Was there any problem with them in the “old” releases? Could we trust them in those releases?
    What can we use in the “new” releases instead?

    Regards,
    R

  6. osborne says:

    Hi Robkol,

    I have no idea why Oracle would change what it spies on between releases. However, it is clear that they do. Check out WRI$_DBU_FEATURE_METADATA for the actual SQL statements used for the checks. (you probably already were aware of that anyway). For example, the checking on AWR usage has changed significantly between 10g and 11gR2. Here’s what they did in 10.2.0.4:

    SYS@LAB1024> select dbms_sqldiag_internal.I_GET_DBVERSION() version from dual;
    
    VERSION
    ----------
    10.2.0.4.0
    
    SYS@LAB1024> @spy
    Enter value for name: Automatic Workload%
    
    NAME                           USG_DET_LOGIC
    ------------------------------ ----------------------------------------------------------------------------------------------------
    Automatic Workload Repository  select count(*), NULL, NULL from wrm$_snapshot where dbid = (select dbid from v$database) and status
                                   = 0 and bitand(snap_flag, 1) = 1 and end_interval_time > (select max(last_sample_date) from
                                   wri$_dbu_usage_sample)
    

    which basically checked to see if you had changed the snap interval. And in 11.2.0.3 they did a more reasonable check (i.e. whether you ran an AWR report).

    SYS@dbm1> select dbms_sqldiag_internal.I_GET_DBVERSION() version from dual
      2  /
    
    VERSION
    ----------
    11.2.0.3.0
    
    Elapsed: 00:00:00.01
    SYS@dbm1> @spy
    Enter value for name: AWR%
    
    NAME                   USG_DET_LOGIC
    ---------------------- ----------------------------------------------------------------------------------------------------
    AWR Baseline           select count(*), count(*), NULL from dba_hist_baseline where baseline_name != 'SYSTEM_MOVING_WINDOW'
    AWR Baseline Template  select count(*), count(*), NULL from dba_hist_baseline_template
    AWR Report             with last_period as
                           (select * from wrm$_wr_usage
                           where upper(feature_type) like 'REPORT'
                           and usage_time >=  (select nvl(max(last_sample_date), sysdate-7) from wri$_dbu_usage_sample) )
                           select decode (count(*), 0, 0, 1),
                           count(*),
                           feature_list
                           from last_period,
                           (select substr(sys_connect_by_path(feature_count, ','),2) feature_list
                           from
                           (select feature_count,
                           count(*) over () cnt,
                           row_number () over (order by 1) seq
                           from
                           (select feature_name || ':' || count(*) feature_count
                           from last_period
                           group by feature_name)
                           )
                           where seq=cnt
                           start with seq=1
                           connect by prior seq+1=seq)
                           group by feature_list
    

    I should probably write blog post about this. :)

Leave a Reply