Re: Cost vs rule based optimizer

From: boulkenospam_at_globalnet.co.uk (Keith Boulton) >> The big problem with the cost based optimiser is that it is not predictable. Often you need to know that a particular process will run in a given time. The main reason for using the cost based optimiser, it seems to me, is that you can use hints to control the access path used. <<

It is my understanding that the two major driving forces behind the cost based optimizer were 1) a request from the user community to be able to modify the access path to other than the path taken by the rule based optimizer and 2) to improve the performance of the growing number of adhoc queries compared to the pre-defined and tested queries found in applications. Basically, both items are performance imporvements issues.

The cost based optimizer does not relieve designers from their responsibility to tune. As an application developer you should know how every important query in your application should retrieve the data and use hints to lock in this path. This adds consistentcy to the application performance, and limits the cost based optimizer to adhoc and the non-crucial queries in the application.

The cost based optimizer has improved with most upgrades, but you can expect that one or two queries to be affected by a flaw and need manual intervention. The most serious problem with the optimizer is that is relies on static statistics. If and when Oracle ever delivers dynamic statistics it should help, but the first release will probably prove painful.

My best advise is to spend the time to learn to use hints to control your query execution. This takes a lot of study, but to really learn how to tune it is a requirement.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Sun Mar 08 1998 - 00:00:00 CST