A quick note, Expert Oracle RAC book co-written by me is available now: Expert Oracle RAC 12c. I have written about 6 chapters covering the RAC internals that you may want to learn :) I even managed to discuss the network internals in deep, after all, network is one of the most important component of a RAC cluster.
Archive for the ‘RAC’ Category
Posted by Riyaj Shamsudeen on September 8, 2013
Posted by Riyaj Shamsudeen on June 12, 2013
This blog entry is to discuss a method to identify the objects inducing higher amount of redo. First,we will establish that redo size increased sharply and then identify the objects generating more redo. Unfortunately, redo size is not tracked at a segment level. However, you can make an educated guess using ‘db block changes’ statistics. But, you must use logminer utility to identify the objects generating more redo scientifically.
Detecting redo size increase
AWR tables (require Diagnostics license) can be accessed to identify the redo size increase. Following query spools the daily rate of redo size. You can easily open the output file redosize.lst in an Excel spreadsheet and graph the data to visualize the redo size change. Use pipe symbol as the delimiter while opening the file in excel spreadsheet.
spool redosize.lst REM You need Diagnostic Pack licence to execute this query! REM Author: Riyaj Shamsudeen col begin_interval_time format a30 set lines 160 pages 1000 col end_interval_time format a30 set colsep '|' alter session set nls_date_format='DD-MON-YYYY'; with redo_sz as ( SELECT sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time , startup_time, VALUE - lag (VALUE) OVER ( PARTITION BY startup_time, sysst.instance_number ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value, EXTRACT (DAY FROM (end_interval_time-begin_interval_time))*24*60*60+ EXTRACT (HOUR FROM (end_interval_time-begin_interval_time))*60*60+ EXTRACT (MINUTE FROM (end_interval_time-begin_interval_time))*60+ EXTRACT (SECOND FROM (end_interval_time-begin_interval_time)) DELTA FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE stat_name='redo size' ) AND snaps.snap_id = sysst.snap_id AND snaps.dbid =sysst.dbid AND sysst.instance_number=snaps.instance_number and begin_interval_time > sysdate-90 ) select instance_number, to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt , sum(stat_value) redo1 from redo_sz group by instance_number, to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') order by instance_number, 2 / spool off
Visualizing the data will help you to quickly identify any pattern anomalies in redo generation. Here is an example graph created from the excel spreadsheet and see that redo size increased recently.
Posted by Riyaj Shamsudeen on June 5, 2013
The restart of a UNIX server call initialization scripts to start processes and daemons. Every platform has a unique directory structure and follows a method to implement server startup sequence. In Linux platform (prior to Linux 6), initialization scripts are started by calling scripts in the /etc/rcX.d directories, where X denotes the run level of the UNIX server. Typically, Clusterware is started at run level 3. For example, ohasd daemon started by /etc/rc3.d/S96ohasd file by supplying start as an argument. File S96ohasd is linked to /etc/init.d/ohasd.
S96ohasd -> /etc/init.d/ohasd /etc/rc3.d/S96ohasd start # init daemon starting ohasd.
Similarly, a server shutdown will call scripts in rcX.d directories, for example, ohasd is shut down by calling K15ohasd script:
K15ohasd -> /etc/init.d/ohasd /etc/rc3.d/K15ohasd stop #UNIX daemons stopping ohasd
In Summary, server startup will call files matching the pattern of S* in the /etc/rcX.d directories. Calling sequence of the scripts is in the lexical order of script name. For example, S10cscape will be called prior to S96ohasd, as the script S10cscape occurs earlier in the lexical sequence.
Google if you want to learn further about RC startup sequence. Of course, Linux 6 introduces Upstart feature and the mechanism is a little different: http://en.wikipedia.org/wiki/Upstart
That’s not the whole story!
Have you ever thought why the ‘crsctl start crs’ returns immediately? You can guess that Clusterware is started in the background as the command returns to UNIX prompt almost immediately. Executing the crsctl command just modifies the ohasdrun file content to ‘restart’. It doesn’t actually perform the task of starting the clusterware. Daemon init.ohasd reads the ohasdrun file every few seconds and starts the Clusterware if the file content is changed to ‘restart’.
# cat /etc/oracle/scls_scr/oel6rac1/root/ohasdrun
Posted by Riyaj Shamsudeen on August 29, 2012
There are many questions from few of my clients about asmlib support in RHEL6, as they are gearing up to upgrade the database servers to RHEL6. There is a controversy about asmlib support in RHEL6. As usual, I will only discuss technical details in this blog entry.
ASMLIB is applicable only to Linux platform and does not apply to any other platform.
Now, you might ask why bother and why not just use OEL and UK? Well, not every Linux server is used as a database server. In a typical company, there are hundreds of Linux servers and just few percent of those servers are used as Database servers. Linux system administrators prefer to keep one flavor of Linux distribution for management ease and so, asking clients to change the distribution from RHEL to OEL or OEL to RHEL is always not a viable option.
Do you need to use ASMLIB in Linux?
Short answer is No. Long answer is possibly No. ASMLIB is an optional support library and eases the administration of ASM devices. Especially, it is helpful while adding new devices to the nodes in a cluster. ASMLIB essentially stamps the devices and so, it is easily visible in other nodes of a cluster in the next asm scandisk. asmlib also provides device persistence, which is the important benefit of ASM (see the discussion below for more details about device persistence).
Posted by Riyaj Shamsudeen on June 1, 2012
This is a quick note about reverse path filtering and impact of that feature to RAC. I encountered an interesting problem recently with a client and it is worth blogging about it, with a strong hope that it might help one of you in the future.
Environment is 126.96.36.199 GI, Linux 5.6. In a 3 node cluster, Grid Infrastructure (GI) comes up cleanly in just one node, but never comes up in other nodes. If we shutdown GI in first node, we can start the GI in second node with no issues. Meaning, GI can be up in just one node at any time.
System Admins indicated that there are no major changes, only few bug fixes. Seemingly, problem started after those bug fixes. But there were few other changes to the environment /init.ora parameter change etc. So, the problem was not immediately attributable to just OS changes.
Posted in Oracle database internals, Performance tuning, RAC | Tagged: "has Disk HB, advanced RAC training, but no Network HB", cssd not joining cluster, RAC performance, reverse path filtering, rp_filter | 11 Comments »
Posted by Riyaj Shamsudeen on May 22, 2012
Let’s first discuss how RAC traffic works before continuing. Environment for the discussion is: 2 node cluster with 8K database block size, UDP protocol is used for cache fusion. (BTW, UDP and RDS protocols are supported in UNIX platform; whereas Windows uses TCP protocol).
UDP protocol, fragmentation, and assembly
UDP Protocol is an higher level protocol stack, and it is implemented over IP Protocol ( UDP/IP). Cache Fusion uses UDP protocol to send packets over the wire (Exadata uses RDS protocol though).
MTU defines the Maximum Transfer Unit of an IP packet. Let us consider an example of MTU set to 1500 in a network interface. One 8K block transfer can not be performed with just one IP packet as the IP packet size (1500 bytes) is less than 8K. So, one transfer of UDP packet of 8K size is fragmented to 6 IP packets and sent over the wire. In the receiving side, those 6 packets are reassembled to create one UDP buffer of size 8K. After the assembly, that UDP buffer is delivered to an UDP port of a UNIX process. Usually, a foreground process will listen on that port to receive the UDP buffer.
Posted in 11g, Oracle database internals, Performance tuning, Presentations, RAC, video | Tagged: cache fusion mtu, fragmentation and reassembly, gc lost packets, ipfrag_high_thres, ipfrag_low_thres, ipfrag_time, Jumbo frames, MTU, MTU=9000, oracle performance, RAC internals, RAC performance, RAC presentations, RAC training, RAC video, RAC videos, RDS, UDP vs tcp, wireshark | 12 Comments »
Posted by Riyaj Shamsudeen on April 29, 2012
We know that database blocks are transferred between the nodes through the interconnect, aka cache fusion traffic. Common misconception is that packet transfer size is always database block size for block transfer (Of course, messages are smaller in size). That’s not entirely true. There is an optimization in the cache fusion code to reduce the packet size (and so reduces the bits transferred over the private network). Don’t confuse this note with Jumbo frames and MTU size, this note is independent of MTU setting.
In a nutshell, if free space in a block exceeds a threshold (_gc_fusion_compression) then instead of sending the whole block, LMS sends a smaller packet, reducing private network traffic bits. Let me give an example to illustrate my point. Let’s say that the database block size is 8192 and a block to be transferred is a recently NEWed block, say, with 4000 bytes of free space. Transfer of this block over the interconnect from one node to another node in the cluster will result in a packet size of ~4200 bytes. Transfer of bytes representing free space can be avoided completely, just a symbolic notation of free space begin offset and free space end offset is good enough to reconstruct the block in the receiving side without any loss of data.This optimization makes sense as there is no need to clog the network unnecessarily.
Posted by Riyaj Shamsudeen on April 19, 2012
If you are attending Collaborate 2012, you might be interested in my content-rich sessions below :
Session Number: 326
Session Title: SCAN, VIP, HAIP, and other RAC acronyms
Session Date/Time/Room: Tue, Apr 24, 2012 (10:45 AM – 11:45 AM) : Surf C
Session Number: 327
Session Title: Internals and Performance Boot Camp: Truss, pstack, pmap, and more
Session Date/Time/Room: Wed, Apr 25, 2012 (03:00 PM – 04:00 PM) : Palm A
Hope to see you there!
Update: I am uploading presentation files. Presentations are much more recent than the document :-)
Thanks for attending!
Posted in Oracle database internals, Performance tuning, Presentations, RAC | Tagged: collaborate 2012 presentations, haip, pfiles, pmap, pstack, RAC, RAC performance, RAC presentations, scan, semtimedop, strace, truss, vip | 1 Comment »
Posted by Riyaj Shamsudeen on April 19, 2012
Last week (March 2012), I was conducting Advanced RAC Training online. During the class, I was recreating a ‘gc buffer busy’ waits to explain the concepts and methods to troubleshoot the issue.
Let’s define these events first. Event ‘gc buffer busy’ event means that a session is trying to access a buffer,but there is an open request for Global cache lock for that block already, and so, the session must wait for the GC lock request to complete before proceeding. This wait is instrumented as ‘gc buffer busy’ event.
From 11g onwards, this wait event is split in to ‘gc buffer busy acquire’ and ‘gc buffer busy release’. An attendee asked me to show the differentiation between these two wait events. Fortunately, we had a problem with LGWR writes and we were able to inspect the waits with much clarity during the class.
Remember that Global cache enqueues are considered to be owned by an instance. From 11g onwards, gc buffer busy event differentiated between two cases:
- If existing GC open request originated from the local instance, then current session will wait for ‘gc buffer busy acquire’. Essentially, current process is waiting for another process in the local instance to acquire GC lock, on behalf of the local instance. Once GC lock is acquired, current process can access that buffer without additional GC processing (if the lock is acquired in a compatible mode).
- If existing GC open request originated from a remote instance, then current session will wait for ‘gc buffer busy release’ event. In this case session is waiting for another remote session (hence another instance) to release the GC lock, so that local instance can acquire buffer.
Following output should show the differentiation with much clarity.
Posted by Riyaj Shamsudeen on February 27, 2012
Just a quick note, I will be conducting next 2-week advanced RAC Training, online class in March 26-30 and April 9-13.
You can find agenda and other details here .
I received few emails about the training outline. You can find the outline below:
Yes, I do accept Purchase Orders and can invoice you :)