185 DBMS_SQLPA
The DBMS_SQLPA package provides the interface to implement the SQL Performance Analyzer.
The chapter contains the following topics:
185.1 DBMS_SQLPA Overview
The DBMS_SQLPA
package provides a capacity to help users predict the impact of system environment changes on the performance of a SQL workload. The interface lets users build and then compare two different versions of the workload performance, analyze the differences between the two versions, and unmask the SQL statements that might be impacted by the changes.
The package provides a task-oriented interface to implement the SQL Performance Analyzer. For example
-
You use the CREATE_ANALYSIS_TASK Functions to create an analysis task for a single statement or a group of SQL statements.s
-
The EXECUTE_ANALYSIS_TASK Function & Procedure executes a previously created analysis task.
-
The REPORT_ANALYSIS_TASK Function displays the results of an analysis task.
185.2 DBMS_SQLPA Security Model
This package is available to PUBLIC
and performs its own security checking. All analysis task interfaces (XXX_ANALYSIS_TASK
) require privilege ADVISOR
.
185.3 Summary of DBMS_SQLPA Subprograms
This table lists the DBMS_SQLPA
subprograms and briefly describes them.
Table 185-1 DBMS_SQLPA Package Subprograms
Subprogram | Description |
---|---|
Cancels the currently executing task analysis of one or more SQL statements |
|
Creates an advisor task to process and analyze one or more SQL statements |
|
Drops a SQL analysis task |
|
Executes a previously created analysis task |
|
Interrupts the currently executing analysis task |
|
Displays the results of an analysis task |
|
Resets the currently executing analysis task to its initial state |
|
Resumes a previously interrupted analysis task that was created to process a SQL tuning set. |
|
Sets the SQL analysis task parameter value |
|
Sets the SQL analysis task parameter default value |
185.3.1 CANCEL_ANALYSIS_TASK Procedure
This procedure cancels the currently executing analysis task. All intermediate result data is removed from the task.
Syntax
DBMS_SQLPA.CANCEL_ANALYSIS_TASK( task_name IN VARCHAR2);
Parameters
Table 185-2 CANCEL_ANALYSIS_TASK Procedure Parameters
Parameter | Description |
---|---|
|
Name of the task to cancel |
Examples
Canceling a task when there is a need to stop it executing and it is not required to view any already-completed results:
EXEC DBMS_SQLPA.CANCEL_ANALYSIS_TASK(:my_task);
185.3.2 CREATE_ANALYSIS_TASK Functions
These functions create an advisor task to process and analyze one or more SQL statements.
Note:
A multitenant container database is the only supported architecture in Oracle Database 21c and later releases. While the documentation is being revised, legacy terminology may persist. In most cases, "database" and "non-CDB" refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, "non-CDB" refers to a non-CDB from a previous release.
You can use different forms of this function to:
-
Create an analysis task for a single statement given its text.
-
Create an analysis task for a single statement from the cursor cache given its identifier.
-
Create an analysis task for a single statement from the workload repository given a range of snapshot identifiers.
-
Create an analysis task for a SQL tuning set.
In all cases, the function creates an advisor task and sets its parameters.
Syntax
SQL text format. This form of the function is called to prepare the analysis of a single statement given its text.
DBMS_SQLPA.CREATE_ANALYSIS_TASK( sql_text IN CLOB, bind_list IN sql_binds := NULL, parsing_schema IN VARCHAR2 := NULL, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
SQL ID format. This form of the function is called to prepare the analysis of a single statement from the cursor cache given its identifier.
DBMS_SQLPA.CREATE_ANALYSIS_TASK( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, task_name IN VARCHAR2 := NULL, con_name IN VARCHAR2 DEFAULT, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
Workload Repository format. This form of the function is called to prepare the analysis of a single statement from the workload repository given a range of snapshot identifiers.
DBMS_SQLPA.CREATE_ANALYSIS_TASK( dbid IN NUMBER DEFAULT, begin_snap IN NUMBER, end_snap IN NUMBER, sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) con_name IN VARCHAR2 DEFAULT, RETURN VARCHAR2;
SQLSET format. This form of the function is called to prepare the analysis of a SQL tuning set.
DBMS_SQLPA.CREATE_ANALYSIS_TASK( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, con_name IN VARCHAR2 DEFAULT, order_by IN VARCHAR2 := NULL, top_sql IN VARCHAR2 := NULL, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL sqlset_owner IN VARCHAR2 := NULL) RETURN VARCHAR2;
Parameters
Table 185-3 CREATE_ANALYSIS_TASK Function Parameters
Parameter | Description |
---|---|
|
Text of a SQL statement |
|
A set of bind values |
|
Name of the schema where the statement can be compiled |
|
Optional analysis task name |
|
The |
|
Container for the SPA task. The semantics depend on the function format: For the SQL ID format, this parameter specifies the container from which the database fetches the SQL statement for using with SPA. SPA will analyze the statement in this container. If null, then the database uses the current PDB for SPA analysis. For the AWR format, this parameter specifies the container from whose AWR data the database fetches the SQL statement for using with SPA. SPA will analyze the statement in this container. If null, then the database uses the current PDB for SPA analysis. The following statements are true of all function formats:
|
|
Description of the SQL analysis task to a maximum of 256 characters |
|
Identifier of a SQL statement |
|
Hash value of the SQL execution plan |
|
Begin snapshot identifier |
|
End snapshot identifier |
|
SQL tuning set name |
|
SQL predicate to filter the SQL from the SQL tuning set |
|
Order-by clause on the selected SQL |
|
Top N SQL after filtering and ranking |
|
The owner of the SQL tuning set, or |
Return Values
A SQL analysis task name that is unique by user (two different users can give the same name to their advisor tasks).
Examples
variable stmt_task VARCHAR2(64); variable sts_task VARCHAR2(64); -- Sql text format EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( sql_text => 'select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-00'')'); -- Sql id format (cursor cache) EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( sql_id => 'ay1m3ssvtrh24'); -- Workload repository format exec :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( begin_snap => 1, end_snap => 2, sql_id => 'ay1m3ssvtrh24'); -- Sql tuning set format (first we need to load an STS, then analyze it) EXEC :sts_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( - sqlset_name => 'my_workload', - order_by => 'BUFFER_GETS', - description => 'process workload ordered by buffer gets');
185.3.3 DROP_ANALYSIS_TASK Procedure
This procedure drops a SQL analysis task.The task and all its result data are deleted.
Syntax
DBMS_SQLPA.DROP_ANALYSIS_TASK( task_name IN VARCHAR2);
Parameters
Table 185-4 DROP_ANALYSIS_TASK Procedure Parameters
Parameter | Description |
---|---|
|
The name of the analysis task to drop |
185.3.4 EXECUTE_ANALYSIS_TASK Function & Procedure
This function and procedure executes a previously created analysis task, the function version returning the new execution name.
Syntax
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name IN VARCHAR2, execution_type IN VARCHAR2 := 'test execute', execution_name IN VARCHAR2 := NULL, execution_params IN dbms_advisor.argList := NULL, execution_desc IN VARCHAR2 := NULL) RETURN VARCHAR2;
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name IN VARCHAR2, execution_type IN VARCHAR2 := 'test execute', execution_name IN VARCHAR2 := NULL, execution_params IN dbms_advisor.argList := NULL, execution_desc IN VARCHAR2 := NULL);
Parameters
Table 185-5 EXECUTE_ANALYSIS_TASK Function & Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of the task to execute |
|
Type of the action to perform by the function. If
|
|
A name to qualify and identify an execution. If not specified, it will be generated by the advisor and returned by function. |
|
List of parameters (name, value) for the specified execution. The execution parameters have effect only on the execution for which they are specified. They will override the values for the parameters stored in the task (set through the SET_ANALYSIS_DEFAULT_PARAMETER Procedures). |
|
A 256-length string describing the execution |
Usage Notes
SQL performance analyzer task can be executed multiples times without having to reset it. For example, when a task is created to perform a change impact analysis on a SQL workload, the created task has to be executed before making any change in the system environment to build a version of the workload that will be used as a reference for performance analysis. Once the change has been made, a second execution is required to build the post-change version of the workload. Finally, the task has to be executed a third time to let the advisor analyze and compare the performance of the workload in both versions.
Examples
1. Create a task with a purpose of change impact analysis
EXEC :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK( sqlset_name => 'my_sts');
2. Make baseline or the before change execution
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => :tname, execution_type => 'test execute', execution_name => 'before_change');
3. Make change
...
4. Make the after change version of the workload performance
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => :tname, - execution_type => 'test execute', execution_name => 'after_change')
5. Compare the two versions of the workload
By default we always compare the results of the two last executions. The SQL Performance Analyzer uses the elapsed_time
as a default metric for comparison. Here we are changing it to buffer_gets
instead.
EXEC DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER( :tname,'comparison_metric', 'buffer_gets'); EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => :tname, - execution_type => 'compare performance', - execution_name => 'after_change');
Use the following call if you would like to explicitly specify the two executions to compare as well as the comparison metric to use.
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => :tname, - execution_type => 'compare performance', execution_params => dbms_advisor.arglist( 'execution_name1', 'before_change', 'execution_name2', 'after_change', 'comparison_metric', 'buffer_gets'));
185.3.5 INTERRUPT_ANALYSIS_TASK Procedure
This procedure interrupts the currently executing analysis task. All intermediate result data will not be removed from the task.
Syntax
DBMS_SQLPA.INTERRUPT_ANALYSIS_TASK( task_name IN VARCHAR2);
Parameters
Table 185-6 INTERRUPT_ANALYSIS_TASK Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of the analysis task to interrupt |
Examples
EXEC DBMS_SQLPA.INTERRUPT_ANALYSIS_TASK(:my_task);
185.3.6 REPORT_ANALYSIS_TASK Function
This procedure displays the results of an analysis task.
Syntax
DBMS_SQLPA.REPORT_ANALYSIS_TASK( task_name IN VARCHAR2, type IN VARCHAR2 := 'TEXT', level IN VARCHAR2 := 'TYPICAL', section IN VARCHAR2 := 'SUMMARY', object_id IN NUMBER := NULL, top_sql IN NUMBER := 100, execution_name IN VARCHAR2 := NULL, task_owner IN VARCHAR2 := NULL, order_by IN VARCHAR2 := NULL) RETURN CLOB;
Parameters
Table 185-7 REPORT_ANALYSIS_TASK Function Parameters
Parameter | Description |
---|---|
|
Name of the task to report |
|
Type of the report to produce. Possible values are |
|
Level of detail in the report:
|
|
Optionally limit the report to a single section (
|
|
Identifier of the advisor framework object that represents a given SQL in a tuning set (STS) |
|
Number of SQL statements in a STS for which the report is generated |
|
Name of the task execution to use. If |
|
Owner of the relevant analysis task. Defaults to the current schema owner. |
|
How to sort SQL statements in the report (summary and body). Possible values:
|
Return Values
A CLOB
containing the desired report.
Usage Notes
ACTIVE
reports have a rich, interactive user interface similar to Enterprise Manager while not requiring any EM installation. The report file built is in HTML
format so it can be interpreted by most modern browsers. The code powering the active report is downloaded transparently by the web browser when the report is first viewed, hence viewing it requires outside connectivity.
Examples
-- Get the whole report for the single statement case. SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:stmt_task) from dual; -- Show me the summary for the sts case. SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY') FROM DUAL; -- Show me the findings for the statement I'm interested in. SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:sts_task, 'TEXT', 'TYPICAL', 'ALL', 5) from dual;
185.3.7 RESET_ANALYSIS_TASK Procedure
This procedure is called on an analysis task that is not currently executing to prepare it for re-execution.
All intermediate result data will be deleted.
Syntax
DBMS_SQLPA.RESET_ANALYSIS_TASK( task_name IN VARCHAR2);
Parameters
Table 185-8 RESET_ANALYSIS_TASK Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of the analysis task to reset |
Examples
-- reset and re-execute a task EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK(:sts_task); -- re-execute the task EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(:sts_task);
185.3.8 RESUME_ANALYSIS_TASK Procedure
This procedure resumes a previously interrupted or FAILED
(with a fatal error) task execution.
Syntax
DBMS_SQLPA.RESUME_ANALYSIS_TASK( task_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL);
Parameters
Table 185-9 RESUME_ANALYSIS_TASK Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of the analysis task to resume |
|
A SQL predicate to filter the SQL from the SQL tuning set. Note that this filter will be applied in conjunction with the basic filter (parameter |
Usage Notes
Resuming a single SQL analysis task (a task that was created to analyze a single SQL statement as compared to a SQL Tuning Set) is not supported.
Examples
-- Interrupt the task EXEC DBMS_SQLPA.INTERRUPT_ANALYSIS_TASK(:conc_task); -- Once a task is interrupted, we can elect to reset it, resume it, or check -- out its results and then decide. For this example we will just resume. EXEC DBMS_SQLPA.RESUME_ANALYSIS_TASK(:conc_task);
185.3.9 SET_ANALYSIS_TASK_PARAMETER Procedures
This procedure sets the SQL analysis task parameter value.
Syntax
This form of the procedure updates the value of a SQL analysis parameter of type VARCHAR2
.
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN VARCHAR2, test_execute_dop IN NUMBER DEFAULT 0, compare_resultset IN BOOLEAN DEFAULT TRUE);
This form of the procedure updates the value of a SQL analysis parameter of type NUMBER
.
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN NUMBER test_execute_dop IN NUMBER DEFAULT 0, compare_resultset IN BOOLEAN DEFAULT TRUE);
Parameters
Table 185-10 SET_ANALYSIS_TASK_PARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
Identifier of the task to execute |
|
Name of the parameter to set. The possible analysis parameters that can be set by this procedure are:
|
|
|
|
|
|
New value of the specified parameter |
|
Specifies the requested level of concurrency with which a SPA task should be executed. Values |
|
Directs SPA to detect if the result-sets between the two trials being compared are different. If differences are seen in the result-sets of any SQL statement between the two trials being compared, the SPA comparison report will indicate this for every such SQL statement.
|
Usage Notes
The actual number of processes granted might be equal to or lower than the number requested using the test_execute_dop
parameter. This parameter applies only to test-execute
or explain plan type
of trials that process a SQL Tuning set.
Examples
To request two concurrent processes to execute the SPA task:
dbms_sqlpa.set_analysis_task_parameter(:tname,'TEST_EXECUTE_DOP',2)
To enable result-set validation
exec dbms_sqlpa.set_analysis_task_parameter(:atname,'COMPARE_RESULTSET','TRUE')
To disable result-set validation:
exec dbms_sqlpa.set_analysis_task_parameter(:atname,'COMPARE_RESULTSET','FALSE')
185.3.10 SET_ANALYSIS_DEFAULT_PARAMETER Procedures
This procedure sets the SQL analysis task parameter default value.
Syntax
This form of the procedure updates the default value of an analyzer parameter of type VARCHAR2
.
DBMS_SQLPA.SET_ANALYSIS_DEFAULT_PARAMETER( parameter IN VARCHAR2, value IN VARCHAR2);
This form of the procedure updates the default
value of an analyzer parameter of type NUMBER
.
DBMS_SQLPA.SET_ANALYSIS_DEFAULT_PARAMETER( parameter IN VARCHAR2, value IN NUMBER);
Parameters
Table 185-11 SET_ANALYSIS_DEFAULT_PARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
Name of the parameter to set. The possible analysis parameters that can be set by this procedure are:
|
|
|
|
New value of the specified parameter |