Hi! Maybe you used analyze command without column analyzing clause, but used dbms_stats package with column analyze clause (for all columns parameter). Or it just could be because dbms_stats calculates some stats somewhat differently (supposedly better), than old analyze command (average column length and some spare columns of hist_head$ have varied in my tests).
If you set SIZE to 1, then only column low and high value are stored in histogram. (In hist_head$ instead of histgrm$). As an alternative to Govind's suggestion, you could increase SIZE parameter (maximum is 254) to give CBO better understanding of data distribution. Note that you should be careful with the METHOD_OPT parameter in gather_schema_statistics procedure in version 9i, because if you supply invalid parameter there, then the procedure just does nothing and returns without error! You should verify from last_analyzed column to see whether a segment was actually analyzed or not. (This was tested on 9.2.0.1 on W2k, it might be fixed on newer patch levels). Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, August 09, 2003 7:29 AM > > Thanks Govind for your reply and suggestion. > > Actually, I did 'FOR ALL COLUMNS SIZE 1' only. > > Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS > ('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE); > > Best Regards, > Prasad > 860 843 8377 > > > > <Govind.Arumugam@ > alltel.com> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent by: cc: > [EMAIL PROTECTED] Subject: RE: DBMS_STATS and CBO > .com > > > 08/09/2003 12:14 > AM > Please respond to > ORACLE-L > > > > > > > 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:[EMAIL PROTECTED] > 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 <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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).