9i Oracle Performance issues bad Cache Hits

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

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