Re: OICA and Oracle choosing the wrong index
- From: Mladen Gogala <mladen@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 11 May 2004 10:12:24 -0400
OPTIMIZER_INDEX_COST_ADJUST is a strange parameter. The description from the
oracle reference
manual reads like this:
"The default for this parameter is 100 percent, at which the optimizer
evaluates index access paths at the regular cost. Any other value makes the
optimizer evaluate the access path at that percentage of the regular cost. For
example, a setting of 50 makes the index access path look half as expensive as
normal."
That is what I call "communism", setting prices by central authority instead of
letting the
database to properly determine the prices itself. The main use for that
parameter is to ease
the transition from RBO to CBO. By saying OICA=20, one says: "Index path is 5
times cheaper
then calculated", or "if there is index, use it", which has been the philosophy
of the rule
based optimizer. There is no valid reason why would someone want to set OICA to
20 and make
index I/O 5 times cheaper then the table one. How can things go wrong? Very
simple: if you
have a report that would be best executed by using the full table scan,
OICA=20, might
force the use of an index, which is suboptimal. You can aid CBO by telling
that 75% of
your index blocks are cached, that is normally true, but don't engage in "price
fixing".
On 05/11/2004 08:47:04 AM, ryan.gaffuri@xxxxxxx wrote:
> This is in reference to a new article by Jonathan Lewis about the OICA:
>
> http://www.dbazine.com/jlewis18.shtml
>
> It states that if you give Oracle a bad OICA it will choose the wrong index.
> Does anyone know why? I have run across this. I have cases where 95% of the
> time Oracle chooses the right index with a given OICA and for say 5% or less
> of queries Oracle chooses the wrong index.
>
> I've noticed Oracle picking bad join orders depending on how I set there.
> Especially with joins of 3 tables or more.
>
> Anyone have any comments or experiences? How much does CPU_COSTING eliminate
> this? We are still in development and have only delivered a small quantity of
> our application, so its not prudent to start messing with CPU_COSTING until I
> get a larger user sample.
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
--
Mladen Gogala
Oracle DBA
Note:
This message is for the named person's use only. It may contain confidential,
proprietary or legally privileged information. No confidentiality or privilege
is waived or lost by any mistransmission. If you receive this message in
error, please immediately delete it and all copies of it from your system,
destroy any hard copies of it and notify the sender. You must not, directly or
indirectly, use, disclose, distribute, print, or copy any part of this message
if you are not the intended recipient. Wang Trading LLC and any of its
subsidiaries each reserve the right to monitor all e-mail communications
through its networks.
Any views expressed in this message are those of the individual sender, except
where the message states otherwise and the sender is authorized to state them
to be the views of any such entity.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
