AWR for RAC
Another little known set of AWR reports are ones that are RAC specific and cluster wide versions, giving the RAC DBA a unique view of the cluster, no individual instance AWR report can provide.
The first report can be run from the command line, resides in the $ORACLE_HOME/rdbms/admin directory and is called awrgrpt.sql. Like the other versions of AWR, there is an HTML and a text version of the report offered, so when your manager or user would like to have something formatted and presented in an attractive format, there is an option.
The report breaks down the cluster by a number of areas that are not a necessity in a single instance environment.
-OS Statistics
-Time Model
-Foreground Wait Classes
The report also breaks down the Top Timed Events by Node and then summarizes them as a whole. This can be very valuable to determine when one node is an issue over the entire cluster.
Top Timed Events
- Instance ‘*’ – cluster wide summary
- ‘*’ Waits, %Timeouts, Wait Time Total(s) : Cluster-wide total for the wait event
- ‘*’ ‘Wait Time Avg (ms)’ : Cluster-wide average computed as (Wait Time Total / Event Waits) in ms
- ‘*’ Summary ‘Avg Wait Time (ms)’ : Per-instance ‘Wait Time Avg (ms)’ used to compute the following statistics
- ‘*’ [Avg/Min/Max/Std Dev] : average/minimum/maximum/standard deviation of per-instance ‘Wait Time Avg(ms)’
- ‘*’ Cnt : count of instances with wait times for the event
| Wait | Event | Wait Time | Summary Avg Wait Time (ms) | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| I# | Class | Event | Waits | %Timeouts | Total(s) | Avg(ms) | %DB time | Avg | Min | Max | Std Dev | Cnt |
| * | User I/O | cell smart table scan | 274,192,144 | 10.13 | 1,315,727.22 | 4.80 | 61.93 | 4.95 | 4.02 | 5.89 | 0.77 | 4 |
| DB CPU | 320,031.63 | 15.06 | 4 | |||||||||
| User I/O | cell single block physical read | 324,729,771 | 0.00 | 262,865.80 | 0.81 | 12.37 | 0.84 | 0.77 | 0.90 | 0.07 | 4 | |
| Concurrency | cursor: pin S wait on X | 877,623 | 0.00 | 55,186.22 | 62.88 | 2.60 | 64.83 | 53.91 | 82.46 | 12.71 | 4 | |
| User I/O | read by other session | 32,262,733 | 0.00 | 28,825.07 | 0.89 | 1.36 | 0.89 | 0.84 | 0.95 | 0.05 | 4 | |
| User I/O | cell multiblock physical read | 6,079,136 | 0.00 | 28,513.35 | 4.69 | 1.34 | 17.70 | 1.67 | 24.02 | 10.72 | 4 | |
| User I/O | cell multiblock physical read | 6,079,136 | 0.00 | 28,513.35 | 4.69 | 1.34 | 17.70 | 1.67 | 24.02 | 10.72 | 4 | |
| User I/O | cell list of blocks physical read | 10,747,445 | 0.00 | 24,110.44 | 2.24 | 1.13 | 2.70 | 1.62 | 4.52 | 1.26 | 4 | |
| System I/O | db file parallel write | 650,104 | 0.00 | 21,075.94 | 32.42 | 0.99 | 35.98 | 23.90 | 49.08 | 10.38 | 4 | |
| Other | PX Deq: Table Q Get Keys | 13,901 | 34.40 | 13,987.59 | 1006.23 | 0.66 | 927.10 | 523.10 | 1538.17 | 436.09 | 4 | |
| Other | DFS lock handle | 91,652 | 28.13 | 12,085.06 | 131.86 | 0.57 | 139.35 | 26.52 | 218.68 | 81.11 | 4 | |
| 1 | User I/O | cell smart table scan | 74,463,177 | 10.25 | 367,051.79 | 4.93 | 68.34 | |||||
| DB CPU | 70,999.53 | 13.22 | ||||||||||
| User I/O | cell single block physical read | 55,649,125 | 0.00 | 50,354.50 | 0.90 | 9.38 | ||||||
| Concurrency | cursor: pin S wait on X | 267,714 | 0.00 | 17,531.68 | 65.49 | 3.26 | ||||||
| System I/O | db file parallel write | 241,884 | 0.00 | 8,191.81 | 33.87 | 1.53 |
The next section covers the Global Cache and other interconnect important areas. Global messaging, global cache efficiency and other important factors to how the nodes are performing between each other are contained here.
Global cache transfer stats, like the timed statistics, are broken down by both node and then summarized to give the DBA a solid view of this data. Full interconnect statistics follow the global cache data with ping, device and interface information breakdown.
SQL statements are then broken down globally on how they affect the performance of the RAC environment as a whole. As individual AWR reports can be taken at the node level, this is important for this report to show in this unique manner.
The report is run as you would run any AWR report, choosing the format, the beginning and end snapshot, along with name of the report, so nothing special when choosing to create this report, so any DBA can take full advantage of this great feature if you are so licensed.
There is also a secondary report offered- the awrgdrpt.sql, which of course is the cluster wide version of the stats diff report. I really appreciate the opportunity to compare different snapshot intervals, so this is a must-have for a RAC DBA.
I believe all DBA’s managing RAC databases and licensed for AWR should take full advantage of these two reports, the information residing within them is invaluable.
Hi,
Can you please share me awrgrpt.sql as same in not available in my 11g RAC db .I want to generate a consolidated RAC instance AWR report to analyze the comparative performance of all RAc instance .I searched on internet also but not found .Pls help .