Tuning Inserts in RAC

If Oracle database is not I/O bounded and is scalable by adding more CPU to single instance database then  it is likely to scale very well in RAC. However this is always not true in some cases and scalability of RAC can be limited  by many well known factors like slow interconnect or too busy interconnect. But there are few cases when RAC does not scale well in-spite of having very good interconnect. One such case is batch jobs with massive inserts on tables if there are too many right hand indexes(using ordered sequences or not ordered sequence with low cache, or ordered values coming from any source other than sequence) which caused major cluster overheads. In case 1) of UN-ordered sequences these GC waits can be minimized by increasing cache to large size (in order of thousands) and in case 2) sequences must be ordered then using reverse indexes or Hash partitioned indexes can minimize GC waits. Case 1) does not have CPU and I/O overhead while case 2) has major CPU and I/O overheads. So it must be evaluated that reduced GC waits benefits over-weigh additional CPU/ and I/O overhead required for Reverse/Hash partitioned indexes.  

TOTAL CLUSTER LATENCY WAITS (Prepare time and transfer time)

in graph 7:46 to 7:56 Reverse key index, 8:00 to 8:07 Hash partitioned indexes, 8:19 to 8:29 Normal Iidexe

Prepare time: 

• Negligible in case of Reverse Indexes
• Modest in Hash Partitioned indexes
• Very High in Normal Index

Transfer time is highest in case of Reverse Indexes as keys are spread as random as number of rows [ cluster factor CF is so high in Reverse key indexes and optimize can tend to ignore this index due high CF ] in table and so high number of blocks are transferred and this is also the reason that Reverse key indexes requires large number of I/O may be ten fold or more higher than normal index. But provided there is no scarcity of buffered cache this request can be satisfied by normal GC waits (negligible GC busy waits ).

Normal indexes has severe BUFFER BUSY waits(gc buffer busy release and gc buffer busy acquire) contention and due large time of pinning and log flushing they have significantly highest CR Block creation time( preparation time).

It will become more clear from below graph which depicts Latency Preparation time :

Below graph depicts transfer latency for all cases which is less in HASH partitioned index case:

WORK LOAD both instances : highest in Reverse key index, least in Normal Index, modest in Hash

Latency of both RAC instances separately:

Cluster Latency :

Input - Output (I/O) summary Graphs:

Throughput(Waite event types) :

You can notice too high tower for single blocks reads coming from too many read calls for db file sequential reads for REVERSE key indexes while these are not high in case of HASH partitioned indexes and Normal Index. Later two's have very large REDO log write required for gcs log flush.


IO Latency: 

Throughput requests (File Type) :

Operating System statistics:

 CPU usage ( at node1[node2 has similar statistics] ):

Disk I/O ( at node1[node2 has similar statistics] ):

Load Average Run Queue ( at node1[node2 has similar statistics] ):

Network packet transfer rate ( at node1[node2 has similar statistics] ):

Conclusion: 

• Contrary to common belief Reverse Indexes caused more logical I/O(and potentially physical I/Os) and more Interconnect traffic.Their I/O time is much more than CPU time. They just save time of pinning buffer and flushing and thus relieve pressure of Log file I/O. But this gain may not be enough to overweigh too I/O and CPU they may incu
• Hash Indexes :   Best choice if there is heavy GC buffer busy events.
• Normal Index: May still be better than above cases if not lot of right hand indexes and have fewer concurrency or fewer number of nodes.