RE: Issues with queries after upgrade,

Did you update your statistics after the upgrade?  Some of the code changes
are bound to affect how the statistics are generated and used so I think
this would be a first step.

There are at least a dozen underbar parameter changes between 8.1.7 and 9.2
that directly affect the optimizer.  If you notice that the SQL statements
having a problem have similar features: sub-queries, nested sub-queries,
nested views, etc.... then you may need to reset one of these parameters
back to the 8.1.7 value, usually false.  I think Jonathan Lewis posted many
of these just this week under the thread: db block size.

But I know Oracle support has had a couple of sites set one or two of these
parameters back for specific SQL performance related problems.

I would not just set all of them back.  I would think tuning for the current
version of the CBO is the best choice going forward.

IMHO -- Mark D Powell --



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Fuad Arshad
Sent: Wednesday, June 30, 2004 11:08 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Issues with queries after upgrade,


We've upgraded to 9.2.0.4 on one of our high volume databases. As usual
everything was fine in dev and test but high volume queries have started to
break   .
one of the problems that we founf the the unnest_subq turned off.
finally to try to fix things i set the optimizer_features_enable=8.1.7
but  some of our explain plans have changed wherein the query was using a
sort is not now using concatenation.
any ideas thoughts on why this is happening.
strangely enough if  i run some of the queires using optimizer_goal=rule
they do run very fast. and have a better explain plan.
 

----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: