138 DBMS_SQLDIAG

The DBMS_SQLDIAG package provides an interface to the SQL Diagnosability functionality.

Using DBMS_SQLDIAG

Overview
Constants
Examples

Overview

In the rare case that a SQL statement fails with a critical error, you can run the SQL Repair Advisor to try to repair the failed statement.This section covers the following topics:

About the SQL Repair Advisor
Running the SQL Repair Advisor
Removing a SQL Patch

About the SQL Repair Advisor

You run the SQL Repair Advisor after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.

Running the SQL Repair Advisor

You run the SQL Repair Advisor by creating and executing a diagnostic task using the CREATE_DIAGNOSIS_TASK and EXECUTE_DIAGNOSIS_TASK respectively. The SQL Repair Advisor first reproduces the critical error and then tries to produce a workaround in the form of SQL patch.

Identify the problem SQL statement

Consider the SQL statement that gives a critical error:
```
DELETE FROM t t1 WHERE t1.a = 'a' AND ROWID <> (SELECT MAX(ROWID) FROM t t2 WHERE t1.a= t2.a AND t1.b = t2.b AND t1.d=t2.d)
```
You use the SQL Repair advisor to repair this critical error.

Create a diagnosis task

Invoke DBMS_SQLDIAG. CREATE_DIAGNOSIS_TASK. You can specify an optional task name, an optional time limit for the advisor task, and problem type. In the example below, we specify the SQL text, the task name as 'error_task' and a problem type as 'DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR'.

DECLARE
    rep_out         CLOB;
    t_id            VARCHAR2(50);
  BEGIN
    t_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK( 
      sql_text => 'DELETE FROM t t1 WHERE t1.a = ''a'' AND ROWID <> (SELECT MAX(ROWID) FROM t t2 WHERE t1.a= t2.a AND t1.b = t2.b AND t1.d=t2.d)',
      task_name => 'error_task',
      problem_type =>DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR);

Execute the diagnosis task

To execute the workaround generation and analysis phase of the SQL Repair Advisor, you call DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK with the task ID returned by the CREATE_DIAGNOSIS_TASK. After a short delay, the SQL Repair Advisor returns. As part of its execution, the SQL Repair Advisor keeps a record of its findings which can be accessed through the reporting facilities of SQL Repair Advisor.
```
DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (t_id);
```
Report the diagnosis task

The analysis of the diagnosis task is accessed through dbms_sqldiag.report_diagnosis_task. If the SQL Repair Advisor was able to find a workaround, it recommends a SQL Patch. A SQL Patch is similar to a SQL profile but unlike the SQL Profile, it is used to workaround compilation or execution errors.
```
rep_out := DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (t_id, DBMS_SQLDIAG.TYPE_TEXT);
 
  DBMS_OUTPUT.PUT_LINE ('Report : ' ||  rep_out);
 
  END;
  /
```
Applying the patch

If a patch recommendation is present in the report, you can run the ACCEPT_SQL_PATCH command to accept the patch by invoking DBMS_SQLDIAG.ACCEPT_SQL_PATCH. This procedure takes the task_name as an argument.
```
EXECUTE DBMS_SQLDIAG.ACCEPT_SQL_PATCH(task_name => 'error_task', task_owner => 'SYS', replace => TRUE);
```
Test the patch

Now that you have accepted the patch, you can rerun the SQL statement. This time, it will not give you the critical error. If you run 'explain plan' for this statement, you will see that a SQL patch was used to generate the plan.
```
DELETE FROM t t1 WHERE t1.a = 'a' AND ROWID <> (select max(rowid) FROM t t2 WHERE t1.a= t2.a AND t1.b = t2.b AND t1.d=t2.d);
```

Removing a SQL Patch

In a situation where you obtained an official patch from Oracle to fix an error, or upgraded to the next patchset or release of Oracle which included the fix for the error, you call DBMS_SQLDIAG.DROP_SQL_PATCH with the patch name to drop the SQL patch. The patch name can be obtained from the explain plan section or by querying the view DBA_SQL_PATCHES.

Constants

The DBMS_SQLDIAG package uses the constants shown in the following tables:

Table 138-1, "DBMS_SQLDIAG Constants - SQLDIAG Advisor Name" describes the name of SQL repair advisor as seen by the advisor framework
Table 138-2, "DBMS_SQLDIAG Constants - SQLDIAG Advisor Task Scope Parameter Values" describes SQLDIAG advisor task scope parameter values
Table 138-3, "DBMS_SQLDIAG Constants - SQLDIAG Advisor time_limit Constants" describes SQLDIAG advisor time_limit constants
Table 138-4, "DBMS_SQLDIAG Constants - Report Type (possible values) Constants" describes possible formats for a report
Table 138-5, "DBMS_SQLDIAG Constants - Report Level (possible values) Constants" describes possible levels of detail in the report
Table 138-6, "DBMS_SQLDIAG Constants - Report Section (possible values) Constants" describes possible report sections (comma delimited)
Table 138-7, "DBMS_SQLDIAG Constants - Problem Type Constants" describes possible values for the problem_type parameter of the CREATE_DIAGNOSIS_TASK Functions
Table 138-8, "DBMS_SQLDIAG Constants - Findings Filter Constants" describes possible values for the _sql_findings_mode parameter

Table 138-1 DBMS_SQLDIAG Constants - SQLDIAG Advisor Name

Constant	Type	Value	Description
`ADV_SQL_DIAG_NAME`	`VARCHAR2`(18)	'SQL Repair Advisor'	Name of SQL repair advisor as seen by the advisor framework

Table 138-2 DBMS_SQLDIAG Constants - SQLDIAG Advisor Task Scope Parameter Values

Constant	Type	Value	Description
`SCOPE_COMPREHENSIVE`	`VARCHAR2`(13)	'`COMPREHENSIVE`'	Detailed analysis of the problem which may take more time to execute
`SCOPE_LIMITED`	`VARCHAR2`(7)	'`LIMITED`'	Brief analysis of the problem

