Oracle DBMS_SQLPA
Version 11.2.0.3

General Information
Purpose Note:  "PA" stands for "Performance Analyzer."

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.
AUTHID CURRENT_USER
Constants
Name Data Type Value
ERR_NO_EXEC2 NUMBER -15740
ERR_NO_COMPARE_EXEC NUMBER -15741
ERR_INV_EXEC_NAME NUMBER -15742
Dependencies
ANYDATA DBMS_SQLTUNE_UTIL2 SQLSET_ROW
DBMS_ADVISOR DBMS_SYS_ERROR SQL_BINDS
DBMS_SQLTUNE DBMS_WRR_INTERNAL USER_ADVISOR_TASKS
DBMS_SQLTUNE_INTERNAL PLITBLM XMLSEQUENCE
DBMS_SQLTUNE_LIB PRVT_ADVISOR XMLSEQUENCETYPE
DBMS_SQLTUNE_UTIL0 PRVT_SMGUTIL XMLTYPE
DBMS_SQLTUNE_UTIL1 PRVT_SQLADV_INFRA  
First Available 11.1.0.6
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsspa.sql
Subprograms
CANCEL_ANALYSIS_TASK GET_SESS_OPTIMIZER_ENV RESET_ANALYSIS_TASK
CREATE_ANALYSIS_TASK INTERRUPT_ANALYSIS_TASK RESET_ANALYSIS_DEFAULT_PARAMETER
DROP_ANALYSIS_TASK REMOTE_PROCESS_SQL RESUME_ANALYSIS_TASK
EXECUTE_ANALYSIS_TASK REPORT_ANALYSIS_TASK SET_ANALYSIS_TASK_PARAMETER
 
CANCEL_ANALYSIS_TASK
Cancels the currently executing task analysis of one or more SQL statements dbms_sqlpa.cancel_analysis_task(task_name IN VARCHAR2);
exec dbms_sqlpa.cancel_analysis_task('TASK_5067');
 
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);
exec dbms_sqlpa.drop_analysis_task('TASK_5067');
 
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 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);
exec dbms_sqlpa.interrupt_analysis_task(:conc_task);

-- once a task is interrupted, you can elect to reset it, resume it, or check out its results and then decide. For this example we will 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');
 
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 (NO).
BASIC_FILTER Basic filter for SQL tuning set
COMPARISON_METRIC Specify an expression 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 Default execution type when none is specified by EXECUTE_ANALYSIS_TASK
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_NAME1 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 Change impact threshold for 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's 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
 
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
 
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

Related Topics
DBMS_SQLTUNE

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved