Re: Keep CBO plan stable(plan stability)

Hi, Lex,

   Real life application is sometimes more complicated, for some historical reasons. In this case, I did test with dynamic sampling, it used bitmap access for btree index plan, which seems very bad.   I believe dynamic sampling is not enabled by default for not analyzed tables, unless we specify the dynamic_sampling hint in SQL, right?

Thanks

On 10/13/05, Lex de Haan <lex.de.haan_at_naturaljoin.nl> wrote:
> let me answer with a counter question:
> why do you ask the CBO to optimize your SQL statements without statistics?
> that's like forcing someone without legs to run the marathon ...
> you might at least allow the CBO to perform dynamic sampling.
>
> to answer your question, in the absence of statistics, the CBO uses a mix
> of hard-coded built-in values, information from the segment header, and
> information from the row cache.
>
> cheers,
>
> Lex.
>
> > hi, all,
> > Recently we have a SQL changed the plan, without any init
> > parameter change, and table analyze.
> > Oracle version is 9.2.0.5, and optimizer_mode=rule. Tables are not
> > analyzed, but we have a no_expand hint, so actually SQL is using CBO.
> > We have 3 database running the identical application, and 1
> > database's plan is good, but two database have their pan
> > changed(previously it should be fine, as we see no timeout in
> > application, and no heavy SQL in statspack). Plan seems changes as
> > data volume changed.
> > I tried to understand why the plan is different on two different
> > database with exact setting (the data volume is a bit didfferent, say,
> > one db the table is 5gb, and another db the table is 2gb). I tried to
> > trace with 10053, and I found Oracle saw different "number of blocks"
> > for the two tables in two database. Table is not analyzed.
> > Table with correct plan:
> > TOTAL :: (NOT ANALYZED) CDN: 2000 NBLKS: 2530 AVG_ROW_LEN: 100
> > table with wrong plan:
> > TOTAL :: (NOT ANALYZED) CDN: 2000 NBLKS: 60 AVG_ROW_LEN: 100
> >
> > Both data for NBLKS are wrong.
> >
> > So I have two questions:
> > 1. How does CBO get this information?
> > 2. When we do not analyze tables, when will CBO change the plan?
> >
> > Thansk
> >
> >
> > --
> > Regards
> > Zhu Chao
> > www.cnoug.org
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
>

--
Regards
Zhu Chao
www.cnoug.org
--
http://www.freelists.org/webpage/oracle-l