Re: Keep CBO plan stable(plan stability)

Thanks for your reply.
My reply in line:

On 10/13/05, oracle-l_at_timothyhopkins.net <oracle-l_at_timothyhopkins.net> wrote:
> 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?

no. they are in the same database.
>
> 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;
two table join. Both are not analyzed.
> (2) this unanalyzed table is joined to another table or appears in a
> subquery or non-mergeable view;

same as point 1. two table join. both not analyze.
> (3) this unanalyzed table has no indexes;
both table have too many indexes, unfortunately.
> (4) this unanalyzed table has more blocks than the number of blocks that
> would be used for dynamic sampling of this table.
yes. I think so. I think dynamic sampling will query 32 or 24 block by default? But dynamic sampling seems not used in my case. As from the 10053 trace file, I don't see comments like:
** Generated dynamic sampling query:

    query text :
SELECT /*+ ALL_ROWS IGNORE_WHERE_CLAUSE */ NVL(SUM(C1),0), NVL(SUM(C2),0), COUNT(DISTINCT C3) FROM (SELECT /*+ NOPARALLEL("UI") */ 1 AS C1, 1 AS C2, "UI"."ID" AS C3 FROM " USER_INFO" SAMPLE BLOCK (0.055029) "UI") SAMPLESUB
If I hint the SQL with dynamic_sampling(3), then in the trace file I see the above rows.
> 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_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
> >
>
>
>

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