Re: Why CBO choose wrong way?
- From: Zhai Jingmin <zhai_jingmin@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 28 May 2005 03:35:24 -0700 (PDT)
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.
FIRST_ROWS_n
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 <zhai_jingmin@xxxxxxxxx> 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
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
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Do You Yahoo!?
Yahoo! Small Business - Try our new Resources site!
--
//www.freelists.org/webpage/oracle-l
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
