Re: OICA and Oracle choosing the wrong index

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_at_cox.net 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------