Re: ordering tables changes cost/plan in 9.2.0.6

hi Jonathan,
  unfortunately, I've seen Wolfgang's observation as well.   Pretty unsettling, to say the list! -- not unlike your "cost" book which uncovers so many inconsistencies and bugs in the product.    

  Bugs -- this is a reason why time and time again I get an answer from my boss, to my request/wish to upgrade to 10g -- "so, when we upgrade, how many bugs is Oracle going to introduce, now?" -- let alone that upgrading db's with tons and tons of applications is not exactly an easy feat ;-)    

  I've seen this time and time again, with upgrades of Oracle, Sybase and MS SQL Server... where the majority of queries/modules are much faster, but you get a few queries/jobs here and there that are "much slower" and put a major doubt on the entire upgrade process/reason.... and then you're back to square one with "tech support" and the likes of joys. ;-)    

  thx for the confirmation though ;-)   Cos               

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:   

I guess it is notionally possible.

The optimizer does not work through all permutations of the table ordering; and - where there is no other determinant for order - the initial join order is dictated by the order of the tables in the FROM clause.

Consequently a change in the table order could mean that a plan was examined for one version of the query, but never reached for the other. You would probably need to do a careful check of all the join orders reported in the 10053 trace to see if this was the cause.

In passing, though, Wolfgang has discovered cases where the optimizer's evaluation of
t1.cola = t2.colb
is different from
t2.colb = t1.cola

i.e. simply reversing the order of operands in a single predicate results in a change in cost - from which I have constructed an example where exactly this swap results in a change in execution plan. So you may be seeing a bug.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "cosmin ioan" To: Sent: Monday, October 30, 2006 3:13 PM Subject: re: ordering tables changes cost/plan in 9.2.0.6

hello all,
I was under the impression that one would not have to worry about the table order in an OPTIMIZER_MODE=CHOOSE type query. I was recently presented with a query whereby the cost did not change that much, but the plan did change drastically by just changing the tables' order in the "FROM" clause.

What can I infer from this? Is this "phenomenon" pretty widespread? thx,
Cos

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.17/505 - Release Date: 27/10/2006

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 30 2006 - 09:50:48 CST