| 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
Rich, I went thru the archives. Are you using the option 'cascade=>true' for gather_table_stats?
-----Original Message-----
Jesse, Rich
Sent: Wednesday, December 03, 2003 4:21 PM
To: Multiple recipients of list ORACLE-L
Yes -- same platform and version. If you look on the list archives (fatcity.com, for example) for a subject of "Burned by DBMS_STATS **AGAIN**" back in April 2003, you may get some insight.
There are also known bugs in GATHER_SCHEMA_STATS in 8i. The recommendation is to iteratively call GATHER_TABLE_STATS.
GL!
Rich
Rich Jesse System/Database Administrator rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA
-----Original Message-----
Sent: Wednesday, December 03, 2003 3:04 PM
To: Multiple recipients of list ORACLE-L
Hello,
Oracle 8.1.7.4 on HP-UX 11i
A week ago, we replaced 'analyze table ... estimate statistics sample 10 percent' with dbms_stats.gather_schema_stats('x', estimate_percent=>10, cascade=>true, degree=>4, method_opt=>''for all indexed columns size 2')
Performace is good against partitioned tables but not for non-partitioned tables.
Saw a note in Metalink that its better to do the above with 'cascade=>false' and then do a gather_index_stats separately.
In my tests, I see that 'analyze' makes the CBO use an index while dbms_stats is making the CBO to use a FTS instead.
Have you faced any similar issues?
TIA
Prakash
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.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).
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 Wed Dec 03 2003 - 22:19:25 CST
 |
 |