Table 138-3 DBMS_SQLDIAG Constants - SQLDIAG Advisor time_limit Constants

Constant	Type	Value	Description
`TIME_LIMIT_DEFAULT`	`NUMBER`	1800	Default time limit for analysis of the problem

Table 138-4 DBMS_SQLDIAG Constants - Report Type (possible values) Constants

Constant	Type	Value	Description
`TYPE_HTML`	`VARCHAR2`(4)	'HTML'	Report from the REPORT_DIAGNOSIS_TASK Function in HTML form
`TYPE_TEXT`	`VARCHAR2`(4)	'TEXT'	Report from the REPORT_DIAGNOSIS_TASK Function in text form
`TYPE_XML`	`VARCHAR2`(3)	'XML'	Report from the REPORT_DIAGNOSIS_TASK Function in XML form

Table 138-5 DBMS_SQLDIAG Constants - Report Level (possible values) Constants

Constant	Type	Value	Description
`LEVEL_ALL`	`VARCHAR2`(3)	'ALL'	Complete report including annotations about statements skipped over
`LEVEL_BASIC`	`VARCHAR2`(5)	'BASIC'	Shows information about every statement analyzed, including recommendations not implemented
`LEVEL_TYPICAL`	`VARCHAR2`(7)	'TYPICAL'	Simple report shows only information about the actions taken by the advisor.

Table 138-6 DBMS_SQLDIAG Constants - Report Section (possible values) Constants

Constant	Type	Value	Description
`SECTION_ALL`	`VARCHAR2`(3)	'ALL'	All statements
`SECTION_ERRORS`	`VARCHAR2`(6)	'ERRORS'	Statements with errors
`SECTION_FINDINGS`	`VARCHAR2`(8)	'FINDINGS'	Tuning findings
`SECTION_INFORMATION`	`VARCHAR2`(11)	'INFORMATION'	General information
`SECTION_PLANS`	`VARCHAR2`(5)	'PLANS'	Explain plans
`SECTION_SUMMARY`	`VARCHAR2`(7)	'SUMMARY'	Summary information

Table 138-7 DBMS_SQLDIAG Constants - Problem Type Constants

Constant	Type	Value	Description
`PROBLEM_TYPE_PERFORMANCE`	`NUMBER`	1	User suspects this is a performance problem
`PROBLEM_TYPE_WRONG_RESULTS`	`NUMBER`	2	User suspects the query is giving inconsistent results
`PROBLEM_TYPE_COMPILATION_ERROR`	`NUMBER`	3	User sees a crash in compilation
`PROBLEM_TYPE_EXECUTION_ERROR`	`NUMBER`	4	User sees a crash in execution
`PROBLEM_TYPE_ALT_PLAN_GEN`	`NUMBER`	5	User to explore all alternative plans

Table 138-8 DBMS_SQLDIAG Constants - Findings Filter Constants

Constant	Type	Value	Description
`SQLDIAG_FINDINGS_ALL`	`NUMBER`	1	Show all possible findings
`SQLDIAG_FINDINGS_VALIDATION`	`NUMBER`	2	Show status of validation rules over structures
`SQLDIAG_FINDINGS_FEATURES`	`NUMBER`	3	Show only features used by the query
`SQLDIAG_FINDINGS_FILTER_PLANS`	`NUMBER`	4	Show the alternative plans generated by the advisor
`SQLDIAG_FINDINGS_CR_DIFF`	`NUMBER`	5	Show difference between two plans
`SQLDIAG_FINDINGS_MASK_VARIANT`	`NUMBER`	6	Mask info for testing
`SQLDIAG_FINDINGS_OBJ_FEATURES`	`NUMBER`	7	Show features usage history
`SQLDIAG_FINDINGS_BASIC_INFO`	`NUMBER`	8	Show the alternative plans generated by the advisor

Examples

Patch Pack / Unpack

Patches can be exported out of one system and imported into another by means of a staging table, provided by subprograms in this package. Like with SQL diagnosis sets, the operation of inserting into the staging table is called a "pack", and the operation of creating patches from staging table data is termed the "unpack".

DBAs should perform a pack/unpack as follows:

Create a staging table owned by user 'SH' through a call to CREATE_STGTAB_SQLPATCH:

EXEC DBMS_SQLDIAG.CREATE_STGTAB_SQLPATCH(
    table_name          =>  'STAGING_TABLE',
    schema_name         =>  'SH');

Call PACK_STGTAB_SQLPATCH one or more times to write SQL patch data into the staging table. In this case, copy data for all SQL patches in the DEFAULT category into a staging table owned by the current schema owner:
```
EXEC DBMS_SQLDIAG.PACK_STGTAB_SQLPATCH(
    staging_table_name  =>  'STAGING_TABLE'); 
```
In this case, only a single SQL patch SP_FIND_EMPLOYEE is copied into a staging table owned by the current schema owner:
```
EXEC DBMS_SQLDIAG.PACK_STGTAB_SQLPATCH(
    patch_name          =>  'SP_FIND_EMPLOYEE',
    staging_table_name  =>  'STAGING_TABLE'); 
```
The staging table can then be moved to another system using either datapump, import/export commands or through a databaselink.
Call UNPACK_STGTAB_SQLPATCH to create SQL patches on the new system from the patch data in the staging table. In this case, change the name in the data for the SP_FIND_EMPLOYEE patch stored in the staging table to 'SP_FIND_EMP_PROD':
```
exec dbms_sqldiag.remap_stgtab_sqlpatch(
   old_patch_name      =>  'SP_FIND_EMPLOYEE',
   new_patch_name      =>  'SP_FIND_EMP_PROD', 
```

Summary of DBMS_SQLDIAG Subprograms

Table 138-9 DBMS_SQLDIAG Package Subprograms

