8 Replies Latest reply: Apr 10, 2012 6:29 AM by CKPT RSS

    How to run oracle AWR Report

    636287
      Hi all ,

      I am trying to create an AWR report through oracle 10g R@ . My OS is win server 2003 .
      This is the query I am using

      SELECT
      output
      FROM TABLE(dbms_workload_repository.awr_report_text ( ));

      I am able to finf the first 2 partameters , L_DBID and L_INST_NUM .

      Can you tell me how can I find the 3rd and 4th parameter value i.e.
      L_BID and L_EID.

      Thank you .
        • 1. Re: How to run oracle AWR Report
          sb92075
          I am trying to create an AWR report through oracle 10g
          use the Oracle supplied SQL script below
          /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql

          SQL> @awrrpt.sql
          • 2. Re: How to run oracle AWR Report
            591186
            From sqlplus prompt,

            @$ORACLE_HOME\rdbms\admin\awrrpt.sql
            @$ORACLE_HOME\rdbms\admin\awrrpti.sql

            HTH
            -Anantha

            Edited by: Anantha R on May 20, 2010 1:13 PM
            • 3. Re: How to run oracle AWR Report
              DBA 24*7
              The Oracle AWR report is the evolution of the STATSPACK report in Oracle 10g, and is invoked as follows:

              $ORACLE_HOME/rdbms/admin/awrrpt.sql
              $ORACLE_HOME/rdbms/admin/awrrpti.sql
              $ORACLE_HOME/rdbms/admin/awrinput.sql
              • 4. Re: How to run oracle AWR Report
                DBA 24*7
                Two procedures that generate AWR reports are awr_report_text and awr_report_html . These procedures generate the AWR report for the specified snapshot range in TEXT or HTML formats, respectively. The following script shows one way of retrieving the AWR text report for the particular snapshot range:

                SELECT
                output
                FROM TABLE (dbms_workload_repository.awr_report_text (37933856,1,2900,2911 ));

                OUTPUT
                --------------------------------------------------------------------
                WORKLOAD REPOSITORY report for

                DB Name DB Id Instance Inst Num Release Cluster Host
                ---------- ----------- --------- -------- ----------- ------- -----
                DBDABR *37933856* dbdabr *1* 10.1.0.2.0 NO Host1

                Snap Id Snap Time Sessions Curs/Sess
                --------- ------------------- -------- ---------
                Begin Snap: *2900* 19-Aug-04 11:00:29 18 5.2
                End Snap: *2911* 19-Aug-04 22:00:16 18 4.6
                Elapsed: 659.78 (mins)
                DB Time: 10.08 (mins)
                • 5. Re: How to run oracle AWR Report
                  566761
                  you need begin and end snapshots IDs you can get them via DBA_HIST_SNAPSHOT table

                  ---------
                  Coskan Gundogar

                  Blog: http://coskan.wordpress.com
                  Twitter: http://www.twitter.com/coskan
                  Linkedin: http://uk.linkedin.com/in/coskan
                  ---------
                  • 6. Re: How to run oracle AWR Report
                    srinirandy
                    Snapshots

                    BEGIN
                    DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
                    retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
                    interval => 30); -- Minutes. Current value retained if NULL.
                    END;
                    /

                    Extra snapshots can be taken and existing snapshots can be removed, as shown below

                    EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

                    BEGIN
                    DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
                    low_snap_id => 22,
                    high_snap_id => 32);
                    END;
                    /

                    Baselines

                    BEGIN
                    DBMS_WORKLOAD_REPOSITORY.create_baseline (
                    start_snap_id => 210,
                    end_snap_id => 220,
                    baseline_name => 'batch baseline');
                    END;
                    /

                    BEGIN
                    DBMS_WORKLOAD_REPOSITORY.drop_baseline (
                    baseline_name => 'batch baseline',
                    cascade => FALSE); -- Deletes associated snapshots if TRUE.
                    END;
                    /

                    Workload Repository Reports
                    @$ORACLE_HOME/rdbms/admin/awrrpt.sql
                    @$ORACLE_HOME/rdbms/admin/awrrpti.sql
                    • 7. Re: How to run oracle AWR Report
                      757008
                      Hello,

                      You can use grid page to generate the report.....

                      database-->performance-->snapshots

                      You get lots of options while generating reports when using grid and is much simpler

                      Regards
                      • 8. Re: How to run oracle AWR Report
                        CKPT
                        vinay.deshmukh wrote:
                        Hello,

                        You can use grid page to generate the report.....

                        database-->performance-->snapshots

                        You get lots of options while generating reports when using grid and is much simpler

                        Regards
                        Its good point too, if i do not have Grid EM enabled? :p