| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Changing optimizer mode and collecting statistics
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: jonas.a.wetterberg_at_manpower.se
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: 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 Thu Oct 18 2001 - 07:13:44 CDT
 |
 |