Oracle | DBMS

Oracle DBMS_SQLPA Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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
QCHK_SOURCE_INITPARAM	VARCHAR2(20)	'oneclick_init_params'
QCHK_SOURCE_INDEX	VARCHAR2(21)	'oneclick_tuning_index'
QCHK_SOURCE_STATS	VARCHAR2(22)	'oneclick_pending_stats'
QCHK_SOURCE_SQLPROFILE	VARCHAR2(27)	'oneclick_tuning_sql_profile'

Dependencies

ANYDATA	DBMS_SQLTUNE_UTIL1	PRVT_ADVISOR
DBMS_ADVISOR	DBMS_SQLTUNE_UTIL2	PRVT_SMGUTIL
DBMS_AUTO_INDEX_INTERNAL	DBMS_STANDARD	PRVT_SQLADV_INFRA
DBMS_SQLDIAG	DBMS_SWAT_VER_INTERNAL	SQLSET_ROW
DBMS_SQLTUNE	DBMS_SYS_ERROR	SQL_BINDS
DBMS_SQLTUNE_INTERNAL	DBMS_WRR_INTERNAL	USER_ADVISOR_TASKS
DBMS_SQLTUNE_LIB	PLITBLM	XMLSEQUENCE
DBMS_SQLTUNE_UTIL0	PRVTEMX_SQL	XMLTYPE

Documented Yes

Exceptions

Error Code	Reason
ORA-15740	ERR_NO_EXEC2
ORA-15741	ERR_NO_COMPARE_EXEC
ORA-15742	ERR_INV_EXEC_NAME

First Available 11.1

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsspa.sql

Subprograms

CANCEL_ANALYSIS_TASK	INTERRUPT_ANALYSIS_TASK	RESUME_ANALYSIS_TASK
CREATE_ANALYSIS_TASK	REMOTE_PROCESS_SQL	SET_ANALYSIS_DEFAULT_PARAMETER
DROP_ANALYSIS_TASK	REPORT_ANALYSIS_TASK	SET_ANALYSIS_TASK_PARAMETER
EXECUTE_ANALYSIS_TASK	RESET_ANALYSIS_TASK	SET_QUICK_CHECK_TASK
GET_SESS_OPTIMIZER_ENV

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, 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) AS 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

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, param_xml IN VARCHAR2 := NULL, result_data_checksum OUT NUMBER, result_type_checksum OUT NUMBER);

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

SET_QUICK_CHECK_TASK

Called to set a task to be a quick check task dbms_sqlpa.set_quick_check_task( task_id IN NUMBER, task_source IN VARCHAR2);

exec dbms_sqlpa.set_quick_check_task(5064, dbms_sqlpa.qchk_source_sqlprofile);