9i Oracle Performance issues bad Cache Hits

[Patti Johnson]

If you mean that all of your tables have a null value for last_analyzed - then statistics have never been generated. If you are using CBO and just did a database upgrade one of the steps should have been to generate new statistics for all user schemas.

Let the DBA's decide this one, but a valid recommendation would be to change the init.ora optmizer_mode parameter back to RULE, assuming that's what it was in the 8i database. Then gradually decide if CBO is a valid option. If the optimizer_mode is RULE ( at the database level) and you have valid statistics on all tables, then you can change individual queries to use the CBO by adding sql hints.

[TwoProc]

Jeff - Patti's follow up advice is dead on. You could start generating stats for all tables in all tablespaces, but, depending on how large the database is, this could 1/2 day, a day, or even days. If I were you I'd definitely have the DBA's set the init.ora file to RULE based. Make sure you bring them your proof (the selects that Patti told you to run) so that you can help get a quick resolution on this. But, I'm pretty sure now that your problem for the slowdown is known. There could be others things.

Let's put it this way, I remember waaay back in 1996(or 97) when the cost optimizer was turned on w/o stats ... and sounds like your day(s) of late. I didn't think of it earlier b/c it just didn't occur to me that any parts of the planet still ran databases not yet converted to CBO yet, having made the conversion quite some time back.

[Leon Allen]

Whether or not an index is used is not so related to cost or rule based optimization.

Just as a debug tool, it can be revealing to delete statistic on a suspect table, and in effect force rule based optimisation, just to force a different execution plan and see the effect (good or bad). Put the statistics back again later.

Seperately, check for full table scans, or use of inappropriate indexes, which will be slowing things down. Drop any indexes which might be getting used on the long operations. Run it again and see if performance improves. Check what index is being used now. Create you own specific indexes where appropriate.

Do this all with enterprise manager consol.

We effectively resolved peformance issues in this manner after changing from 817 to 920.

[Jean-Luc Oudart]

I would suggest you read (or ask your DBA to read) the Metalink doc Note:160089.1 "Why are my queries slow after upgrading my database?"

One aspect is that the Optimizer has changed and you have new oracle parameters too with default value that may affect the optimisation of some of the queries.
It is strongly advised to re-run the statistics gathering as they should be issued with same Oracle version you run the database.

Regarding which optimizer is used cf. Metalink doc Note:66484.1 "Which Optimizer is Being Used ?"

Regards
Jean-Luc

[TwoProc]

Well Jeff,

Did the DBA's try turning off the CBO, and did it fix the problem? Just wondering how it turned out thus far.

[Jeff Carlin]

John, I forwarded the evidence and the suggestion to specify RULE in the init file. I haven't heard anything and probably wont until Tuesday. Hopefully this is the problem and we can get things resolved.

Thanks everyone!

[TwoProc]

A RPW whispered in my ear and told me that you've gotten a lot of help - but haven't assigned points. Could you give a fella a few points who is down on his luck?

:-)

(asking pardons PR)

[Jeff Carlin]

The DBA's are now looking into the DB for the cause. Seems we've collectively convinced them that the system is probably not to blame and the buffer cache is a symptom rather than the problem. Thanks for all the help - I learned a lot on this thread!