OPTIMIZER_INDEX_COST_ADJ

845641

Firstly i am in 10G R2 on rhel5, Well frankly speaking about the parameter OPTIMIZER_INDEX_COST_ADJ, that more value i keep more optimizer will think that it will be expensive to go for an index scan..Range of values i saw in oracle doc 1 to 10000 default value 100 ... Default value oracle says oracle will evaluate at regular cost, i mean the cost of index access paths.. So my question is simple .. is it applicable for all index scans like index unique scan , index range scans, index fast full scan and so on ...

Regards
Satyajeet

Antonio Navarro

It is usually probe and error and it depends on application, I use this parameter in one database with Siebel and seted to 1 (it is ok more is worst) and in other database I seted to 100 (it is ok too, less is worst).

HTH
Antonio NAVARRO

845641

You said 1 is ok more is worst and then you said 100 is ok less is worst.. So what to do and what not to .. Any example in which we use it.. do we use it in testing where lets say i keep it to 1000 and let oracle favour more of full table scans

713555

totally depends on your milage. FTS are not good, FTS are not bad.

If you do not want the CBO to select FTS over index scan, reduce optimizer_index_cost_adj to anythign lower then the default of 100 (this is the trial and error bit, start at 1 which the above was hinting at), which tells the CBO to give a lower cost to index scans over FTS. Milage may vary, if you find the CBO is totally ignoring the FTS when a FTS is actually what is needed then you must adjust.

this is, as Im sure youve read, rule of thumb stuff, your own system may have its own requirements.

Hoek

You might want to read:

http://richardfoote.wordpress.com/2009/07/08/the-cbo-and-indexes-optimizer_index_cost_adj-part-i/
http://richardfoote.wordpress.com/2009/07/22/the-cbo-and-indexes-optimizer_index_cost_adj-part-ii/
http://richardfoote.wordpress.com/2009/08/20/the-cbo-and-indexes-optimizer_index_cost_adj-part-iii/

Thierry H.

Hi Satyajeet,

You may need to tell if you are talking about a OTLP or DSS database type since the requirements differ.
For example, i would consider "low" values for OPTIMIZER_INDEX_COST_ADJ and DB_FILE_MULTIBLOCK_READ_COUNT parameters for an OLTP system, when i would go for higher values for a data wharehouse system.

HTH,
Thierry

Chinar

So my question is simple .. is it applicable for all index scans like index unique scan , index range scans, index fast full scan and so on ...

OPTIMIZER_INDEX_COST_ADJ - this parameter value will apply of cost calculation for single block read operation but not multiblock read operation.
So for index range and unique scans this will affect but index fast full scan will not

942062

Hi,

I understand that OPTIMIZER_INDEX_COST_ADJ is the parameter which makes optimizer to estimate the cost of Index scans. The default for this parameter is 100, at which the optimizer evaluates index access paths at the regular cost(same as table scan). 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.

But, I am facing a wierd issue dependent on OPTIMIZER_INDEX_COST_ADJ setting. Please check the timings of execution of my statements below:

*1) When OPTIMIZER_INDEX_COST_ADJ is set to 1*:

+12:59:09 SQL> create table test (id varchar2(9));+

Table created.

Elapsed: 00:00:00.03
+13:01:06 SQL> drop table test;+

Table dropped.

Elapsed: 00:00:54.33


*2) When OPTIMIZER_INDEX_COST_ADJ is set to 100*:

+14:20:20 SQL> create table test (id varchar2(9));+

Table created.

Elapsed: 00:00:00.02
+14:20:43 SQL> drop table test;+

Table dropped.

Elapsed: 00:00:00.04


I couldnt understand how OPTIMIZER_INDEX_COST_ADJ is affecting time taken in dropping a little table. Our DB requirement is to set OPTIMIZER_INDEX_COST_ADJ to 1, but it's causing teriffic performance issue while dropping a table; When OPTIMIZER_INDEX_COST_ADJ=100, it works absolutely fine.
Can anyone give some suggestions on this please??

Regards
Aaveg

JustinCave

In both cases, are you really dropping an empty table?

What version of Oracle are you using? Are you using locally or dictionary managed tablespaces? Automatic or manual extent allocation?

Justin

942062

Hi,

Yes, in both cases, I am dropping empty table.
Oracle version : 11.2.0.2.0
Extent Management : Local
Allocation Type : Uniform (tried with a user having default tablespace with Allocation Type SYSTEM also & got same results)
Segment space mgnt : AUTO (tried with a user having default tablespace with Segment Mngmnt MANUAL also & got same result).

Aaveg

Aman....

I can't seem to reproduce on 11201,

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 6 16:57:49 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table test;

Table dropped.

SQL> set timing on
SQL> show parameter cost

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj             integer     100
SQL> alter session set optimizer_index_cost_adj=1;

Session altered.

Elapsed: 00:00:00.00
SQL> create table test(id varchar2(9));

Table created.

Elapsed: 00:00:00.65
SQL> drop table  test;l
  2
SQL> drop table  test;

Table dropped.

Elapsed: 00:00:00.26
SQL> alter session set optimizer_index_cost_adj=100;

Session altered.

Elapsed: 00:00:00.00
SQL> create table test(id varchar2(9));

Table created.

Elapsed: 00:00:00.04
SQL> drop table  test;

Table dropped.

Elapsed: 00:00:00.09
SQL> purge recyclebin;

Recyclebin purged.

Elapsed: 00:00:00.25
SQL> alter session set optimizer_index_cost_adj=1;

Session altered.

Elapsed: 00:00:00.00
SQL> create table test(id varchar2(9));

Table created.

Elapsed: 00:00:00.01
SQL> drop table  test;

Table dropped.

Elapsed: 00:00:00.00
SQL> alter session set optimizer_index_cost_adj=100;

Session altered.

Elapsed: 00:00:00.00
SQL> create table test(id varchar2(9));

Table created.

Elapsed: 00:00:00.00
SQL> drop table  test;

Table dropped.

Elapsed: 00:00:00.03
SQL>

You can see that its a marginal difference of timing. I shall try to test it on 11203 if shall get the time but for the meantime, I can't think of anything which would be causing the time difference in your case that can be connected to the OICA parameter.

Just my 2 cents.

Aman....