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

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


----- Original Message ----- 
From: "Powell, Mark D" <mark.powell@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, June 30, 2004 4:30 PM
Subject: 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.


----------------------------------------------------------------
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: