Forum Stats

  • 3,840,090 Users
  • 2,262,565 Discussions
  • 7,901,149 Comments

Discussions

OPTIMIZER_INDEX_COST_ADJ

845641
845641 Member Posts: 198
edited Jun 7, 2012 4:44PM in General Database Discussions
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
«1

Answers

  • 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
    845641 Member Posts: 198
    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
    713555 Member Posts: 824
    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.
    713555
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    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.
    Thierry H. Member Posts: 854
    edited Apr 24, 2012 7:28AM
    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
    Chinar Member Posts: 5,542
    edited Apr 24, 2012 7:31AM
    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
    942062 Member Posts: 3
    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
    JustinCave Member Posts: 30,293 Gold Crown
    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
    942062 Member Posts: 3
    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....
    Aman.... Member Posts: 22,919 Gold Crown
    edited Jun 6, 2012 10:46PM
    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....
This discussion has been closed.