Re: Starne behaviour of optimizer within in 9i vs 8i

cha_at_christian-hartmann.de (Christian Hartmann) wrote in message news:<7c275508.0310270248.76f4f06f_at_posting.google.com>...
> Hi there,
>
> we are facing strange behaviours of the optimizer after migration from
> Oracle 8.1.7 to Oracle 9.2.0.4

Christian,

You're not alone. The CBO in 9.2 is a different beast. I hope that this is your test database, and not your production one.

Can you provide info as to how statistics are being gathered (dbms_stats.gather_schema_stats w/cascade?), and what the settings of the folloing parameters:

db_file_multiblock_read_count
compatible

I would first consider increasing the value of:

optimizer_index_caching

 to 85, flush the shared pool and try again.

Try reducing the db_file_multiblock_read_count for your session, flush the shared pool and re-run the query. If you have an 8k block size try dbfmrc=8.

optimizer_features_enable=8.1.7 would most likely "fix" this query but would not fix the problem. it must be set instance-wide.

you can dump a 10053 trace to see what decisions the cost based optimizer is using. search for a paper written by Wolfgang Breitling titled:

"A Look under the Hood of CBO - the 10053 Event".

dumping the 10053 trace file will be a good step toward having enough info to post an iTAR. it lists all of the parameters used by the optimizer, statistics info, base table access costs, index access costs, etc.

hth.

Pd Received on Mon Oct 27 2003 - 08:26:06 CST