optimizer_index_cost_adj

Description

optimizer_index_cost_adj specifies the proportion of statistics cost in the index scanning cost calculated by the optimizer.

Privilege requirements

• Query the parameter

  sys and MySQL user tenants can use the SHOW PARAMETERS statement or the GV$OB_PARAMETERS view to query this parameter.

• Modify the parameter

  sys and MySQL user tenants can modify this parameter.

Attributes

Considerations

We recommend that you use this parameter in scenarios where business indexes have data skew.

• The value 0 indicates that the optimizer uses only the row estimation information at the storage layer to calculate the index scanning cost.
• The value 10 indicates that the optimizer calculates the index scanning cost by adding 10% of the statistics cost and 90% of the storage layer information cost.
• The value 100 indicates that the optimizer uses only statistics to calculate the index scanning cost.

Examples

• sys tenant

  • Disable index selection optimization for all tenants:

    obclient> ALTER SYSTEM SET optimizer_index_cost_adj=0 tenant=all_user;
    

  • Enable index selection optimization:

    obclient> ALTER SYSTEM SET optimizer_index_cost_adj=10 tenant=all_user;
    

• MySQL tenant

  • Disable index selection optimization for the current tenant:

    obclient> ALTER SYSTEM SET optimizer_index_cost_adj=0;
    

  • Enable index selection optimization:

    obclient> ALTER SYSTEM SET optimizer_index_cost_adj=10;
    

• Oracle tenant

  • Disable index selection optimization for the current tenant:

    obclient> ALTER SYSTEM SET "optimizer_index_cost_adj"=0;
    

  • Enable index selection optimization:

    obclient> ALTER SYSTEM SET "optimizer_index_cost_adj"=10;