Migration from RULE base to COST base

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...

Who is Participating?
BobMcConnect With a Mentor Commented:
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
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.

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

peledcAuthor Commented:
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.
peledcAuthor Commented:
Dear Bigfam5,
This link lead to a 404 error, could you please check it again?

MAny thanks
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.

Mark GeerlingsDatabase AdministratorCommented:
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.
peledcAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.