Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Why CBO choose wrong way?

Re: Why CBO choose wrong way?

From: Zhai Jingmin <>
Date: Sat, 28 May 2005 03:35:24 -0700 (PDT)
Message-ID: <>

I changed optimizer_mode to 'first_rows_1',and my query can use the right index too!I never thought there's so much difference between 'first_rows' and 'first_rows_1' before this query problem,I checked the oracle manuels,found following words,please note the 'Note',hope anyone like me note this big difference.Thanks a lot for Lex's notification.


The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.

FIRST_ROWS The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.

Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.

Zhai Jingmin <> wrote: Thanks both of you very much!I changed the optimizer_mode parameter to 'first_rows_10',and it works! I tried several combination of that two 'optimizer_index' parameters,seems they are not very useful here.Because our application is pure OLTP one,so we set those optimizer* parameters as default and never have any problem except this one we just found.Maybe we are too 'aggressive':-). It's a great list,thanks all!
Mladen Gogala wrote:

On 05/28/2005 04:24:45 AM, Lex de Haan wrote:

> statistics. and why do you have optimizer_index_cost_adj set to 30?

I believe I can answer this one. Whenever I have to deal with an OLTP datab= ase,=20
I set the following values:

optimizer_index_cached=3D80 and

I do that to simulate the RBO behavior, in other words, to use indexes if t= hey=20
exist. In an OLTP database, I need a stoopid optimizer which will use index= if=20
it's there, regardless of histograms and values calculated by treating my bind variables in voyeuristic way. I don't want to have an overly smart=20 optimizer for an OLTP database. To strongly prefer index path, I need to "elevate its rank" or, in the language of CBO, make index I/O much cheaper then the table one. I am fully aware of Jonathan's article in DBAZine and I enjoyed reading it, but I had no negative experiences with using those two parameters in such setup. So, if we are talking an OLTP database, I'd consider even more aggressive setup then the original poster has. If the database is partly used for large reports and DW loads, it's a different st= ory,
but pure and unadulterated OLTP database needs the closest equivalent to RB= O
I can get. I put indexes on the tables where I want them to be used. I don'= t
want optimizer to get too smart for its own good and decide that, based on the bind variable peek, a full table scan is the way to go. In an OLTP database, that is ALWAYS wrong decision. Rigging optimizer_index parameters is a good way to prevent such decision.
The bottom line here is that I do that in a very specific situation and tha= t I know=20
why am I doing that. Based on my experience, in an OLTP database, it's a go= od thing
to do in an OLTP database. Trying to tune based on statistics in an OLTP da= tabase is
a stupid thing to do because full table scan is explicitly prohibited in su= ch setup.
You don't want it to happen - ever.
Mladen Gogala
Oracle DBA


Do You Yahoo!?
Yahoo! Small Business - Try our new Resources site!


Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 

Received on Sat May 28 2005 - 06:40:14 CDT

Original text of this message