Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h9ANV6U06222 for ; Fri, 10 Oct 2003 18:31:07 -0500 X-ClientAddr: 66.27.56.213 Received: from www2.fatcity.com (rrcs-west-66-27-56-213.biz.rr.com [66.27.56.213]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h9ANV6c06217 for ; Fri, 10 Oct 2003 18:31:06 -0500 Received: (from root@localhost) by www2.fatcity.com (8.11.6/8.11.6) id h9AKlNG10181 for oracle-l@orafaq.net; Fri, 10 Oct 2003 13:47:23 -0700 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005D2B93; Fri, 10 Oct 2003 13:44:25 -0800 Message-ID: Date: Fri, 10 Oct 2003 13:44:25 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Oracle-L" Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Oracle-L" Subject: CBO Puzzles Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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@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@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).