| General Information |
| Note: 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. |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsspa.sql |
| First Available |
11.1 |
| Constants |
| Name |
Data Type |
Value |
| ERR_NO_EXEC2 |
NUMBER |
-15740 |
| ERR_NO_COMPARE_EXEC |
NUMBER |
-15741 |
| ERR_INV_EXEC_NAME |
NUMBER |
-15742 |
|
| Dependencies |
| ANYDATA |
DUAL |
SQL_BINDS |
| DBMS_ADVISOR |
PLAN_TABLE$ |
SQL_PLAN_ROW_TYPE |
| DBMS_SQLTUNE |
PLITBLM |
SQL_PLAN_TABLE_TYPE |
| DBMS_SQLTUNE_INTERNAL |
PRVT_ADVISOR |
USER_ADVISOR_TASKS |
| DBMS_SQLTUNE_LIB |
PRVT_SMGUTIL |
XMLSEQUENCETYPE |
| DBMS_SQLTUNE_UTIL1 |
PRVT_SQLADV_INFRA |
XMLTYPE |
| DBMS_SQLTUNE_UTIL2 |
SQLSET_ROW |
|
|
| Security Model |
Execute is granted to PUBLIC |
| |
| CANCEL_ANALYSIS_TASK |
| Cancels the currently executing task analysis of one or more SQL statements |
dbms_sqlpa.cancel_analysis_task(task_name IN VARCHAR2); |
| TBD |
| |
| CREATE_ANALYSIS_TASK |
Creates an advisor task to process and analyze one or more SQL statements
Overload 1 |
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; |
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'); |
| Overload 2 |
dbms_sqlpa.create_analysis_task(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2; |
| TBD |
| Overload 3 |
dbms_sqlpa.create_analysis_task(
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)
RETURN VARCHAR2; |
| TBD |
Overload 4
This demo includes an Exadata cell simulation |
dbms_sqlpa.create_analysis_task(
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
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; |
-- display the 10 most active SQL tuning sets
SELECT *
FROM (
SELECT name, owner, statement_count sql_count, SUBSTR(description, 1, 20) descript
FROM dba_sqlset
ORDER BY last_modified desc)
WHERE rownum < 11;
-- create a spa analysis task to test cell simulation
set serveroutput on
DECLARE
taskid user_advisor_tasks.task_name%TYPE;
BEGIN
taskid := dbms_sqlpa.create_analysis_task('SH_TSet1', sqlset_owner => 'SH');
dbms_output.put_line(taskid);
-- test execute statements with cell simulation disabled
dbms_sqlpa.execute_analysis_task(
task_name => taskid,
execution_type => 'execute',
execution_name => 'cell_simulation_DISABLED',
execution_params => dbms_advisor.arglist('cell_simulation_enabled',
'FALSE'));
-- test execute statements with cell simulation enabled
dbms_sqlpa.execute_analysis_task(
task_name => taskid,
execution_type => 'execute',
execution_name => 'cell_simulation_ENABLED',
execution_params =>
dbms_advisor.arglist('cell_simulation_enabled',
'TRUE'));
-- compare peformance and generate analysis report
dbms_sqlpa.execute_analysis_task(taskid, 'compare',
execution_params => dbms_advisor.arglist('comparison_metric',
'io_interconnect_bytes'));
END;
/
SELECT dbms_sqlpa.report_analysis_task('TASK_5067', 'text', top_sql => 10) spa_summary
FROM dual; |
| |
| DROP_ANALYSIS_TASK |
| Drops a SQL analysis task |
dbms_sqlpa.drop_analysis_task(task_name IN VARCHAR2); |
| TBD |
| |
| EXECUTE_ANALYSIS_TASK |
Executes a previously created analysis task
Overload 1 |
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; |
| TBD |
| Overload 2 |
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); |
| See CREATE_ANALYSIS_TASK Demo Above |
| |
GET_SESS_OPTIMIZER_ENV (new in 11.2.0.1)  |
| Returns the compilation environment from the session for a remote SPA trial |
dbms_sqlpa.get_sess_optimizer_env RETURN RAW; |
| SELECT dbms_sqlpa.get_sess_optimizer_env FROM dual; |
| |
| INTERRUPT_ANALYSIS_TASK |
| Interrupts the currently executing analysis task |
dbms_sqlpa.interrupt_analysis_task(task_name IN VARCHAR2); |
-- 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); |
| |
| REMOTE_PROCESS_SQL (new 11.2.0.1 and 11.2.0.2 parameters) |
| Undocumented function for internal usage only |
dbms_sqlpa.remote_process_sql(
sql_text IN CLOB,
parsing_schema IN VARCHAR2,
bind_data IN RAW,
bind_list IN VARRAY,
action IN VARCHAR2,
time_limit IN NUMBER,
plan_hash1 OUT NUMBER,
buffer_gets OUT NUMBER,
cpu_time OUT NUMBER,
elapsed_time OUT NUMBER,
disk_reads OUT NUMBER,
disk_writes OUT NUMBER,
rows_processed OUT NUMBER,
optimizer_cost OUT NUMBER,
parse_time OUT NUMBER,
err_code OUT NUMBER,
err_mesg OUT VARCHAR2,
flags IN BINARY_INTEGER := 0,
extra_res OUT NOCOPY VARCHAR2,
other_xml IN OUT NOCOPY VARCHAR2,
physical_read_requests OUT NUMBER,
physical_write_requests OUT NUMBER,
physical_read_bytes OUT NUMBER,
physical_write_bytes OUT NUMBER,
user_io_time OUT NUMBER,
plan_hash2 OUT NUMBER,
io_interconnect_bytes OUT NUMBER,
action_flags IN BINARY_INTEGER := 0,
control_options_xml IN VARCHAR2 := NULL); |
| TBD |
| |
| REPORT_ANALYSIS_TASK (new 11.2.0.2 parameter) |
| Displays the results of an analysis task |
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; |
| See CREATE_ANALYSIS_TASK Demo Above |
| |
| RESET_ANALYSIS_TASK |
| Resets the currently executing analysis task to its initial state |
dbms_sqlpa.reset_analysis_task(task_name IN VARCHAR2); |
| exec dbms_sqlpa.reset_analysis_task('TASK_5064'); |
| |
| RESUME_ANALYSIS_TASK |
| Resumes a previously interrupted analysis task that was created to process a SQL tuning set |
dbms_sqlpa.resume_analysis_task(task_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL); |
| TBD |
| |
| RESET_ANALYSIS_DEFAULT_PARAMETER |
Sets the SQL analysis task parameter default value
Overload 1 |
dbms_sqlpa.reset_analysis_default_parameter(
parameter IN VARCHAR2,
value IN VARCHAR2);
| Parameter |
Description |
| APPLY_CAPTURED_COMPILENV |
Indicates whether the advisor could use the compilation environment captured with the SQL statements. The default is 0 (that is, NO). |
| BASIC_FILTER |
Basic filter for SQL tuning set |
| COMPARISON_METRIC |
Specify an expression of execution statistics to use in performance comparison (Example: buffer_gets, cpu_time + buffer_gets * 10) |
| DAYS_TO_EXPIRE |
Number of days until the task is deleted |
| DEFAULT_EXECUTION_TYPE |
The task will default to this type of execution when none is specified by the EXECUTE_ANALYSIS_TASK Function & Procedure. |
| EXECUTION_DAYS_TO_EXPIRE |
Number of days until the tasks's executions will be deleted (without deleting the task) |
| EXECUTION_NAME1 |
Name of the first task execution to analyze |
| EXECUTION_NAME2 |
Name of the second task execution to analyze |
| LOCAL_TIME_LIMIT |
Per-statement time out (seconds) |
| PLAN_FILTER |
Plan filter for SQL tuning set (see SELECT_SQLSET for possible values) |
| RANK_MEASURE1 |
First ranking measure for SQL tuning set |
| RANK_MEASURE2 |
Second possible ranking measure for SQL tuning set |
| RANK_MEASURE3 |
Third possible ranking measure for SQL tuning set |
| RESUME_FILTER |
A extra filter for SQL tuning sets besides BASIC_FILTER |
| SQL_IMPACT_THRESHOLD |
Threshold of a change impact on a SQL statement. Same as the previous parameter, but at the level of the SQL statement. |
| SQL_LIMIT |
Maximum number of SQL statements to tune |
| SQL_PERCENTAGE |
Percentage filter of SQL tuning set statements |
| TIME_LIMIT |
Global time out (seconds) |
| WORKLOAD_IMPACT_THRESHOLD |
Threshold of a SQL statement impact on a workload. Statements which workload change impact is below the
absolute value of this threshold will be ignored and not considered for improvement or regression. |
|
| TBD |
| Overload 2 |
dbms_sqlpa.reset_analysis_default_parameter(parameter IN VARCHAR2, value IN NUMBER); |
| TBD |
| |
| SET_ANALYSIS_TASK_PARAMETER |
Sets the SQL analysis task parameter value
Overload 1 |
dbms_sqlpa.set_analysis_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value IN VARCHAR2); |
| TBD |
| Overload 2 |
dbms_sqlpa.set_analysis_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value IN NUMBER); |
| TBD |
