| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Fuad Arshad
Sent: Wednesday, June 30, 2004 11:08 AM
To: oracle-l_at_freelists.org
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.
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |