Performance Issue

[mehul_3]

Hi,
My server configuration is as follows:
-Dual processor with 750MHz.
-2GB RAM.
-Oracle9i database has a capacity of 34GB is installed on RAID5 configuration out of total capacity of 150GB with partition u01,u02,u03.
-Concurrent user is 45.
-Total SGA size is 440Mbyte(Out of 2GB)

As a part of daily performance monitoring, I have observed that CPU usage is very high with total %user and %sys (appr 90% at peak time) and RAM usage is only 42%.

It would be highly obliged if someone suggest to improve performance.

Million thanks in advance

Mehul

[Steven E. Protter · Accepted solution]

Two issues.

Raid 5 provides less peformance, especially on writes than Oracle recommends.

You need more memory to efficiently run Oracle.

Performance doc
http://www2.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-1335382922+1104416273473+28353475&searchCrit=allwords

attaching data collection scripts that will prove my contentions.

SEP

[Alzhy]

If you're not the DBA of the instance - get 'em involved and ask for specific performance related stats to point to what Oracle's complaint is all about -- that is IF you really have a serious perf problem. If you have -- has this problem manifested just recently? Or it got worse progressively with marked increase in users? I also will add - were there new processes added to the mix?

Yo mention that during peak usage - you only consume 42% memory out of the 2GB (looks like you're using DirectIO or RAW storage) - so increase your SGA to approach say 1.2-1.4 GB total. BTW, what's your SHMMAX ? It should be large enough so an entire SGA can fit in one segment.

Other things to look for: disk stats -- specifically "sar -d 5 10" and "sar 5 10" -- look for excessive wait IO's and unusally high access/service times on individual disks that compose your RAID5 volumes...


HTH.

[TwoProc]

What application are you running with Oracle?
Also, I agree with the previous responder that your SGA is not big enough. But in ways? Is your shared pool full? You may need to increase the shared pool. You certainly will benefit with more db_cache in 9i. Also, do you see latch waits? Oracle 9.2.0.6 is the latest and the greatest, but much was done to repair this issue with 9.2.0.5. *IF* you're running 9.2.0.5 there are a few gotchas out there in the init.ora file...

For instance - setting the following two parameters to anything OTHER than default in 9.2.0.5 will make your database run at 1/2 speed...

#pga_aggregate_target = 524288000
#workarea_size_policy = auto

The above two params were what I used in 9.2.0.4 - commenting them out of 9.2.0.5 brought me back to where the system was supposed to be.

What database version are you on?

Also, I think you should try to get rollback(undo), redo, temp, and archive logs off of raid 5 at a minimum.

What about the queries that are running? Are they well tuned? Pop up the top running queries (easiest to identify from Toad, or from Oracle Enterprise Manager).

You probably have (like most of us) lots of untuned code out there doing lots and lots of full table scans. Full table scans a) take up lots of I/O bandwidth, and take up lots of SGA memory - shoving good things out of your precious cache_buffers and shared pool.

Run statspack to get an idea of who and what are your worst offenders out there.

[Chris Vail]

I don't think your system is too busy. A CPU utilization of 90% merely means that the computing load is pretty well matched to the computing resources available.
Now, if the users complain that the system is too slow AND CPU utilization is at 90%, then there is need for further investigation. You'll need guidance from management as to whether you should be more concerned for peak or for average loads. If, on average, response time is good, then nothing more need be done, except perhaps scheduling large, compute-heavy tasks to a less-busy time of the day

However, if the users are complaining, and somebody has asked you to look into this, the first thing to do is to tune Oracle, not mess with the OS. Invariably, such issues turn out to be caused by poor database design, improper SQL queries, busted indexes and full-table scans. If Oracle is busted, then no amount of twiddling with the OS will improve anything very much for very long.
If Oracle has been tuned, all the indexes rebuilt (even if the DBA's say they're fine), all the SQL statements are as tight and as compact as can be imagine, and there are no full-table scans happening, THEN you can look at OS and hardware issues.

I would first look at raising the SGA (440MB isn't very big). If this doesn't work, the only thing left to do is purchase more (and/or faster) CPU's for your system--or purchase something faster altogether.

This is a situation where you need to have a competent, senior DBA whom you can trust to certify that the Oracle is as good as it possibly can be. THEN look into fixing the OS and/or hardware. You're describing a not-very-large system. The hardware and OS you have is probably a little bit of overkill.

Chris