Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


  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.


DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------
SSD 534227347 ssd2 2 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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter