RAC performance tuning: Understanding Global cache performancePosted by Riyaj Shamsudeen on December 23, 2009 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.
I have created few basic scripts to generate AWR reports from all instances in a single step. Script awrrpt_all_gen.sql queries AWR tables and gv$instance to access last snap_id and various instance_ids. Then this script generates AWR reports for all instances in that cluster in the current directory, for the last AWR snap. This script is quite handy while debugging RAC performance issues. Now, we have AWR reports from all instances in one directory and we can easily grep these files to understand metrics in various instances. @awrrpt_all_gen.sql awrrpt_all_gen.sql v1.00 by Riyaj Shamsudeen @ To generate AWR Report from all RAC instances concurrently. Creates reports using last two snap_ids. ...Generating awrrpt_all.sql script.... Please wait.... ...Completed script generation. Executing awrrpt_all.sql to generate AWR reports. ...Generates AWR reports with file name format awrrpt___.txt for each instance. ...Please wait for few minutes... ...AWR report created for instance 1. Please wait.. ...AWR report created for instance 2. Please wait.. ...AWR report created for instance 3. Please wait.. ...AWR report created for instance 4. Please wait.. AWR reports created.
If you would like ability to choose the snap_ids, then use the script awrrpt_all_range_gen.sql Use granular approach to GC measurements It is important to measure Global cache performance metrics correctly, especially those damned averages. Incorrect measurements of averages can lead to faulty analysis. For example, following query is retrieving average global cache CR receive time from gv$sysstat view. Problem with this SQL statement is that this statement is retrieving average global cache receive time from the restart of the instances. Global cache performance is quite sensitive to workload and querying over a wide time range, as in this query, can lead to faulty analysis. REM This is really a bad idea!! select b1.inst_id, b2.value "RECEIVED", b1.value "RECEIVE TIME", ((b1.value / b2.value) * 10) "AVG RECEIVE TIME (ms)" from gv$sysstat b1, gv$sysstat b2 where b1.name = 'gc current block receive time' and b2.name = 'gc current blocks received' and b1.inst_id = b2.inst_id; As an analogy, above query is equivalent to a patient-Doctor conversation: Patient: Doctor, I don’t feel good. I feel that I am having fever. It is essential to review the global cache performance metrics at a granular level. Use AWR reports or statspack reports for a short period of time. Better yet, if you want to quickly review GC performance metrics, you can use my script gc_traffic_print.sql. This script captures various statistics relevant to Global cache performance in to a PL/SQL table, sleeps for N seconds (default N=60) and re-captures the statistics after the sleep. Then the script prints all the relevant details in easily readable tabular format. Very useful in quickly reviewing the Global cache performance metrics. SQL> @gc_traffic_print.sql gc_traffic_print.sql v1.20 by Riyaj Shamsudeen @ ...Calculating GC Rx and Tx timing and blocks.. ...Default collection period is 60 seconds.... Please wait for at least 60 seconds... Enter value for sleep: 30 ---------|--------------|---------|----------------|----------|---------------|---------------|-------------| Inst | CR blocks Rx | CR time | CUR blocks Rx | CUR time | CR blocks Tx | CUR blocks Tx |Tot blocks | ---------|--------------|---------|----------------|----------|---------------|---------------|-------------| 1 | 29288| 12.17| 14914| 4.58| 27059| 17439| 88700| 2 | 21201| 5.75| 12835| 5.19| 26831| 16741| 77608| 3 | 22942| 4.64| 11751| 3.82| 23918| 9205| 67816| 4 | 32075| 4.78| 19260| 4.55| 31046| 19331| 101712| ---------|--------------|---------|----------------|----------|---------------|---------------|-------------| PL/SQL procedure successfully completed.
Column key for the above script is: Breakup: global cache performance Global cache processing performance can be broken down to its sub components ( excluding wire latency, but that can be calculated). Breakup of this metrics must be done at a granular level also. We can use yet another script gc_traffic_processing.sql to review the performance metrics for the past N seconds. For example, in the output printed below, Global cache CR processing time is broken in to various sub-components of global cache processing. Remember that these performance metrics are in the SEND side of Global cache performance metrics. Meaning, inefficient global cache processing in one instance affects other instances’ global cache receive time, but not the self. For example, in the table above, GC CR receive performance was at 12.1ms for instance 1. That is due to another instance (probably node 4 in this case) suffering from high GC processing time (more specifically high ‘CR block flush time’ in node 4). It is far more accurate to review global cache performance issues in a granular basis. Caution though, try not to specify at least a minute so that these numbers are consistent. @gc_traffic_procssing.sql gc_traffic_processing.sql v1.00 by Riyaj Shamsudeen @ ...Prints various timing related information for the past N seconds ...Default collection period is 60 seconds.... Please wait for at least 60 seconds... Enter value for sleep: ---------|-----------|---------|-----------|----------|------------|------------|------------|----------| Inst | CR blk Tx | CR bld | CR fls tm | CR snd tm| CUR blk TX | CUR pin tm | CUR fls tm |CUR snd tm| ---------|-----------|---------|-----------|----------|------------|------------|------------|----------| 1 | 62732| .2| 1.25| .21| 48920| .96| .22| .22| 2 | 46037| .13| .76| .2| 42019| .68| .17| .2| 3 | 72237| .63| .79| .19| 22697| .44| .29| .23| 4 | 113816| .13| 2.04| .15| 51020| .87| .12| .13| -------------------------------------------------------------------------------------------------------- PL/SQL procedure successfully completed.
Here is the column definitions for the script output: Review histograms for GC events Averages can be misleading. Revealing performance issues underneath the average is a very important task to do. For example, we see that ‘gc cr block receive time’ average is high. But, let’s review the histogram for the gc events related to Global cache receive time. My script gc_event_histogram.sql is quite handy to review event histograms. This script provides a difference in performance statistics between two samples and prints them in a readable format. In this case, node 1 is has 8578 waits for the event ‘gc cr block 2-way’ under 2ms and the sessions in instance 1 had 9851 waits between 2-4ms. Only few waits over 16ms. @gc_event_histogram.sql gc_event_histogram.sql v1.0 by Riyaj Shamsudeen @ Default collection period is sleep seconds. Please wait.. Enter value for event: gc cr block 2-way Enter value for sleep: 60 ---------|-----------------------|----------------|----------| Inst id | Event |wait time milli |wait cnt | ---------|-----------------------|----------------|----------| 1 |gc cr block 2-way | 1| 98| 1 |gc cr block 2-way | 2| 8578| 1 |gc cr block 2-way | 4| 9851| 1 |gc cr block 2-way | 8| 2291| 1 |gc cr block 2-way | 16| 117| 1 |gc cr block 2-way | 32| 6| 1 |gc cr block 2-way | 64| 1| 1 |gc cr block 2-way | 128| 2| 1 |gc cr block 2-way | 256| 0| 1 |gc cr block 2-way | 512| 0| 1 |gc cr block 2-way | 1024| 0| 1 |gc cr block 2-way | 2048| 0| 1 |gc cr block 2-way | 4096| 0| 1 |gc cr block 2-way | 8192| 0| 1 |gc cr block 2-way | 16384| 0| 2 |gc cr block 2-way | 1| 155| 2 |gc cr block 2-way | 2| 10792| 2 |gc cr block 2-way | 4| 14201| 2 |gc cr block 2-way | 8| 3887| 2 |gc cr block 2-way | 16| 204| ... But, we also need to see 3-way waits. Let’s review 3-way waits with the above script. We will simply supply ‘gc cr block 3-way’ as the wait event. In this case, we see that there are 12,062 waits over between 4-8ms. Of course, this must be improved. @gc_event_histogram.sql gc_event_histogram.sql v1.0 by Riyaj Shamsudeen @ Default collection period is sleep seconds. Please wait.. Enter value for event: gc cr block 3-way Enter value for sleep: 60 ---------|-----------------------|----------------|----------| Inst id | Event |wait time milli |wait cnt | ---------|-----------------------|----------------|----------| 1 |gc cr block 3-way | 1| 3| 1 |gc cr block 3-way | 2| 320| 1 |gc cr block 3-way | 4| 17017| 1 |gc cr block 3-way | 8| 12062| 1 |gc cr block 3-way | 16| 1725| 1 |gc cr block 3-way | 32| 113| 1 |gc cr block 3-way | 64| 0| 1 |gc cr block 3-way | 128| 0| 1 |gc cr block 3-way | 256| 0| 1 |gc cr block 3-way | 512| 0| 1 |gc cr block 3-way | 1024| 0| 1 |gc cr block 3-way | 2048| 0| 1 |gc cr block 3-way | 4096| 0| 1 |gc cr block 3-way | 8192| 0| ...
In essence, these four scripts will be useful in debugging Global cache performance issues. Please let me know if you encounter errors in these scripts. We will use these scripts in future discussions to show how we resolved few RAC performance issues. 20 Responses to “RAC performance tuning: Understanding Global cache performance” |
|
Statistique said
Hi,
Nice series of script. But gc_traffic_print.sql seems to have a little “bug”. If the sleeping period is too short or this is no activity on the database the following error happens :
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 121
Environnement is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
One of those operation migth result into a division by zero :
(e_cr_tm_recv (i) – b_cr_tm_recv (i))
/ (e_cr_blks_recv (i) – b_cr_blks_recv (i))
I thought you might like to know !
And oh !!! Happy holidays !
orainternals said
Thank you Statistique.
I have modified the scripts to handle zero divisor issue. Please download the new versions.
But, it is probably not a good idea to keep the time window too small either such as 1 or 2 seconds. Time out for few events are at 1 second and keeping the window too small can cause issues too.
Cheers
Riyaj
Bernard Polarski said
Idem:
SQL> /
Enter value for sleep: 1
old 77: select nvl (‘&sleep’,60) into l_sleep from dual;
new 77: select nvl (’1′,60) into l_sleep from dual;
———|————–|———|—————-|———-|—————|—————|————-|
Inst | CR blocks Rx | CR time | CUR blocks Rx | CUR time | CR blocks Tx | CUR blocks Tx |Tot blocks |
———|————–|———|—————-|———-|—————|—————|————-|
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 115
orainternals said
Thanks for visiting my blog. I have uploaded new versions of two scripts: gc_traffic_print.sql and gc_traffic_processing.sql
Cheers
Riyaj
Yasser said
Thanks for sharing scripts.
Eagerly waiting for future posts where you have used these scripts.
-Yasser
Blogroll Report – 18/12/2009-25/12/2009 ? Coskan’s Approach to Oracle said
[...] 16-How to diagnose global cache performance ? -Scripts collection Riyaj Shamsudeen-RAC performance tuning: Understanding Global cache performance [...]
karlarao said
Cool script! this will be very useful..
- Karl Arao
MirchiFM said
Thanks for sharing the scripts.
baskar loganathan said
Hi ,
I executed the script in my env but the output i got is of zero bytes..
SQL> @awrrpt_all_gen.sql
awrrpt_all_gen.sql v1.00 by Riyaj Shamsudeen @
To generate AWR Report from all RAC instances concurrently.
Creates reports using last two snap_ids.
…Generating awrrpt_all.sql script…. Please wait….
…Completed script generation.
Executing awrrpt_all.sql to generate AWR reports.
…Generates AWR reports with file name format awrrpt___.txt for each instance.
…Please wait for few minutes…
AWR reports created.
SQL> !ls -lrt awrrpt_all*
-rw-r–r– 1 osteelp dba 4247 Jul 19 14:32 awrrpt_all_gen.sql
-rw-r–r– 1 osteelp dba 0 Jul 19 14:32 awrrpt_all.sql
!!!
thanks,
baskar.l
orainternals said
Hello Baskar
That’s because serveroutput is off in your session. Please turn on serveroutput on with “set serveroutput on” command, then rerun the script.
Cheers
Riyaj
baskar loganathan said
Hi Thanks very much Riyaj…Thanks again for sharing a very useful script..
regards,
baskar.l
baskar loganathan said
Hi Riyaj,
Just got confused..i executed the script first time it generated the output..and executed again i didnt generate a output..
First time
SQL> set termout off
SQL> set serveroutput on
@awrrpt_all.sql
SQL> set termout on
set pagesize 24
PROMPT AWR reports created.
set serveroutput off
…AWR report created for instance 1. Please wait..
SQL> SQL> SQL> AWR reports created.
SQL> SQL>
second time when i executed the same script..it doesnt generate..
SQL> PROMPT
SQL> set termout off
SQL> set serveroutput on
@awrrpt_all.sql
set termout on
SQL> SQL> SQL> set pagesize 24
SQL> PROMPT AWR reports created.
AWR reports created.
SQL> set serveroutput off
SQL>
thanks,
baskar.l
orainternals said
It worked correctly both times for me. Are you calling awrpt_all_gen or awrrpt_all.sql?
What version?
SQL> @awrrpt_all_gen
awrrpt_all_gen.sql v1.00 by Riyaj Shamsudeen @
To generate AWR Report from all RAC instances concurrently.
Creates reports using last two snap_ids.
…Generating awrrpt_all.sql script…. Please wait…. …Completed script generation.
Executing awrrpt_all.sql to generate AWR reports. …Generates AWR reports with file name format awrrpt___.txt for each instance. …Please wait for few minutes…
…AWR report created for instance 1. Please wait.. AWR reports created. SQL> @awrrpt_all_gen
awrrpt_all_gen.sql v1.00 by Riyaj Shamsudeen @
To generate AWR Report from all RAC instances concurrently.
Creates reports using last two snap_ids.
…Generating awrrpt_all.sql script…. Please wait…. …Completed script generation.
Executing awrrpt_all.sql to generate AWR reports. …Generates AWR reports with file name format awrrpt___.txt for each instance. …Please wait for few minutes…
…AWR report created for instance 1. Please wait.. AWR reports created.
Cheers
Riyaj Shamsudeen Principal DBA, Ora!nternals – http://www. Specialists in Performance, Recovery and EBS11i Blog: http://orainternals. OakTable member Oracle ACE Co-author of the book”Expert Oracle Practices” http:///book-expert-oracle-practices
orainternals said
Baskar
As we discussed, serveroutput should be on as the script uses dbms_output utility. I have uploaded recent version of the script.
Cheers
Riyaj
Adhika said
Hi Riyaj,
What is the difference between CR and Current? What are their relations?
Thank you so much for the scripts.
Thanks,
Adhika
orainternals said
Hello Adhika
Sorry for the late reply. CR means Consistent Read buffers, essentially, a buffer consistent at a SCN (time). When a SELECT statement accesses a buffer that session can not see uncommitted changes. So, undo records are applied to rollback uncommitted transactions creating a consistent copy of the buffer (database block) and SELECT statement can use that buffer.
In RAC, When a session opens an access request for a buffer, the request will include SCN version of the block required. If there are pending transactions in the block in remote cache, then LMS process in the remote instance will apply undo records to create a consistent version of the block and then ship the block to the requestor. This is known as CR transfer.
Current buffer means, that buffer includes latest changes, i.e. it is current. SELECT statement can access current mode buffers also, (1) if the buffer version in the memory and the block version in the disk are the same. (2) Buffer contains latest changes with no pending transactions.
Obviously, application changes only happen in current mode buffers even if there are many outstanding transactions in that block already. Row level locking mechanism protect session stepping at each other.
In a nutshell, CR mode buffers are use-and-throw buffers as the CR mode buffers are created consistent at a specific SCN. Current mode buffers contains all latest changes and it is this mode buffer that can be written to the disk. CR mode buffers can not be written to the disk and they are transient. (may or may not have pending transactions).
Hope this helps.
Cheers
Riyaj
adhikarexuss said
Hi Riyaj,
Thank you so much for the explanation. It helps a lot to understand this.
May I know which Oracle documentation also explain about this? (if exist)
Thank you,
Adhika
orainternals said
Hello Adhika
I am sure that concepts manual will indicate this difference. But, I can’t find the page with this information though..
Thanks
Riyaj