7 Replies Latest reply: Aug 18, 2011 5:01 AM by AronTunzi RSS

    AWR and Standard Edition RAC?

    thtsang
      I know the option packs are not available in Standard Edition. However, In the "Oracle Database Licensing Information" document, it's said that Standard Edition RAC includes "Automatic Workload Management", which in turn includes AWR.

      http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/editions.htm#BABDJGGI
      http://download.oracle.com/docs/cd/E11882_01/rac.112/e16795/hafeats.htm#sthref412

      Searching Oracle document showed that the phrase appeared only in these 2 location. So they should mean the same thing. That means if we run Standard Edition RAC, we should be able to run the AWR reports.

      However, the report does not contain valuable information. Actually most of the AWR tables, like WRH$_SQLSTAT, WRH$_ACTIVE_SESSION_HISTORY are empty.

      Any idea?


      Oracle 11g 11.2.0.2 Standard Edition RAC
      RHEL5.3 (surely irrelevant)
        • 1. Re: AWR and Standard Edition RAC?
          P.Forstmann
          Automatic Workload Management and Automatic Workload Repository are 2 differents things as clearly stated in the link you mentioned http://download.oracle.com/docs/cd/E11882_01/rac.112/e16795/hafeats.htm#sthref412:

          >
          Automatic workload management enables you to manage workload distributions to provide optimal performance for users and applications
          >

          vs

          >
          The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for the database.
          • 2. Re: AWR and Standard Edition RAC?
            AronTunzi
            Try to check the value of the parameter statistics_level. It needs to be TYPICAL or ALL.

            Try to manually take 2 snaphots and check the results.

            EXEC dbms_workload_repository.create_snapshot;


            If it works it can be that the job for automatic statistic collection is disabled.
            • 3. Re: AWR and Standard Edition RAC?
              P.Forstmann
              thtsang wrote:

              However, the report does not contain valuable information. Actually most of the AWR tables, like WRH$_SQLSTAT, WRH$_ACTIVE_SESSION_HISTORY are empty.

              Any idea?


              Oracle 11g 11.2.0.2 Standard Edition RAC
              Did you check following instance parameter:
              SQL> show parameter control_m
              
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ------------------------------
              control_management_pack_access       string      NONE
              SQL>
              • 4. Re: AWR and Standard Edition RAC?
                thtsang
                P. Forstmann wrote
                Automatic Workload Management and Automatic Workload Repository are 2 differents things...
                But the Oracle document also says
                Automatic workload management comprises the following:
                ...
                - Automatic Workload Repository (AWR):... >

                For initialization parameters:
                SQL> show parameter statistics_level
                
                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- ------------------------------
                statistics_level                     string      TYPICAL
                SQL> show parameter control_m
                
                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- ------------------------------
                control_management_pack_access       string      NONE
                I guess it's no way we can set control_management_pack_access to DIAGNOSTIC?
                • 5. Re: AWR and Standard Edition RAC?
                  P.Forstmann
                  Even with 11.2 Standard Edition database you can activate Diagnostic pack :
                  SQL> select * from v$version;
                  
                  BANNER
                  --------------------------------------------------------------------------------
                  Oracle Database 11g Release 11.2.0.1.0 - Production
                  PL/SQL Release 11.2.0.1.0 - Production
                  CORE    11.2.0.1.0      Production
                  TNS for Linux: Version 11.2.0.1.0 - Production
                  NLSRTL Version 11.2.0.1.0 - Production
                  
                  SQL> show parameter control_m
                  
                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  control_management_pack_access       string      NONE
                  
                  SQL> select count(*) from v$active_session_history;
                  
                    COUNT(*)
                  ----------
                           0
                  
                  SQL> alter system set control_management_pack_access=diagnostic;
                  
                  System altered.
                  
                  SQL> show parameter control_m
                  
                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  control_management_pack_access       string      DIAGNOSTIC
                  Start in another session a long running query like:
                   select count(*) from all_objects, all_objects;
                  and you can check that Active Session History is working:
                  SQL> select count(*) from v$active_session_history;
                  
                    COUNT(*)
                  ----------
                          28
                  So technically you can do it BUT from a licensing point of view you cannot do it.
                  • 6. Re: AWR and Standard Edition RAC?
                    thtsang
                    It seems to me the situation is
                    1) AWR is part of Diagnostic Pack
                    2) AWR is also part of Standard Edition RAC
                    3) To enable AWR, you tell the database (and therefore Oracle when they audit) you purchased Diagnostic Pack
                    4) Diagnostic Pack is not available to Standard Edition RAC

                    Either you are allowed to enable Diagnostic Pack in case you use Standard Edition RAC :D , or Standard Edition RAC includes AWR but you can't use it >:(
                    • 7. Re: AWR and Standard Edition RAC?
                      AronTunzi
                      Be aware that to genarate AWR report in a clustering enviromnent you have other script so use.
                      - awrrpti.sql or
                      - awrgrpt.sql

                      More details here:
                      Generating an Oracle RAC AWR Report/Generating an AWR Report on a Specific Database Instance - http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/autostat.htm#PFGRF95087