Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h795jpY04921 for ; Sat, 9 Aug 2003 00:45:51 -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 h795jpp04916 for ; Sat, 9 Aug 2003 00:45:51 -0500 Received: (from root@localhost) by www2.fatcity.com (8.11.6/8.11.6) id h793M1728275 for oracle-l@orafaq.net; Fri, 8 Aug 2003 20:22:01 -0700 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005C9B7A; Fri, 08 Aug 2003 20:14:23 -0800 Message-ID: Date: Fri, 08 Aug 2003 20:14:23 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Subject: RE: DBMS_STATS and CBO 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: 8bit Prasad, We ran into the same problem when we did FOR ALL INDEXED COLUMNS using DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then CBO started to use the indexes. execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE, 'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE); Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree. Hope this helps. Govind -----Original Message----- [mailto:Prasada.Gunda@hartfordlife.com] Sent: Friday, August 08, 2003 9:24 PM To: Multiple recipients of list ORACLE-L We have a table (monthly fact table) which has 24 partitions and partitioned by month. There is data in only 3 partitions. All the indexes are locally partitioned. In dev db, I analyzed the table and indexes with analyze table..compute statistics. When I query the no. of rows group by month, it returns the query in couple of seconds and does the index scan(bitmap) on month column. I created this table in production db and this time I used the dbms_stats to create the statistics (compute both on table and indexes). When I run this query, it does full table scan. The only way I could make it to use index scan by specifying hints. All the parameters(init.ora) are exactly the same on both databases and it is 8.1.7.4. In prod db, I tried various combinations of optimizer_index_caching, optimizer_index_cost_adj to favor the index scan. There was no use. Then, I did analyze table .. compute statistics on prod table but it was still doing the FTS. I did not delete the stats created by dbms_stats before using analyze table stmnt. Finally, I deleted the stats generated by dbms_stats/analyze table before generating stats again with Analyze table compute statistics. It is doing the index scan now. I have no clue 1. why it does not use index when I generated the stats w/dbms_stats. 2. why I had to explicitly delete the stats before generating the stats again to make use of the index. Thanks for your help in advance. Thanks. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Prasada.Gunda@hartfordlife.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: