From ed@mb.ru Thu, 18 Oct 2001 10:30:30 -0700 From: "Edward Shevtsov" Date: Thu, 18 Oct 2001 10:30:30 -0700 Subject: Re: Changing optimizer mode and collecting statistics Message-ID: MIME-Version: 1.0 Content-Type: text/plain Hi,   try 'Plan Stability' feature to fix your current RBO plans. It may be usefull unless your sql is mostly based on literals   Regards, Ed
  Would suggest testing first with choose.  If you were application was written for RULE it might not run good for CHOOSE.  It could run better, same or worse.   Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204>>> jonas.a.wetterberg@manpower.se 10/18/01 9:20:24 AM >>>Hello We upgraded our Oracle version from 7.3 to Oracle 8.1.7 for a couple ofmonths ago. In Ora 7 environment we used optimizer mode RULE, but now wewould like to change to CHOOSE, and hopefully gain some performance. As far as I understand everything, we must collect statistics for thedifferent schemas in the database to have any advantage of the cost basedoptimizer mode. Our plan is to do this once a week (is this good enought ortoo often?). According to Oracle manual, I should use the dbms_stats packageto get statistics. So a run the following procedure to get statistics:DBMS_STATS.GATHER_SCHEMA_STATS (ownname          => '',                                estimate_percent => 10,                                 block_sample     => TRUE,                                method_opt       => 'FOR ALL COLUMNS SIZE1',                                degree           => NULL,                                granularity      => 'DEFAULT',                                 cascade          =>  TRUE);I plan to schedule this and run it once a week. Is this all I have to do? Itseem 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@manpower.seFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing).