Manually generate an AWR report

I. AWR description

Before 10 Gb, Oracle used the statspack tool to test the Database performance. Oracle Database 10 Gb provided a new tool: (AWR: Automatic Workload Repository ). Oracle recommends that you replace Statspack with this. AWR is essentially an Oracle built-in tool that collects performance-related statistics and exports performance measurements from those statistics to track potential problems. Unlike Statspack, snapshots are automatically collected hourly by a new background process called MMON and its slave processes. To save space, the collected data is automatically cleared 7 days later. You can modify the snapshot frequency and retention time. It generates two types of output: The text format (similar to the text format of the Statspack report but from the AWR Information Library) and the default HTML format (with all the hyperlinks to the part and sub-part ).

AWR uses several tables to store collected statistics. All Tables are stored in the SYS mode in a specific tablespace named SYSAUX, it is named in the format of WRM $ _ * And WRH $. The previous type stores metadata information (such as checked databases and collected snapshots), and the latter type stores actual collected statistics. H Represents "historical" and M represents "metadata )". Several views with DBA_HIST _ prefix are built on these tables. These views can be used to compile your own performance diagnostic tools. The view name is directly related to the table. For example, the view DBA_HIST_SYSMETRIC_SUMMARY is built on the WRH $ _ SYSMETRIC_SUMMARY table.

 
Note:
Statistics_level has three values: ALL, TYPICAL, and BASIC. The default value is typical. Table monitoring is activated in 10 Gb. It is strongly recommended that the value of this parameter be typical in 10 Gb. If STATISTICS_LEVEL is set to basic, not only tables cannot be monitored, but also the following 10g new functions are disabled:
ASH (Active Session History)
ASSM (Automatic Shared Memory Management)
AWR (Automatic Workload Repository)
ADDM (Automatic Database Diagnostic Monitor)

Ii. manually generate AWR reports
If an OEM is installed, an AWR report can be generated through the OEM. In this example, an AWR report is generated manually.
SQL> @? /Rdbms/admin/awrrpt. SQL

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

DB Id DB Name Inst Num Instance
-------------------------------------------
2100083002 TEST 1 test

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: html

Type Specified: html

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

DB Id Inst Num DB Name Instance Host
--------------------------------------------------------
* 2100083002 1 TEST even. oracle.
Com

Using 2100083002 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: 1

Listing the last day's Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
--------------------------------------------------------
Test TEST 12 20 Dec 2012 1
13 20 Dec 2012 1
14 20 Dec 2012 1
15 20 Dec 2012 1
16 20 Dec 2012 1
17 20 Dec 2012 1
18 20 Dec 2012 1
19 20 Dec 2012 1
20 20 Dec 2012 1
21 20 Dec 2012 1
22 20 Dec 2012 1
23 20 Dec 2012 1
24 20 Dec 2012 1
25 20 Dec 2012 1
26 20 Dec 2012 1
27 20 Dec 2012 1
28 20 Dec 2012 1
29 20 Dec 2012 1
30 20 Dec 2012 1
31 20 Dec 2012 1
32 20 Dec 2012 1

 

Specify the Begin and End Snapshot Ids

Enter value for begin_snap: 30
Begin Snapshot Id specified: 30

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

 

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

Enter value for report_name:/home/oracle/20121231_AWR_rpt.html
......
.....
......
......
 

Report written to/home/oracle/20121231_AWR_rpt.html

Now AWR report is generated. My report is in/home/oracle/20121231_AWR_rpt.html.

1 2 Next Page