Re: Why CBO choose wrong way?

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
optimizer_index_cost_adj=3D25

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.
--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l