RE: Keep CBO plan stable(plan stability)
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <zhuchao@xxxxxxxxx>
- Date: Thu, 13 Oct 2005 17:26:41 +0200
so it is the IN-list cost versus the estimated costs of a full table scan that
matter.
if the problem is caused by the change from NL to HASH join, a simple USE_NL
hint would be sufficient.
in 9i, you can also use the (undocumented) hints CARDINALITY and SELECTIVITY to
tell the optimizer
how many rows to expect and how selective the WHERE clause is, to overrule the
(apparently wrong) assumptions.
about your last question: the CBO is quite "intelligent" and therefore
unpredictable -- unless you specify a sufficient set of hints to force a
ceertain behavior. by the way, a stored outline is nothing else but a set of
hints :-)
kind regards,
Lex.
------------------------------------------------------------------
Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html
------------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of zhu chao
Sent: Thursday, October 13, 2005 17:01
To: lex.de.haan@xxxxxxxxxxxxxx
Cc: oracle list
Subject: Re: Keep CBO plan stable(plan stability)
I tried level 3. Oracle is 9.2.0.5 , and optimizer_feature_enable=9.2 SQL is not
complicated, just like:
select a.col1, col2,b.col3,b.col4 from user_info a, users b where a.id in
(:b1,:b2,...,b25) and a.id=b.id;
There is index on a.id, b.id. Correct plan used a.id index scan and then NL join
a,b.
While wrong plan used a.id index scan, and then b.FTS, and then Hash join.
The key problem I don't understand is, why oracle changed the plan , when there
is no statistics there. (from your comments, it should read from segment header
for the NLBK, others remaining unchanged). But seems what the trace file reflect
does not show the the correct number of blocks.
Also I want to know:
If we do keep have the statistics for CBO, and after some time's running we feel
satisfied with current execution plan/database performance, we don't analyze any
table again (to keep the plan stable, even with data distribution change, we
want to reuse current plan), will CBO keep the plan unchanged afterwords? of
course other things like optimizer related parameter does not change.
My manager want to use stored outline, while it is very troublesome to maintain
outline when there is a lot of database, I am trying to reach this goal with
minimum workload.
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583
SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD
Other related posts:
