Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: RBO to RIP
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
>
Received on Tue Jun 04 2002 - 09:45:21 CDT