Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

[Hein van den Heuvel]

>> Here is the monitor summary report
>> There is very high Interrupt and Kernel percentage during that time.

Just FWIW, without trying to solve your problem perse, IMHO the stats you shared show very reasonable, possible LOW kernel and interupt time.

It suggests that Oracle and friends are doing some 'real' work, like IOs and LOCKs while doing the user stuff. Not unreasonable IMHO.

From a different perspective... if you where to 'tune' the kernel and int stack time entirely away (not that you could), then I don't think your users would notice
the extra idle time much.

However, if you could somehow half the user time, then the box would have a whole cpu idle. So where do want to spend your tuning time and energy?

Dispatchers and shared servers where all the rage 10+ years ago. They nicely save on memory needs and time wasted resolving contention. They may however have outplived theyr use for you. Have you tried without? A direct client to oracle-slave link, or does that create a prohibitive amount of processes on the backend? I suppose that with a 2 CPU system the shared server may be more appropriate then with higher SMP counts.

Just thinking out loud...

Hein.

[John H. Reinhardt]

You say you are testing a new version of the application. I see later you mention re-writing it from using PowerHouse to being web-based. What version of VMS and Oracle is the current production app running on (I assume that verion is running satifactorily)? During the re-design were the SQL queries changed? If the version of Oracle changed did the re-design take into account any changes in Oracle? Have statistics been generated for the data you are testing on? The Rule-based optimiser in Oracle can behave quite badly if the statistics are not current for the data in the tables. It WILL make a difference.

It's been too long since I was in charge of a busy Oracle database on VMS but the Monitor stats for Processor Mode don't seem to far off for a 100 process load.

What are the figures for your SGA sizes? Have you done the calculations for the various hit counts? Data buffer hit percentage? SQL cursor re-use? Latch waits?

MTS has historically performed horribly on VMS since Oracle 7.3 but supposedly this has been fixed in 9iR2 (9.2.0.3 and up) so I doubt MTS is the problem. You really need to do a good test with STATPACK running and find out what Orable is doing. My guess is that it's the application which is out of tune rather than the VMS system, but until we know what Oracle is doing it's just a guess.

[Thomas Ritter · Accepted solution]

Paul, when we over work out systems, 4 node ES45 cluster with three cpus each and 24 GB of RAM, we typically have a COM queue around 40 and CPU maxed out at 300% percent. We had exec mode at about 90% which indicated something with oracle/rdb was not working as it should.
Your figures look good from a system perspective. The slow user experience looks application related.

[Paul Benford]

Okay

To catch everyone up to what we have done here and more configuration detail

We are running OpenVMS 7.3-2 with TCPIP 5.4 ECo5.

Oracle verison is 9205 with a patch from Oracle that addresses various items with shared server. VMS patches were also loaded. Neither made a difference.

Late on Dec 9 the DBA reduced the number of dispatchers to 2. That improved the response time by 20% !. The compute queue went way down.

attached is a monitor summary of the test with 2 dispatchers, statspak with 10 dispatchers (Nov) and a stats pak with 2 dispatchers as well as a listing of VMS patches loaded.


VMs patches

[Paul Benford]

Well we made some application changes (changing one routine that opened a connection to the database 34,000 times in 10 minutes to do it once) and increased the performance by 64 %.

Thanks to all who contributed. Merry Christmas and happy new year

[Paul Benford]

see previous entry