Generate and view Oracle AWR reports

You can view the current configuration through dba_hist_wr_control. The current awr is a data snapshot every hour, and the retention time is 7 days.

SYS @ peng SQL> select * from dba_hist_wr_control;
     DBID SNAP_INTERVAL             RETENTION                           TOPNSQL
-----------------------------------------------------------------------------------
 620748923 + 00000 01:00:00. 0         + 00007 00:00:00. 0                   DEFAULT

Elapsed: 00:00:00. 16
SYS @ peng SQL>

Adjust configuration data:

SYS @ peng SQL> execute dbms_workload_repository.modify_snapshot_settings (interval = & gt; 30, retention = & gt; 14000 );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00. 17
Then, view the modified results:

SYS @ peng SQL> select * from dba_hist_wr_control;
     DBID SNAP_INTERVAL             RETENTION                           TOPNSQL
-----------------------------------------------------------------------------------
 620748923 + 00000 00:30:00. 0         + 00009 17:20:00. 0                   DEFAULT

Elapsed: 00:00:00. 02
SYS @ peng SQL>

In addition to automatic snapshot creation, you can also manually create snapshots as needed. Data is used the same as automatic snapshot creation.

SYS @ peng SQL> select dbms_workload_repository.create_snapshot () from dual;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ()
------------------------------------------
                                       68

Elapsed: 00:00:01. 00
SYS @ peng SQL> select dbms_workload_repository.create_snapshot () from dual;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ()
------------------------------------------
                                       69

Elapsed: 00:00:00. 60
SYS @ peng SQL>

[Oracle @ localhost admin] $ pwd
/Oracle/product/10g/rdbms/admin

A lot of SQL scripts are stored here, so you can start reading them if you have time to improve the level. Here we use awrrpt. SQL.

You can check the content first.

[Oracle @ localhost admin] $ cat awrrpt. SQL

Rem $ Header: awrrpt. SQL 24--2003.12: 04: 53 pbelknap Exp $
Rem
Rem awrrpt. SQL
Rem
Rem Copyright (c) 1999,200 3, Oracle Corporation.All rights reserved.
Rem
Rem  NAME
Rem    Awrrpt. SQL
Rem
Rem  DESCRIPTION
Rem    This script defaults the dbid and instance number to that of
Rem    Current instance connected-to, then CILS awrrpti. SQL to produce
Rem    The Workload Repository report.
Rem
Rem  NOTES
Rem    Run as select_catalog privileges.
Rem    This report is based on the Statspack report.
Rem
Rem    If you want to use this script in an non-interactive fashion,
Rem    See the 'customer-mizmizable report settings' section in
Rem    Awrrpti. SQL
Rem
Rem  MODIFIED (MM/DD/YY)
Rem  Pbelknap  10/24/03-swrfrpt to awrrpt
Rem  Pbelknap  10/14/03-moving params to rpti
Rem  Pbelknap  10/02/03-adding non-interactive mode cmcnt
Rem  Mlfeng    09/10/03-heading on
Rem  Aime      04/25/03-aime_going_to_main
Rem  Mlfeng    01/27/03-mlfeng_swrf_reporting
Rem  Mlfeng    01/13/03-Update comments
Rem  Mlfeng    07/08/02-swrf flushing
Rem  Mlfeng    06/12/02-Created
Rem

--
-- Get the current database/instance information-this will be used
-- Later in the report along with bid, eid to lookup snapshots

Set echo off heading on underline on;
Column inst_numHeading "Inst Num"New_value inst_numFormat 99999;
Column inst_name heading "Instance"New_value inst_name format a12;
Column db_name Heading "DB Name" New_value db_name Format a12;
Column dbid    Heading "DB Id"   New_value dbid    Format 9999999999 just c;

Prompt
Prompt Current Instance
Prompt ~~~~~~~~~~~~~~~~

Select d. dbid          Dbid
    , D. name          Db_name
    , I. instance_number inst_num
    , I. instance_name Inst_name
 From v $ database d,
      V $ instance I;

@ Awrrpti

Undefine num_days;
Undefine report_type;
Undefine report_name;
Undefine begin_snap;
Undefine end_snap;
--
-- End of file

The result of one section is
Select d. dbid          Dbid
    , D. name          Db_name
    , I. instance_number inst_num
    , I. instance_name Inst_name
 From v $ database d,
      V $ instance I;

     DBID DB_NAME   INST_NUM INST_NAME
---------------------------------------------
 620748923 PENG             1 peng

Then awrrpti. SQL is called, which contains a large amount of content and will be specially analyzed later.

Here we run awrrpt. SQL directly.

SYS @ peng SQL> @/oracle/product/10g/rdbms/admin/awrrpt. SQL

Current Instance
~~~~~~~~~~~~~~~~

  DB Id  DB Name    Inst Num Instance
-------------------------------------------
 620748923 PENG              1 peng

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Wocould you like an HTML report, or a plain text report?
Enter 'html' for an html report, or 'text' for plain text
Ults to 'html'
Enter value for report_type: text

Type Specified:Text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~

  DB Id   Inst Num DB Name    Instance   Host
--------------------------------------------------------
* 620748923       1 PENG       Peng       Localhost

Using620748923 for database Id
Using        1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(N) days of snapshots being listed.Pressing <return>
Specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

                                                       Snap
Instance   DB Name      Snap Id  Snap Started  Level
--------------------------------------------------------
Peng       PENG              58 12 Dec 2010    1
                                59 12 Dec 2010    1
                                60 12 Dec 2010    1
                                61 12 Dec 2010    1
                                62 12 Dec 2010    1

                                63 13 Dec 2010 0:00    1

                                64 13 Dec 2010    1
                                65 13 Dec 2010    1
                                66 13 Dec 2010    1
                                67 13 Dec 2010    1
                                68 13 Dec 2010    1
                                69 13 Dec 2010    1
                                70 13 Dec 2010    1

 

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 64
Begin Snapshot Id specified: 64

Enter value for end_snap: 70
End Snapshot Id specified: 70

 

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_000064_70.txt.To use this name,
Press <return> to continue, otherwise enter an alternative.

Enter value for report_name:/tmp/awrrpt_1_64_70.txt

The generated file is stored in the specified/tmp.

The file contains the following content:

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Memory Statistics
• Streams Statistics
• Resource Limit Statistics
• Init. ora Parameters

1 2 3 4 Next Page