Slow performance on a 3 node RAC instance

[bhatia333]

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.

[tamilselvan]

Do you have baseline data ( NO RAC) for the benchmark?

Tamil

[bhatia333]

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.

[tamilselvan]

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

[sagoswam]

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

[bhatia333]

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