RE: Job taking longer with more RAC nodes

From: Bryan Thomas <bthomas_at_perftuning.com>
Date: Thu, 9 Mar 2006 13:19:34 -0600
Message-Id: <200603091919.k29JJYLg009008@server02.mail.corenap.com>

I have seen the same thing.

If you do not put a decent load on a standalone server, adding additional servers in a cluster will cause things to run slower.

One customer POC I was on ran a single app server against a single DB server, 2-node, and 3-node RAC. The performance got progressively worse with the addition of each node.

It turned out that the app server was the bottleneck and the standalone database server had only 15% utilization.

Bryan

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir
Sent: Thursday, March 09, 2006 12:42 PM
To: oracle-l_at_freelists.org; racdba-bounce_at_freelists.org Subject: 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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 09 2006 - 13:19:34 CST