| 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,
Unfortunately it's not just the special parameters. Some of the code errors have been corrected, so some costs have changed.
For example:
colA in ('A','B')
is rewritten as
colA = 'A' or colA = 'B'
Until Oracle 9(.2 possibly) the optimizer forgot that colA was the same column in both branches of the OR, so would work out the selectivity as
sel( colA = 'A') plus
sel( colA = 'B') minus
select( colA = 'A' and (completely different colA = 'B'))
so the selectivity of in-lists was always under-calculated.
In 9.2, the calculation is correct - which means the selectivity is higher - which pushes Oracle towards tablescans and merge or hash joins instead of nested loops.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
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.
 |
 |