FAQ
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).

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 2 | next ›
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 10, '03 at 9:44p
activeOct 11, '03 at 1:04a
posts2
users2
websiteoracle.com

2 users in discussion

Oracle-L: 1 post Wolfgang Breitling: 1 post

People

Translate

site design / logo © 2022 Grokbase