Interpreting RAC global workload characteristics in AWR report, understanding RAC related statistics part in AWR

The following sections are key to analyze in the AWR report and identify for RAC global characteristics or load profile.

• Number of Instances
• Instance global cache load profile
• Global cache efficiency percentages
• GCS-GES workload characteristics
• Messaging statistics
• Service Statistics
• Service Wait class statistics
• Top segments related to CR and current blocks

Number of Instances:-

Global Cache Load Profile section:-

Explanation:-

the first two statistics indicate the number of blocks transferred to or from this instance, thus if you are using a 8K block size

         Sent:        240 x 8,192 = 1966080 bytes/sec = 2.0 MB/sec
         Received:  315 x 8,192 = 2580480 bytes/sec = 2.6 MB/sec

to determine the amount of network traffic generated due to messaging you first need to find the average message size (this was 193 on my system)

SQL>select sum(kjxmsize * (kjxmrcv + kjxmsnt + kjxmqsnt)) / sum((kjxmrcv + kjxmsnt + kjxmqsnt)) "avg Message size" from x$kjxm
        where kjxmrcv > 0 or kjxmsnt > 0 or kjxmqsnt > 0;

then calculate the amount of messaging traffic on this network
    193 (765 + 525) = 387000 = 0.4 MB

to calculate the total network traffic generated by cache fusion
     = 2.0 + 2.6 + 0.4 = 5 MBytes/sec
      = 5 x 8 = 40 Mbits/sec

The DBWR Fusion writes statistic indicates the number of times the local DBWR was forced to write a block to disk due to remote instances, this number should be low.

Global Cache Efficiency Percentage:-

Explanation:-

this section shows how the instance is getting all the data blocks it needs. The best order is the following

• Local cache
• Remote cache
• Disk

The first two give the cache hit ratio for the instance, you are looking for a value less than 10%, if you are getting higher values then you may consider application partitioning.

GCS & GES Workload characteristics Section:-

  Explanation:-

this section contains timing statistics for global enqueue and global cache. As a general rule you are looking for

• All timings related to CR (Consistent Read) processing block should be less than 10 ms
• All timings related to CURRENT block processing should be less than 20 ms

GCS & GES Message Statistics section:-

  Explanation:-

The first section relates to sending a message and should be less than 1 second.

The second section details the breakup of direct and indirect messages, direct messages are sent by a instance foreground or the user processes to remote instances, indirect are messages that are not urgent and are pooled and sent.

Service Wait section:-

 Explanation:-

shows the resources used by all the service instance supports

Service Wait class section:-

Explanation:- 

summarizes waits in different categories for each service

Top segment section:-

Explanation:-

Contains the names of the top 5 contentious segments (table or index). If a table or index has a very high percentage of CR and Current block transfers you need to investigate. This is pretty much like a normal single instance.

Reference:- Oracle 10g Real application clusters handbook, K. GopalKrishnan

-Thanks

Geek DBA