RE: Scalability fo Batch Operations in RAC

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <k.sriramkumar@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 26 Dec 2005 11:26:02 -0500

Your traces should tell you a lot. Of course the RA in RAC stands for Real
Applications with at least a marketing meaning that you don't have to
re-write your applications to run on multiple nodes.

However, if you take a batch application that is threading through blocks in
search of rows to work on that are intertwined with likely identical plans,
those 4 ms block shuttles, even though pretty doggone fast,
are likely the extra overhead. You might also have  "consistent write" (see
Tom Kyte) problems in running the job in parallel that are exacerbated by
bringing a second node into the equation. Hmm, let's see - twice as much
horsepower and the job takes 3 halves the previous elapsed time. That is
pretty bad scaling. If adding 3rd, 4th, ... nth nodes tracks, then you can
use half-life equations to see how many nodes you have to add to make the
job asymptotic to forever.

So while you don't have to re-write the application to make it continue to
function logically correctly in RAC, maybe you can come up with a reasonable
way to make the 2 (or n) nodes at least start out after
different blocks to begin with. If there is a natural way to partition or
cluster for the batch job so that threads on node 1 naturally go after one
set of blocks and threads on node 2 naturally go after a different set of
blocks, then you are more likely to reduce the overall elasped time by
keeping inter node traffic light. Even the overhead of assembling disparate
lists of blocks to process into intermediate "select against" tables and
adding the node's block candidate list to the query might work out in your
favor if partitioning is not a reasonable solution. Do please remember that
disjoint sets of rows are NOT good enough and that you need disjoint sets of
blocks to avoid internode dependencies.

Good luck.

  -----Original Message-----
  From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sriram Kumar
  Sent: Monday, December 26, 2005 10:50 AM
  Subject: Scalability fo Batch Operations in RAC

  Hi Gurus,

     We are running a heavy batch operation of a banking application on RAC.
Say the configuration is a 2 node 4 CPU Itanium2 box running

    We have batch operation that can be run in parallel. What I am noticing
is that when I run all the batch operations in node 1, it gets over in 2
hours . During this time, the other node is not used at all.  To optimally
use the resources , I run the batches across 2 nodes, I see a degradation in
performance( the elapsed time would be around 3 hours).There are no blocking
locks and interconnect performance is good( block transfer time is around
4ms). I am planning to do a 10046 on both the scenarios and compare the top
SQL's and waits.

  I just wanted to know if any one of you had faced this issue before or is
this normal?.

  Best Regards

  Sriram Kumar

Other related posts: