RE: Changing optimizer mode and collecting statistics

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Thu, 18 Oct 2001 05:51:46 -0700
Message-ID: <F001.003AE887.20011018055519@fatcity.com>

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Christopher Spence
INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------