Migration from RULE base to COST base

Posted on 2004-10-15
Medium Priority
Last Modified: 2007-12-19
Oracle --> Oracle 9.2

Does anyone have a white paper transfering an application from RULE base optimizer to COST base?
Steps, best practice, encountered problems, known issues etc...

Question by:peledc
LVL 23

Expert Comment

ID: 12318192
More than likely, you have will index usage problem.

RULE---->COST migration is more difficult than ever thought...

but the common approach is that:

Use exp tool to export the data without statistics from Oracle817

and then use imp tool to import the data into Oracle9i

rebuild the indexes.
then recalculate the statistics.


Expert Comment

ID: 12318249

Author Comment

ID: 12318250
Thanks, but what I'm looking for is something much more detaild, That's the reason I am looking for a white paper or any other detaild document.
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Expert Comment

ID: 12319075

Author Comment

ID: 12319510
Dear Bigfam5,
This link lead to a 404 error, could you please check it again?

MAny thanks
LVL 10

Expert Comment

ID: 12321084
Since Rule Based Optimizer will be removed in Oracle10g, it may be better to focus on tuning the sql under the Cost Based Optimizer.

Here is a metalink note which is a FAQ for CBO/RBO. Let me know if you dont have access to metalink and I can paste the document here for you.

LVL 35

Expert Comment

by:Mark Geerlings
ID: 12321732
There are known issues and significant differences between the two optimizers.  In general, the CBO is more sophisticated and can support all of the new Oracle features: partitioning, bit-mapped indexes, function-based indexes, etc., but it needs to have accurate, up-to-date statistics on the tables and indexes.  The RBO is much simpler and more predictable.

You will likely have to make the switch, then be prepared to react quickly to the queries/reports/processes that perform much more slowly when using the cost-based optimizer.  My guess is that 2-5% of the queries/reports/processes will be slower at first with the CBO.  These may or may not be significant or frequently-used programs.

Do you have access to and control over all of the application's source code?  If so, you may need to change some queries/reports/processes so they can use the CBO efficiently.   Optimizer hints and/or changes in "where" clauses may be needed.  If you don't have access to the source code, you will need to be prepared to add, change or even remove indexes to better support the CBO.  Another option is to create (or change existing) views so they have "where" clauses or optimizer hints that work well with the CBO.

Author Comment

ID: 12326326
Thanks All,

My current plan goes like this:

1. Create a testing instance by using an export file. This will enable me to fully test the correct values and behavior.
2. Analyze all tables.
3. Run a massive QA session and try to locate potential road bumps.
4. Fix source code with hints, indexes, and init.ora parameters and histograms.
5. Re run the QA session.
6. Move changes to PROD
7. Sleepless nights during the first few days while fixing all unseen problems
8. After the application is stable, suggest further performance improvement by utilizing CBO new features.

Sincerely Yours

Accepted Solution

BobMc earned 1776 total points
ID: 12332959
Its nigh on impossible to predict how things will behave, so the only way to do it is via a trial run - baseline the performance of your applications, do a trial upgrade, and check the performance again.

Pay particular attention to month end reports o  that job that the bloke in the next office runs when the director phones him up - these are the ones that give most grief asyou invariably need to sort them out instantly.

You will find that some bits will run quicker, and others slower - although the performance overall will be the same, people will complain so make sure you have sufficiently detailed aceptance criteria

The biggest problems you will find is where you have applications specifically written for rule based operation - I support a maintenance management system, that when we upgraded it instantly corrupted its own database - it was expecting oracle to use an index to read in some config data, and so didnt specify an order by clause. Oracle decided not to use the index, and when the data was saved, buggered up everything - we had three goes at reloading the data before we worked out what was happening!

You will also need to add some way of keeping your statistics up to date where appripriate. Im not a big fan of nightly/weekly analyzing for the sake of it after several experiences where one day the optimiser suddenly decides to go down the wrong path. At least with DBMS_STATS, you can store safe values that you know work...

good luck

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question