“Free” DBA_HIST AWR views in 11g…

I just noticed this in 11g Licensing doc ( http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/options.htm#sthref69 ):

  • All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Diagnostic Pack license.

This exception is not present in 10.2 license guide, so before 11g you can query V$SEGMENT_STATISTICS and V$UNDOSTAT’s history “for free” :)

Of course, collecting this data manually with a 1-line PL/SQL loop script isn’t hard either :)

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

6 Responses to “Free” DBA_HIST AWR views in 11g…

  1. patrick says:

    I was told Oracle has way to know whether AWR tables have been queried ? what if someone export all the AWR data to an external non-Oracle db for
    reporting/consolidation ? would the license apply to this case ?
    interesting topic

  2. Tanel Poder says:

    When you access Oracle AWR (and other licensed features/tables/views) then these do show up in DBA_FEATURE_USAGE_STATISTICS. Exporting the contents is the same as accessing these with your SQL.

    Nevertheless, you don’t want to be breaking your license agreement even if Oracle wouldn’t track the usage. However I think many companies are unknowingly violating the conditions as DBAs and developers like to download random scripts from internet to see whether it fixes their problem or not.

    Oracle introduced a parameter in 11g which allows you to disable access to some separately licensed features, you can set control_management_pack_access = none to make diag/tuning pack stuff unavailable to users.

  3. patrick says:

    I have this on one db
    you can notice the recent upgrade …
    though I can’t see anything for AWR tables
    I guess now with control_management_pack_access = none
    Oracle has a better way to check for AWR use
    I shall make a test with 11gR2 …

    NAME VERSION DETECTED_USAGES
    —————————————- ———- —————
    Automatic Database Diagnostic Monitor 10.2.0.3.0 16
    Automatic SQL Execution Memory 10.2.0.3.0 88
    Automatic Segment Space Management (syst 10.2.0.3.0 88
    em)

    Automatic Segment Space Management (user 10.2.0.3.0 87
    )

    Automatic Storage Manager 10.2.0.3.0 88
    Automatic Undo Management 10.2.0.3.0 88
    Character Set 10.2.0.3.0 88
    Dynamic SGA 10.2.0.3.0 80
    Internode Parallel Execution 10.2.0.3.0 85
    Locally Managed Tablespaces (system) 10.2.0.3.0 88
    Locally Managed Tablespaces (user) 10.2.0.3.0 88
    Parallel SQL DDL Execution 10.2.0.3.0 2
    Parallel SQL Query Execution 10.2.0.3.0 85
    Partitioning (system) 10.2.0.3.0 88
    Protection Mode – Maximum Performance 10.2.0.3.0 88
    RMAN – Tape Backup 10.2.0.3.0 80
    Real Application Clusters (RAC) 10.2.0.3.0 87
    Recovery Manager (RMAN) 10.2.0.3.0 80
    SQL Tuning Advisor 10.2.0.3.0 4
    Segment Advisor 10.2.0.3.0 87
    Server Parameter File 10.2.0.3.0 87
    Shared Server 10.2.0.3.0 20
    Streams (system) 10.2.0.3.0 88
    Automatic SQL Execution Memory 10.2.0.4.0 1
    Automatic Segment Space Management (syst 10.2.0.4.0 1
    em)

    Automatic Segment Space Management (user 10.2.0.4.0 1
    )

    Automatic Storage Manager 10.2.0.4.0 1
    Automatic Undo Management 10.2.0.4.0 1
    Character Set 10.2.0.4.0 1
    Internode Parallel Execution 10.2.0.4.0 1
    LOB 10.2.0.4.0 1
    Locally Managed Tablespaces (system) 10.2.0.4.0 1
    Locally Managed Tablespaces (user) 10.2.0.4.0 1
    Parallel SQL Query Execution 10.2.0.4.0 1
    Partitioning (system) 10.2.0.4.0 1
    Protection Mode – Maximum Performance 10.2.0.4.0 1
    RMAN – Tape Backup 10.2.0.4.0 1
    Real Application Clusters (RAC) 10.2.0.4.0 1
    Recovery Manager (RMAN) 10.2.0.4.0 1
    Segment Advisor 10.2.0.4.0 1
    Server Parameter File 10.2.0.4.0 1
    Streams (system) 10.2.0.4.0 1

  4. patrick says:

    new statictic name have been added in 11g, namely

    AWR Baseline => At least one AWR Baseline has been created by the user

    AWR Baseline Template => At least one AWR Baseline Template has been created by the user

    AWR Report => At least one Workload Repository Report has been created by the user

    CONTROL_MANAGEMENT_PACK_ACCESS specifies which of the Server Manageability Packs should be active. The following packs are available:

    *

    The DIAGNOSTIC pack includes AWR, ADDM, and so on.
    *

    The TUNING pack includes SQL Tuning Advisor, SQLAccess Advisor, and so on.

    A license for DIAGNOSTIC is required for enabling the TUNING pack.

    Values:

    *

    NONE

    Both packs are not available.
    *

    DIAGNOSTIC

    Only the DIAGNOSTIC pack is available.
    *

    DIAGNOSTIC+TUNING

    Both packs are available. This is the default.

    so only the DIAGNOSTIC pack license
    is ncessary for using AWR

  5. Chris Brown says:

    I think you interpretation is off. Also the wording in the DOC is obtuse.

    “They can be used without the Diagnostic Pack license” refers to the preceding sentence only and not to every DBA_HIST view…only a subset can be used.

    Do you now agree?

    • Tanel Poder says:

      I haven’t even offered any interpretation in that article, basically just quoted the docs for everyone to interpret themselves. Someone might misinterpret the title of my post (that “free AWR views” means “everything is free now” as opposed to “some AWR views are free”)… but that’s exactly why I posted the document excerpt.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>