Database Reference
In-Depth Information
In the following call, an execution plan associated to a specific SQL plan baseline is disabled:
ret := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_492bdb47e8861a89',
plan_name => 'SQL_PLAN_4kayv8zn8c6n93fdbb376',
attribute_name => 'enabled',
attribute_value => 'no');
Activating SQL Plan Baselines
The query optimizer uses the available SQL plan baselines only when the
optimizer_use_sql_plan_baselines
initialization parameter is set to
TRUE
(this is the default value). You can change it at the session and system levels.
Moving SQL Plan Baselines
The
dbms_spm
package provides several procedures for moving SQL plan baselines between databases. This is needed
when, for example, the SQL plan baselines have to be generated on a development or test database and moved to the
production database. As shown in Figure
11-10
, the following features are provided:
•
You can create a staging table using the
create_stgtab_baseline
procedure.
You can copy SQL plan baselines from the data dictionary to the staging table through the
•
pack_stgtab_baseline
function.
You can copy SQL plan baselines from the staging table into the data dictionary through the
•
unpack_stgtab_baseline
function.
Figure 11-10.
Moving SQL plan baselines with the
dbms_spm
package
Notice that moving the staging table between databases is performed by means of any data movement technique
(for example, Data Pump or the legacy export and import utilities) and not with the
dbms_spm
package itself (see
Figure
11-10
).
