Slow performance on a 3 node RAC instance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Slow performance on a 3 node RAC instance

  1. #1
    Join Date
    Aug 2002
    Posts
    56

    Slow performance on a 3 node RAC instance

    Hi,
    We have a 3 node RAC instance on Linux x86 2.14 boxes using Oracle 9.2.0.7. The boxes have 4 Cpu with 8g mem on each one of them. Below are the important parameter settings

    db_cache_size big integer 2147483648
    java_pool_size big integer 67108864
    large_pool_size big integer 0
    hared_pool_size big integer 536870912
    session_cached_cursors integer 200


    The client is running some performance tests using mercury. They are using Oracle E-business suite. The are expereincing slowness when concurrent users connect and perform some routine tasks. The statspack shows a lot of enqueue waits:

    Event Waits Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    rdbms ipc reply 466 720 27.77
    CPU time 719 27.73
    enqueue 674,444 294 11.33
    global cache null to x 23,541 202 7.80
    global cache null to s 7,895 160 6.19
    -------------------------------------------------------------
    Also a lot of sleeps for ges caches resource list latch

    gcs opaque info freelist 93,477 0.0 0.0 0 0
    gcs resource freelist 4,262 0.0 0.0 0 0
    gcs resource hash 625,158 0.0 0.0 0 269 0.7
    gcs resource scan list 540 0.0 0 0
    gcs shadows freelist 7,611 0.1 0.0 0 0
    ges caches resource list 435,062 0.1 0.0 0 43,539 0.5


    I have attached the statspack report too. What can be done in order to tune the RAC instance. Any help is appreciated.

    Thanks.
    When in doubt ...go to the basics!!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you have baseline data ( NO RAC) for the benchmark?

    Tamil

  3. #3
    Join Date
    Aug 2002
    Posts
    56
    No I dont. But when I spoke to the client they said that they were exp. slowness for some of the UI actions with 10-15 sec delay. Thats the user perception I have but no concrete data.
    When in doubt ...go to the basics!!

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can generate baseline data by shutting down 2 nodes.
    Then you can compare the UI response time with 3 nodes.
    It's difficult to say CACHE fushion is causing poor perf.

    Tamil

  5. #5
    Join Date
    Dec 2006
    Posts
    1
    To reduce "CPU time" tune sqls with high buffer gets.

    what kind of enqueue wait you are getting TX ? TM ? or something else. Also check the rquested mode for that enqueu. Check 'enqueue activity' and v$lock for enqueue details.

    Cheers,
    Santanu

  6. #6
    Join Date
    Aug 2002
    Posts
    56
    I checked during the runs and there were no enqueue locks on the database although when using the racdiag.sql script I got the following detail:

    WAITING SESSIONS:

    INST_ID SID STATE EVENT SECONDS P1 P2 P3 LAST_SQL
    ---------- ---------- ------- ------------------------- ---------- ---------- -------
    --- ---------- ----------------------------------------
    2 37 WAITING queue messages 8 311453 2597698332 10 BEGI
    N FND_CP_GSM_IPC.Get_Message(:1,:2,:
    2 59 WAITING queue messages 4 243944 2597714652 10 BEGIN WF_EVENT_OJMSTEXT
    _QH.DEQUEUE(:1, :
    1 64 WAITING enqueue 1 1431044102 1073784457 0 declare v_ret number := 0; begin

    GES LOCK WAITERS:

    INST_ID SID SPID RESOURCE_NAME1 GRANT_LEVEL REQUEST_LEVEL STATE
    SID EVENT SEC
    ------- ----- ------------ ------------------------------ -------------------------------- ---------------------------
    ----- ---------------- ----- ------------------------------ -----
    3 93 30223 [0x4000a689][0x0],[UL] Null Exc
    lusive 93 SQL*Net message from client
    8
    3 111 30225 [0x4000a689][0x0],[UL] Null Exclusive 111 SQL
    *Net message from client 8
    3 107 30230 [0x4000a689][0x0],[UL] Null Exclusive
    107 SQL*Net message from client 7
    3 61 30223 [0x4000a689][0x0],[UL] Null Exclusive
    61 enqueue
    1
    3 101 30225 [0x4000a689][0x0],[UL] Null Exclusive 101 enqueue
    1
    3 112 30230 [0x4000a689][0x0],[UL] Null Exclusive
    112 enqueue 1



    GES LOCK BLOCKERS:

    INST_ID SID SPID RESOURCE_NAME1 GRANT_LEVEL
    REQUEST_LEVEL STATE SID EVENT
    SEC
    ------- ----- ------------ ------------------------------ -------------------------------- -------------------------------- ---------------- --
    --- ------------------------------ -----
    2 102 26687 [0x4000a689][0x0],[UL] Exclusive Exclusive
    102 SQL*Net message from client 8
    2 155 26687 [0x4000a689][0x0],[UL] Exclusive Excl
    usive 155 lock escalate retry
    0
    When in doubt ...go to the basics!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width