[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

656 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