optimizer_index_cost_adj

Description

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

Privilege requirements

• Query the parameter

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

• Modify the parameter

  The sys tenant and MySQL user tenant 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 uses only the row count estimates from the storage layer to calculate the cost of an index scan.
• When the value is 10, the optimizer calculates the cost of an index scan as 10% of the statistics cost plus 90% of the storage layer information cost.
• When the value is 100, the optimizer uses only the statistics cost to calculate the cost of an index scan.

Examples

• System 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 user 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 user tenant

  • Disable index selection optimization:

    obclient> ALTER SYSTEM SET "optimizer_index_cost_adj"=0;
    

  • Enable index selection optimization:

    obclient> ALTER SYSTEM SET "optimizer_index_cost_adj"=10;