|
 |
|
Manipulating the CBO in an OLTP Environment
Oracle Tips by Mladen Gogala |
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
You can buy it for 30%-off directly from the
publisher at this link:
http://www.rampant-books.com/book_2005_2_php_oracle.htm
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
