Operating System - HP-UX
1748180 Members
3997 Online
108759 Solutions
New Discussion юеВ

Re: 9i Oracle Performance issues bad Cache Hits

 
SOLVED
Go to solution
Jeff Carlin
Frequent Advisor

9i Oracle Performance issues bad Cache Hits

We are having performance issues with Oracle 9i that seem to have happen after the DB upgrade (from 8) or applying the standard December 2004 Support Plus patch bundle.

The test of both the upgrade and patches came out fine on the QA systems. On production it seems to have ruptured something. The applications are all running 50% to 400% longer. Needless to say, my life is fun right now since this system handles all of our import retail here at Sears (Kmart).

I have been working with HP on the issues for the last few weeks and they suggested reducing the buffer cache in the server from 2gb to 400mb because the wcache was at <54. The rcache has always been between 70-80 on average.

After completing the change (buffpages=102400) my apps are now running insanely longer. Most are not done from the weekend so I have no idea if they are now 400% or 1000% longer. Cache hits are 36/59 (rcache/wcache). My manager is now stacking empty boxes by my cube ;-)

The SGA in Oracle is defined at 1.3gb and they (DBA) are seeing consistent 91% cache hits. The filesystems for Oracle are NOT raw. So I am assuming that Oracle is buffering and the system is also buffering before there is anything physical happens to disk. Is there any guidance and help available on how large I should set the system buffer cache? The reboot window is only once a week, but they are opening up a reboot window for me today after the weekend jobs finish. Should I just set the buffer cache back to 2gb or is there a better value? Should we look at the size of the SGA?

Oh great, now the cube vultures are showing upтАж
Where wisdom is called for, force is of little use. --Of course, a hammer does wonders for relieving stress.
32 REPLIES 32
Tim Nelson
Honored Contributor

Re: 9i Oracle Performance issues bad Cache Hits

A general rule of thumb is to let the database take care of the caching.. Reduce LVM cache and use that memory to increase SGA. But, HP and others recommend to leave some FS cache for other processes. Typically I have dbc_max_pct set at 5% and dbc_min_pct set at 3% ( this depends on the amount of memory, hard numbers would be 400-700MB of cache max). With these settings we have consistantly hit 94%+ on read hits using 400MB of cache.
Set bufpages to 0 and use dynamic cache for this.
How about comparing some other kernel settings between your QA and production. i.e. shared mem, semaphore, etc. There was also a great document out here somewhere that discussed the shared mememory windowing in Oracle.
Also take a look at the processes in glance while running, look at wait states, this may give you an idea. If not io then your problem is somewhere other than the fs cache.



RAC_1
Honored Contributor

Re: 9i Oracle Performance issues bad Cache Hits

The buffer cache that HP suggested, I would agree with that. But you are using static
buffer cahe and as result if there is need to buffer more, you just can not do that. You may want to set dynamic buffer cahe (in the range of 400-800mb) and check. Set dbc_max_pct and dbc_min_pct.

But before you do that, if you look at system as a whole, what kind of bottleneck do you see?? CPU-priority run que, global run queue,
MEM-glance -m, vmstat, swapinfo, DISK-is oracle stripped?? Orcle recommands SAME (Stripe all, mirror everything), are disk i/os OK??

Run oracle statspack and check what is happenening?? How are the read and write cahe rates now??
There is no substitute to HARDWORK
Jean-Luc Oudart
Honored Contributor

Re: 9i Oracle Performance issues bad Cache Hits

Hi Jeff,

I would have 1st a few questions :
- OS version
- Database size
- server memory size
- is the storage SAN attached?

Also do you use the new Oracle9i features to manage the PGA and SGA ?
(Dynamic Buffer Cache Advisory feature)
could you run (or get it from the DBA) ?
=> select * from V$PGA_TARGET_ADVICE

Also the cache hit does it not the only one metric.
Would you have a statspack report available ?
OS kmtune report ?

Regards
Jean-Luc

fiat lux
Jean-Luc Oudart
Honored Contributor

Re: 9i Oracle Performance issues bad Cache Hits

Hi again,

