ALTER_SQL_PLAN_BASELINE is used to modify the attributes of one or more plans in a baseline. The return value indicates the number of modified baselines.
Syntax
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
database_name VARCHAR(65535),
sql_handle VARCHAR(65535),
plan_name VARCHAR(65535) DEFAULT NULL,
attribute_name VARCHAR(65535),
attribute_value VARCHAR(65535)
)
RETURN DECIMAL;
Parameters
| Parameter | Description |
|---|---|
| database_name | The name of the database. This parameter is required only in MySQL mode. |
| sql_handle | The identifier of the SQL statement, which is also referred to as SQL_ID. |
| plan_name | The name of the plan, which is also the Plan Hash Value. If the value is NULL, all evolving plans of the SQL statement are accepted. |
| attribute_name | The name of the plan attribute. For more information, see Attribute description. |
| attribute_value | The value of the plan attribute. For more information, see Attribute description. |
Attribute description
| Attribute | Possible values | Description |
|---|---|---|
| enabled | 'YES' or 'NO' | 'YES' indicates that the plan baseline is valid. Whether the plan baseline is used depends on the acceptance. |
| fixed | 'YES' or 'NO' | 'YES' indicates that the current plan baseline is preferentially used and not automatically evolved. |
| autopurge | 'YES' or 'NO' | 'YES' indicates that the plan baseline will be automatically eliminated after a period of time without being used; 'NO' indicates that the plan baseline will never be eliminated. |
| plan_name | A string with a maximum of 30 characters. | The name of the plan. |
| description | A string with a maximum of 500 bytes. | The description of the plan. |
Examples
After a plan baseline is fixed, the SQL statement uses only the plan in this baseline.
DECLARE
v_alter_plans NUMBER;
BEGIN
v_alter_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
plan_name => '3388268709115914355',
attribute_name => 'fixed',
attribute_value => 'YES' );
END;
/