Re: RBO to RIP

Hemant K Chitale wrote:

> Here's the text of Oracle Note 189702.1 :
>
> Desupport of the Rule-Based Optimizer
>
> The rule-based optimizer (RBO) will be no longer be a valid optimization
> choice when Oracle9i is de-supported. The release after Oracle9i (referred
> to in this article as Oracle10i) will only support the cost-based optimizer
> (CBO). Hence Oracle9i Release 2 is the last releases to contain the RBO.
> Partners and customers should certify their applications with the CBO before
> that time. The CBO has been Oracle's primary optimization method for many
> years now, and most major independant software vendors use the CBO by
> choice. Our surveys indicate that over 80% of customers are using the CBO
> with Oracle8i, and this number is expected to increase with Oracle9i. For
> more information on the CBO please refer to the Query Optimization in
> Oracle9i white paper which can be found at:
> <http://otn.oracle.com/products/bi/pdf/o9i_optimization_twp.pdf>
>
> What is being Desupported?
>
> The RBO is Oracle's legacy optimizer originating from Oracle Version 6 and
> earlier. The RBO is being desupported in Oracle10i. The RBO was superseded
> in Oracle7 by the CBO and has continued to be available for backwards
> compatibility. Oracle anticipates that Oracle10i will contain only one
> optimizer, and all applications running on that release will use that
> optimizer.
>
> Versions Affected
>
> Support for the RBO will be removed in Oracle10i. The last release that
> supports the rule-based optimizer will be Oracle9i Release 2.
>
> Platforms Affected
>
> GENERIC - All platforms will be affected by this change.
>
> Why is the RBO being Desupported?
>
> The existence of the RBO prevents Oracle from making key enhancements to its
> query-processing engine. The removal of the RBO will permit Oracle to
> improve performance and reliability of the query-processing components of
> the database engine. Furthermore, using the RBO prevents customers from
> taking advantage of all the query-processing technologies introduced since
> Oracle 7.3. For example, the RBO cannot take advantage of partitioned
> tables, bitmap indexes, hash join, parallel query, index organized tables,
> function-based indexes, materialized views and many others. As a result,
> customers never realize the benefits and the superior performance gained by
> these techniques.
>
> Third Party Applications
>
> This RBO desupport message is being distributed to both partners and
> customers. Partners are being encouraged to certify their products on
> Oracle's CBO as soon as possible. Customers should contact their software
> provider directly if they have concerns.
>
> Timescales
>
> The RBO will be supported throughout the lifetime of Oracle9i Release 2.
> From the date this notice is issued, May 2002, customers and ISVs have at
> least 2 years to prepare for this change. In addition, Oracle has
> consistently documented that all applications written after the Oracle
> Version 6 timeframe (over ten years ago) should use the CBO. Oracle has also
> publicly stated for many years that the RBO would be de-supported in a
> future release. The only new information in this notice is that Oracle now
> has a specific timeframe for desupporting the RBO.
>
> RBO Support in Oracle9i
>
> Until Oracle9i is desupported, Oracle will continue its current level of
> support for the RBO. For the RBO, Oracle only fixes bugs such as internal
> (i.e. ORA-00600) errors, 'wrong-results' from queries, and
> backwards-compatibility issues. Consistent with this policy (which was
> implemented with Oracle8), no new functionality will be added to the RBO.
> Oracle will not enhance the RBO in any way; thus, Oracle will not modify the
> RBO to take advantage of new features or to remove existing limitations or
> to generate different execution plans for any queries, with the exception of
> when the chosen execution plan generates incorrect query results.
>
> What action should I take now?
>
> Oracle advises customers to prepare for this change now by starting to
> convert any RBO based applications to CBO.
>
> How do I know whether I am using the RBO?
>
> If your optimizer_mode initialization parameter is set to CHOOSE (default
> setting) and you have not collected statistics on your database objects, or
> your optimizer_mode is set to RULE, then you may be using the RBO to
> optimize your queries. For more details see <Note:66484.1
> </metalink/plsql/showdoc?db=Not&id=66484.1>> Which Optimizer is Used? and
> <Note:98956.1 </metalink/plsql/showdoc?db=Not&id=98956.1>> How to Set the
> Optimizer Mode for the Database
>
> Switching to the CBO
>
> CBO Prerequisites
>
> The CBO relies on accurate statistics to determine the optimal access path
> for a query. Information regarding gathering and maintenance of statistics
> is included below. The Oracle9i Database Performance Tuning Guide and
> Reference extensively documents the behavior of the CBO.
>
> Moving applications tuned for the RBO to work well with the CBO
>
> The main issue is ensuring that appropriate statistics are collected and
> maintained. Little, if any, change to the actual SQL application code is
> required. Customers new to the CBO should gather statistics on all of their
> database objects, and should carefully consider the proper setting for the
> 'OPTIMIZER_MODE' parameter (FIRST_ROWS will be an good setting for many OLTP
> applications previously using the RBO).
>
> Testing applications under the CBO
>
> Oracle strongly recommends that applications should be thoroughly tested
> prior to any major change such as implementation of a new optimizer. Testing
> can take place on a dedicated test system or even on a production
> environment. By explicitly setting the optimizer mode to 'RULE', database
> administrators can ensure that their production users continue to use the
> RBO. Meanwhile, application developers can force the use of CBO in their
> sessions to observe the behavior of the CBO.
>
> Management of statistics
>
> Extensive facilities are provided to facilitate the collection and
> maintenance of optimizer statistics. Optimizer statistics gathering can be
> as simple as turning on DML Monitoring and collecting statistics through the
> DBMS_STATS package.
> Turning on DML monitoring for the entire system can be accomplished by
> calling the relevant procedure:
>
> execute dbms_stats.alter_database_tab_monitoring;
>
> Once monitoring has been initiated, functions such as gather_schema_stats
> can be used to gather the appropriate statistics for the optimizer:
>
> execute dbms_stats.gather_schema_stats(<schema_name>, options => 'GATHER
> AUTO');
>
> This procedure can be called on regular intervals using Oracle's job queue
> mechanism (see the DBMS_JOB package).
> These packages provide many more monitoring options than can be detailed
> here. Please refer to the following documents for more details:
>
> Oracle9i Database Performance Tuning Guide and Reference for information
> about using DBMS_STATS to gather statistics for the optimizer
> Oracle9i Supplied PL/SQL Packages and Types Reference for a description of
> the DBMS_STATS and DBMS_JOB packages
>
> Maintaining RBO access paths
>
> In some cases it may be desirable to maintain the original RBO access paths
> for queries. Plan stability for these queries can be maintained using Stored
> outlines, an Oracle8i feature, to capture, save, and re-use the execution
> plans for all of the queries of a given application. This feature can thus
> preserve the RBO behavior for a fixed set of queries. See Oracle9i Database
> Performance Tuning Guide and Reference for information about Using Plan
> Stability
>
> Useful CBO Articles
>
> <Note:35934.1 </metalink/plsql/showdoc?db=Not&id=35934.1>> TECH: Cost Based
> Optimizer - Common Misconceptions and Issues
>
> Note The term Oracle10i is used in this article to mean the next major
> release after Oracle9i. The next major release may or may not be called
> Oracle10i. .
>
> Hemant K Chitale
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3CFB8BAC.4A46F9A0_at_exesolutions.com...
> > Pete Sharman wrote:
> >
> > > In article <v5dffu4viqq9i93tnrrl9u4q4irpbe30ds_at_4ax.com>, Sybrand says...
> > > >
> > > snip
> > > >
> > > >
> > > >7.3.4 and 7.1.5.2, many of the 8+ applications NOT using CBO
> > > >
> > > >
> > > >Crying
> > >
> > > Don't cry Sybrand. You're obviosuly not concerned abouyt being
> supported , so
> > > just never upgrade those databases to 10i and you'll be fine! ;)
> > >
> > > >
> > > >
> > > >
> > > >
> > > >Sybrand Bakker, Senior Oracle DBA
> > > >
> > > >To reply remove -verwijderdit from my e-mail address
> > >
> > > HTH. Additions and corrections welcome.
> > >
> > > Pete
> > >
> > > SELECT standard_disclaimer, witty_remark FROM company_requirements;
> >
> > I have been unable to access the remarks on metalink about RBO and 10i.
> Could
> > someone please repost it.
> >
> > Thanks.
> >
> > Daniel Morgan
> >

Thanks.

Daniel Morgan Received on Tue Jun 04 2002 - 11:03:05 CDT