optimizer_index_cost_adj

Description

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

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;