3 Replies Latest reply: Dec 12, 2012 11:04 PM by XBOX RSS

    AWR on a RAC Environment

    InquisitiveDBA
      Hello Knowledgeable Guys of Oracle Forums...

      Finally, our management has bought a license for AWR (after years of using the system!!!) because the database performance has been degrading for quite a while....goodbye to statspack!

      I have a question on AWR collection on RAC..
      We have a 5 Node RAC Cluster working on Oracle 10g R2 on Linux 5.1...

      I would like to know:

      1. How to collect AWR snapshots in a RAC environment? Do I need to collect AWR per node (per instance)?
      2. Is there a way to collect AWR for the entire RAC cluster (Node 1 to Node 5)?
      3. What is the proper way (best way) to collect AWR reports in our 5-node RAC?
      4. How does AWR work on RAC?

      Any sample blogsites...or sample actual AWR collection that you use in your system...

      Thank you
        • 1. Re: AWR on a RAC Environment
          Victor Armbrust
          Hi


          1. How to collect AWR snapshots in a RAC environment? Do I need to collect AWR per node (per instance)?
          A: No. You should collect it at DATABASE level


          2. Is there a way to collect AWR for the entire RAC cluster (Node 1 to Node 5)?
          A: Yes. You can simple generate an html or text report for your RAC environment (eg: AWR report)


          3. What is the proper way (best way) to collect AWR reports in our 5-node RAC?
          A: You can use the snapshots to compare periods, or reports


          4. How does AWR work on RAC?
          A: AWR snapshots are collect at database level, and you can use this snapshot to generate your AWR reports.


          You can also take snapshot in a given moment and use them to reports.

          Exeamples:

          TO CHANGE AWR SNAPSHOTS INTERVAL
          * retention /interval in seconds

          Exec dbms_workload_repository.modify_snapshot_settings (retention=>43200, interval=> 60);

          TO TAKE A SNAPSHOT MANUALLY
          execute dbms_workload_repository.create_snapshot;

          TO GENERATE AWR REPORTS
          @?/rdbms/admin/awrrpt.sql


          Victor
          • 2. Re: AWR on a RAC Environment
            Victor Armbrust
            Hi


            1. How to collect AWR snapshots in a RAC environment? Do I need to collect AWR per node (per instance)?
            A: No. You should collect it at DATABASE level


            2. Is there a way to collect AWR for the entire RAC cluster (Node 1 to Node 5)?
            A: Yes. You can simple generate an html or text report for your RAC environment (eg: AWR report)


            3. What is the proper way (best way) to collect AWR reports in our 5-node RAC?
            A: You can use the snapshots to compare periods, or reports


            4. How does AWR work on RAC?
            A: AWR snapshots are collect at database level, and you can use this snapshot to generate your AWR reports.


            You can also take snapshot in a given moment and use them to reports.

            Exeamples:

            TO CHANGE AWR SNAPSHOTS INTERVAL
            * retention /interval in seconds

            Exec dbms_workload_repository.modify_snapshot_settings (retention=>43200, interval=> 60);

            TO TAKE A SNAPSHOT MANUALLY
            execute dbms_workload_repository.create_snapshot;

            TO GENERATE AWR REPORTS
            @?/rdbms/admin/awrrpt.sql


            Victor
            • 3. Re: AWR on a RAC Environment
              XBOX
              For 10gr2 RAC database:_-

              1. Generate AWR reports for the current Instance:
              $ cd $ORACLE_HOME/rdbms/admin/
              $ sqlplus sys as sysdba
              SQL> *@awrrpt.sql*

              2. Generate AWR reports for any instance in the cluster:
              The script will ask you for the instance number in output format.
              $ cd $ORACLE_HOME/rdbms/admin/
              $ sqlplus sys as sysdba
              SQL> *@awrrpti.sql*


              From 11gr2 RAC We hav following options available:-

              1.Generate AWR Cluster aggregated statistics from all the instances:
              This report shows the aggregated statistics from the whole cluster database also, described as a global AWR cluster report

              $ cd $ORACLE_HOME/rdbms/admin/
              $ sqlplus sys as sysdba
              SQL> *@awrgrpt.sql*

              2. Generate AWR Global Cluster Difference Report:
              Ths report compares the statistic results of differences between two different snapshot intervals, for the whole cluster database

              $ cd $ORACLE_HOME/rdbms/admin/
              $ sqlplus sys as sysdba
              SQL> *@awrgdrpt.sql*

              Regards,