Job taking longer with more RAC nodes
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <racdba-bounce@xxxxxxxxxxxxx>
- Date: Thu, 9 Mar 2006 13:41:32 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