Subprogram	Description
ACCEPT_SQL_PATCH Function & Procedure	Accepts a recommended SQL patch as recommended by the specified SQL diagnosis task
ALTER_SQL_PATCH Procedure	Alters specific attributes of an existing SQL patch object
CANCEL_DIAGNOSIS_TASK Procedure	Cancels a diagnostic task
CREATE_DIAGNOSIS_TASK Functions	Creates a diagnostic task in order to diagnose a single SQL statement
CREATE_STGTAB_SQLPATCH Procedure	Creates the staging table used for transporting SQL patches from one system to another
DROP_DIAGNOSIS_TASK Procedure	Drops a diagnostic task
DROP_SQL_PATCH Procedure	Drops the named SQL patch from the database
EXECUTE_DIAGNOSIS_TASK Procedure	Executes a diagnostic task
EXPLAIN_SQL_TESTCASE Function	Explains a SQL test case
EXPORT_SQL_TESTCASE Procedures	Exports a SQL test case to a directory
EXPORT_SQL_TESTCASE_DIR_BY_INC Function	Generates a SQL Test Case corresponding to the incident ID passed as an argument.
EXPORT_SQL_TESTCASE_DIR_BY_TXT Function	Generates a SQL Test Case corresponding to the SQL passed as an argument
GET_FIX_CONTROL Function	Returns the value of fix control for a given bug number
GET_SQL Function	Imports a SQL test case
IMPORT_SQL_TESTCASE Procedures	Imports a SQL test case into a schema
INCIDENTID_2_SQL Procedure	Initializes a `sql_setrow` from an incident ID
INTERRUPT_DIAGNOSIS_TASK Procedure	Interrupts a diagnostic task
LOAD_SQLSET_FROM_TCB Function	Loads a `SQLSET` from Test Case Builder (TCB) file
PACK_STGTAB_SQLPATCH Procedure	SQL patches into the staging table created by the CREATE_STGTAB_SQLPATCH Procedure
REPORT_DIAGNOSIS_TASK Function	Reports on a diagnostic task
RESET_DIAGNOSIS_TASK Procedure	Resets a diagnostic task
RESUME_DIAGNOSIS_TASK Procedure	Resumes a diagnostic task
SET_DIAGNOSIS_TASK_PARAMETER Procedure	Sets a diagnosis task parameter
UNPACK_STGTAB_SQLPATCH Procedure	Unpacks from the staging table populated by a call to the PACK_STGTAB_SQLPATCH Procedure, using the patch data stored in the staging table to create patches on this system

ACCEPT_SQL_PATCH Function & Procedure

This procedure accepts a recommended SQL patch as recommended by the specified SQL diagnosis task.

Syntax

DBMS_SQLDIAG.ACCEPT_SQL_PATCH (
   task_name      IN  VARCHAR2,
   object_id      IN  NUMBER := NULL,
   name           IN  VARCHAR2 := NULL,
   description    IN  VARCHAR2 := NULL,
   category       IN  VARCHAR2 := NULL,
   task_owner     IN  VARCHAR2 := NULL,
   replace        IN  BOOLEAN := FALSE,
   force_match    IN  BOOLEAN := FALSE)
 RETURN VARCHAR2;

DBMS_SQLDIAG.ACCEPT_SQL_PATCH (
   task_name      IN  VARCHAR2,
   object_id      IN  NUMBER := NULL,
   name           IN  VARCHAR2 := NULL,
   description    IN  VARCHAR2 := NULL,
   category       IN  VARCHAR2 := NULL,
   task_owner     IN  VARCHAR2 := NULL,
   replace        IN  BOOLEAN := FALSE,
   force_match    IN  BOOLEAN := FALSE);

Parameters

Table 138-10 ACCEPT_SQL_PATCH Function & Procedure Parameters

Parameter	Description
`taskname`	Name of the SQL diagnosis task
`object_id`	Identifier of the advisor framework object representing the SQL statement associated to the diagnosis task
`name`	Name of the patch. It cannot contain double quotation marks. The name is case sensitive. If not specified, the system will generate a unique name for the SQL patch.
`description`	User specified string describing the purpose of this SQL patch. Maximum size of description is 500.
`category`	Category name which must match the value of the `SQLDIAGNOSE_CATEGORY` parameter in a session for the session to use this patch. It defaults to the value `DEFAULT`. This is also the default of the `SQLDIAGNOSE_CATEGORY` parameter. The category must be a valid Oracle identifier. The category name specified is always converted to upper case. The combination of the normalized SQL text and category name create a unique key for a patch. An accept will fail if this combination is duplicated.
task_owner	Owner of the diagnosis task. This is an optional parameter that has to be specified to accept a SQL Patch associated to a diagnosis task owned by another user. The current user is the default value.
`replace`	If the patch already exists, it will be replaced if this argument is `TRUE`. It is an error to pass a name that is already being used for another signature/category pair, even with replace set to `TRUE`.
`force_match`	If `TRUE` this causes SQL Patches to target all SQL statements which have the same text after normalizing all literal values into bind variables. (Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.) This is analogous to the matching algorithm used by the `FORCE` option of the `CURSOR_SHARING` parameter. If `FALSE`, literals are not transformed. This is analogous to the matching algorithm used by the `EXACT` option of the `CURSOR_SHARING` parameter.

Return Values

Name of the SQL patch

Usage Notes

Requires CREATE ANY SQL PROFILE privilege

ALTER_SQL_PATCH Procedure

This procedure alters specific attributes of an existing SQL patch object.

Syntax

DBMS_SQLDIAG.ALTER_SQL_PATCH (
   name            IN  VARCHAR2,
   attribute_name  IN  VARCHAR2,
   value           IN  VARCHAR2);

Parameters

Table 138-11 ALTER_SQL_PATCH Procedure Parameters

Parameter Description

Parameter	Description
`name`	Name of SQL patch to alter.
`attribute_name`	Name of SQL patch to alter. Possible values: `STATUS` -> can be set to `ENABLED` or `DISABLED` `NAME` -> can be reset to a valid name (must be a valid Oracle identifier and must be unique). `DESCRIPTION` -> can be set to any string of size no more than 500 `CATEGORY` -> can be reset to a valid category name (must be valid Oracle identifier and must be unique when combined with normalized SQL text) This parameter is mandatory and is case sensitive.
`value`	New value of the attribute. See `attribute_name` for valid attribute values. This parameter is mandatory.

name

Name of SQL patch to alter.

attribute_name

Name of SQL patch to alter. Possible values:

STATUS -> can be set to ENABLED or DISABLED
NAME -> can be reset to a valid name (must be a valid Oracle identifier and must be unique).
DESCRIPTION -> can be set to any string of size no more than 500
CATEGORY -> can be reset to a valid category name (must be valid Oracle identifier and must be unique when combined with normalized SQL text)

This parameter is mandatory and is case sensitive.

value

New value of the attribute. See attribute_name for valid attribute values. This parameter is mandatory.

Usage Notes

Requires ALTER ANY SQL PATCH privilege

CANCEL_DIAGNOSIS_TASK Procedure

This procedure cancels a diagnostic task.

Syntax

DBMS_SQLDIAG.CANCEL_DIAGNOSIS_TASK (
    taskname        IN   VARCHAR2);

Parameters

Table 138-12 CANCEL_DIAGNOSIS_TASK Procedure Parameters

Parameter	Description
`taskname`	Name of task

CREATE_DIAGNOSIS_TASK Functions

This function creates a diagnostic task in order to diagnose a single SQL statement. It returns a SQL diagnosis task unique name

Syntax

Prepares the diagnosis of a single statement given its text:

DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (
    sql_text           IN   CLOB,
    bind_list          IN   sql_binds := NULL,
    user_name          IN   VARCHAR2  := NULL,
    scope              IN   VARCHAR2  := SCOPE_COMPREHENSIVE,
    time_limit         IN   NUMBER    := TIME_LIMIT_DEFAULT,
    task_name          IN   VARCHAR2  := NULL,
    description        IN   VARCHAR2  := NULL,
    problem_type       IN   NUMBER    := PROBLEM_TYPE_PERFORMANCE)
  RETURN VARCHAR2;

Prepares the diagnosis of a single statement from the Cursor Cache given its identifier:

DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (
    sql_id             IN   VARCHAR2,
    plan_hash_value    IN   NUMBER    := NULL,
    scope              IN   VARCHAR2  := SCOPE_COMPREHENSIVE,
    time_limit         IN   NUMBER    := TIME_LIMIT_DEFAULT,
    task_name          IN   VARCHAR2  := NULL,
    description        IN   VARCHAR2  := NULL,
    problem_type       IN   NUMBER    := PROBLEM_TYPE_PERFORMANCE)
  RETURN VARCHAR2;

Prepares the diagnosis of a Sqlset:

DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (
    sqlset_name       IN VARCHAR2,
    basic_filter      IN VARCHAR2 :=  NULL,
    object_filter     IN VARCHAR2 :=  NULL,
    rank1             IN VARCHAR2 :=  NULL,
    rank2             IN VARCHAR2 :=  NULL,
    rank3             IN VARCHAR2 :=  NULL,
    result_percentage IN NUMBER   :=  NULL,
    result_limit      IN NUMBER   :=  NULL,
    scope             IN VARCHAR2 :=  SCOPE_COMPREHENSIVE,
    time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
    task_name         IN VARCHAR2 :=  NULL,
    description       IN VARCHAR2 :=  NULL,
    plan_filter       IN VARCHAR2 :=  'MAX_ELAPSED_TIME',
    sqlset_owner      IN VARCHAR2 :=  NULL,
    problem_type      IN NUMBER   := PROBLEM_TYPE_PERFORMANCE)  RETURN VARCHAR2;

Parameters

Table 138-13 CREATE_DIAGNOSIS_TASK Function Parameters

Parameter	Description
`sql_text`	Text of a SQL statement
`bind_list`	Set of bind values
`user_name`	Username for who the statement/sqlset will be diagnosed
`scope`	Diagnosis scope (limited/comprehensive)
`time_limit`	Maximum duration in seconds for the diagnosis session
`task_name`	Optional diagnosis task name
description	Maximum of 256 SQL diagnosis session description
`problem_type`	Determines the goal of the task. Possible values are: `PROBLEM_TYPE_WRONG_RESULTS` `PROBLEM_TYPE_COMPILATION_ERROR` `PROBLEM_TYPE_EXECUTION_ERROR`
`sql_id`	Identifier of the statement
`plan_hash_value`	Hash value of the SQL execution plan
`sqlset_name`	Sqlset name
`basic_filter`	SQL predicate to filter the SQL from the SQL tuning set (STS)
`object_filter`	Object filter
`rank(i)`	Order-by clause on the selected SQL
`result_percentage`	Percentage on the sum of a ranking measure
`result_limit`	Top L(imit) SQL from (filtered/ranked) SQL
`plan_filter`	Plan filter. It is applicable in case there are multiple plans (`plan_hash_value`). This filter allows selecting one plan (plan_hash_value) only. Possible values are: `LAST_GENERATED`: plan with most recent timestamp `FIRST_GENERATED`: opposite to `LAST_GENERATED` `LAST_LOADED`: plan with most recent `first_load_time` stat info `FIRST_LOADED`: opposite to `LAST_LOADED` `MAX_ELAPSED_TIME`: plan with maximum elapsed time `MAX_BUFFER_GETS`: plan with maximum buffer gets `MAX_DISK_READS`: plan with maximum disk reads `MAX_DIRECT_WRITES`: plan with maximum direct writes `MAX_OPTIMIZER_COST`: plan with maximum optimum cost
`sqlset_owner`	Owner of the sqlset, or null for current schema owner

CREATE_STGTAB_SQLPATCH Procedure

This procedure creates the staging table used for transporting SQL patches from one system to another.

Syntax

DBMS_SQLDIAG.CREATE_STGTAB_SQLPATCH (
   table_name       IN  VARCHAR2,
   schema_name      IN  VARCHAR2 := NULL,
   tablespace_name  IN  VARCHAR2 := NULL);

Parameters

Table 138-14 CREATE_STGTAB_SQLPATCH Procedure Parameters

Parameter	Description
`table_name`	(Mandatory) Name of the table to create (case-sensitive)
`schema_name`	Schema to create the table in, or `NULL` for current schema (case-sensitive)
`tablespace_name`	Tablespace to store the staging table within, or `NULL` for current user's default tablespace (case-sensitive)

DROP_DIAGNOSIS_TASK Procedure

This procedure drops a diagnostic task.

Syntax

DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK (
    taskname        IN   VARCHAR2);

Parameters

Table 138-15 DROP_DIAGNOSIS_TASK Procedure Parameters

Parameter	Description
`taskname`	Name of task

DROP_SQL_PATCH Procedure

This procedure drops the named SQL patch from the database.

Syntax

DBMS_SQLDIAG.DROP_SQL_PATCH (
   name     IN  VARCHAR2,   ignore   IN  BOOLEAN := FALSE);

Parameters

Table 138-16 DROP_SQL_PATCH Function & Procedure Parameters

Parameter	Description
`name`	Name of patch to be dropped. The name is case sensitive.
`ignore`	Ignore errors due to object not existing.

Usage Notes

Requires DROP ANY SQL PATCH privilege

EXECUTE_DIAGNOSIS_TASK Procedure

This procedure executes a diagnostic task.

Syntax

DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (
    taskname        IN   VARCHAR2);

Parameters

Table 138-17 EXECUTE_DIAGNOSIS_TASK Procedure Parameters

Parameter	Description
`taskname`	Name of task

EXPLAIN_SQL_TESTCASE Function

This procedure explains a SQL test case.

Syntax

DBMS_SQLDIAG.EXPLAIN_SQL_TESTCASE (
    sqlTestCase        IN   CLOB)
  RETURN CLOB;

Parameters

Table 138-18 EXPLAIN_SQL_TESTCASE Function Parameters

Parameter	Description
`sqlTestCase`	XML document describing the SQL test case

EXPORT_SQL_TESTCASE Procedures

This procedure exports a SQL test case to a directory.

Syntax

This variant has to be provided with the SQL information.

DBMS_SQLDIAG.EXPORT_SQL_TESTCASE (
    directory                IN              VARCHAR2,
    sql_text                 IN              CLOB,
    user_name                IN              VARCHAR2  :=  NULL,
    bind_list                IN              sql_binds :=  NULL,
    exportEnvironment        IN              BOOLEAN   :=  TRUE,
    exportMetadata           IN              BOOLEAN   :=  TRUE,
    exportData               IN              BOOLEAN   :=  FALSE,
    exportPkgbody            IN              BOOLEAN   :=  FALSE,
    samplingPercent          IN              NUMBER    :=  100,
    ctrlOptions              IN              VARCHAR2  :=  NULL,
    timeLimit                IN              NUMBER    :=  0,
    testcase_name            IN              VARCHAR2  :=  NULL,
    testcase                 IN OUT NOCOPY   CLOB,
    preserveSchemaMapping    IN              BOOLEAN   :=  FALSE);

This variant extracts the SQL information from an incident file.

DBMS_SQLDIAG.EXPORT_SQL_TESTCASE (
    directory                IN              VARCHAR2,
    incident_id              IN              VARCHAR2,
    exportEnvironment        IN              BOOLEAN   :=  TRUE,
    exportMetadata           IN              BOOLEAN   :=  TRUE,
    exportData               IN              BOOLEAN   :=  FALSE,
    exportPkgbody            IN              BOOLEAN   :=  FALSE,
    samplingPercent          IN              NUMBER    :=  100,
    ctrlOptions              IN              VARCHAR2  :=  NULL,
    timeLimit                IN              NUMBER    :=  
                                                DBMS_SQLDIAG.TIME_LIMIT_DEFAULT,
    testcase_name            IN              VARCHAR2  :=  NULL,
    testcase                 IN OUT NOCOPY   CLOB,
    preserveSchemaMapping    IN              BOOLEAN   :=  FALSE);

This variant allow the SQL Test case to be generated from a cursor present in the cursor cache. Use V$SQL to get the SQL identifier and the SQL hash value.

DBMS_SQLDIAG.EXPORT_SQL_TESTCASE (
    directory                IN              VARCHAR2,
    sql_id                   IN              VARCHAR2,
    plan_hash_value          IN              NUMBER    := NULL,
    exportEnvironment        IN              BOOLEAN   :=  TRUE,
    exportMetadata           IN              BOOLEAN   :=  TRUE,
    exportData               IN              BOOLEAN   :=  TRUE,
    exportPkgbody            IN              BOOLEAN   :=  FALSE,
    samplingPercent          IN              NUMBER    :=  100,
    ctrlOptions              IN              VARCHAR2  :=  NULL,
    timeLimit                IN              NUMBER    := 
                                                DBMS_SQLDIAG.TIME_LIMIT_DEFAULT,
    testcase_name            IN              VARCHAR2  :=  NULL,
    testcase                 IN OUT NOCOPY   CLOB,
    preserveSchemaMapping    IN              BOOLEAN   :=  FALSE);

Parameters

Table 138-19 EXPORT_SQL_TESTCASE Procedure Parameters

Parameter	Description
`directory`	Directory to store the various generated files
`sql_text`	Text of the SQL statement to export
`incident_id`	Incident ID containing the offending SQL
`sql_id`	Identifier of the statement in the cursor cache
`username`	Name of the user schema to use to parse the SQL, defaults to `SYS`
`bind_list`	List of bind values associated to the statement
`exportEnvironment`	`TRUE` if the compilation environment should be exported
`exportMetadata`	`TRUE` if the definition of the objects referenced in the SQL should be exported
`exportData`	`TRUE` if the data of the objects referenced in the SQL should be exported
`exportPkgbody`	`TRUE` if the body of the packages referenced in the SQL are exported
`samplingPercent`	If is `TRUE`, specify the sampling percentage to use to create the dump file
`ctrlOptions`	Opaque control parameters
`timeLimit`	How much time should we spend exporting the SQL test case
`testcaseName`	An optional name for the SQL test case. This is used to prefix all the generated scripts
`testcase`	Resulting testcase
`preservesSchemaMapping`	`TRUE` if the schema (or schemas) are not re-mapped from the original environment to the test environment

Usage Notes

A SQL test case generates a set of files needed to help reproduce a SQL failure on a different machine. It contains:
- a dump file containing schemas objects and statistics (.dmp)
- the explain plan for the statements (in advanced mode)
- diagnostic information gathered on the offending statement
- an import script to execute to reload the objects
- a SQL script to replay system statistics of the source
- a table of contents file describing the SQL test case
- metadata. (xxxxmain.xml)
You should not run Test Case Builder (TCB) under user SYS. Instead, use another user who can be granted the DBA role.
The default setting for TCB is that data is not exported. However, in some cases data is required, such as to diagnose an outcome with a result that is not optimal. To export data, call EXPORT_SQL_TESTCASE with exportData=>TRUE and the data will be imported by default, unless turned OFF by importData=>FALSE.
TCB includes PL/SQL package spec by default, but not the PL/SQL package body. However, you may need to have the package body as well, for example, to invoke the PL/SQL functions, or because you have a Virtual Private Database (VPD) function defined in a package. To export a PL/SQL package body, call EXPORT_SQL_TESTCASE with exportPkgbody=>TRUE. To import a PL/SQL package body, call IMPORT_SQL_TESTCASE Procedures with importPkgbody=>TRUE.

EXPORT_SQL_TESTCASE_DIR_BY_INC Function

This function generates a SQL Test Case corresponding to the incident ID passed as an argument. It creates a set of scripts and dump file in the directory passed as an argument.

Syntax

DBMS_SQLDIAG.EXPORT_SQL_TESTCASE_DIR_BY_INC (
    incident_id        IN   NUMBER,
    directory          IN   VARCHAR2,
    exportEnvironment  IN   VARCHAR2 := 'TRUE',
    exportMetadata     IN   VARCHAR2 := 'TRUE',
    exportData         IN   VARCHAR2 := 'FALSE',
    samplingPercent    IN   VARCHAR2 := '100', 
    ctrlOptions        IN   VARCHAR2 := NULL)
 RETURN BOOLEAN;

Parameters

Table 138-20 EXPORT_SQL_TESTCASE_DIR_BY_INC Function Parameters

Parameter	Description
`incident_id`	Incident ID containing the offending SQL. For more information about Incidents, see Oracle Database Performance Tuning Guide.
`directory`	Directory to store the various generated files
`exportEnvironment`	`TRUE` if the compilation environment should be exported
`exportMetadata`	`TRUE` if the definition of the objects referenced in the SQL should be exported
`exportData`	`TRUE` if the data of the objects referenced in the SQL should be exported
`samplingPercent`	If is `TRUE`, specify the sampling percentage to use to create the dump file
`ctrlOptions`	Opaque control parameters

EXPORT_SQL_TESTCASE_DIR_BY_TXT Function

This function generates a SQL Test Case corresponding to the SQL passed as an argument. It creates a set of scripts and dump files in the directory passed as an argument.

Syntax

DBMS_SQLDIAG.EXPORT_SQL_TESTCASE_DIR_BY_TXT (
    incident_id        IN   NUMBER,
    directory          IN   VARCHAR2,
    sql_text           IN   CLOB,
    user_name          IN   VARCHAR2 := 'SYS',
    exportEnvironment  IN   VARCHAR2 := 'TRUE',
    exportMetadata     IN   VARCHAR2 := 'TRUE',
    exportData         IN   VARCHAR2 := 'FALSE',
    samplingPercent    IN   VARCHAR2 := '100',     ctrlOptions        IN   VARCHAR2 := NULL)
  RETURN BOOLEAN;

Parameters

Table 138-21 EXPORT_SQL_TESTCASE_DIR_BY_TXT Function Parameters

Parameter	Description
`incident_id`	Incident ID containing the offending SQL
`directory`	Directory to store the various generated files
`sql_text`	Text of the SQL statement to explain
`username`	Name of the user schema to use to parse the SQL, defaults to `SYS`
`exportEnvironment`	`TRUE` if the compilation environment should be exported
`exportMetadata`	`TRUE` if the definition of the objects referenced in the SQL should be exported
`exportData`	`TRUE` if the data of the objects referenced in the SQL should be exported
`samplingPercent`	If is `TRUE`, specify the sampling percentage to use to create the dump file
`ctrlOptions`	Opaque control parameters

GET_FIX_CONTROL Function

This function returns the value of fix control for a given bug number.

Syntax

DBMS_SQLDIAG.GET_FIX_CONTROL (
    bug_number   IN    NUMBER)
  RETURN NUMBER;

Parameters

Table 138-22 GET_FIX_CONTROL Function Parameters

Parameter	Description
`bug_number`	Bug number

GET_SQL Function

This function loads a sql_setrow from the trace file associated to an the given incident ID.

Syntax

DBMS_SQLDIAG.GET_SQL (
    incident_id  IN     VARCHAR2)
  RETURN SQLSET_ROW;

Parameters

Table 138-23 GET_SQL Function Parameters

Parameter	Description
`incident_id`	Identifier of the incident

IMPORT_SQL_TESTCASE Procedures

This procedure imports a SQL test case into a schema.

Syntax

This variant requires a source directory and SQL Testcase metadata object (in XML format).

DBMS_SQLDIAG.IMPORT_SQL_TESTCASE (
    directory                IN   VARCHAR2,
    sqlTestCase              IN   CLOB,
    importEnvironment        IN   BOOLEAN   :=  TRUE,
    importMetadata           IN   BOOLEAN   :=  TRUE,
    importData               IN   BOOLEAN   :=  TRUE,
    importPkgbody            IN   BOOLEAN   :=  FALSE,
    importDiagnosis          IN   BOOLEAN   :=  TRUE,
    ignoreStorage            IN   BOOLEAN   :=  TRUE,
    ctrlOptions              IN   VARCHAR2  :=  NULL,
    preserveSchemaMapping    IN   BOOLEAN   :=  FALSE);

This variant requires a source directory name of SQL Testcase metadata file.

DBMS_SQLDIAG.IMPORT_SQL_TESTCASE (
    directory                IN   VARCHAR2,
    filename                 IN   VARCHAR2,
    importEnvironment        IN   BOOLEAN   :=  TRUE,
    importMetadata           IN   BOOLEAN   :=  TRUE,
    importData               IN   BOOLEAN   :=  TRUE,
    importPkgbody            IN   BOOLEAN   :=  FALSE,
    importDiagnosis          IN   BOOLEAN   :=  TRUE,
    ignoreStorage            IN   BOOLEAN   :=  TRUE,
    ctrlOptions              IN   VARCHAR2  :=  NULL,
    preserveSchemaMapping    IN   BOOLEAN   :=  FALSE);

Parameters

Table 138-24 IMPORT_SQL_TESTCASE Procedure Parameters

Parameter	Description
`directory`	Directory containing test case files
`filename`	Name of a file containing an XML document describing the SQL test case
`importEnvironment`	`TRUE` if the compilation environment should be imported
`importMetadata`	`TRUE` if the definition of the objects referenced in the SQL should be imported
`importData`	`TRUE` if the data of the objects referenced in the SQL should be imported
`importPkgbody`	`TRUE` if the body of the packages referenced in the SQL are imported
`importDiagnosis`	`TRUE` if the diagnostic information associated to the task should be imported
`ignoreStorage`	`TRUE` if the storage attributes should be ignored
`ctrlOptions`	Opaque control parameters
`preservesSchemaMapping`	`TRUE` if the schema (or schemas) are not re-mapped from the original environment to the test environment

Usage Notes

A SQL test case generates a set of files needed to help reproduce a SQL failure on a different machine. It contains:
- a dump file containing schemas objects and statistics (.dmp)
- the explain plan for the statements (in advanced mode)
- diagnostic information gathered on the offending statement
- an import script to execute to reload the objects
- a SQL script to replay system statistics of the source
- a table of contents file describing the SQL test case
- metadata. (xxxxmain.xml)
You should not run Test Case Builder (TCB) under user SYS. Instead, use another user who can be granted the SYSDBA privilege
The default setting for TCB is that data is not exported. However, in some cases data is required, such as to diagnose an outcome with a result that is not optimal. To export data, call EXPORT_SQL_TESTCASE Procedures with exportData=>TRUE and the data will be imported by default, unless turned OFF by importData=>FALSE.
TCB includes PL/SQL package spec by default, but not the PL/SQL package body. However, you may need to have the package body as well, for example, to invoke the PL/SQL functions, or because you have a Virtual Private Database (VPD) function defined in a package. To export a PL/SQL package body, call EXPORT_SQL_TESTCASE Procedures with exportPkgbody=>TRUE. To import a PL/SQL package body, call IMPORT_SQL_TESTCASE Procedures with importPkgbody=>TRUE.

INCIDENTID_2_SQL Procedure

This procedure initializes a sql_setrow from an incident ID.

Syntax

DBMS_SQLDIAG.INCIDENTID_2_SQL (
    incident_id   IN     VARCHAR2,
    sql_stmt      OUT    SQLSET_ROW,
    problem_type  OUT    NUMBER, 
    err_code      OUT    BINARY_INTEGER,
    err_mesg      OUT    VARCHAR2);

Parameters

Table 138-25 INCIDENTID_2_SQL Procedure Parameters

Parameter	Description
`incident_id`	Identifier of the incident
`sql_stmt`	Resulting SQL
`problem_type`	Tentative type of SQL problem (currently among `PROBLEM_TYPE_COMPILATION_ERROR` and `PROBLEM_TYPE_EXECUTION_ERROR`)
`err_code`	Error code if any otherwise it is set to `NULL`
`err_msg`	Error message if any otherwise it is set to `NULL`

INTERRUPT_DIAGNOSIS_TASK Procedure

This procedure interrupts a diagnostic task.

Syntax

DBMS_SQLDIAG.INTERRUPT_DIAGNOSIS_TASK (
    taskname        IN   VARCHAR2);

Parameters

Table 138-26 INTERRUPT_DIAGNOSIS_TASK Procedure Parameters

Parameter	Description
`taskname`	Name of task

LOAD_SQLSET_FROM_TCB Function

This function loads a SQLSET from a Test Case Builder file.

Syntax

DBMS_SQLDIAG.LOAD_SQLSET_FROM_TCB (
    directory        IN     VARCHAR2,
    filename         IN     VARCHAR2,
    sqlset_name      IN     VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2;

Parameters

Table 138-27 LOAD_SQLSET_FROM_TCB Function Parameters

Parameter	Description
`directory`	Name of directory
`filename`	Name of file
`sqlset_name`	Name of `SQLSET`

PACK_STGTAB_SQLPATCH Procedure

This procedure packs SQL patches into the staging table created by a call to the CREATE_STGTAB_SQLPATCH Procedure.

Syntax

DBMS_SQLDIAG.UPPACK_STGTAB_SQLPATCH (
   patch_name            IN  VARCHAR2 := '%',
   patch_category        IN  VARCHAR2 := 'DEFAULT',
   staging_table_name    IN  VARCHAR2,
   staging_schema_owner  IN  VARCHAR2 := NULL);

Parameters

Table 138-28 UPPACK_STGTAB_SQLPATCH Procedure Parameters

Parameter	Description
`patch_name`	Name of patch to pack (% wildcards acceptable, case-sensitive)
`patch_category`	Category to which to pack patches (% wildcards acceptable, case-insensitive)
`staging_table_name`	(Mandatory) Name of the table to use (case-sensitive)
`staging_schema_owner`	Schema where the table resides, or `NULL` for current schema (case-sensitive)

Usage Notes

Requires: ADMINISTER SQL PLAN MANAGEMENT OBJECT privilege and INSERT privilege on the staging table
By default, we move all SQL patches in category DEFAULT. See the Examples for details. Note that the subprogram issues a COMMIT after packing each SQL patch, so if an error is raised in mid-execution, some patches may be in the staging table.

REPORT_DIAGNOSIS_TASK Function

This function reports on a diagnostic task. It returns a CLOB containing the desired report.

Syntax

DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (
    taskname           IN   VARCHAR2,
    type               IN   VARCHAR2  := TYPE_TEXT,
    level              IN   VARCHAR2  := LEVEL_TYPICAL,
    section            IN   VARCHAR2  := SECTION_ALL,
    object_id          IN   NUMBER    := NULL,
    result_limit       IN   NUMBER    := NULL,
    owner_name         IN   VARCHAR2  := NULL)
  RETURN CLOB;

Parameters

Table 138-29 REPORT_DIAGNOSIS_TASK Function Parameters

Parameter	Description
`taskname`	Name of task to report
`type`	Type of the report. Possible values are: TEXT, HTML, XML (see Table 138-4, "DBMS_SQLDIAG Constants - Report Type (possible values) Constants").
`level`	Format of the recommendations. Possible values are TYPICAL, BASIC, ALL (Table 138-5, "DBMS_SQLDIAG Constants - Report Level (possible values) Constants").
`section`	Particular section in the report. Possible values are: SUMMARY, FINDINGS, PLAN, INFORMATION, ERROR, ALL (Table 138-6, "DBMS_SQLDIAG Constants - Report Section (possible values) Constants").
`object_id`	Identifier of the advisor framework object that represents a given statement in a SQL Tuning Set (STS).
`result_limit`	Number of statements in a STS for which the report is generated
`owner_name`	Name of the task execution to use. If `NULL`, the report will be generated for the last task execution.

RESET_DIAGNOSIS_TASK Procedure

This procedure resets a diagnostic task.

Syntax

DBMS_SQLDIAG.RESET_DIAGNOSIS_TASK (
    taskname        IN   VARCHAR2);

Parameters

Table 138-30 RESET_DIAGNOSIS_TASK Procedure Parameters

Parameter	Description
`taskname`	Name of task

RESUME_DIAGNOSIS_TASK Procedure

This procedure resumes a diagnostic path.

Syntax

DBMS_SQLDIAG.RESUME_DIAGNOSIS_TASK (
    taskname        IN   VARCHAR2);

Parameters

Table 138-31 RESUME_DIAGNOSIS_TASK Procedure Parameters

Parameter	Description
`taskname`	Name of task

SET_DIAGNOSIS_TASK_PARAMETER Procedure

This procedure is called to update the value of a SQL diagnosis parameter of type VARCHAR2. The task must be set to its initial state before calling this procedure. The diagnosis parameters that can be set by this procedure are:

MODE: diag scope (comprehensive, limited)
_SQLDIAG_FINDING_MODE: findings in the report (see "DBMS_SQLDIAG Constants - Findings Filter Constants" for possible values)

Syntax

DBMS_SQLDIAG.SET_DIAGNOSIS_TASK_PARAMETER (
    taskname           IN   VARCHAR2,
    parameter          IN   VARCHAR2,    value              IN   NUMBER);

Parameters

Table 138-32 SET_DIAGNOSIS_TASK_PARAMETER Procedure Parameters

Parameter	Description
`taskname`	Identifier of the task to execute
`parameter`	Name of the parameter to set
`value`	New value of the specified parameter

UNPACK_STGTAB_SQLPATCH Procedure

This procedure unpacks from the staging table populated by a call to the PACK_STGTAB_SQLPATCH Procedure. It uses the patch data stored in the staging table to create patches on this system. Users can opt to replace existing patches with patch data when they exist already. In this case, note that it is only possible to replace patches referring to the same statement if the names are the same (see the ACCEPT_SQL_PATCH Function & Procedure).

Syntax

DBMS_SQLDIAG.UPPACK_STGTAB_SQLPATCH (
   patch_name            IN  VARCHAR2 := '%',
   patch_category        IN  VARCHAR2 := '%',
   replace               IN  BOOLEAN,
   staging_table_name    IN  VARCHAR2,
   staging_schema_owner  IN  VARCHAR2 := NULL);

Parameters

Table 138-33 UPPACK_STGTAB_SQLPATCH Procedure Parameters

Parameter	Description
`patch_name`	Name of patch to unpack (% wildcards acceptable, case-sensitive)
`patch_category`	Category from which to unpack patches (% wildcards acceptable, case-insensitive)
replace	Replace patches if they already exist. Note that patches cannot be replaced if there is one in the staging table with the same name as an active patch on different SQL. The subprogram raises an error if there an attempt to create a patch that already exists.
staging_table_name	(Mandatory) Name of the table to use (case-sensitive)
staging_schema_owner	Schema where the table resides, or `NULL` for current schema (case-sensitive)

Usage Notes

Requires: ADMINISTER SQL MANAGEMENT OBJECT privilege and SELECT privilege on the staging table
By default, all SQL patches in the staging table are moved. The function commits after successfully loading each patch. If it fails in creating an individual patch, it raises an error and does not proceed to those remaining in the staging table.