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

  • From: Mladen Gogala <mgogala@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 1 Mar 2004 22:23:52 -0500

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2004