Operating System - HP-UX
1748151 Members
3711 Online
108758 Solutions
New Discussion юеВ

Re: 9i Oracle Performance issues bad Cache Hits

 
SOLVED
Go to solution
Jeff Carlin
Frequent Advisor

Re: 9i Oracle Performance issues bad Cache Hits

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...
Where wisdom is called for, force is of little use. --Of course, a hammer does wonders for relieving stress.
Ariel Cary
Frequent Advisor

Re: 9i Oracle Performance issues bad Cache Hits

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
Regular Advisor

Re: 9i Oracle Performance issues bad Cache Hits

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
Time's fun when your having flys (ancient frog saying)
Jeff Carlin
Frequent Advisor

Re: 9i Oracle Performance issues bad Cache Hits

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
Where wisdom is called for, force is of little use. --Of course, a hammer does wonders for relieving stress.
Jeff Carlin
Frequent Advisor

Re: 9i Oracle Performance issues bad Cache Hits

Leon, the applications are rule based and not cost based. Would indexing issues still cause problems?
Where wisdom is called for, force is of little use. --Of course, a hammer does wonders for relieving stress.
TwoProc
Honored Contributor

Re: 9i Oracle Performance issues bad Cache Hits

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.
We are the people our parents warned us about --Jimmy Buffett
Brian Crabtree
Honored Contributor

Re: 9i Oracle Performance issues bad Cache Hits

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
Frequent Advisor

Re: 9i Oracle Performance issues bad Cache Hits

How would I check to see if the CBO is running?
Where wisdom is called for, force is of little use. --Of course, a hammer does wonders for relieving stress.
Patti Johnson
Respected Contributor

Re: 9i Oracle Performance issues bad Cache Hits

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
Frequent Advisor

Re: 9i Oracle Performance issues bad Cache Hits

It states CHOOSE. Does that mean 100% that CBO is running? Does choose mean that somewhere else the correct optimizer is chosen?
Where wisdom is called for, force is of little use. --Of course, a hammer does wonders for relieving stress.