Using the optimizer_index_cost_adj Parameter in Oracle

Query tuning is part art, part science and usually occurs on a case-by-case basis. There may be occasions though, where a series of queries, similar in structure and differing in predicate values, need to be tuned. Rather than go through each and every query, setting up a SQL Profile and enabling it, it may be easier to alter the setting for optimizer_index_cost_adj (presuming indexes are in use) so the index scans/index access paths are more ‘favorable’ than a table scan. Let’s look at an example of why this might be a good plan of attack.

The optimizer, in its infinite wisdom and using current statistics, computes the cost of access for every table and associated index touched by the problem query. Sometimes the index cost is ever so slightly greater than that for a table scan and, as a result, the index path gets scrapped in favor of the table scan. In such cases nudging the optimizer in the ‘proper’ direction is as simple as changing the value on the optimizer_index_cost_adj parameter, which defaults to 100. The value you need should be chosen carefully, hopefully so that the queries you want affected will be affected and most others won’t. In our example let’s look at a very small portion of the level 2 10053 trace; the names were changed to protect the ‘innocent’:

 ****** Costing Index PLORGENFLOTZ_PK
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
  Access Path: index (RangeScan)
    Index: PLORGENFLOTZ_PK
    resc_io: 3.000000  resc_cpu: 342602
    ix_sel: 0.954069  ix_sel_with_filters: 0.954069
    Cost: 3.014879  Resp: 3.014879  Degree: 1
...
  Best:: AccessPath: TableScan
         Cost: 2.006465  Degree: 1  Resp: 2.006465  Card: 228.000000  Bytes: 0.000000

Notice the cost of the index access is just slightly higher than the cost of a full table scan so the optimizer passes up that option and chooses the table scan. This is where optimizer_index_cost_adj can change things. If, for example, we set optimizer_index_cost_adj to 50 the cost of the index access will go down:

 ****** Costing Index PLORGENFLOTZ_PK
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
  Access Path: index (IndexOnly)
    Index: PLORGENFLOTZ_PK
    resc_io: 1.000000  resc_cpu: 63786
    ix_sel: 0.954069  ix_sel_with_filters: 0.954069
    Cost: 1.001385  Resp: 1.001385  Degree: 0
    SORT ressource         Sort statistics
      Sort width:        5989 Area size:     1048576 Max Area size:  1046896640
      Degree:               1
      Blocks to Sort: 1 Row size:     21 Total Rows:            243
      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
      Total IO sort cost: 0.000000      Total CPU sort cost: 23112595
      Total Temp space used: 0
...
  Best:: AccessPath: IndexRange
  Index: PLORGENFLOTZ_PK
         Cost: 1.507553  Degree: 1  Resp: 1.507553  Card: 34.346487  Bytes: 0.000000

The calculated cost of using this index has been cut in half (which should be expected when setting optimizer_index_cost_adj to 50) so now the optimizer elects to take the index range scan as the best possible path. Notice that the optimizer_index_cost_adj isn’t applied until the actual cost has been calculated; the total cost is adjusted by the percentage provided in the optimizer_index_cost_adj setting as the final step. Looking at the final execution plan we see the following steps:

...
| 44  |                   TABLE ACCESS BY INDEX ROWID BATCHED         | PLORGENFLOTZ_TBL              |    34 |  1326 |     2 |  00:00:01 |      |      |     |        |       |
| 45  |                    INDEX RANGE SCAN                           | PLORGENFLOTZ_PK               |   243 |       |     1 |  00:00:01 |      |      |     |        |       |
...

which replaced this step in the plan where optimizer_index_cost_adj was unmodified:

...
| 111 |                        TABLE ACCESS FULL                           | PLORGENFLOTZ_TBL            |    32 |  1248 |     2 |  00:00:01 |      |      |           |       | 
...

Other path steps were changed in addition to those listed here and the overall execution plan was shortened, as evidenced by the step numbers from the included plan excerpts.

Careful planning and testing needs to be done before settling on a value for optimizer_index_cost_adj as it will affect all index access calculations and could change acceptable plans using table scans to less-than-desirable plans forcing index access. The value of 50 used here was chosen after several runs using smaller and smaller settings until the desired plans were obtained. Being aggressive isn’t necessarily best when setting optimizer_index_cost_adj as extremely small settings, such as 20 or lower, may make some queries run very fast and make some others very slow (because index access isn’t always the best path to choose). Never make such changes on a production system without first investigating the effects in your test environment. The user community does not like unpleasant surprises.

It’s usually best to tune queries on an individual basis, but sometimes applications generate a set of queries that need attention. Judicious setting of optimizer_index_cost_adj could be the answer to such a tuning task. Remember that making such changes at the database level can affect more than you had bargained for so test, test, test to verify minimal impact outside of the set of queries you are targeting.

See all articles by David Fitzjarrell