Database Reference
In-Depth Information
The following example, an excerpt of the
baseline_clone.sql
script, shows how to copy a SQL plan baseline
from one database into another. First, the
mystgtab
staging table is created in the current schema:
dbms_spm.create_stgtab_baseline(table_name => 'MYSTGTAB',
table_owner => user,
tablespace_name => 'USERS');
Then a SQL plan baseline is copied from the data dictionary into the staging table. You can identify which SQL
plan baselines are to be processed in four ways:
•
Identify the SQL plan baseline exactly, through the
sql_handle
and, optionally,
plan_name
parameters.
•
Select all SQL plan baselines that contain a specific string in the text of the SQL statement
associated with them. For this purpose, the
sql_text
parameter, which also supports
wildcards (for example,
%
), is available. Note that the parameter is case-sensitive.
Select all SQL plan baselines matching one or several of the following parameters:
•
creator
,
origin
,
enabled
,
accepted
,
fixed
,
module
, and
action
. If several parameters are specified, all
of them must be fulfilled.
•
Processing all SQL plan baselines. For that, no parameters are specified.
The following call shows an example where the SQL plan baseline is identified exactly:
ret := dbms_spm.pack_stgtab_baseline(table_name => 'MYSTGTAB',
table_owner => user,
sql_handle => 'SQL_492bdb47e8861a89',
plan_name => 'SQL_PLAN_4kayv8zn8c6n93fdbb376');
At this point, the
mystgtab
staging table is copied by means of a data movement utility from one database
into another.
Finally, the SQL plan baseline is copied from the staging table into the data dictionary in the target database.
To identify which SQL plan baselines are processed, the same methods as for the
pack_stgtab_baseline
function
are available. The following call shows an example where the SQL plan baselines are identified by the text of the SQL
statement associated with them:
ret := dbms_spm.unpack_stgtab_baseline(table_name => 'MYSTGTAB',
table_owner => user,
sql_text => '%FROM t%');
Dropping SQL Plan Baselines
You can use the
drop_sql_plan_baseline
procedure in the
dbms_spm
package to drop a SQL plan baseline from the
data dictionary. The
sql_handle
and
plan_name
parameters identify the execution plan and/or the SQL plan baseline
to be dropped. At least one of the two must be specified. The following call illustrates this:
ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_492bdb47e8861a89',
plan_name => 'SQL_PLAN_4kayv8zn8c6n93fdbb376');
