 |
|
Generating the AWR Report
Oracle Tips by Burleson Consulting |
This chapter focuses on the skills required for
reading and interpreting an Automated Workload Repository (AWR)
report, which is very similar to the STATSPACK elapsed-time report.
The AWR elapsed-time report contains valuable information regarding
the health of the Oracle instance, but considerable skill is
required to fully understand and interpret each section.
While there is only enough room in this chapter
to cover the highlights, this important chapter should give users a
good idea about what to look for in an AWR report and how to use
this data to identify performance problems.
Generating the AWR Report
The procedure for creating a standard report
provided by the STATSPACK utility in previous Oracle releases has
already been introduced. The only way to get a final STATSPACK
report is to manually run the
spreport.sql script in an ad-hoc SQL environment like
SQL*Plus. The procedure of report generation in
Oracle10g is quite different with AWR. The final AWR report can be
built by using the PL/SQL API provided in the
dbms_workload_repository
package.
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)
Cache
Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 48M Std Block
Size: 8K
Shared Pool Size: 56M Log Buffer:
256K
Load
Profile
~~~~ Per Second Per Transaction
-------- ---------------
Redo size: 1,766.20 18,526.31
Logical reads: 39.21 411.30
Block changes: 11.11 116.54
Physical reads: 0.38 3.95
Physical writes: 0.38 3.96
User calls: 0.06 0.64
Parses: 2.04 21.37
Hard parses: 0.14 1.45
Sorts: 1.02 10.72
Logons: 0.02 0.21
Executes: 4.19 43.91
The old-fashioned AWR report generation
procedure has also been preserved from STATSPACK. The
awrrpt.sql script in
SQL*Plus can simply be run, and the parameters
necessary to build the AWR report can be provided. In fact, the
awrrpt.sql script
calls the corresponding procedure from the
dbms_workload_repository package and stores its output in
the target report file.
The next section outlines the evolution of the
STATSPACK report, spreports, into the Oracle10g
AWR report.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
|
