FAQ
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

Search Discussions

  • Lex de Haan at Oct 13, 2005 at 8:58 am
    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
    --
    http://www.freelists.org/webpage/oracle-l
  • Zhu chao at Oct 13, 2005 at 9:36 am
    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 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
  • Oracle-l_at_timothyhopkins.net at Oct 13, 2005 at 9:46 am
    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 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
    --
    http://www.freelists.org/webpage/oracle-l
  • Zhu chao at Oct 13, 2005 at 9:59 am
    Thanks for your reply.
    My reply in line:
    On 10/13/05, 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 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
  • Wolfgang Breitling at Oct 13, 2005 at 9:07 am
    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
  • Karen Morton at Oct 13, 2005 at 9:12 am
    Love that analogy Lex!

    To add another bit, you said you have your instance set to RULE and don't analyze your tables but use the NO_EXPAND hint which, as you noted, causes the CBO to kick in. My question is why would you put a hint in that causes the CBO to kick in at all when you seem to want rule-based optimization? What happens without the hint? I suspect you're trying to avoid some query transformation that is being done. But, as Lex said, you're forcing the CBO to try and do it's job with a severe handicap and unless you give it statistics to help it, your results will be unpredictable.

    Also, your last question was "When we do not analyze tables, when will CBO change the plan?". The plan could change every time it is hard parsed. How often that happens depends on how often the current plan gets aged/flushed out.




    Karen Morton
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com

    -----Original Message-----
    From: Lex de Haan
    Sent: Thursday, October 13, 2005 9:55 AM
    To: zhuchao_at_gmail.com
    Cc: oracle list
    Subject: Re: Keep CBO plan stable(plan stability)

    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
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Zhu chao at Oct 13, 2005 at 9:33 am
    Hi,
    Thanks Wolfang/Karen/Lex for your answers!
    Let me explain with more detail.
    The major application is still using Rule Based Optimizer, as you
    see our optimizer_mode is rule.

    For that specific SQL, As RBO inable to use in-list efficiently, we
    added the no_expand hint. it works fine in oracle 8.1.7 for several
    years. But after we deploy the application in oracle 9.2, it seems
    have some trouble. I am suggesting we analyze the underlying two
    tables, but as this is the key tables of our application,
    Management/Other DBA are very cautious to this change. Without the
    hint, oracle use concat, which make big use of shared pool. We don't
    like it.

    --The plan could change every time it is hard parsed.
    Could you please explain more about this? What kind of factor can
    lead to the plan change? The table size(from segment header, the NBLK?
    anything else?)

    Wolfgang, thanks for your clear explaination.
    --the optimizer will get the actual number of blocks from the table's
    segment header
    But in our case, it seems oracle is not getting the NBLK correctly
    from the segment header. The actual table size is like :
    If you are interested, I can send you the two trace files from different hosts.
    SQL> exec show_space('USER_INFO')

    Free Blocks.............................62
    Total Blocks............................128000
    Total Bytes.............................1048576000
    Unused Blocks...........................41535
    Unused Bytes............................340254720
    Last Used Ext FileId....................25
    Last Used Ext BlockId...................12809
    Last Used Block.........................9665

    SQL> 1* select
    table_name,num_rows,partitioned,blocks,empty_blocks,last_analyzed from
    dba_tables where table_name='USER_INFO'
    SQL> /

    TABLE_NAME NUM_ROWS PAR BLOCKS EMPTY_BLOCKS LAST_ANALYZED

    -------------------- ---------- --- ---------- ------------ ---------------
    USER_INFO NO

    Another question about CBO:
    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.

    Thanks!
    On 10/13/05, Karen Morton wrote:
    Love that analogy Lex!

    To add another bit, you said you have your instance set to RULE and don't analyze your tables but use the NO_EXPAND hint which, as you noted, causes the CBO to kick in. My question is why would you put a hint in that causes the CBO to kick in at all when you seem to want rule-based optimization? What happens without the hint? I suspect you're trying to avoid some query transformation that is being done. But, as Lex said, you're forcing the CBO to try and do it's job with a severe handicap and unless you give it statistics to help it, your results will be unpredictable.

    Also, your last question was "When we do not analyze tables, when will CBO change the plan?". The plan could change every time it is hard parsed. How often that happens depends on how often the current plan gets aged/flushed out.



    Karen Morton
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com







    -----Original Message-----
    From: Lex de Haan
    Sent: Thursday, October 13, 2005 9:55 AM
    To: zhuchao_at_gmail.com
    Cc: oracle list
    Subject: Re: Keep CBO plan stable(plan stability)

    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

    --
    http://www.freelists.org/webpage/oracle-l
    --
    Regards
    Zhu Chao
    www.cnoug.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Oct 13, 2005 at 9:43 am
    you might want to throw in a DYNAMIC_SAMPLING hint, at level 2, and see
    what happens with the execution plans and the parsing time.
    at least, you will not need to gather any statistics...

    by the way, note that query transformations take place before the
    optimizer is called -- that is, query transformations happen regardless
    whether you run RBO or CBO. another possible reason why RBO plans change.
    Another well-known reason for different RBO plans is the order in which
    certain indexes are created.

    cheers,

    Lex.
    Hi,
    Thanks Wolfang/Karen/Lex for your answers!
    Let me explain with more detail.
    The major application is still using Rule Based Optimizer, as you
    see our optimizer_mode is rule.
    For that specific SQL, As RBO inable to use in-list efficiently, we
    added the no_expand hint. it works fine in oracle 8.1.7 for several
    years. But after we deploy the application in oracle 9.2, it seems
    have some trouble. I am suggesting we analyze the underlying two
    tables, but as this is the key tables of our application,
    Management/Other DBA are very cautious to this change. Without the
    hint, oracle use concat, which make big use of shared pool. We don't
    like it.

    --The plan could change every time it is hard parsed.
    Could you please explain more about this? What kind of factor can
    lead to the plan change? The table size(from segment header, the NBLK?
    anything else?)
    --
    http://www.freelists.org/webpage/oracle-l
  • Zhu chao at Oct 13, 2005 at 10:03 am
    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.

    Thanks
    On 10/13/05, Lex de Haan wrote:
    depends on the version.

    in 9i, the default is dynamic sampling at level 1; in 10g, it is level 2
    (because in 10g the RBO is obsoleted.) which level did you try?

    how does the actual statement look like? do you have a complicated where
    clause? just trying to guess why the CBO would choose for bitmap
    conversion. sometimes that is quite smart, sometimes it isn't ...

    cheers,

    Lex.
    --
    Regards
    Zhu Chao
    www.cnoug.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Oracle-l_at_timothyhopkins.net at Oct 13, 2005 at 10:27 am
    Hi Zhu,

    If you have the time, could we please just check everything is as expected
    with the segment header for that table:

    Grab the file and block id from the following query:

    SELECT header_file,

    header_block
    FROM
    dba_segments
    WHERE segment_name = 'USER_INFO';

    And substitute the returned values into the following query:

    ALTER SYSTEM DUMP DATAFILE &header_file BLOCK &header_block;

    This should produce a file in your user dump directory, which contains a
    line like the following:

    #blocks below: 19

    It's this value the CBO uses for the NBLK stat. We just need to check that
    it's correctly recorded as a large number for your table.

    In relation to your other question; no you can't guarantee plan stability
    without stored outlines. As discussed, the segment header will change over
    time and if someone collects system statistics these could also alter your
    execution plan.

    Cheers,
    Tim
  • Zhu chao at Oct 14, 2005 at 4:24 am
    Hi, Tim,
    The segment header dump is like: (for the table with wrong plan)
    *** 2005-10-14 02:16:50.796
    *** SESSION ID:(4612.10519) 2005-10-14 02:16:50.794

    Start dump data blocks tsn: 12 file#: 30 minblk 38409 maxblk 38409
    buffer tsn: 12 rdba: 0x07809609 (30/38409)
    scn: 0x0315.8fb11a2a seq: 0x01 flg: 0x00 tail: 0x1a2a1001
    frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
    Extent Control Header

    Extent Header:: spare1: 0 spare2: 0 #extents: 10 #blocks: 127999
    last map 0x00000000 #maps: 0 offset: 4128
    Highwater:: 0x06c06003 ext#: 5 blk#: 11770 ext size: 12800

    #blocks in seg. hdr's freelists: 66
    #blocks below: 75769
    mapblk 0x00000000 offset: 5

    Unlocked
    Map Header:: next 0x00000000 #extents: 10 obj#: 6749 flag: 0x40000000

    Extent Map

    As for the plan stability, while running CBO, ifI do have statistics,
    Is there any other case, that CBO is still able to change the plan?
    Can you give me a test case?

    Thanks very much.
    On 10/13/05, oracle-l_at_timothyhopkins.net wrote:
    Hi Zhu,

    If you have the time, could we please just check everything is as expected
    with the segment header for that table:

    Grab the file and block id from the following query:

    SELECT header_file,
    header_block
    FROM
    dba_segments
    WHERE segment_name = 'USER_INFO';

    And substitute the returned values into the following query:

    ALTER SYSTEM DUMP DATAFILE &header_file BLOCK &header_block;

    This should produce a file in your user dump directory, which contains a
    line like the following:

    #blocks below: 19

    It's this value the CBO uses for the NBLK stat. We just need to check that
    it's correctly recorded as a large number for your table.

    In relation to your other question; no you can't guarantee plan stability
    without stored outlines. As discussed, the segment header will change over
    time and if someone collects system statistics these could also alter your
    execution plan.

    Cheers,
    Tim
    --
    Regards
    Zhu Chao
    www.cnoug.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Oct 13, 2005 at 10:29 am
    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_at_freelists.org On
    Behalf Of zhu chao
    Sent: Thursday, October 13, 2005 17:01
    To: lex.de.haan_at_naturaljoin.nl
    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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Christian Antognini at Oct 14, 2005 at 6:27 am
    Hi
    As for the plan stability, while running CBO, ifI do have statistics,
    Is there any other case, that CBO is still able to change the plan?
    Can you give me a test case?
    Different literals or bind variables (remember, in 9.2 Oracle peeks the bind variables value) may lead to different cardinalities and therefore to different plans.

    HTH

    Chris

    New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com

    Italiano: Lugano (24-Nov)
    Français: Genève (17-Nov)
    Deutsch: München (20-Oct), Basel (25-Oct), Frankfurt (27-Oct),
    Düsseldorf (23-Nov), Zürich (29-Nov), Stuttgart (13-Dec)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 13, '05 at 8:10a
activeOct 14, '05 at 6:27a
posts14
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase