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