Solved

Migration from RULE base to COST base

Posted on 2004-10-15
9
892 Views
Last Modified: 2007-12-19
Oracle 8.1.7.4 --> 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...

Thanks
0
Comment
Question by:peledc
9 Comments
 
LVL 23

Expert Comment

by:seazodiac
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.

0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 12318249
0
 
LVL 1

Author Comment

by:peledc
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.
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 12319075
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

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

MAny thanks
Peled
0
 
LVL 10

Expert Comment

by:SDutta
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.

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

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.
0
 
LVL 1

Author Comment

by:peledc
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
0
 
LVL 7

Accepted Solution

by:
BobMc earned 444 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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now