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.