Oracle 11i Forms-based connections hang in RAC

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 26 Aug 2005 17:01:17 -0400
Message-ID: <77A4D80DB2ADD74EB5D7F1D31626F0C00232670D@usa0300ms03.na.xerox.net>

Hi All,
I need some assistance/suggestions with an issue that we are facing during our test runs of a two-node 11i-RAC environment. Following is the system configuration:

• Hardware Configuration: ** Servers: RAC Node#1: Sun 15K domain with 12 CPUs, 24 GB RAM RAC Node#2: Sun 15K domain with 12 CPUs, 24 GB RAM These are dedicated nodes for this RAC environment and do not run any other instance/application
• Interconnect: Three private gigabit interconnects between the two servers connected via a switch
• Network: The network interface between the middle-tier and the backend tier goes over a gigabit private network.
• Storage: EMC DMX 3000
• Software configuration Veritas DBE/AC for 9iRAC 3.5/MP3 Oracle applications 11.5.9 Oracle RDBMS 9.2.0.6 (64-bit) configured with ODM Shared Pool size: 1GB Buffer cache size: 6GB Total SGA size: ~ 7.5 GB

The concurrent managers are configured with PCP and are running fine.

Problem Description:
This environment is a copy of production and is ~750GB in size. We have a process in place where we run baseline tests using WinRunner for our production releases to assess how the new code or any new/changed configuration might have an impact once rolled into production. For this environment, using the same test process, we gathered baseline transaction timings on a single-instance non-RAC'd configuration. This was done so that we could compare the impact of RAC on the apps. We ran the next test with a two-node RAC configuration. The test did not go well. The forms-based connections, run by WinRunner, were getting timed out and loosing connections. While this was happening, I tried to open a forms-based connection to the core apps and it took me over a minutes to finally log into the apps. It normally takes around five to ten seconds. At the same time I also ran a script from the middle-tier to loop and repeatedly make connections to the database tier using SQL*Net and the connections were quick and I did not see any latency. I re-ran the same test and this time set event 10046 at level 12 to capture trace files from the forms connections as well as scheduled StatsPack to run every five minutes. The information captured from the RAW trace files as well as the StatsPack is consistent for all the forms server sessions that were trying to establish connection to the database. Following is a section from the tkprof'd file:

begin :v1 := fnd_bes_proc.process_event(:v2, :v3); end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 3 0.00 0.00 0 0 0 0
Execute 3 4.98 145.05 0 13027 24410 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

---

total 6 4.98 145.05 0 13027 24410 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173 (recursive depth: 1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

• Waited ----------

  ---

  global cache null to x 393 0.17 9.47 global cache open s 16 0.07 0.42 global cache open x 109 0.07 0.19 global cache s to x 61 0.07 0.34 log file sync 30 0.00 0.14 buffer busy waits 1802 0.91 18.51 buffer busy global cache 2540 0.17 87.80 buffer deadlock 2078 0.00 0.00 latch free 195 0.02 0.36 enqueue 443 0.49 19.37 global cache null to s 10 0.09 0.31 KJC: Wait for msg sends to complete 28 0.00 0.00 global cache cr request 1618 0.12 2.05 library cache pin 1 0.00 0.00 buffer busy global CR 305 0.00 0.39 global cache busy 38 0.07 0.99 lock escalate retry 3 0.00 0.00

  ---

  ---

The "buffer busy global" event is the highest contributor among all the events in most of the connections that either took long time to connect or failed to connect due to timeout. The file#, block# information from the RAW trace file point to an IOT that is part of a standard Oracle workflow Queue table. What I am failing to understand is that why a simple connection to the core apps would call the workflow queue. I can also send an output from StatsPack if anyone is interested. Any help/suggestion will be appreciated.

Thanks
Amir

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

Received on Fri Aug 26 2005 - 16:04:32 CDT