Re: Keep CBO plan stable(plan stability)
- From: oracle-l@xxxxxxxxxxxxxxxxxx
- To: zhuchao@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 13 Oct 2005 10:44:09 -0400 (EDT)
Hi,
The numbers shown for cardinality and average row length (2000 and 100,
respectively) look suspiciously like the values the CBO uses for
unanalyzed remote tables. You don't happen to be querying over a database
link do you?
In general, the conditions which trigger dynamic sampling with the default
9i init.ora setting are:
Level 1:
Sample all tables that have not been analyzed if the following criteria
are met:
(1) there is at least 1 unanalyzed table in the query;
(2) this unanalyzed table is joined to another table or appears in a
subquery or non-mergeable view;
(3) this unanalyzed table has no indexes;
(4) this unanalyzed table has more blocks than the number of blocks that
would be used for dynamic sampling of this table.
Would all of the above apply in your situation?
Cheers,
Tim
> 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@xxxxxxxxxxxxxx> 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
>> > --
>> > //www.freelists.org/webpage/oracle-l
>> >
>>
>>
>>
>
>
> --
> Regards
> Zhu Chao
> www.cnoug.org
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
