That would work, you may want to change the method opt as that will create
histograms on many columns which you may not want/need.
Good choice to use DBMS_STATS instead of analyze table. There are some bugs
with the collection in analyze table, dbms_stats only gathers what is needed
for cbo so is faster, and it can be parallelized.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-----Original Message-----
Sent: Thursday, October 18, 2001 9:20 AM
To: Multiple recipients of list ORACLE-L
Hello
We upgraded our Oracle version from 7.3 to Oracle 8.1.7 for a couple of
months ago. In Ora 7 environment we used optimizer mode RULE, but now we
would like to change to CHOOSE, and hopefully gain some performance.
As far as I understand everything, we must collect statistics for the
different schemas in the database to have any advantage of the cost based
optimizer mode. Our plan is to do this once a week (is this good enought or
too often?). According to Oracle manual, I should use the dbms_stats package
to get statistics. So a run the following procedure to get statistics:
DBMS_STATS.GATHER_SCHEMA_STATS (ownname => '<schema>',
estimate_percent => 10,
block_sample => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE
1',
degree => NULL,
granularity => 'DEFAULT',
cascade => TRUE);
I plan to schedule this and run it once a week. Is this all I have to do? It
seem too easy. There must some catch somewhere or ??
I'm thankfull for any help
/Jonas
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonas A Wetterberg
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
