Overview
optimizer_index_cost_adj controls the proportion of statistics cost in the optimizer's calculation of index scan costs.
Privilege requirements
Query parameters
Both the
systenant and all user tenants can query this parameter using theSHOW PARAMETERSstatement or theGV$OB_PARAMETERSview.Modify parameters
The
systenant and all user tenants can modify this parameter.
Attributes
Attribute |
Description |
|---|---|
| Parameter type | Int |
| Default Value | 0 |
| Value range | [0, 100]
NoteWe recommend that you set the value to 10. To adjust it to other values, contact technical support. |
| Modifiable | Yes, you can use the following methods:ALTER SYSTEM SETStatement modification. |
| Whether to restart the OBServer node for the change to take effect | No, the setting takes effect immediately. |
Usage instructions
We recommend that you enable this parameter when data skew exists in business indexes:
- When the value is 0, the optimizer uses only the row count estimates from the storage layer to compute the cost of index scans.
- When the value is 10, it means that when calculating the cost of an index scan, the optimizer uses 10% of the statistical information cost and 90% of the storage layer information cost as the index scan cost.
- When the value is 100, the optimizer uses only statistics to calculate the cost of index scans.
Configuration example
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;
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;