180 DBMS_SPM
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.
This chapter contains the following topics:
See Also:
For more information about "Using SQL Plan Management" in the Oracle Database SQL Tuning Guide
180.1 DBMS_SPM Overview
The DBMS_SPM
package allows the user to manage SQL execution plans using SQL plan management.
SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by recording and evaluating the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system. Common usage scenarios where SQL plan management can improve or preserve SQL performance include:
-
A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements, with most of the plan changes resulting in either no performance change or improvement. However, certain plan changes may cause performance regressions. The use of SQL plan baselines significantly minimizes potential performance regressions resulting from a database upgrade.
-
Ongoing system and data changes can impact plans for some SQL statements, potentially causing performance regressions. The use of SQL plan baselines helps to minimize performance regressions and stabilize SQL performance.
-
Deployment of new application modules means introducing new SQL statements into the system. The application software may use appropriate SQL execution plans developed under a standard test configuration for the new SQL statements. If the system production configuration differs significantly from the test configuration, SQL plan baselines can be evolved over time to produce better performance.
180.2 DBMS_SPM 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.
180.3 DBMS_SPM Constants
The DBMS_SPM
package provides constants that can be used for specifying parameter values.
These are shown in the following table. These constants are defined as standard input for the time_limit
parameter of the EVOLVE_SQL_PLAN_BASELINE Function.
Table 180-1 DBMS_SPM Constants
Constant | Type | Value | Description |
---|---|---|---|
|
|
|
Oracle determines the appropriate time spent by the EVOLVE_SQL_PLAN_BASELINE Function. |
|
|
|
There is no limit to the time spent by the EVOLVE_SQL_PLAN_BASELINE Function. |
180.4 DBMS_SPM Examples
These examples will help you understand use of DBMS_SPM
.
Detailed examples are located under the following topics:
180.6 Summary of DBMS_SPM Subprograms
This table lists and briefly describes the DBMS_SPM
package subprograms.
Table 180-2 DBMS_SPM Package Subprograms
Subprogram | Description |
---|---|
Accepts a plan based on the recommendation of an evolve task |
|
ADD_VERIFIED_SQL_PLAN_BASELINE Function |
Finds the plans available for the given SQL_ID in different sources such Cursor Cache, Auto SQL Tuning Set, and Automatic Workload Repository. |
Changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format |
|
Cancels a currently executing evolve task |
|
Sets configuration options for SQL management base, in parameter/value format |
|
Creates an advisor task and sets its parameters |
|
Creates a staging table that used for transporting SQL plan baselines from one system to another |
|
Drops an evolved task |
|
Drops a single plan, or all plans associated with a SQL statement |
|
Evolves SQL plan baselines associated with one or more SQL statements |
|
Executes a previously created evolve task |
|
Implements a plan based on the recommendation of an evolve task |
|
Interrupts a currently executing evolve task |
|
Loads one or more plans present in the cursor cache for a SQL statement |
|
Loads the SQL Management Base (SMB) with SQL plan baselines for a set of SQL statements using the plans from the AWR, and returns the number of plans loaded |
|
Loads plans stored in a SQL tuning set (STS) into SQL plan baselines |
|
Migrates existing stored outlines to SQL plan baselines |
|
Packs (exports) SQL plan baselines from SQL management base into a staging table |
|
Resets an evolve task to its initial state |
|
Resumes a previously interrupted task |
|
Displays the results of an execution of an automatic evolve task. |
|
Displays the results of an evolved task |
|
Sets a parameter of an evolve task |
|
Unpacks (imports) SQL plan baselines from a staging table into SQL management base |
180.6.1 ACCEPT_SQL_PLAN_BASELINE Procedure
The procedure accepts a plan based on the recommendation of an evolve task.
Syntax
DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE ( task_name IN VARCHAR2, object_id IN NUMBER := NULL, task_owner IN VARCHAR2 := NULL, force IN BOOLEAN := FALSE);
Parameters
Table 180-3 ACCEPT_SQL_PLAN_BASELINE Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of task to implement |
|
Identifier of the advisor framework object that represents a single plan. If |
|
Owner of the evolve task. Defaults to the current schema owner. |
|
Accept the plan even if the advisor did not recommend such an action. The default is |
180.6.2 ADD_VERIFIED_SQL_PLAN_BASELINE Function
This function finds the plans available for the given SQL_ID in different sources such Cursor Cache, Auto SQL Tuning Set, and Automatic Workload Repository.
Syntax
DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE (
sql_id IN VARCHAR2
);
Parameters
Table 180-4 ADD_VERIFIED_SQL_PLAN_BASELINE Function Parameters
Parameter | Description |
---|---|
|
The SQL statement identifier, which is used to identify the plans in different sources, such as the cursor cache. |
Return Value
A CLOB containing a report of SQL plan baselines verified, reproduced and accepted.
Usage Notes
SQL plan baselines are created to ensure that the best-known plans are used for the selected SQL statement. These are the steps that the function executes:
- Loads plans from Cursor Cache, Automatic Workload Repository, and Auto
SQL Tuning Set (
SYS_AUTO_STS
) into SQL Plan Management SQL plan history in a non-accepted state. - Uses the SQL Plan Management Evolve Advisor internally to identify the best-performing execution plans. The best-performing plans are accepted.
When the function has completed, it will have accepted the best plans among the plans available from the Cursor Cache, AWR, and the Automatic SQL Tuning Set.
Note:
"Administer SQL Management Object" privilege. is required.Examples
36k32wnz0v0fd
is in
the Cursor Cache with plan hash value 923079310
(this plan is an index
unique
scan).select sql_id, plan_hash_value, sql_text
from v$sql
where sql_text like 'select /* SPM_TEST_QUERY%'
/
SQL_ID PLAN_HASH_VALUE SQL_TEXT
--------------- --------------- --------------------------------------------------------------------
36k32wnz0v0fd 923079310 select /* SPM_TEST_QUERY */ num from example_spm_table where id = 100
923079310
) and a Full Table Scan
(2448381833
).select sql_text, sqlset_name, plan_hash_value
from dba_sqlset_statements
where sql_text like 'select /* SPM_TEST_QUERY%';
SQL_TEXT SQLSET_NAME PLAN_HASH_VALUE
----------------------------------------------------------------------- ------------- ---------------
select /* SPM_TEST_QUERY */ num from example_spm_table where id = 100 SYS_AUTO_STS 923079310
select /* SPM_TEST_QUERY */ num from example_spm_table where id = 100 SYS_AUTO_STS 2448381833
set tab off
set serveroutput on
set pagesize 100
set linesize 250
set long 100000
column report format a200
var rep clob;
BEGIN
:rep := DBMS_SPM. ADD_VERIFIED_SQL_PLAN_BASELINE('36k32wnz0v0fd');
END;
/
select :rep report from dual;
SQL Plan Baselines verified for SQL ID: 36k32wnz0v0fd
------------------------------------------------------------------------------------------
Plan Hash Value Plan Name Reproduced Accepted Source
--------------- ------------------------------ ---------- -------- --------------
923079310 SQL_PLAN_163tr5qgzwmgt05ce4c2e YES YES CURSOR CACHE
2448381833 SQL_PLAN_163tr5qgzwmgt1f191f3e YES NO SQL TUNING SET
-----------------------------------------------------------------------------------------
SQL Handle : SQL_130f372d9ffe4df9
SQL Text : select /* SPM_TEST_QUERY */ num from example_spm_table where id = 100
-----------------------------------------------------------------------------------------
select sql_text, accepted, enabled, sql_handle, plan_name
from dba_sql_plan_baselines
where sql_text like 'select /* SPM_TEST_QUERY%';
SQL_TEXT ACC ENA SQL_HANDLE PLAN_NAME
---------------------------------------------------------------------- --- --- -------------------- ------------------------------
select /* SPM_TEST_QUERY */ num from example_spm_table where id = 100 YES YES SQL_130f372d9ffe4df9 SQL_PLAN_163tr5qgzwmgt05ce4c2e
select /* SPM_TEST_QUERY */ num from example_spm_table where id = 100 NO YES SQL_130f372d9ffe4df9 SQL_PLAN_163tr5qgzwmgt1f191f3e
select * from table(dbms_xplan.display_sql_plan_baseline('SQL_130f372d9ffe4df9'));
--------------------------------------------------------------------------------
SQL handle: SQL_130f372d9ffe4df9
SQL text: select /* SPM_TEST_QUERY */ num from example_spm_table where id = 100
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_163tr5qgzwmgt05ce4c2e Plan id: 923079310
Enabled: YES Fixed: NO Accepted: YES Origin: EVOLVE-LOAD-FROM-CURSOR-CACHE
Plan rows: From Auto SQL Tuning Set
--------------------------------------------------------------------------------
Plan hash value: 923079310
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EXAMPLE_SPM_TABLE | 1 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SPM_TAB_PK | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_163tr5qgzwmgt1f191f3e Plan id: 2448381833
Enabled: YES Fixed: NO Accepted: NO Origin: EVOLVE-LOAD-FROM-STS
Plan rows: From Auto SQL Tuning Set
--------------------------------------------------------------------------------
Plan hash value: 2448381833
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EXAMPLE_SPM_TABLE | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
180.6.3 ALTER_SQL_PLAN_BASELINE Function
This function changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format.
Syntax
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;
Parameters
Table 180-5 ALTER_SQL_PLAN_BASELINE Function Parameters
Parameter | Description |
---|---|
|
SQL statement handle. It identifies plans associated with a SQL statement for an attribute change. If |
|
Name of a specific plan. Default |
|
Name of the plan attribute to set (see table below). |
|
Value of the plan attribute to use (see table below) |
Table 180-6 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 bytes |
Return Values
The number of plans altered.
Usage Notes
When a single plan is specified, one of the 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).
180.6.4 CANCEL_EVOLVE_TASK Procedure
The procedure cancels a currently executing evolve task. All intermediate results are removed from the task.
Syntax
DBMS_SPM.CANCEL_EVOLVE_TASK ( task_name IN VARCHAR2);
Parameters
Table 180-7 CANCEL_EVOLVE_TASK Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of task to cancel |
180.6.5 CONFIGURE Procedure
This procedure sets configuration options for the SQL management base and for the maintenance of SQL plan baselines. You can call this function multiple times, setting a different configuration option each time.
Syntax
DBMS_SPM.CONFIGURE (
parameter_name IN VARCHAR2,
parameter_value IN VARCHAR2 := NULL,
allow IN BOOLEAN := TRUE);
Parameters
Table 180-8 CONFIGURE Procedure Parameters
Parameter | Description |
---|---|
|
Name of parameter to set (see table below). |
|
Value of parameter to use (see table below). The maximum length of |
|
Whether to include ( |
Table 180-9 Names and Values for CONFIGURE Procedure Parameters
Parameter Name | Description | Possible Values |
---|---|---|
|
Action to include ( |
Action name, for example, |
|
Module to include ( The database only uses this filter when |
Module name, for example, |
|
Parsing schema to include ( The database only uses this filter when |
Schema name, for example, |
|
Search pattern to apply to SQL text of The database only uses this filter when |
Text of a SQL statement, for example, |
|
Setting to enable or disable the high-frequency SPM Evolve Advisor task.
Note: See About Automatic SQL Plan Management in the Oracle Database SQL Tuning Guide, which discusses the high frequency SPM Evolve task and explains the difference between real-time and background-verified Automatic SQL Plan Management. |
|
|
Number of weeks to retain unused plans before the database purges them. A null value resets to the default value of 53 weeks, or 1 year plus 1 week. (This retains plans for annually executing queries.) The value of |
5–523 (default is 53) |
|
Maximum percent of The database issues alerts when this amount is exceeded. A null value resets the percentage to the default value of 10%. The value of |
1–50 (default is 10) |
Exceptions
Table 180-10 CONFIGURE Exceptions
Error Number | Description |
---|---|
ORA-38133 |
Invalid parameter name |
ORA-38134 |
Invalid parameter value |
ORA-38150 |
Not enough space for new filter |
ORA-38151 |
Module name too long |
ORA-38152 |
Action name too long |
ORA-38304 |
Missing or invalid user name |
Usage Notes
-
When
parameter_name
isauto_capture_sql_text
, theparameter_value
is an automatic search filter. The filter uses the search pattern ofLIKE
parameter_name
whenallow=>true
. The filter uses the patternNOT LIKE
parameter_name
whenallow=>false
.For all other non-null
parameter_name
values, the search pattern depends on theallow
setting. The parameter uses an equal sign (=
) whenallow=>true
. The parameter uses a not-equal sign (<>
) whenallow=>false
. -
You can configure multiple automatic capture parameters of different types. You cannot specify multiple values for the same parameter. Instead, the values specified for a particular parameter are combined. For example, specifying
auto_capture_sql_text
to be '%TABLE1%
',TRUE
, and '%TABLE2%
',FALSE
will result in matching SQL textLIKE '%TABLE1%'
andNOT LIKE '%TABLE2%'
. The database uses these configuration settings only when the initialization parameterOPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
is set toTRUE
. -
A null value for
parameter_value
removes the filter forparameter_name
entirely. By usingparamter_value=>'%'
in combination withallow=FALSE
, you can filter out all values for a parameter, and then create a separate filter to include only specified values. TheDBA_SQL_MANAGEMENT_CONFIG
view shows the current filters. -
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.
Examples
The following example creates a filter for SQL text that is like SELECT a%
:
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', 'select a%', 'TRUE');
The following example filters out the HR
parsing schema:
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'HR', 'FALSE');
The following example removes any existing filters for SQL text:
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', NULL, NULL);
The following example removes any LIKE
or NOT LIKE
filters for the SQL text select a%
:
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', 'select a%', NULL);
The following example creates a filter with the predicate (action LIKE 'R%') OR (action LIKE '%E_')
:
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_ACTION', 'R%', 'TRUE');
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_ACTION', '%E_', 'TRUE');
The following example creates a filter with the predicate NOT(module LIKE 'LOGGER') AND NOT(module LIKE 'UTIL__')
:
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_MODULE', 'LOGGER', 'FALSE');
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_MODULE', 'UTIL__', 'FALSE');
180.6.6 CREATE_EVOLVE_TASK Function
The function has two overloads, both of which create an advisor task and sets its parameters. This version which takes a SQL handle creates an evolve task in order to evolve one or more plans for a given SQL statement.
Syntax
DBMS_SPM.CREATE_EVOLVE_TASK ( sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL, time_limit IN NUMBER := DBMS_SPM.AUTO_LIMIT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2; DBMS_SPM.CREATE_EVOLVE_TASK ( plan_list IN DBMS_SPM.NAME_LIST, time_limit IN NUMBER := DBMS_SPM.AUTO_LIMIT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
Parameters
Table 180-11 CREATE_EVOLVE_TASK Function Parameters
Parameter | Description |
---|---|
|
Handle of a SQL statement. The default |
|
List of plan names. The plans may belong to different SQL statements. |
|
Plan identifier. The default |
|
Time limit in number of minutes. The time limit is global and it is used in the following manner. The time limit for first non-accepted plan is equal to the input value. The time limit for the second non-accepted plan is equal to (input value - time spent in first plan verification) and so on. The default |
|
Evolve task name |
|
Description of the task (maximum 256 characters) |
Return Values
SQL evolve task unique name
180.6.7 CREATE_STGTAB_BASELINE Procedure
This procedure creates a staging table used for transporting SQL plan baselines from one system to another.
Syntax
DBMS_SPM.CREATE_STGTAB_BASELINE (
table_name IN VARCHAR2,
table_owner IN VARCHAR2 := NULL,
tablespace_name IN VARCHAR2 := NULL);
Parameters
Table 180-12 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. The default, |
|
Name of the tablespace. The default, |
Usage Notes
The creation of staging table is the first step. To migrate SQL plan baselines from one system to another, the user or 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
-
Copy the staging table from the source system to the target system. For example, you can use Oracle Data Pump to export and import the staging table.
-
Select SQL plan baselines from the staging table and unpack them into the target system
180.6.8 DROP_EVOLVE_TASK Procedure
The procedure drops an evolved task.
Syntax
DBMS_SPM.DROP_EVOLVE_TASK (
task_name IN VARCHAR2);
Parameters
Table 180-13 DROP_EVOLVE_TASK Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of the task that you want to drop |
180.6.9 DROP_SQL_PLAN_BASELINE Function
This function drops a single plan, or all plans associated with a SQL statement.
Syntax
DBMS_SPM.DROP_SQL_PLAN_BASELINE ( sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL) RETURN PLS_INTEGER;
Parameters
Table 180-14 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 |
Return Values
The number of plans dropped
180.6.10 EVOLVE_SQL_PLAN_BASELINE Function
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.
Syntax
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;
Parameters
Table 180-15 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
|
Return Values
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.
Usage Notes
Invoking this subprogram requires the ADMINISTER
SQL
MANAGEMENT
OBJECT
privilege.
180.6.11 EXECUTE_EVOLVE_TASK Function
The function executes a previously created evolve task.
Syntax
DBMS_SPM.EXECUTE_EVOLVE_TASK ( task_name IN VARCHAR2, execution_name IN VARCHAR2 := NULL, execution_desc IN VARCHAR2 := NULL); RETURN VARCHAR2;
Parameters
Table 180-16 EXECUTE_EVOLVE_TASK Function Parameters
Parameter | Description |
---|---|
|
Evolve task name |
|
Name to qualify and identify an execution. If not specified, it is generated by the advisor and returned by the function. |
|
Description of the execution (maximum 256 characters) |
Return Values
Name of the new execution
180.6.12 IMPLEMENT_EVOLVE_TASK Function
The function implements all the actions recommended by an evolve task.
Syntax
DBMS_SPM.IMPLEMENT_EVOLVE_TASK ( task_name IN VARCHAR2, task_owner IN VARCHAR2 := NULL, execution_name IN VARCHAR2 := NULL, force IN BOOLEAN := FALSE) RETURN NUMBER;
Parameters
Table 180-17 IMPLEMENT_EVOLVE_TASK Function Parameters
Parameter | Description |
---|---|
|
Identifier of task to report |
|
Owner of the evolve task. Defaults to the current schema owner. |
|
Name to qualify and identify an execution. If |
|
Accept all plans even if the advisor did not recommend such an action. The default is |
Return Values
The number of plans accepted
180.6.13 INTERRUPT_EVOLVE_TASK Procedure
The procedure interrupts a currently executing evolve task. The task ends its operations as at a normal exit and the user can access the intermediate results. The task can be resumed later.
Syntax
DBMS_SPM.INTERRUPT_EVOLVE_TASK ( task_name IN VARCHAR2);
Parameters
Table 180-18 INTERRUPT_EVOLVE_TASK Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of task to interrupt |
180.6.14 LOAD_PLANS_FROM_AWR Function
This function loads the SQL Management Base (SMB) with SQL plan baselines for a set of SQL statements using the plans from the AWR, and returns the number of plans loaded.
Syntax
DBMS_SPM.LOAD_PLANS_FROM_AWR begin_snap IN NUMBER, end_snap IN NUMBER, basic_filter IN VARCHAR2 := NULL, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES', commit_rows IN NUMBER := 1000) RETURN PLS_INTEGER;
Parameters
Table 180-19 LOAD_PLANS_FROM_AWR Function Parameters
Parameter | Description |
---|---|
|
Begin snapshot |
|
End snapshot |
|
SQL predicate to filter the SQL from AWR. Specifies the SQL predicate that filters the SQL from the shared SQL area defined on attributes of the |
|
Default |
|
Default |
|
Number of SQL plans to load before doing a periodic commit. |
|
The DBID that is used for imported or PDB-level AWR data. |
Usage Notes
Requires the Administer SQL Management Object
privilege
See Also:
For information on the SQLSET_ROW
objects, see SQLSET_ROW Object Type.
180.6.15 LOAD_PLANS_FROM_CURSOR_CACHE Functions
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.
Syntax
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;
Parameters
Table 180-20 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 is used to identify the SQL plan baseline into which the plan(s) are 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 ' |
Return Values
Number of plans loaded
Usage Notes
Invoking this subprogram requires the ADMINISTER
SQL
MANAGEMENT
OBJECT
privilege.
180.6.16 LOAD_PLANS_FROM_SQLSET Function
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 function can be used to seed SQL management base with new SQL plan baselines.
Syntax
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;
Parameters
Table 180-21 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. |
Return Values
The number of plans loaded
Usage Notes
-
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 function.
-
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.
180.6.17 MIGRATE_STORED_OUTLINE Functions
This function migrates stored outlines for one or more SQL statements to plan baselines in the SQL management base (SMB). Users can specify which stored outline(s) to be migrated based on outline name, SQL text, or outline category, or migrate all stored outlines in the system to SQL plan baselines.
This second overload of the function migrates stored outlines for one or more SQL statements to plan baselines in the SQL management base (SMB) given one or more outline names.
Syntax
DBMS_SPM.MIGRATE_STORED_OUTLINE ( attribute_name IN VARCHAR2, attribute_value IN CLOB, fixed IN VARCHAR2 := 'NO') RETURN CLOB; DBMS_SPM.MIGRATE_STORED_OUTLINE ( outln_list IN DBMS_SPM.NAME_LIST, fixed IN VARCHAR2 := 'NO') RETURN CLOB;
Parameters
Table 180-22 MIGRATE_STORED_OUTLINE Function Parameters
Parameter | Description |
---|---|
|
Specifies the type of parameter used in
|
|
Based on
|
|
|
|
List of outline names to be migrated |
Return Values
A CLOB
containing a formatted report to describe the statistics during the migration, including:
-
Number of stored outlines successfully migrated
-
Number of stored outlines (and also the corresponding outline names) failed to be migrated and the reasons for the failure
Usage Note
-
When the user specifies an outline name, the function migrates stored outlines to plan baseline based on given outline name, which uniquely identifies a single stored outline to be migrated.
-
When the user specifies SQL text, the function migrates all stored outlines created for a given SQL statement. A single SQL statement can have multiple stored outlines created for it under different category names. One plan baseline plan is created for each stored outline. The new plan baselines have category names set to
DEFAULT
. The module name of a plan baseline is set to be the same as the stored outline. -
When the user specifies a category name, the function migrates all stored outlines with the given category name. Only one stored outline exists per category per SQL statement. One plan baseline is created for each stored outline.
-
When user specifies to migrate
all
, the function migrates all stored outlines in the system to plan baselines. One plan baseline is created for each stored outline.
180.6.18 PACK_STGTAB_BASELINE Function
This function packs (exports) SQL plan baselines from SQL management base into a staging table.
Syntax
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;
Parameters
Table 180-23 PACK_STGTAB_BASELINE Function Parameters
Parameter | Description |
---|---|
|
Name of staging table into which SQL plan baselines are 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) |
Return Values
Number of SQL plan baselines packed
180.6.19 RESET_EVOLVE_TASK Procedure
This procedure resets an evolve task to its initial state.
All intermediate results will be removed from the task. Call this procedure on a task that is not currently executing.
Syntax
DBMS_SPM.RESET_EVOLVE_TASK ( task_name IN VARCHAR2);
Parameters
Table 180-24 RESET_EVOLVE_TASK Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of task to reset |
180.6.20 RESUME_EVOLVE_TASK Procedure
The procedure resumes a previously interrupted task.
Syntax
DBMS_SPM.RESUME_EVOLVE_TASK ( task_name IN VARCHAR2);
Parameters
Table 180-25 RESUME_EVOLVE_TASK Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of task to resume |
180.6.21 REPORT_AUTO_EVOLVE_TASK Function
The procedure displays the results of an execution of an automatic evolve task.
Syntax
DBMS_SPM.REPORT_AUTO_EVOLVE_TASK ( type IN VARCHAR2 := TYPE_TEXT, level IN VARCHAR2 := LEVEL_TYPICAL, section IN VARCHAR2 := SECTION_ALL, object_id IN NUMBER := NULL, execution_name IN VARCHAR2 := NULL) RETURN CLOB;
Parameters
Table 180-26 REPORT_AUTO_EVOLVE_TASK Function Parameters
Parameter | Description |
---|---|
|
Type of the report. Possible values are |
|
Format of the report. Possible values are |
|
Particular section in the report. Possible values are: |
|
Identifier of the advisor framework object that represents a single plan. If |
|
Name to qualify and identify an execution. If |
Return Values
The report
180.6.22 REPORT_EVOLVE_TASK Function
The procedure displays the results of an evolved task.
Syntax
DBMS_SPM.REPORT_EVOLVE_TASK ( task_name IN VARCHAR2, type IN VARCHAR2 := TYPE_TEXT, level IN VARCHAR2 := LEVEL_TYPICAL, section IN VARCHAR2 := SECTION_ALL, object_id IN NUMBER := NULL, task_owner IN VARCHAR2 := NULL, execution_name IN VARCHAR2 := NULL) RETURN CLOB;
Parameters
Table 180-27 REPORT_EVOLVE_TASK Function Parameters
Parameter | Description |
---|---|
|
Identifier of task to report |
|
Type of the report. Possible values are |
|
Format of the report. Possible values are |
|
Particular section in the report. Possible values are: |
|
Identifier of the advisor framework object that represents a single plan. If |
|
Owner of the evolve task. Defaults to the current schema owner. |
|
Name to qualify and identify an execution. If |
Return Values
The report
180.6.23 SET_EVOLVE_TASK_PARAMETER Procedure
The procedure sets a parameter of an evolve task, either a VARCHAR2
or a NUMBER
.
Syntax
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER (
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value IN NUMBER);
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER (
task_name IN VARCHAR2 := NULL,
parameter IN VARCHAR2,
value IN VARCHAR2);
Parameters
Table 180-28 SET_EVOLVE_TASK_PARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
Evolve task name |
|
Name of the parameter to set (see following table) |
|
Value of the parameter (see following table) |
The following table describes parameters for the SET_EVOLVE_TASK_PARAMETER
procedure.
Table 180-29 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER Parameters
Parameter | Description | Default |
---|---|---|
|
Determines which sources to search for additional plans:
You can combine multiple values with the plus sign ( |
The default depends on whether the SPM Evolve Advisor task is automated or manual:
|
|
Determines which alternative plans should be loaded:
You can combine multiple values with the plus sign ( |
|
|
Specifies the maximum number of plans to load in total (that is, not the limit for each SQL statement). |
The default depends on whether the SPM Evolve Advisor task is automated or manual:
|
|
Specifies whether to accept recommended plans automatically. When When |
|
|
Global time limit in seconds. This is the total time allowed for the task. |
The default depends on whether the SPM Evolve Advisor task is automated or manual:
|
See Also:
Oracle Database Licensing Information User Manual for details on which features are supported for different editions and services
180.6.24 UNPACK_STGTAB_BASELINE Function
This function unpacks (imports) SQL plan baselines from a staging table into SQL management base.
Syntax
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;
Parameters
Table 180-30 UNPACK_STGTAB_BASELINE Function Parameters
Parameter | Description |
---|---|
|
Name of staging table from which SQL plan baselines are 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) |
Return Values
Number of plans unpacked