optimizer_index_cost_adj

Description

optimizer_index_cost_adj specifies the ratio of the cost of statistics in the cost of index scans calculated by the optimizer.

Privilege requirements

• Query the parameter

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

• Modify the parameter

  sys tenant and user tenants cannot modify this parameter.

Attributes

Considerations

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

• When the value is 0, the optimizer calculates the cost of index scans based only on the estimated number of rows in the storage layer.
• When the value is 10, the optimizer calculates the cost of index scans as 10% of the cost of statistics plus 90% of the cost of information in the storage layer.
• When the value is 100, the optimizer calculates the cost of index scans based only on the cost of statistics.

Examples

• In the sys tenant

  • Disable the index selection optimization feature for all user 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 tenant

  • Disable the index selection optimization feature for the current user 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 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;