Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: rm RULE based optimizer != GOOD IDEA
One other thing that I note is missing is no one seems to have
suggested using the newer syntax in reformatting the query. I've
recently been presented with a query that was taking too long (in excess
of 1 minute to return one row). By changing from the usual method of
constructing a query into the newer join syntax the performance dropped
into the sub-second range while continuing to use CBO. The execution
plan did change from it's original to what I would have expected from
the RBO.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Terry Sutton
Sent: Thursday, April 21, 2005 7:33 PM
To: oracle-l_at_freelists.org
Subject: Re: rm RULE based optimizer !=3D GOOD IDEA
You haven't shown the execution plans which were used for the two
"versions"
(not the explain plans, the actual execution plans obtained from
v$sqlplan,
assuming you're using 9i), but I suspect there are hints other than RULE
which would cause the query to use the indexes/nested loops/etc. that
the
RULE hint does. A big problem with the RULE hint is that it *doesn't*
guarantee the same execution plan in the future as it currently uses.
If
you add an index to the table, a rule-based execution plan could change
completely, not necessarily for the better. If you had to use a hint,
it
would be better to use hint(s) which would cause the query to follow the
same execution plan even if another index is added. Even better would
be to
give the optimizer the information necessary to get it to pick the
better
execution plan on its own. This could consist of things like o_i_c &
o_i_c_a
settings, cardinality information, accurate table stats, etc.
--Terry
Just got finished working on a little SQL perf problem. The query was something like...
select a.col, b.col, (select count(distinct foo) from tbl3 where
batch_id=3D3Db.batch_id) errors
from tbl1 a,
tbl2 b
where a.batch_id=3D3Db.batch_id and a.status not in ('X','Z')=3D20 and b.trans_type=3D3D'Q';
Now that ran really slow using the COST optimizer...
but when we added the RULE hint, it ran about much faster (50 sec to < 1 sec).
Of course we can't do that so we spent a lot of time writing the query in different ways, moved the select count(*) into an inline view and joined to that, query dropped from 50 seconds to about 20 seconds but did not approach the sub-second performance of the RULE method.
The explain plan was not helpful because it looked the same as the plan generated using RULE but the difference was in the way the select count() was being handled, and the plan wasn't showing us that.
So what is the gist of this post? Well, I am sure if we continued to
look at this that we might come up with a solution but why waste all
that time when /*+RULE */ does the trick? What would be the point of
Oracle removing something that time and time again demonstrates it's
usefulness like this? It essentially (in this particular case) is a
GO_FASTER hint and it worked. So maybe they will get rid of the ability
to set the optimizer mode to RULE but this will hopefully always be
there at the statement level.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 22 2005 - 08:49:31 CDT
![]() |
![]() |