The DBMS_SPM
package supports the SQL plan management feature by providing an interface for the DBA or other user to perform controlled manipulation of plan history and SQL plan baselines maintained for various SQL statements.
See Also:
For more information about "Using SQL Plan Management" in the Oracle Database Performance Tuning GuideThis chapter contains the following topics:
Security Model
The package is owned by SYS
. The EXECUTE
package privilege is required to execute its procedures. Any user granted the ADMINISTER
SQL
MANAGEMENT
OBJECT
privilege is able to execute the DBMS_SPM
package.
This table list the package subprograms in alphabetical order.
Table 121-1 DBMS_SPM Package Subprograms
Subprogram | Description |
---|---|
Changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format |
|
Sets configuration options for SQL management base, in parameter/value format |
|
Creates a staging table that will be used for the purpose of transporting SQL plan baselines from one system to another |
|
drops a single plan, or all plans associated with a SQL statement |
|
Evolves SQL plan baselines associated with one or more SQL statements |
|
Loads one or more plans present in the cursor cache for a SQL statement |
|
Loads plans stored in a SQL tuning set (STS) into SQL plan baselines |
|
Packs (exports) SQL plan baselines from SQL management base into a staging table |
|
Unpacks (imports) SQL plan baselines from a staging table into SQL management base |
This function changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format.
DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL, attribute_name IN VARCHAR2, attribute_value IN VARCHAR2) RETURN PLS_INTEGER;
Table 121-2 ALTER_SQL_PLAN_BASELINE Function Parameters
Parameter | Description |
---|---|
|
SQL statement handle. It identifies plans associated with a SQL statement for an attribute change. If |
|
Plan name. It identifies a specific plan. Default |
|
Name of plan attribute to set (see table below). |
|
Value of plan attribute to use (see table below) |
Table 121-3 Names & Values for ALTER_SQL_PLAN_BASELINE Function Parameters
Name | Description | Possible Values |
---|---|---|
|
' |
' |
|
' |
' |
|
' |
' |
|
Name of the plan |
String of up to 30-characters |
|
Plan description. |
String of up to 500-characters |
The number of plans altered.
When a single plan is specified, one of various statuses, or plan name, or description can be altered. When all plans for a SQL statement are specified, one of various statuses, or description can be altered. This function can be called numerous times, each time setting a different plan attribute of same plan(s) or different plan(s).
This procedure sets configuration options for SQL management base, in parameter/value format. This function can be called numerous times, each time setting a different configuration option.
DBMS_SPM.CONFIGURE ( parameter_name IN VARCHAR2, parameter_value IN NUMBER);
Table 121-4 CONFIGURE Procedure Parameters
Parameter | Description |
---|---|
|
Name of parameter to set (see table below) |
|
Value of parameter to use (see table below) |
Table 121-5 Names & Values for CONFIGURE Procedure Parameters
Name | Description | Possible Values | Default Value |
---|---|---|---|
|
Maximum percent of |
1,2, …, 50 |
10 |
|
Number of weeks to retain unused plans before they are purged |
5,6, …, 523 |
53 |
The default space budget for SQL management base is no more than ten percent of the size of SYSAUX
tablespace. The space budget can be set to a maximum of 50%. The default unused plan retention period is one year and one week, which means a plan will be automatically purged if it has not been used for more than a year. The retention period can be set to a maximum of 523 weeks (i.e. a little over 10 years).
When the space occupied by SQL management base exceeds the defined space budget limit, a weekly database alert is generated.
This procedure creates a staging table that will be used for the purpose of transporting SQL plan baselines from one system to another.
DBMS_SPM.CREATE_STGTAB_BASELINE ( table_name IN VARCHAR2, table_owner IN VARCHAR2 := NULL, tablespace_name IN VARCHAR2 := NULL);
Table 121-6 CREATE_STGTAB_BASELINE Procedure Parameters
Parameter | Description |
---|---|
|
Name of staging table to create for the purpose of packing and unpacking SQL plan baselines |
|
Name of owner of the staging table. Default |
|
Name of tablespace. Default NULL means create staging table in the default tablespace. |
The creation of staging table is the first step. To migrate SQL plan baselines from one system to another, the user/DBA has to perform a series of steps as follows:
Create a staging table in the source system
Select SQL plan baselines in the source system and pack them into the staging table
Export staging table into a flat file using Oracle EXP utility or Data Pump
Transfer flat file to the target system
Import staging table from the flat file using Oracle IMP utility or Data Pump
Select SQL plan baselines from the staging table and unpack them into the target system
This function drops a single plan, or all plans associated with a SQL statement.
DBMS_SPM.DROP_SQL_PLAN_BASELINE ( sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL) RETURN PLS_INTEGER;
Table 121-7 DROP_SQL_PLAN_BASELINE Function Parameters
Parameter | Description |
---|---|
|
SQL statement handle. It identifies plans associated with a SQL statement that are to be dropped. If |
|
Plan name. It identifies a specific plan. Default |
The number of plans dropped
This function evolves SQL plan baselines associated with one or more SQL statements. A SQL plan baseline is evolved when one or more of its non-accepted plans is changed to an accepted plan or plans. If interrogated by the user (parameter verify
= 'YES
'), the execution performance of each non-accepted plan is compared against the performance of a plan chosen from the associated SQL plan baseline. If the non-accepted plan performance is found to be better than SQL plan baseline performance, the non-accepted plan is changed to an accepted plan provided such action is permitted by the user (parameter commit
= 'YES').
The second form of the function employs a plan list format.
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL, time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT, verify IN VARCHAR2 := 'YES', commit IN VARCHAR2 := 'YES') RETURN CLOB; DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( plan_list IN DBMS_SPM.NAME_LIST, time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT, verify IN VARCHAR2 := 'YES', commit IN VARCHAR2 := 'YES') RETURN CLOB;
Table 121-8 EVOLVE_SQL_PLAN_BASELINE Function Parameters
Parameter | Description |
---|---|
|
SQL statement identifier. Unless |
|
Plan identifier. Default |
|
A list of plan names. Each plan in the list can belong to same or different SQL statement. |
|
Time limit in number of minutes. This applies only if
|
|
Specifies whether to execute the plans and compare the performance before changing non-accepted plans into accepted plans. A performance verification involves executing a non-accepted plan and a plan chosen from corresponding SQL plan baseline and comparing their performance statistics. If non-accepted plan shows performance improvement, it is changed to an accepted plan.
|
|
Specifies whether to update the
|
A CLOB
containing a formatted text report showing non-accepted plans in sequence, each with a possible change of its ACCEPTED
status, and if verify = 'YES
' the result of their performance verification.
Invoking this subprogram requires the ADMINISTER
SQL
MANAGEMENT
OBJECT
privilege.
This function loads one or more plans present in the cursor cache for a SQL statement, or a set of SQL statements. It has four overloads: using SQL statement text, using SQL handle, using SQL ID, or using attribute_name and attribute_value pair.
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, sql_text IN CLOB, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES') RETURN PLS_INTEGER; DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, sql_handle IN VARCHAR2, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES') RETURN PLS_INTEGER; DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES') RETURN PLS_INTEGER; DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( attribute_name IN VARCHAR2, attribute_value IN VARCHAR2, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES') RETURN PLS_INTEGER;
Table 121-9 LOAD_PLANS_FROM_CURSOR_CACHE Function Parameters
Parameter | Description |
---|---|
|
SQL statement identifier. Identifies a SQL statement in the cursor cache. Note: In the third overload the text of identified SQL statement is extracted from cursor cache and it is used to identify the SQL plan baseline into which the plan(s) will be loaded. If the SQL plan baseline doesn't exist it is created. |
|
Plan identifier. Default |
|
SQL text to use in identifying the SQL plan baseline into which the plans are loaded. If the SQL plan baseline does not exist, it is created. The use of text is crucial when the user tunes a SQL statement by adding hints to its text and then wants to load the resulting plan(s) into the SQL plan baseline of the original SQL statement. |
|
SQL handle to use in identifying the SQL plan baseline into which the plans are loaded. The |
|
Default ' |
|
One of possible attribute names:
|
|
Attribute value is used as a search pattern of |
|
Default ' |
Number of plans loaded
Invoking this subprogram requires the ADMINISTER
SQL
MANAGEMENT
OBJECT
privilege.
This function loads plans stored in a SQL tuning set (STS) into SQL plan baselines. The plans loaded from STS are not verified for performance but added as accepted plans to existing or new SQL plan baselines. This procedure can be used to seed SQL management base with new SQL plan baselines.
DBMS_SPM.LOAD_PLANS_FROM_SQLSET ( sqlset_name IN VARCHAR2, sqlset_owner IN VARCHAR2 := NULL, basic_filter IN VARCHAR2 := NULL, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES' commit_rows IN NUMBER := 1000) RETURN PLS_INTEGER;
Table 121-10 LOAD_PLANS_FROM_SQLSET Function Parameters
Parameter | Description |
---|---|
|
Name of the STS from where the plans are loaded into SQL plan baselines |
|
Owner of STS. |
|
A filter applied to the STS to select only qualifying plans to be loaded. The filter can take the form of any |
|
Default ' |
|
Default ' |
|
Number of SQL plans to load before doing a periodic commit. This helps to shorten the undo log. |
The number of plans loaded
To load plans from a remote system, first load the plans into an STS on the remote system, export/import the STS from remote to local system, and then use this procedure.
To load plans from Automatic Workload Repository (AWR), first load the plans stored in AWR snapshots into an STS, and then use this procedure.
The user can also capture plans resident in the cursor cache for one or more SQL statements into an STS, and then use this procedure.
This function packs (exports) SQL plan baselines from SQL management base into a staging table.
DBMS_SPM.PACK_STGTAB_BASELINE ( table_name IN VARCHAR2, table_owner IN VARCHAR2 := NULL, sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL, sql_text IN CLOB := NULL, creator IN VARCHAR2 := NULL, origin IN VARCHAR2 := NULL, enabled IN VARCHAR2 := NULL, accepted IN VARCHAR2 := NULL, fixed IN VARCHAR2 := NULL, module IN VARCHAR2 := NULL, action IN VARCHAR2 := NULL) RETURN NUMBER;
Table 121-11 PACK_STGTAB_BASELINE Function Parameters
Parameter | Description |
---|---|
|
Name of staging table into which SQL plan baselines will be packed (case insensitive unless double quoted) |
|
Name of staging table owner.Default |
|
SQL handle (case sensitive) |
|
Plan name (case sensitive, % wildcards accepted) |
|
SQL text string (case sensitive, % wildcards accepted) |
|
Creator of SQL plan baseline (case insensitive unless double quoted) |
|
Origin of SQL plan baseline, should be |
|
Must be ' |
|
Must be ' |
|
Must be ' |
|
Module (case sensitive) |
|
Action (case sensitive) |
Number of SQL plan baselines packed
This function unpacks (imports) SQL plan baselines from a staging table into SQL management base.
DBMS_SPM.UNPACK_STGTAB_BASELINE ( table_name IN VARCHAR2, table_owner IN VARCHAR2 := NULL, sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL, sql_text IN CLOB := NULL, creator IN VARCHAR2 := NULL, origin IN VARCHAR2 := NULL, enabled IN VARCHAR2 := NULL, accepted IN VARCHAR2 := NULL, fixed IN VARCHAR2 := NULL, module IN VARCHAR2 := NULL, action IN VARCHAR2 := NULL) RETURN NUMBER;
Table 121-12 UNPACK_STGTAB_BASELINE Function Parameters
Parameter | Description |
---|---|
|
Name of staging table from which SQL plan baselines will be unpacked (case insensitive unless double quoted) |
|
Name of staging table owner.Default NULL means current schema is the table owner |
|
SQL handle (case sensitive) |
|
Plan name (case sensitive, % wildcards accepted) |
|
SQL text string (case sensitive, % wildcards accepted) |
|
Creator of SQL plan baseline (case insensitive unless double quoted) |
|
Origin of SQL plan baseline, should be |
|
Must be ' |
|
Must be ' |
|
Must be ' |
|
Module (case sensitive) |
|
Action (case sensitive) |
Number of plans unpacked