Oracle 9i resource utilisation on OpenVMS

[Matt West]

Hi

We are shortly moving onto Oracle 9i for OpenVMS and I have concerns relating to the resource requirement of the product. My reason for having these concerns stems from the previous upgrade to Oracle 8i, where we saw a 30% increase on CPU utilisation.

Anyone know of any issues?

[Hein van den Heuvel]

I suspect you will like 9i. You'd need to describe your application more to get better help. OLTP-ish. Warehouse-ish. Interactive?Free style queries? Long lasting connections? Quick connect-query-exit? Likely to benefit from teh cost-based optimizer?

I happen to have been working a little with a (Dutch) customer that experienced a severe (2x !) CPU increase going from Oracle 7.1.4 to 8i.

The reason/excuse given was that Oracle dramatically improved the optimizer (and they did) and parser and stuff like that.
Their application however was special in that they basically only did singleton selects / updates. So there was nothing to optimize. All Oracles efforts only made their codepath longer!

They have since tested with 9i and did NOT see further reduction. I fact they are pretty happy with to combined improvements or Oracle, VMS and Alpha.

You may want to review MEMORY settings, like the pga target and java pools. You probably do not need/want any Java pool, but Oracle defaults to 50MB, so if you run several databases...

If your application connects in frequently, then be sure to doublecheck anything and everything on the process startup. Early exits from sylogin? login? blah blah...

Good luck!
Hein.

[Matt West]

Thanks very much for taking the time to respond. Our application acts as a trade settlement engine for a busy business area and performs OLTP and random query functions. The application itself is already CPU bound (12 x 731mhz GS320) and often 98% utilised at peak loads. We run an SGA of 3GB and have 16GB of physical memory. I know that pending application changes are already going to increase the CPU requirement but wondered if another 7 to 8 overhead would reoccur.

[Hein van den Heuvel]

Sounds like 4 QBBs with 3 CPUs in each!?
I would encourage you to try 3 QBBs with 4 cpu's each and just 12 GB.

You might see 10% less cpu used. This is from a course average memory latency calculation: avg-lat=(local+(N-1)*remote)/N
With local latency = 300, remote = 900 this gives:
1 300
2 600
3 700
4 750
5 780
6 800
7 814
8 825

So just 750 -> 700 in your case, but if you can help it a little more by carefull process / memory / Io placement it could be enough to make a real difference.

Of course the big solution here is a Marvel! (and even a 1Ghz or 1.25 Ghz wildfire upgrade it going to give good bang for the buck)

> The application itself is already CPU bound (12 x 731mhz GS320) and often 98% utilised at peak loads. We run an SGA of 3GB and have 16GB of physical memory. I know that pending application changes are already going to increase the CPU requirement but wondered if another 7 to 8 overhead would reoccur.

How about that 3GB SGA, you are already using Reserved Memory for that I hope ?!
Any benefit from more still? cursor_space_for_time? alter table xxx cache/

Hein.

[Matt West]

It is already 3 x QBB.

[Hein van den Heuvel]

Ah, good. You had me confused with the 16 GB. I assumed 4*4gb. So i guess it is 8+4+4.
If you have a QBB with no cpus but 4GB memory, and you don't really need that memory, then you may want to switch it off.

fwiw,
Hein.

[Matt West]

Spot on, 8+4+4 it is.

[Martin P.J. Zinser]

I concurr with Hein, the Marvel will most probably help you quite a bit, since the memory access times have improved considerably over the Wildfire (and are much more uniform). Since you do run serious SMP also getting to the latest versions of VMS and TCP/IP services (7.3-2 and 5.4) should help. If you are not yet at 7.3-1 this applies even more.