Performance problem on K-class

[Kevin Dillon]

Hi all

I posted a message last week about performance troubleshooting strategies.
I've collected some stats and would welcome any second opinions.

We have a K360 with 3CPU's and 1Gb memory running Oracle.
I have been asked to account for the time it takes to perform several simultaneous accesses of the application.
I am looking at it from the perspective of : Are there any clear signs of system stress which can be aleviated by hardware/ software changes?

To this end, I attach some sar, vmstat and glance data collected whilst these simultaneous attempts at access were ongoing (c. 4 minute period). - processes appear around 23:04!

From what I can see, whilst the 3 CPU's are operating at high levels of utilisation, there is no great evidence of swapping (save when the simultaneous activities first start!). Neither is the system IO bound. The run queues are around 3 for the duration of the accesses.

Could it be that the 180MHz CPU's are completeing the processes as fast as they can and that no amount of "tweaking" will make them complete any faster?

thanks in advance

[Stefan Farrelly]

Your stats look pretty good!

(Wait on I/O) wio% is low (for a few seconds it gets high but overall nice and low).
Memory also gets low as your run goes on, down to 4MB free which isnt very much. Your system could benefit from adding more RAM or freeing up more RAM and allocating it to oracle to utilise for caching etc, this would speed things up a little.
CPU is very high, your cpu bound. Certainly faster processors would help - as your not being slowed down by IO or disk so a faster processor would make your jobs run faster. If youre running 180Mhz imagine what a new 550Mhz would do!

[Santosh Nair_1]

Judging from the glance output, looks like your buffer cache is quite big (around 20-30% memory). Might want to consider lowering it to say 10%. This will give back some valuable memory to other processes.

You seem to have burst of disk activity, but the system seems to be handling it quite well...but your memory situation is very dire. Like Stefan said, look into getting more memory for the system. Oracle tends to a bit of memory hog (as do most databases).

-Santosh

[Kevin Dillon]

stefan/ santosh

many thanks

santosh, re the buffer cache, would these kernel paramaters look better:

dbc_max_pct = 10 (currently 50%)
dbc_min_pct = 2
bufpages = 0

Is it worth pursuing these settings even though we have not implemented oracle on raw disk.
I'd thought the memory sitauation was OK because I wasn't seeing much swapping!
Kevin.

[A. Clay Stephenson · Accepted solution]

Hi Kevin:

The very first thing I would do in your case is to disable dynamic buffer cache and fix buffer cache at about 100MB by setting bufpages to 25600. In your case, the system is feeling both file buffer cache memory pressure and application memory pressure; the two are fighting and that in turn can lead to some of your CPU usage as well. By fixing buffer cache, it becomes much easier to observe the behavior and thus tune the other parameters.
If you go to raw/io or the Online Vxfs mount options convosync=direct,mincache=direct which effectively do the same thing, you can reduce bufpages even more. This may leave additional space for buffers in the SGA. 1GB is very marginal for large Oracle applications.

If tuning the memory usage (and/or adding memory) doesn't help much and you are still seeing very high CPU utilization then you are faced with two choices: 1) Faster CPU's (you can get 240MHz processors on the used market quite cheaply; 2)New L or N box); or 3) Tune the SQL code itself.

Whenever I am looking for 10X performance increases, I very seldom look at the hardware/OS but rather at the code. My rule of thumb is that I can tune the OS and replace equipment and if I am very good I can get you a 2X increase but if you need a 10X increase, you better look at the application code. Often, adding 1 crucial index will make a tremendous difference.


Regards, Clay

[Sridhar Bhaskarla]

Your sar stats are impressive. Looks like your computing power is runnig out. Your runq-sz above 2 with %runocc being 100 tells that your system needs more CPU power. Memory at 100% is due to your 50% dbc_max_pct. The max memory prescribed for dbc_max_pct is around 300MB. Anything more than that would be a waste of memory. In your case I foresee that decreasing this value may make %wio to go up a little bit. What kind of disk subsystem do you have?.

So, buying faster CPUs and adjusting your dbc_max_pct (<300MB) will improve the performance.

-Sri

[Bill Hassell]

And one last (but often overlooked) suggestion (which DBA s don't like):

Tune your Oracle code!!!

There's a lot of good and very bad SQL code out there, along with poorly designed values for the Oracle SGA and startup paramters. Enormous perfomance gains are possible by simply using the 'explain' command to trace SQL procedures to setect corrupt indexes and/or full index searches (rather than partial).

Oracle has a huge number of products and versions, any of which can be easily configured to bring a high powered server to it's knees. I say (somewhat as a joke) that all performance problems are due to applications, but in reality, if you don't run the applications, performance is fine. So in general, working on performance requires a cooperative effort. DBAs will say their code is fine, but what they often mean is that the code functions, rather than works efficiently.