find attached a document from Oracle Metalink : Note:262946.1 "Performance Issues After Increasing Workload"

Regards
Jean-Luc
fiat lux
TwoProc
Honored Contributor

Re: 9i Oracle Performance issues bad Cache Hits

Jeff, I've seen it when we upgraded to 9.2.0.5. from 9.2.0.4. Try turning off the automatic allocation of the PGA, and go back to the manual settings for managing sort_area, etc. This difference was something that looked amazingly like what you're describing, and turning off this feature put us right back in the game after restarting the database. Your performance, once normalized, should run right about what you had with 8i (no, you probably won't run noticibly faster as many in the Oracle areas will proclaim - in fact our testing told us we took a 3% performance hit, your results will vary).

We are the people our parents warned us about --Jimmy Buffett
Steven E. Protter
Exalted Contributor
Solution

Re: 9i Oracle Performance issues bad Cache Hits

I personally reccommend from prior experience 5% and 7% for the max_dbc figures.

There are two possible areas, SGA and oracle tuning, which you mention. I would install the oracle stats pack if not installed and use it to optimize the database.

Here is an article on the OS aspects, written by one of HP's oracle tuning experts.


http://www1.itrc.hp.com/service/cki/docDisplay.do?docLocale=en_US&admit=-682735245+1125328248032+28353475&docId=200000077186712
Document ID: UPERFKBAN00000726

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Jeff Carlin
Frequent Advisor

Re: 9i Oracle Performance issues bad Cache Hits

Here is the server info:

K580
6 x 240mhz
7gb memory
XP512 SAN 2 fiber cards using SecurePath to aggregate over both.
DB is about 120gb in size.

Where wisdom is called for, force is of little use. --Of course, a hammer does wonders for relieving stress.
Michael T. Boduch
Occasional Advisor

Re: 9i Oracle Performance issues bad Cache Hits

Jeff,

As a DBA that recently upgraded from 8.1.7.4 to 9.2.0.6 and saw his system performance go to hell overnight I'd suggest the following (based on my experience:)

1) Assume that since the DB changed but the OS didn't that the solution lies in the DB. Therefore, don't go changing a bunch of OS parms. Leave that constant.
2) We found that while all tables and indexes appeared to have usable statistics AND EXPLAIN PLANS appeared unchanged, in some cases the perfomance was VERY different. Once we re-computed Oracle statistics on all tables and indexes a large number of these performance problems went away. So that's my best recommendation: Have your DBAs re-compute all statistics ASAP.
3) Oracle's CBO (Cost-Based Optimizer) changes--supposedly for the better--with each release. Once you have fresh statistics there may actually be SQL with changed execution plans that will need to be re-tuned. Again, this is a job for your DBA.

Again, I just went through this (cube vultures and all.) Good luck, hope this helps.

Re: 9i Oracle Performance issues bad Cache Hits

Jeff,

Oracle performance tools like Statspack and particularly SQL trace will allow you know exactly what the problems is and what "parameters" (Oracle, OS, hardware system architecture: more CPU, disks, etc.) to change. This is to warn against guesstimates and changing this or that parameter and hope the system will run better.
Double caching (OS + Oracle) indeed is to be avoided, most certainly so if raw devices are used. With file systems it actually is not that bad as Oracle caches blocks obtained by a full table scan (FTS) only till the next FTS comes along. If the first table needs to be read again you will definitely benefit if it is still cached in the file cache. That said, I agree that using the dynamic file cache with the dbc_ parameters (min=2 max=5, or something of that nature) will suit most systems better that the default settings of then using a fixed cache. Is the system swapping a lot?
Tuning Oracle with BCHR (buffer cache hit ratio) is not very fashionable anymore these days: you might want to consult "Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok" (attached).
You might find the following docs helpful also:
On file cache:
docs.hp.com/en/5580/Misconfigured_Resources.pdf

HP-UX Performance Cookbook
h21007.www2.hp.com/dspp/files/unprotected/ devresource/Docs/TechPapers/UXPerfCookBook.pdf

A paper on the combination HP and SAN
Tuning I/O for Better Performance - Focusing on HP and EMC (http://www.oraperf.com/whitepapers.html)