| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_STATS and CBO
Prasad,
Make sure that low_value and high_value columns do not have null values ie. generate statistics on all columns. Analyze table generates the correct values for these columns whereas FOR ALL INDEXED columns in DBMS_STATS do not. Once these values are available through FOR ALL COLUMNS SIZE 1 ( ie do not generate histograms ), we seen the queries to be using the indexes without any hints. We ran into this problem when we went to 9i for the first time.
select
column_name,
NUM_DISTINCT ,
decode(LOW_VALUE ,null,null,'Full') Low ,
decode(HIGH_VALUE ,null,null,'Full') High ,
DENSITY , NUM_NULLS , NUM_BUCKETS , LAST_ANALYZED ,
Govind
-----Original Message-----
Sent: Saturday, August 09, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L
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.
>
> 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). -- 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).Received on Sat Aug 09 2003 - 12:19:24 CDT
 |
 |