9i Oracle Performance issues bad Cache Hits

[Jeff Carlin]

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…

[Tim Nelson]

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]

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

[Jean-Luc Oudart]

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

[Jean-Luc Oudart]

Hi again,

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

Regards
Jean-Luc

[TwoProc]

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

[Steven E. Protter · Accepted solution]

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

[Jeff Carlin]

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.

[Michael T. Boduch]

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.

[Denys van Kempen]

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)

[Jeff Carlin]

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

[Ariel Cary]

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

[Leon Allen]

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

[Jeff Carlin]

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

[Jeff Carlin]

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

[TwoProc]

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.

[Brian Crabtree]

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

[Jeff Carlin]

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

[Patti Johnson]

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.

[Jeff Carlin]

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

[Patti Johnson]

That means it is the default optimizer mode for the database.

At connect time you can alter your session to rule, or you could put a hint is every statement that you run stating to use the RULE optimizer.

CHOOSE is the default for 9i, so to use RULE you must change the init.ora parameter and restart the database.

If they tell you they are changing the optimizer at the session level you can verify with this statement.

select sql_text, OPTIMIZER_MODE from v$sql;

It will return one row for each session in the database giving you the optimizer_mode that session is running with. Unless they all come back RULE - you know you are using CBO.

As others have stated, if you use the CBO without valid statistics, then that could explain a lot of your performance issues.

[Jeff Carlin]

Many are CHOOSE and many are RULE.

[Jeff Carlin]

So, if the init specifies CHOOSE and the session specifies CHOOSE, it trys CBO and that would be a performance hit? I would like to take this to the DBA's without looking like a fool... ;-)

[Patti Johnson]

The CBO is not necessarily a 'bad' thing for performance, but it does need up to date object (tables, index) level statistics to make the best choices. If your dba's stated that they are using RULE, then that may be what the application was designed to use, if so they may not be running the jobs necessary to generate valid statitics for all objects. You can check this by running

select last_analyzed from dba_tables;
select last_analyzed from dba_indexes;

If a query accesses multiple tables and any one table has statistics then it will try and use the CBO.

A lot of queries have problems because under the RBO, if an index exists, then it is used. With CBO Oracle tries to determine if the index is selective enough or if a full table scan will be faster. If your db_file_multiblock_read_count ( another init.ora parameter) is set high (16 or better) then the CBO may think full-table scans are faster than index reads.

These are all things that the DBA should be looking at -

[Jeff Carlin]

Nothing showed up on that.