FAQ
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:

two-node RAC
one-node RAC
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.

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

Execute 1 523.60 552.10 1318 1213932 415042
1
Fetch 0 0.00 0.00 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

Execute 1 444.98 450.08 2244 1220178 415182
1
Fetch 0 0.00 0.00 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

Execute 1 460.04 459.60 1170 1208212 415492
1
Fetch 0 0.00 0.00 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

Search Discussions

  • Bryan Thomas at Mar 9, 2006 at 7:19 pm
    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
    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:

    two-node RAC
    one-node RAC
    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.

    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

    Execute 1 523.60 552.10 1318 1213932 415042
    1
    Fetch 0 0.00 0.00 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

    Execute 1 444.98 450.08 2244 1220178 415182
    1
    Fetch 0 0.00 0.00 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

    Execute 1 460.04 459.60 1170 1208212 415492
    1
    Fetch 0 0.00 0.00 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
  • John d parker at Mar 9, 2006 at 9:34 pm
    Do you have any degree of parallelism? or is parallel degree set to
    default?

    In general, A well behaved app will perform approx the same on RAC
    maybe even marginally slower. A poorly behaved app will absolutely
    stink when moved to RAC. RAC is not a performance enhancing solution.

    John

    "Hameed, Amir" wrote:
    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.
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Mogens Nørrgaard at Mar 26, 2006 at 9:30 am
    Well spoken. If you have a read-mostly app and/or tight node/user
    partitioning, you will minimize the necessary communication between
    nodes that ensures coherency and coordination. But that necessary
    overhead won't and can't go away, so apps can never run faster on RAC
    than non-RAC unless something else changes, too (like moving to
    Opterons, and so forth).

    Mogens

    john d parker wrote:
    Do you have any degree of parallelism? or is parallel degree set to
    default?

    In general, A well behaved app will perform approx the same on RAC
    maybe even marginally slower. A poorly behaved app will absolutely
    stink when moved to RAC. RAC is not a performance enhancing solution.


    John
    --
    http://www.freelists.org/webpage/oracle-l
  • LiShan Cheng at Mar 26, 2006 at 3:54 pm
    Hi Mogens

    What does Opteron differ from other chipsets that it can improve RAC?

    Cheers

    LSC
    On 3/26/06, Mogens Nørrgaard wrote:

    Well spoken. If you have a read-mostly app and/or tight node/user
    partitioning, you will minimize the necessary communication between
    nodes that ensures coherency and coordination. But that necessary
    overhead won't and can't go away, so apps can never run faster on RAC
    than non-RAC unless something else changes, too (like moving to
    Opterons, and so forth).

    Mogens

    john d parker wrote:
    Do you have any degree of parallelism? or is parallel degree set to
    default?

    In general, A well behaved app will perform approx the same on RAC
    maybe even marginally slower. A poorly behaved app will absolutely
    stink when moved to RAC. RAC is not a performance enhancing solution.


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

    --
    http://www.freelists.org/webpage/oracle-l
  • Hameed, Amir at Mar 9, 2006 at 9:53 pm
    This is an Oracle 11i application 11.5.9) and the database version is
    9.2.0.6 (64-bit). The thing that is surprising me is that there are no
    RAC specific waits. It is the CPU usage that is going high. I am sure
    there is an explanation to this and that is what I am trying to find
    out.

    -----Original Message-----
    From: john d parker
    Sent: Thursday, March 09, 2006 4:35 PM
    To: Hameed, Amir; oracle-l_at_freelists.org; racdba-bounce_at_freelists.org
    Subject: Re: Job taking longer with more RAC nodes

    Do you have any degree of parallelism? or is parallel degree set to
    default?

    In general, A well behaved app will perform approx the same on RAC maybe
    even marginally slower. A poorly behaved app will absolutely stink when
    moved to RAC. RAC is not a performance enhancing solution.

    John

    "Hameed, Amir" wrote:
    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.
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Oracle-l-bounce_at_freelists.org at Mar 10, 2006 at 1:01 am
    Hameed,

    In looking at the tkprof output, no rows were fetched in any of the
    tests. This was after an even 1.2 *million* LIOs and all of this is just
    EXEC (rather than FETCH)? An LIO is extremely/fully CPU intensive and
    that is why your elapsed time is mostly taken up by cpu time. Was that a
    valid SQL? Also, it seems that this is a custom code.

    In any case, I would assume that with RAC_ON, the kernel would go
    through additional code to check cross-instance buffer status and hence
    you suffer additional CPU usage for the same number of LIOs. (I am not a
    RAC expert by any measure so take this with a big pinch of salt).

    John Kanagaraj <><
    DB Soft Inc
    Phone: 408-970-7002 (W)


    Co-Author: Oracle Database 10g Insider Solutions
    http://www.amazon.com/exec/obidos/tg/detail/-/0672327910/


    The opinions and facts contained in this message are entirely mine
    and do not reflect those of my employer or customers **

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Hameed, Amir
    Sent: Thursday, March 09, 2006 1:54 PM
    To: john d parker; oracle-l_at_freelists.org; racdba-bounce_at_freelists.org
    Subject: RE: Job taking longer with more RAC nodes

    This is an Oracle 11i application 11.5.9) and the database version is
    9.2.0.6 (64-bit). The thing that is surprising me is that there are no
    RAC specific waits. It is the CPU usage that is going high. I am sure
    there is an explanation to this and that is what I am trying to find
    out.

    -----Original Message-----
    From: john d parker
    Sent: Thursday, March 09, 2006 4:35 PM
    To: Hameed, Amir; oracle-l_at_freelists.org; racdba-bounce_at_freelists.org
    Subject: Re: Job taking longer with more RAC nodes

    Do you have any degree of parallelism? or is parallel degree set to
    default?

    In general, A well behaved app will perform approx the same on RAC maybe
    even marginally slower. A poorly behaved app will absolutely stink when
    moved to RAC. RAC is not a performance enhancing solution.

    John

    "Hameed, Amir" wrote:
    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.
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

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

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 9, '06 at 6:41p
activeMar 26, '06 at 3:54p
posts7
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase