 |
|
Oracle Tips by Burleson |
Using
AWRRPT Reports
Introduced in Oracle10g, the AWRRPTreport
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
AWRRPTreport 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 CPUis 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.
However, the data is still present to allow users
to determine if the system would benefit from being a SSD system or
not. One thing to note in the header is the amount of time spent doing
I/O versus doing CPU related work. In the above header listing,
non-I/O related events are displayed. All of the events, other than
the TX enqueueand CPU values, are RAC interconnect related. This report
was taken during one of the fully cached data runs.
If the report is run on a RAC environment, the
section immediately following the heading is for RAC related
statistics. The listing below shows an example from the same test run
as the last listing above.
RAC Statistics
DB/Inst: SSD/ssd2 Snaps: 3-4
Begin End
----- -----
Number of Instances: 2 2
Global Cache
Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Global Cache
blocks received: 25.46 12.25
Global Cache
blocks served: 30.66 14.75
GCS/GES
messages received:
45.19 21.74
GCS/GES
messages sent:
41.24 19.84
DBWR
Fusion writes:
2.61
1.25
Global Cache
Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access -
local cache %: 98.40
Buffer access -
remote cache %: 1.51
Buffer access
- disk %: 0.09
Global Cache and
Enqueue Services - Workload Characteristics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg global enqueue get time (ms): 5.5
Avg
global cache cr block receive time (ms): 1.6
Avg global
cache current block receive time (ms): 1.7
Avg
global cache cr block build time (ms): 0.0
Avg
global cache cr block send time (ms): 0.0
Global
cache log flushes for cr blocks served %: 4.2
Avg
global cache cr block flush time (ms): 0.6
Avg
global cache current block pin time (ms): 0.2
Avg
global cache current block send time (ms): 0.0
Global cache
log flushes for current blocks served %: 0.5
Avg
global cache current block flush time (ms): 21.9
Global Cache and
Enqueue Services - Messaging Statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg message sent queue time (ms): 0.0
Avg
message sent queue time on ksxp (ms): 0.6
Avg message received queue time (ms): 0.0
Avg GCS
message process time (ms): 0.1
Avg GES
message process time (ms): 0.0
% of direct sent messages: 72.39
% of indirect sent messages: 26.84
% of flow controlled messages: 0.77
-------------------------------------------------------------
The RAC summary listing shown above gives
summarized data about cache transfers and other RAC related
statistics. It is likely that the most important statistics related to
making a determination about use of SSD are the statistics relating to
the Global Cache Service(GCS). The GCS
performs the transfer of blocks between the instances. From looking at
this section, one can see that the transfer times for the RAC cluster
are in the range of one to two milliseconds for the transfer of
blocks. This essentially says that as long as read and write times
from the SSD device are faster than the interconnect block transfer
time, it is prudent to push processing away from the interconnect and
to the SSD.
The
above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
