Re: query slow in 9i, but not slow in 8i

On 03/01/2004 09:38:54 PM, zhu chao wrote:
> hi, guang:
> If all other SQL performs well in the new 9i instance, I would consider
> using dbms_stats to transfer the statistics from 8i to 9i and check if the
> explain plan goes better, if it still does not work, I would use HINT. Since
> using use_hash get the good plan, what do you means by saying: sort
> opration costs too high? Is it the actual SQL cost more time or just the
> explain cost get high? IF only the explain cost high, I won't care about it
> at all.
> Do you use workarea_size_policy=auto in 9i?
> If more SQLs get bad, I would consider using
> optimizer_features_enable=8.1.7 and other optimizer related parameter the
> same as 8i.

I don't think that this is such a great idea. First, I believe that changes of the timings (10msec -> microsec) can mess things up significantly. Second, setting optimizer features to 8.1.7 will turn a fancy 9.2 database into an ancient 8.1.7, I'm not really sure that it's the right thing to do. Whenever you change versions, you have few queries to tune. Put in few hints to get the desired plan or use outlines and it will be OK.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------