optimizer_index_cost_adj

Description

optimizer_index_cost_adj specifies the proportion of the statistics cost in the cost of an index scan.

Privilege requirements

• Query the parameter

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

• Modify the parameter

  sys and MySQL user tenants can modify this parameter.

Attributes

Considerations

We recommend that you use this parameter in scenarios where data skew exists in business indexes:

• When the value is 0, the optimizer calculates the cost of an index scan based on the estimated number of rows in the storage layer.
• When the value is 10, the optimizer calculates the cost of an index scan based on 10% of the statistics cost and 90% of the storage layer cost.
• When the value is 100, the optimizer calculates the cost of an index scan based on the statistics cost.

Examples

• In a sys tenant

  • Disable the index selection optimization feature for all tenants:

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

  • Enable the index selection optimization feature:

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

• In a MySQL user tenant

  • Disable the index selection optimization feature for the current tenant:

    obclient> ALTER SYSTEM SET optimizer_index_cost_adj=0;
    

  • Enable the index selection optimization feature:

    obclient> ALTER SYSTEM SET optimizer_index_cost_adj=10;
    

• In an Oracle user tenant

  • Disable the index selection optimization feature:

    obclient> ALTER SYSTEM SET "optimizer_index_cost_adj"=0;
    

  • Enable the index selection optimization feature:

    obclient> ALTER SYSTEM SET "optimizer_index_cost_adj"=10;