topic Re: 9i Oracle Performance issues bad Cache Hits in Operating System - HP-UX

9i Oracle Performance issues bad Cache Hits

Jeff Carlin — Mon, 29 Aug 2005 09:39:58 GMT

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…

Re: 9i Oracle Performance issues bad Cache Hits

Tim Nelson — Mon, 29 Aug 2005 09:53:29 GMT

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.

Re: 9i Oracle Performance issues bad Cache Hits

RAC_1 — Mon, 29 Aug 2005 09:56:16 GMT

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??

Re: 9i Oracle Performance issues bad Cache Hits

Jean-Luc Oudart — Mon, 29 Aug 2005 10:00:43 GMT

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

Re: 9i Oracle Performance issues bad Cache Hits

Jean-Luc Oudart — Mon, 29 Aug 2005 10:05:15 GMT

Hi again,

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

Regards
Jean-Luc

Re: 9i Oracle Performance issues bad Cache Hits

TwoProc — Mon, 29 Aug 2005 10:08:48 GMT

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).

Re: 9i Oracle Performance issues bad Cache Hits

Steven E. Protter — Mon, 29 Aug 2005 10:11:19 GMT

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

Re: 9i Oracle Performance issues bad Cache Hits

Jeff Carlin — Mon, 29 Aug 2005 10:21:12 GMT

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.

Re: 9i Oracle Performance issues bad Cache Hits

Michael T. Boduch — Tue, 30 Aug 2005 07:45:53 GMT

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

Denys van Kempen — Tue, 30 Aug 2005 07:53:02 GMT

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)

Re: 9i Oracle Performance issues bad Cache Hits

Jeff Carlin — Tue, 30 Aug 2005 10:22:45 GMT

I've had to tune up the system buffer cache to 1.8gb to see any acceptable application performance.

The application group stated that they are not using CBO, but rather rules.

The DBA's stated the all the tables in the DB have NOT been reorged in 2 years!

Starting to sound more like a DB issue...

Re: 9i Oracle Performance issues bad Cache Hits

Ariel Cary — Tue, 30 Aug 2005 10:29:59 GMT

Jeff,

I would like to hear the answers as for the server features to have a clearer idea of what may be happening. That is, SGA size, RAM, OS version (IA-64, PA-RISC), Oracle version (9.2.0.6?).
Also, be advised that Oracle9i processes consume much more memory then in earlier releases. I have seen on IA-64 user processes taking between 12M-20M, and even some background processes 90M. So, this could really be the cause of poor performance based on what you said about your wcache. I'd suggest monitoring the available RAM at peak hour.

Regards,

ARC

Re: 9i Oracle Performance issues bad Cache Hits

Leon Allen — Tue, 30 Aug 2005 17:16:46 GMT

One problem I (and others, based on cases in metalink) had after going from 817 to 920 was the application started using wrong indexes, resulting in full table scans where index table scans use to take place. There were no changes to indexes - or anything. The system just stopped using the intended indexes.

We debugged it by doing things like removing statistic; removing indexes; and recreating indexes (all can usually be done on the fly).

The effect of a full table scan (as opposed to an index scan) on very large tables (with a 120Gb databse - I bet you've got a few) can be absolutely disasterous (in terms of performance)

Identify the full table scans, and get rid of the old index, and make the system use another one. That's my tip.

Good luck!

Leon

Re: 9i Oracle Performance issues bad Cache Hits

Jeff Carlin — Wed, 31 Aug 2005 09:23:29 GMT

We're running HPUX 11i (11.11)

I want to thank everyone for the responses so far. I've forwarded them all to the DBA's and I think they are starting to take some ownership of the problem. I'll keep everyone updated and dole out the points soon.

JC

Re: 9i Oracle Performance issues bad Cache Hits

Jeff Carlin — Wed, 31 Aug 2005 11:04:07 GMT

Leon, the applications are rule based and not cost based. Would indexing issues still cause problems?

Re: 9i Oracle Performance issues bad Cache Hits

TwoProc — Wed, 31 Aug 2005 11:38:28 GMT

I believe Leon's remark is within the cost optimizer umbrella. Your DBA's (and application) should consider using the cost optimizer to increase performance. There is some work to turning it on and making it work well for you, so by all means don't "just turn it on." But, keep in mind that by default the Cost Optimizer is turned on in 9i - unless you go in and turn if off either in the init.ora file or at connect time in each session, or via hints in sqlplus packages. In fact, this one thing is a possible cause of the tremendous slowdown you're seeing - that is, inadvertently having the cost optimizer running and not having solid stats to support it.

Re: 9i Oracle Performance issues bad Cache Hits

Brian Crabtree — Wed, 31 Aug 2005 12:16:26 GMT

Jeff,

I would recommend the following mount options for your filesystems as well.

logfiles:
log,nodatainlog,mincache=direct,convosync=direct

datafiles:
delaylog,nodatainlog,mincache=direct,convosync=direct

If you just have mixed filesystems (with log and datafiles together), just use the one for datafiles. This should bypass the buffer cache for the UX side, which might help.

Brian

Re: 9i Oracle Performance issues bad Cache Hits

Jeff Carlin — Wed, 31 Aug 2005 12:26:35 GMT

How would I check to see if the CBO is running?

Re: 9i Oracle Performance issues bad Cache Hits

Patti Johnson — Wed, 31 Aug 2005 13:27:00 GMT

You would need to connect to the database and issue either of these commands.

SQL> select value from v$parameter where name = 'optimizer_mode';

or

SQL> show parameter optimizer_mode

A value of CHOOSE means you are running the CBO. If you are using RBO it will be RULE.

Re: 9i Oracle Performance issues bad Cache Hits

Jeff Carlin — Wed, 31 Aug 2005 13:57:02 GMT

It states CHOOSE. Does that mean 100% that CBO is running? Does choose mean that somewhere else the correct optimizer is chosen?