Re: ordering tables changes cost/plan in 9.2.0.6
- From: cosmin ioan <cosmini@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Mon, 30 Oct 2006 07:50:48 -0800 (PST)
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@xxxxxxxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
