Oracle DBMS_SQLPA
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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  
Documented Yes
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
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 (new 12.1 parameter)
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,
con_dbid       IN NUMBER    := NULL)
RETURN VARCHAR2;
SELECT dbid, name
FROM v$pdbs
ORDER BY 2;

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-12'')', con_dbid => 2248214021);

-- 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,
con_name        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,
con_name        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
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 12.1 parameter)
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,
con_dbid                IN            NUMBER         := NULL,
con_name                   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,
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
 
SET_ANALYSIS_DEFAULT_PARAMETER
Sets the SQL analysis task default parameter value

Overload 1
dbms_sqlpa.set_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
CELL_SIMULATION_ENABLED Exadata simulation support
COMPARISON_METRIC Specify an expression to use in performance comparison (Example: buffer_gets, cpu_time + buffer_gets * 10)
CON_DBID_MAPPING Mapping of container database IDs
DATABASE_LINK can be set to the global name of a PUBLIC database link. When it is set, SQL Performance Analyzer will use the database link for all TEST EXECUTE and EXPLAIN PLAN operations by sending the SQL statements to the remote database to be processed remotely. The analysis results will still be stored on the local database.
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
DISABLE_MULTI_EXEC SQL statements are executed multiple times and runtime statistics are then averaged. Set this parameter to 'TRUE' to disable this capability. In this case, each SQL in the SQL tuning set is executed only once.
EXECUTION_DAYS_TO_EXPIRE Number of days until the tasks's executions will be deleted (without deleting the task)
EXECUTE_FULLDML TRUE to execute DML statement fully, including acquiring row locks and modifying rows; FALSE (default) to execute only the query part of the DML without modifying data. When TRUE, SQL Performance Analyzer will issue a rollback following DML execution to prevent persistent changes from being made by the DML.
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)
METRIC_DELTA_THRESHOLD Threshold of the difference between the SQL performance metric before and after the change. The default value is zero.
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
SQLSET_NAME Name of the SQL tuning set to associate to the specified task or task execution. This parameter is mainly using in comparing two SQL tuning sets using SPA.
SQLSET_OWNER Owner of the SQL tuning set specified using task parameter SQLSET_NAME.
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.
BEGIN
  dbms_sqlpa.set_analysis_default_parameter('SQL_LIMIT',  '40');
  dbms_sqlpa.set_analysis_default_parameter('DAYS_TO_EXPIRE',  '30');
END;
/
Overload 2 dbms_sqlpa.set_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

Related Topics
DBMS_SQLTUNE
Packages

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-2014 Daniel A. Morgan All Rights Reserved