Slow Oracle network connections setup (54 Views)
Occasional Advisor
john leavens_1
Posts: 12
Registered: ‎02-18-2003
Message 1 of 2 (54 Views)
Accepted Solution

Slow Oracle network connections setup

I am running Oracle 9i R2 on a 1 processor rx2600 with HPUX 11i v1.6

The database serves 5 Win2k application servers. We are using dynamic connection pooling on the app servers so connections are maintained at a preset high water mark. We noticed unusually high system CPU utilization when ever we need to create connections ( 80% system usage ). I am not seeing any swapping.

As a test, I ran a script from sqlplus that just exits, so all we see is the time it takes to startup the connection and fork a dedicated server process and then exit.
I loop through 20 of these.
It takes about ~1-2 seconds to setup the connection and System CPU goes to ~80%.
If I run the same script against the same version of Oracle on a Win2k box (with similiar CPU speed and less RAM) it is at least 4 times as fast.

I looked at an Oracle connection trace and found most of the time occurs when the new dedicated server process is forked. I know Windows just fires another thread so the overhead of allocating another process is saved but ~1 second to create a session sounds absurd to me.
Any ideas or suggestions?

John Leavens
Please use plain text.
Honored Contributor
Hein van den Heuvel
Posts: 6,578
Registered: ‎05-19-2003
Message 2 of 2 (54 Views)

Re: Slow Oracle network connections setup

Yeah upgrade V1.6 (11.22) to V2 (11.23). That will solve it.

Nest I know V1.6 was never intended for production was it?

I have observed this a while ago and analyzed it witl caliper. Here is the average profile for an oracle slave activation under 11.22:

USER portion of profile: 8 hits = 0.08 seconds. KTC time was 0.052 seconds
25% 25% 2 0.02 UT_memcpy /usr/lib/hpux64/
12% 38% 1 0.01 __milli_memcpy /apps/oracle92/bin/oracle
12% 50% 1 0.01 kkshchv /apps/oracle92/bin/oracle
12% 62% 1 0.01 ttci2u /apps/oracle92/bin/oracle
12% 75% 1 0.01 LE_sym_name /usr/lib/hpux64/
12% 88% 1 0.01 LE_get_opd_entry /usr/lib/hpux64/

KERNEL portion of profile: 123 hits = 1.23 seconds. KTC time was 1.209 seconds

As soon as we upgraded to 11.23 our application (on an 4p IPF box) went from 25% systems time, blocking an entire cpu, to well below 5%.

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation