Operating System - OpenVMS
1748358 Members
5115 Online
108763 Solutions
New Discussion юеВ

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

 
SOLVED
Go to solution
Paul Benford
Advisor

Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

we are testing a new verison of our corporate application and get very poor performance.

We run tests using a test engine to simulate user load with our application.

Oracle is used as the back end and is RAC enabled. It is running on a 3 node ES45 cluster using memory channel and 2 CPUs' per ndoe and 3 Gbytes memory per node.

We have 70 % memory utilization during the test.

Once we get the number of users up to about 80 the CPUs' are saturated and running at 100 %. The compute queue is at 16 process.

Oracle is configured to use dispatchers and shared servers. The CPU consumption seems to be on the shared servers and the dispathers are heavy users of buffered I/O.

There is very little direct I/O.

I have attached a Performance Analysis summary.

We have tried several hardware configurations so the report I am attaching is a 2 node cluster with 4 cpus each and 6 Gbyte of RAM each.

as part of trouble shooting we removed RAC added CPUs etc to see what we would get. all the resource usage looks the same except when you add CPUs' but the problem occurs further into the test.
15 REPLIES 15
Thomas Ritter
Respected Contributor

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

Paul, post a CPU usage summary. Use "$ monitor modes" and look for items like EXEC mode. Have a corresponding "$ monitor system".

It will be interesting to study the EXEC mode usage. Like type repeated SQLs with unoptimised access strategies can perform badly.

Hein van den Heuvel
Honored Contributor

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

Paul,

The attachment failed. Please re-reply with new attachment.

Please add an STATSPACK result?


Did it ever work?
How good/bad did the old version work?
I like the reconfig test to understant 3*2CPU vs 2*4CPU and so on.
Have you also tested non-RAC as reference data.

Test engines, simulating users, and too easily run 'full blast' with no think time. I assume that when you mentoin 80 users ou don't mean 80 streams going max-sped, but a situation where the driver generates about as much load as 80 users, with think time and UI time, would generate when working hard.

hth,
Hein.
DICTU OpenVMS
Frequent Advisor

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

You probably know this one, but just to make sure : take a look at metlink Note:282965.1

Just in case...
Paul Benford
Advisor

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

Thanks for all the repsonses. I did not have much time yesterday when I started this thread so I will add more detail in this note.

First of all - a bit of back ground here

The original legacy application was created using powerhouse and interbase. It is a host based application. The target architecture is a 3 tier web based application. XP clients, WIN2003 middle tiers (started with win2000) and Oracle on OpenVMS as the back end. (the back end clusters are what the legacy application is currently using in production)

We started to rewrote the code to be a web based application. Since then we have been trying to improve the code. Over time hardware in the intel world has improved and now with Opteron based middle tier systems we can test up to 240 users ( limited to licensing .. will be fixed soon). Also fixes to the middle tier code has helped things along as well. The end result is that the middle tier systems are no longer a bottle neck. We have to test up to 600 users. That is the forecast number of users per region. (we have 6 regions).

Our testing tool is very robust and think times are built into it. It is the Rational product.

Testing methodology

we are currently using the rational test tool to exercise about 6 screens. Login, upate screens etc. (there are 600 screens but for peformance testing we are testing the 6 forecast heavy usage screens. 1 test lasts about 1 hour and ramps the usage up from 20 to 240 users in increments of 10. There is think time built in.

Last week we tried to go to 240 users (as we got the new Opterons (HP DL385 dual CPU )and seen the response time drop off. I checked the back end found the symptoms as descibed in my first note. The cluster configuration at that time was 3 ES45s, 3Gbytes Ram each, 2 cpus each, memory channel interconnect and attached to a HSG80 san using a redundant configuration (2 controllers, 2 switches)

So this week we have done the following tests and we see the same symptoms in all the configurations. all that varies is the time at which the problems occur(100% CPU, 16 + processes in compute queue and huge buffered i/o (8k to 11K)). The T# is the test number used for reference,.

1 ES45 with 6 Gbytes of RAM, 4 cpus, No RAC and VAXcluster = 0, T1263
1 ES45 with 6 gbytes of RAM, 3 CPUS, No Rac, and vaxcluster=0, T1264
1 ES45 with 6 Gbytes of RAM, 2 CPUS, No RAC and Vax cluster=0, T1265

2 ES45s with 6 gbytes of RAM, 4 CPUS, RAC enabled and in a cluster of 2, T1266
2 ES45s with 6 gbytes of RAM, 3 CPUS, RAC enabled and in a cluster of 2, T1267
2 ES45s with 6 gbytes of RAM, 2 CPUS, RAC enabled and in a cluster of 2, T1268

I have zip file with performance reports from PSPA in ZIP files by test number and I zipped them into 1 zip file to allow me to attach them ( I hope it works !)

We could not do a 3 node cluster as we do not have enough CPUs' not in use :).
Paul Benford
Advisor

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

Also on stats pak

We do not have a stats pak for these specific tests.

A DBA here came by and just told me he has gotten a patch from Oracle that addresses several shared server issues.

will let you all know what happens !

I will post a stats pak if the problem persists after applying the patch.
Thanks
Thomas Ritter
Respected Contributor

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

Pau, montior the usage realtime. Look at the output of monitor modes. It maybe very telling. If oracle is the problem, then exec mode be very high.
Thomas Ritter
Respected Contributor

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

Paul, I find running monitor in non-interactive mode very useful.

syntax is something like this

$monitor/begin="''f$time()'"/endin="''f$time()'+0 06:00" -
/display=dud.tmp -
/record=monraw.tmp process/topcpu, process/topdio,modes,system

If there is a specific time interval under study then you can replay at that time. Very quick and can be very telling.
Paul Benford
Advisor

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

I do run monitor as a detached process as well as the data collector for PSPA.

But due to the reboots for the hardware reconfig the saved binary file is not getting closed properly and has nothing in it. I guess I have to check my procedure. It is basically the monitor procedure from sys$examples spruced up a bit .. but not enough I guess.

But for some of the tests I do have good monitor files.

But in the files I attached exec mode and interrupt mode CPU usage is in there and they are taking about 8% of the CPU.

I will create a monitor summary file for the test period of CPU modes and attach it.
Paul Benford
Advisor

Re: Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

Here is the monitor summary report

modes, state and sys/cur of 5 minutes when the system is exhibitig the less then desirable performance.

There is very high Interrupt and Kernel percentage during that time. I guess the other tool I am using is averaging out those values over the test period