Re: DBMS_STATS and CBO

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" <ORACLE-L_at_fatcity.com> 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 <ORACLE-L_at_fatcity.com>
> Sent by: cc:
> ml-errors_at_fatcity 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:Prasada.Gunda_at_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 <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_at_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_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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <Govind.Arumugam_at_alltel.com
> INET: Govind.Arumugam_at_alltel.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).
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Prasada.Gunda_at_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_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).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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