| |
 |
|
Oracle Tips by Burleson |
Using AWRRPT Reports
Introduced in Oracle10g, the AWRRPT report replaces the STATSPACK
reports in previous releases. One note of caution is that specific
licenses are required for the use of the AWRRPT, just as with any of
the other new tuning features in 10g. The STATSPACK reports are
still available in 10g and are still cost free, so they can continue
to be used.
The AWRRPT report comes in two flavors, the text based and the HTML
based versions. To post results to a web page, or if the fancy
format is appealing, the HTML version is the one to use. Anyone who
likes the look and feel of the old STATSPACK will still be able to
rely upon the text based version. To generate the either the HTML or
text version, the $ORACLE_HOME/rdbms/admin/awrrpt.sql script can be
used, and it will prompt for the appropriate version of the report
to generate.
The header from the AWRRPT output looks nearly identical to that
from the STATSPACK reports as shown in the listing report. A
complete sample AWRRPT report is shown in Appendix B.
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num
Release Cluster Host
------------ ----------- ------------ -------- ----------- ------
SSD 534227347 ssd2 2 10.1.0.3.0 YES amd44.supers
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 3 28-Jul-05 18:59:48 124 14.4
End Snap: 4 28-Jul-05 20:00:19 106 .7
Elapsed: 60.52 (mins)
DB Time: 9.87 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 768M Std Block Size: 8K
Shared Pool Size: 244M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 14,614.81 7,031.32
Logical reads: 1,690.46 813.30
Block changes: 81.77 39.34
Physical reads: 1.52 0.73
Physical writes: 12.73 6.12
User calls: 8.15 3.92
Parses: 1.40 0.68
Hard parses: 0.00 0.00
Sorts: 0.59 0.28
Logons: 0.03 0.02
Executes: 19.37 9.32
Transactions: 2.08
% Blocks changed per Read: 4.84 Recursive Call %: 75.25
Rollback per transaction %: 2.08 Rows per Sort: 9.38
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.97 Redo NoWait %: 99.98
Buffer Hit %: 99.91 In-memory Sort %: 100.00
Library Hit %: 99.99 Soft Parse %: 99.86
Execute to Parse %: 92.76 Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 104.48 % Non-Parse CPU: 99.66
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 37.87 38.16
% SQL with executions>1: 84.98 86.15
% Memory for SQL w/exec>1: 77.84 80.07
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) DB Time Wait Class
------------------------------ ----------- ----------- ---------
enq: TX - row lock contention 596 208 35.17 Application
CPU time 204 34.39
gc cr block 2-way 49,722 77 13.00 Cluster
gc current block 2-way 36,791 52 8.74 Cluster
gc buffer busy 1,726 29 4.86 Cluster
-------------------------------------------------------------
About the only item changed in the heading for the AWRRPT report
from the STATSPACK report is that the Top 5 Timed Event section
reports are based on the percentage of total DB time and reports the
Wait Class. It appears that some of the statistics reported may be
suspect or mislabeled. For example, on some of the reports examined
during testing, the value for % Non-Parse CPU is negative. In
earlier versions of STATSPACK, this also occurred, but it was
corrected. It appears to have been reborn. Also noted was that in
some of the Top 5 Wait Events, % Total DB Time is not a percentage
but appears to be the actual time. Unfortunately, the code that
generates these reports has been internalized so it is difficult to
get a good idea of what the developers meant to have in these
places.
The above book excerpt is from:
Oracle RAC
& Grid Tuning with Solid State Disk
Expert Secrets for High Performance Clustered Grid Computing
ISBN:
0-9761573-5-7
Mike Ault, Donald K. Burleson
http://www.rampant-books.com/book_2005_2_rac_ssd_tuning.htm |
