Re: Keep CBO plan stable(plan stability)

That's what you get using CBO without statistics. However, what I do not understand is how, without any statistics ( or did you set numrows=>2000 ?? what does dba_tables have for num_rows? ), the cbo gets exactly the same number for CDN.
Normally, in the absence of statistics, the optimizer will get the actual number of blocks from the table's segment header and, using a default avg_row_len of 100 and the table's block size, calculates the cardinality to use.
Therefore, if you are using the cbo with tables with no statistics, the generated access plan can change just from table growth alone since that affects NBLKS which subsequently affects cardinality - and also predicate selectivity - and that drives everything.

As I said, I have never seen your case, where - without statistics - cdn is fixed at such a nice round number (looks very suspicious to me), avg_row_len is 100 (default) and nblks is different. Without statistics, the four values - cdn, nblks, avg_row_len, and block_size are linked, so if three are the same, the fourth should be the same as well.

zhu chao wrote:
> 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

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l