Oracle DBMS_SQLPA SQL Performance Analyzer

Search the Reference Library pages:

Oracle DBMS_SQLPA

Version 11.1

General

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

Dependencies

dba_analysis_task	dbms_sqltune_util1	prvt_sqladv_infra
dbms_advisor	dbms_sqltune_util2	sqlset_row
dbms_sqltune	prvt_advisor	sql_binds
dbms_sqltune_internal	prvt_smgutil

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

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;

TBD

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;

-- 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);

Overload 2

TBD

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 in 11.1.0.7)

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,
new_phv        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,
extra_res      OUT VARCHAR2);

TBD

REPORT_ANALYSIS_TASK

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,
task_owner     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL))
RETURN CLOB;

-- 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', 'FINDINGS', 5) from dual;

RESET_ANALYSIS_TASK

Resets the currently executing analysis task to its initial state

dbms_sqlpa.reset_analysis_task(task_name IN VARCHAR2);

TBD

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

-----