 |
|
Oracle: Creating an AWR Report
Oracle Tips by Burleson Consulting |
Creating an AWR Report
The remaining procedures in the
dbms_workload_repository package are
awr_report_text and
awr_report_html, which generate the
AWR report for the specified snapshot range in text or HTML formats,
respectively. The following script segment shows how to retrieve
the AWR text report for any snapshot range or duration:
SELECT
output
FROM
TABLE(dbms_workload_repository.awr_report_text (37933856,1,2900,2911 ));
The sample output below shows the typical report
generated for AWR data. The output displays shows the four
arguments to the
awr_report_text stored procedure:
§
The database ID is 37933856.
§
The instance number for RAC is 1.
§
The starting snapshot number is 2900.
§
The ending snapshot number is 2911.
This standard Oracle elapsed time report has
evolved over the past 12 years and had several names:
§
In
Oracle7 and Oracle8, this BSTAT-ESTAT was taken by running the
utlbstat.sql followed
by utlestat.sql in the
$ORACLE-HOME/rdbms/admin directory.
§
:
From Oracle8i to Oracle10g, this is an enhanced BSTAT-ESTAT report
where the user chooses the beginning and ending snapshot numbers.
§
In Oracle
10g, this is the latest time-series report, and it is produced by
running a SQL*Plus script in the $ORACLE_HOME/rdbms/admin
directory. awrrpt.sql
is a text-based report.
awrrpti.sql is a HTML-based report for online publishing of
time-series reports.
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
This is very similar to the old STATSPACK
reports from Oracle9i, and it contains vital elapsed-time change
information for what happened during the particular snapshot range.
More details on reading the standard AWR report
are included later in this book.
The next step is to learn about the
dbms_advisor package
and see how it can yield important Oracle tuning insights.
You can also use
Ion for Oracle to
automate the generation of AWR deltas, in a more usable
report format, with additional tips.
 |
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_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
