RE: Scalability fo Batch Operations in RAC

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.

mwf
  -----Original Message-----
  From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Sriram Kumar   Sent: Monday, December 26, 2005 10:50 AM   To: ORACLE-L
  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 9.2.0.4.

    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 Received on Mon Dec 26 2005 - 17:26:02 CST