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).
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.