Job taking longer with more RAC nodes

Folks,
I need some help in understanding why a job is taking longer when it is
run in multiple nodes under RAC versus a single node in RAC and with RAC
disabled. I generated trace files with 10046 at level 12 under the
following scenarios:

1. two-node RAC
2. one-node RAC
3. Single instance with RAC disabled

In each of the above runs the same job was run in the same environment.
When I look at the tkprof output, I do not see any significant global
cache related waits and that is why I am a little confused that why the
job takes longer as we increase the number of nodes. I am pasting a
section that is taking the longest amount of time from trace files from
each run.


1. TKPROF output from two-node run

BEGIN XRX_MAIN_CUST_PROD_PKG.CUST_PROD_MAIN( :errbuf, :rc,:A0); END;


call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.00       0.00          0          0          0
0
Execute      1    523.60     552.10       1318    1213932     415042
1
Fetch        0      0.00       0.00          0          0          0
0
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        2    523.60     552.11       1318    1213932     415042
1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  SQL*Net message to client                       4        0.00
0.00
  SQL*Net message from client                     4        0.00
0.00
  library cache pin                              30        0.00
0.04
  row cache lock                                 29        0.00
0.04
  rdbms ipc reply                                29        0.00
0.03
  log file sync                                   1        0.09
0.09
************************************************************************
********


2. TKPROF from one-node RAC
BEGIN XRX_MAIN_CUST_PROD_PKG.CUST_PROD_MAIN( :errbuf, :rc,:A0); END;


call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.00       0.00          0          0          0
0
Execute      1    444.98     450.08       2244    1220178     415182
1
Fetch        0      0.00       0.00          0          0          0
0
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        2    444.98     450.08       2244    1220178     415182
1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  SQL*Net message to client                       4        0.00
0.00
  SQL*Net message from client                     4        0.00
0.00
  log file sync                                   1        0.00
0.00
************************************************************************
********

3. TKPROF from single-instance with RAC disabled:
BEGIN XRX_MAIN_CUST_PROD_PKG.CUST_PROD_MAIN( :errbuf, :rc,:A0); END;


call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.00       0.00          0          0          0
0
Execute      1    460.04     459.60       1170    1208212     415492
1
Fetch        0      0.00       0.00          0          0          0
0
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        2    460.04     459.61       1170    1208212     415492
1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 173

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  SQL*Net message to client                       4        0.00
0.00
  SQL*Net message from client                     4        0.00
0.00
  library cache pin                               6        0.00
0.00
  rdbms ipc reply                                 6        0.00
0.00
  row cache lock                                  1        0.00
0.00
************************************************************************
********

It seems to me that the database CPU usage related to the work done also
increases with RAC regardless whether there are any GC related waits or
not. Any help would be appreciated.

Thanks
Amir

--
http://www.freelists.org/webpage/oracle-l

Other related posts: