124 DBMS_SQLPA

Using DBMS_SQLPA

• Overview

• Security Model

---

Overview

The DBMS_SQLPA package 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.

The package provides a task-oriented interface to implement the SQL Performance Analyzer. For example

1. You use the CREATE_ANALYSIS_TASK Functions to create an analysis task for a single statement or a group of SQL statements.s

2. The EXECUTE_ANALYSIS_TASK Function & Procedure executes a previously created analysis task.

3. The REPORT_ANALYSIS_TASK Function displays the results of an analysis task.

---

Security Model

This package is available to PUBLIC and performs its own security checking. All analysis task interfaces (XXX_ANALYSIS_TASK) require privilege ADVISOR.

Summary of DBMS_SQLPA Subprograms

---

CANCEL_ANALYSIS_TASK Procedure

This procedure cancels the currently executing analysis task. All intermediate result data is removed from the task.

Syntax

DBMS_SQLPA.CANCEL_ANALYSIS_TASK(
 task_name         IN VARCHAR2);

Parameters

Examples

Canceling a task when there is a need to stop it executing and it is not required to view any already-completed results:

EXEC DBMS_SQLPA.CANCEL_ANALYSIS_TASK(:my_task);

---

CREATE_ANALYSIS_TASK Functions

These functions create an advisor task to process and analyze one or more SQL statements. You can use different forms of this function to:

• Create an analysis task for a single statement given its text.

• Create an analysis task for a single statement from the cursor cache given its identifier.

• Create an analysis task for a single statement from the workload repository given a range of snapshot identifiers.

• Create an analysis task for a SQL tuning set.

In all cases, the function creates an advisor task and sets its parameters.

Syntax

SQL text format. This form of the function is called to prepare the analysis of a single statement given its text.

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;

SQL ID format. This form of the function is called to prepare the analysis of a single statement from the cursor cache given its identifier.

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;

Workload Repository format. This form of the function is called to prepare the analysis of a single statement from the workload repository given a range of snapshot identifiers.

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;

SQLSET format. This form of the function is called to prepare the analysis of a SQL tuning set.

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;

Parameters

Return Values

A SQL analysis task name that is unique by user (two different users can give the same name to their advisor tasks).

Examples

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

---

DROP_ANALYSIS_TASK Procedure

This procedure drops a SQL analysis task.The task and all its result data are deleted.

Syntax

DBMS_SQLPA.DROP_ANALYSIS_TASK(
 task_name         IN VARCHAR2);

Parameters

---

EXECUTE_ANALYSIS_TASK Function & Procedure

This function and procedure executes a previously created analysis task, the function version returning the new execution name.

Syntax

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;

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

Parameters

Usage Notes

SQL performance analyzer task can be executed multiples times without having to reset it. For example, when a task is created to perform a change impact analysis on a SQL workload, the created task has to be executed before making any change in the system environment to build a version of the workload that will be used as a reference for performance analysis. Once the change has been made, a second execution is required to build the post-change version of the workload. Finally, the task has to be executed a third time to let the advisor analyze and compare the performance of the workload in both versions.

Examples

1. Create a task with a purpose of change impact analysis

EXEC :tname := dbms_sqltune.create_tuning_task(
    sqlset_name     => 'my_sts');

2. Make baseline or the before change execution

EXEC dbms_sqltune.execute_tuning_task(
    task_name       => :tname,
    execution_type  => 'test execute',     execution_name  => 'before_change');

3. Make change

...

4. Make the after change version of the workload performance

EXEC dbms_sqltune.execute_tuning_task(
   task_name          => :tname, -
   execution_type     => 'test execute ',
   execution_name     => 'after_change')

5. Compare the two versions of the workload

By default we always compare the results of the two last executions. The SQL Performance Analyzer uses the elapsed_time as a default metric for comparison. Here we are changing it to buffer_gets instead.

EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
   :tname,'comparison_metric', 'buffer_gets');
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
   task_name         => :tname, -
   execution_type    => 'test execute', - 
   execution_name    => 'after_change');

Use the following call if you would like to explicitly specify the two executions to compare as well as the comparison metric to use.

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
   task_name        => :tname, -
   execution_type   => 'compare performance', 
   execution_params => dbms_advisor.arglist(
      'execution_name1', 
      'before_change', 
      'execution_name2', 
      'after_change',
      'comparion_metric', 
      'buffer_gets'));

---

INTERRUPT_ANALYSIS_TASK Procedure

This procedure interrupts the currently executing analysis task. All intermediate result data will not be removed from the task.

Syntax

DBMS_SQLPA.INTERRUPT_ANALYSIS_TASK(
   task_name         IN VARCHAR2);

Parameters

Examples

EXEC DBMS_SQLPA.INTERRUPT_ANALYSIS_TASK(:my_task);

---

REPORT_ANALYSIS_TASK Function

This procedure displays the results of an analysis task.

Syntax

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;

Parameters

Return Values

A CLOB containing the desired report.

Examples

-- 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 Procedure

This procedure is called on an analysis task that is not currently executing to prepare it for re-execution. All intermediate result data will be deleted.

Syntax

DBMS_SQLPA.RESET_ANALYSIS_TASK(
 task_name         IN VARCHAR2);

Parameters

Examples

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

---

RESUME_ANALYSIS_TASK Procedure

This procedure resumes a previously interrupted or FAILED (with a fatalerror) task execution.

Syntax

DBMS_SQLPA.RESUME_ANALYSIS_TASK(
 task_name         IN VARCHAR2,
 basic_filter      IN VARCHAR2 := NULL);

Parameters

Usage Notes

Resuming a single SQL analysis task (a task that was created to analyze a single SQL statement as compared to a SQL Tuning Set) is not supported.

Examples

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

---

SET_ANALYSIS_TASK_PARAMETER Procedures

This procedure sets the SQL analysis task parameter value.

Syntax

This form of the procedure updates the value of a SQL analysis parameter of type VARCHAR2.

DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
  task_name         IN VARCHAR2,
  parameter         IN VARCHAR2,
  value             IN VARCHAR2);

This form of the procedure updates the value of a SQL analysis parameter of type NUMBER.

DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
  task_name         IN VARCHAR2,
  parameter         IN VARCHAR2,
  value             IN NUMBER);

Parameters

---

SET_ANALYSIS_DEFAULT_PARAMETER Procedures

This procedure sets the SQL analysis task parameter default value.

Syntax

This form of the procedure updates the default value of an analyzer parameter of type VARCHAR2.

DBMS_SQLPA.SET_ANALYSIS_DEFAULT_PARAMETER(
   parameter    IN  VARCHAR2,
   value        IN  VARCHAR2);

This form of the procedure updates the default value of an analyzer parameter of type NUMBER.

DBMS_SQLPA.SET_ANALYSIS_DEFAULT_PARAMETER(
   parameter    IN  VARCHAR2,
   value        IN  NUMBER);

Parameters