Migration from RULE base to COST base

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.

Here's a good discussion on this topic

http://asktom.oracle.com/pls/ask/f?p=4950:8:5692839277585648379::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4460679969175,

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.

http://download-east.oracle.com/owsf_2003/1

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

MAny thanks
Peled

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.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=35934.1

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.

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