Global cache performance metrics are not correctly measured. It is not understood clearly either. There are even few blogs and web pages disseminating incorrect information. This blog entry is an attempt to offer few methods and scripts to understand global cache performance.
Always review all instances
It is very important to review the performance metrics from all instances in that RAC cluster, not just one instance that you are connected. If you have access to AWR reports, then it is critical to generate AWR reports (or statspack reports) from all instances. But, the problem is that, DBAs tend to generate AWR reports after logging in to each instance iteratively, enter couple of parameters and then reports are generated. Not exactly a convenient practice.
REM connect to each instance separately, type in the beginning snap_id and ending snap_id for each node etc.. sqlplus mydba@proddb1 @$ORACLE_HOME/rdbms/admin/awrrpt.sql exit; sqlplus mydba@proddb2 @$ORACLE_HOME/rdbms/admin/awrrpt.sql exit; sqlplus mydba@proddb3 @$ORACLE_HOME/rdbms/admin/awrrpt.sql exit;
There are few issues with this approach. It is a cumbersome practice if the instance count is higher. In addition to that, all of AWR reports are, in turn, accessing underlying AWR tables. Physically, rows from all instances are together in the same block and so, by executing these reports connecting to various instances, Global cache traffic is increased. If the database is suffering from Global cache (GC) performance issues then generating reports connecting to various instances is probably not a grand idea.