Re: CBO Puzzles

Until I have seen the 10053 traces I do not believe that the access plans are identical. Most of the time if the performance of a query changes when index statistics are deleted it is because the default statistics, especially clustering factor is drastically different than the real statistics. I have not worked with a 8.0 database in 4 years and can't remember if the version emits STAT entries to the trace with a 10046 trace. That would be the ultimate word on what access path was used.

At 03:44 PM 10/10/2003, you wrote:
>All
>
>We have a 8.0.4 database using choose optimizer. We have a query that access
>a single table using index.
>
>If we analyze the table and indexes - query takes 8 min to complete
>
>If we analyze table and then delete stats on indexes - query runs in two
>minutes.
>
>Explain plan is the same in both cases. I did a event 10053 trace but could
>not find any difference between the choices the CBO makes except when the
>indexes are not analyzed it thinks the cost is low.
>
>Any ideas why the query would execute faster - with the same explain plan -
>if the CBO thinks its cost is low ?
>
>PS : We ruled out the effects of caching in the buffer by reversing the
>order of the tests..
>
>TIA
>
>Babu
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Oracle-L
> INET: orclbabu_at_hotmail.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).