Mladen Gogala, author of the exciting book on dynamic web pages with Oracle, "Easy Oracle PHP" has published a great article about Oracle CBO internals for OLTP systems:

Part 1: Introduction

The year is 2006. It is time to say hello to Oracle 10g and it is time to say goodbye to the venerable RBO (Rule Based Optimizer) that we all used to resort to so frequently when a query just wouldn't want to use an index. With Oracle 10g, RBO is still here, but is no longer documented or supported. This article is an attempt to give philosophical support to Jonathan Lewis book which provides the gory details of the cost based optimizer. Why was RBO so much loved and why is it so hard to part from it? There are many reasons, but the most important are the following:

● It was easy and simple to understand. The main philosophy of the RBO was: "if there is an index, use it". Rule based optimizer was only looking into availability of certain access paths. Access paths were ranked and the path with the best rank was used. That was it.

● It wasn't changing much. RBO pretty much remained the same from Oracle 5.1.22 until 9.2. This is more then a decade without change. RBO served us well for more then a decade. It's an enviable record indeed.

● It was stable. You could move database from one platform to the next, from one machine to another, from one disk farm to another and plans would never change. If a query with an embedded /*+ RULE */ hint was using index access path On Oracle 7.3.4 on a Windows NT machine, it would use index access path on Oracle 9.2 on a HP SuperDome. Queries did not need to be re-tuned for each new version and each new platform. Developers would develop applications on a small Windows 2k server, run EXPLAIN PLAN and the plan would remain the same on the big box, without particular effort from the programmer or a DBA.

● It was easy to manipulate. We all remember tricks like adding 0 to number or date columns or concatenating with empty strings, in case of character columns, to prevent an index from being used when it wasn't desired. The only place to manipulate RBO was within the SQL statement itself.

As you can see, none of that is true for the CBO (Cost Based Optimizer). First, CBO is not yet a finished product. It is very far from reaching a stable state. I suspect that Oracle will kill it if it ever does approach a stable state. CBO is changing not just between versions, but between patch sets as well. CBO is so complex then an authority like Jonathan Lewis had to write a book, several hundreds pages in length solely to deal with CBO. Second, with machine calibration introduced in Oracle 9i system statistics, collected by DBMS_STATS.GATHER_SYSTEM_STATS) your plans are likely to change even when moving an application from a development box to the QA box and then, again, when moving it to production. In other words, CBO messed up the whole development process. This article is an attempt to answer the obvious question: how to deal with all this change and instability.

Before we start going into the technical details, we have to observe one more crucial difference between RBO and CBO: RBO just wasn't very good with large reports or in a data warehouse environment. RBO was an OLTP beast, pure and simple. And it was very good at it. CBO is a people pleaser: it tries to be everything to everybody. CBO can deal with an index access path as well as a hash join, star schema, query rewrite and materialized views. As the title suggests, this article restricts itself to an OLTP environment as it is precisely the environment which suffers the most from the retirement of the RBO.

Part 2: Oracle RBO and CBO for OLTP part 2

Get the Complete
Oracle SQL Tuning Information

The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

��