The DBMS_SQLDIAG package provides an interface to the SQL Diagnosability functionality.
See Also:
Oracle Database Administrator's Guide for more information about "Managing Diagnostic Data"This chapter contains the following topics:
Overview
Constants
Examples
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:
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);
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.
The DBMS_SQLDIAG package uses the constants shown in the following tables:
Table 152-1, "DBMS_SQLDIAG Constants - SQLDIAG Advisor Name" describes the name of SQL repair advisor as seen by the advisor framework
Table 152-2, "DBMS_SQLDIAG Constants - SQLDIAG Advisor Task Scope Parameter Values" describes SQLDIAG advisor task scope parameter values
Table 152-3, "DBMS_SQLDIAG Constants - SQLDIAG Advisor time_limit Constants" describes SQLDIAG advisor time_limit constants
Table 152-4, "DBMS_SQLDIAG Constants - Report Type (possible values) Constants" describes possible formats for a report
Table 152-5, "DBMS_SQLDIAG Constants - Report Level (possible values) Constants" describes possible levels of detail in the report
Table 152-6, "DBMS_SQLDIAG Constants - Report Section (possible values) Constants" describes possible report sections (comma delimited)
Table 152-7, "DBMS_SQLDIAG Constants - Problem Type Constants" describes possible values for the problem_type parameter of the CREATE_DIAGNOSIS_TASK Functions
Table 152-8, "DBMS_SQLDIAG Constants - Findings Filter Constants" describes possible values for the _sql_findings_mode parameter
Table 152-1 DBMS_SQLDIAG Constants - SQLDIAG Advisor Name
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
'SQL Repair Advisor' |
Name of SQL repair advisor as seen by the advisor framework |
Table 152-2 DBMS_SQLDIAG Constants - SQLDIAG Advisor Task Scope Parameter Values
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
' |
Detailed analysis of the problem which may take more time to execute |
|
|
|
' |
Brief analysis of the problem |
Table 152-3 DBMS_SQLDIAG Constants - SQLDIAG Advisor time_limit Constants
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
1800 |
Default time limit for analysis of the problem |
Table 152-4 DBMS_SQLDIAG Constants - Report Type (possible values) Constants
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
'HTML' |
Report from the REPORT_DIAGNOSIS_TASK Function in HTML form |
|
|
|
'TEXT' |
Report from the REPORT_DIAGNOSIS_TASK Function in text form |
|
|
|
'XML' |
Report from the REPORT_DIAGNOSIS_TASK Function in XML form |
Table 152-5 DBMS_SQLDIAG Constants - Report Level (possible values) Constants
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
'ALL' |
Complete report including annotations about statements skipped over |
|
|
|
'BASIC' |
Shows information about every statement analyzed, including recommendations not implemented |
|
|
|
'TYPICAL' |
Simple report shows only information about the actions taken by the advisor. |
Table 152-6 DBMS_SQLDIAG Constants - Report Section (possible values) Constants
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
'ALL' |
All statements |
|
|
|
'ERRORS' |
Statements with errors |
|
|
|
'FINDINGS' |
Tuning findings |
|
|
|
'INFORMATION' |
General information |
|
|
|
'PLANS' |
Explain plans |
|
|
|
'SUMMARY' |
Summary information |
Table 152-7 DBMS_SQLDIAG Constants - Problem Type Constants
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
1 |
User suspects this is a performance problem |
|
|
|
2 |
User suspects the query is giving inconsistent results |
|
|
|
3 |
User sees a crash in compilation |
|
|
|
4 |
User sees a crash in execution |
|
|
|
5 |
User to explore all alternative plans |
Table 152-8 DBMS_SQLDIAG Constants - Findings Filter Constants
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
1 |
Show all possible findings |
|
|
|
2 |
Show status of validation rules over structures |
|
|
|
3 |
Show only features used by the query |
|
|
|
4 |
Show the alternative plans generated by the advisor |
|
|
|
5 |
Show difference between two plans |
|
|
|
6 |
Mask info for testing |
|
|
|
7 |
Show features usage history |
|
|
|
8 |
Show the alternative plans generated by the advisor |
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',
Table 152-9 DBMS_SQLDIAG Package Subprograms
| Subprogram | Description |
|---|---|
|
Accepts a recommended SQL patch as recommended by the specified SQL diagnosis task |
|
|
Alters specific attributes of an existing SQL patch object |
|
|
Cancels a diagnostic task |
|
|
Creates a diagnostic task in order to diagnose a single SQL statement |
|
|
Creates the staging table used for transporting SQL patches from one system to another |
|
|
Drops a diagnostic task |
|
|
Drops the named SQL patch from the database |
|
|
Executes a diagnostic task |
|
|
Explains a SQL test case |
|
|
Exports a SQL test case to a directory |
|
|
Generates a SQL Test Case corresponding to the incident ID passed as an argument. |
|
|
Generates a SQL Test Case corresponding to the SQL passed as an argument |
|
|
Returns the value of fix control for a given bug number |
|
|
Imports a SQL test case |
|
|
Imports a SQL test case into a schema |
|
|
Initializes a |
|
|
Interrupts a diagnostic task |
|
|
Loads a |
|
|
SQL patches into the staging table created by the CREATE_STGTAB_SQLPATCH Procedure |
|
|
Reports on a diagnostic task |
|
|
Reports on a diagnostic task |
|
|
Resets a diagnostic task |
|
|
Resumes a diagnostic task |
|
|
Sets a diagnosis task parameter |
|
|
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 |
This procedure accepts a recommended SQL patch as recommended by the specified SQL diagnosis task.
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);
Table 152-10 ACCEPT_SQL_PATCH Function & Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the SQL diagnosis task |
|
|
Identifier of the advisor framework object representing the SQL statement associated to the diagnosis task |
|
|
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. |
|
|
User specified string describing the purpose of this SQL patch. Maximum size of description is 500. |
|
|
Category name which must match the value of the |
|
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. |
|
|
If the patch already exists, it will be replaced if this argument is |
|
|
If |
This procedure alters specific attributes of an existing SQL patch object.
DBMS_SQLDIAG.ALTER_SQL_PATCH ( name IN VARCHAR2, attribute_name IN VARCHAR2, value IN VARCHAR2);
Table 152-11 ALTER_SQL_PATCH Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of SQL patch to alter. |
|
|
Name of SQL patch to alter. Possible values:
This parameter is mandatory and is case sensitive. |
|
|
New value of the attribute. See |
This function creates a diagnostic task in order to diagnose a single SQL statement. It returns a SQL diagnosis task unique name
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;
Table 152-13 CREATE_DIAGNOSIS_TASK Function Parameters
| Parameter | Description |
|---|---|
|
|
Text of a SQL statement |
|
|
Set of bind values |
|
|
Username for who the statement/sqlset will be diagnosed |
|
|
Diagnosis scope (limited/comprehensive) |
|
|
Maximum duration in seconds for the diagnosis session |
|
|
Optional diagnosis task name |
|
description |
Maximum of 256 SQL diagnosis session description |
|
|
Determines the goal of the task. Possible values are:
|
|
|
Identifier of the statement |
|
|
Hash value of the SQL execution plan |
|
|
Sqlset name |
|
|
SQL predicate to filter the SQL from the SQL tuning set (STS) |
|
|
Object filter |
|
|
Order-by clause on the selected SQL |
|
|
Percentage on the sum of a ranking measure |
|
|
Top L(imit) SQL from (filtered/ranked) SQL |
|
|
Plan filter. It is applicable in case there are multiple plans (
|
|
|
Owner of the sqlset, or null for current schema owner |
This procedure creates the staging table used for transporting SQL patches from one system to another.
DBMS_SQLDIAG.CREATE_STGTAB_SQLPATCH ( table_name IN VARCHAR2, schema_name IN VARCHAR2 := NULL, tablespace_name IN VARCHAR2 := NULL);
Table 152-14 CREATE_STGTAB_SQLPATCH Procedure Parameters
| Parameter | Description |
|---|---|
|
|
(Mandatory) Name of the table to create (case-sensitive) |
|
|
Schema to create the table in, or |
|
|
Tablespace to store the staging table within, or |
This procedure exports a SQL test case to a directory.
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,
version IN VARCHAR2 := 'COMPATIBLE');
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);
Table 152-19 EXPORT_SQL_TESTCASE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Directory to store the various generated files |
|
|
Text of the SQL statement to export |
|
|
Incident ID containing the offending SQL |
|
|
Identifier of the statement in the cursor cache |
|
|
Name of the user schema to use to parse the SQL, defaults to |
|
|
List of bind values associated to the statement |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
If is |
|
|
Opaque control parameters. For example, to execute three times, set
|
|
|
How much time should we spend exporting the SQL test case |
|
|
An optional name for the SQL test case. This is used to prefix all the generated scripts |
|
|
Resulting testcase |
|
|
|
|
|
Version of database objects to be extracted. This option is only valid for
|
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)
a README.txt file that explain the usage of the TCB
the outlines used by the statement (ol.xml)
a list of parameters set in the exporting db/env (prmimp.sql)
a SQL monitor report, if any (smrpt.html)
an AWR report, if any (awrrpt.html)
a list of binds used in this statement (bndlst.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.
To export objects statistics history, the database compatibility should be set to 12.0 or higher.
This procedure does not export data and statistics on a Global Temporary Table (GTT).
The user can specify multiple parameters in the ctrlOptions encapsulated either b y using the <parameters> parent tag or without the parent tag.
<parameters> <parameter name="capture"> with_runtime_info </parameter> <parameter name="mexec_count"> 1 </parameter></parameters>
<parameter name="capture"> with_runtime_info </parameter> <parameter name="mexec_count"> 1 </parameter>'
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.
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;
Table 152-20 EXPORT_SQL_TESTCASE_DIR_BY_INC Function Parameters
| Parameter | Description |
|---|---|
|
|
Incident ID containing the offending SQL. For more information about Incidents, see Oracle Database Performance Tuning Guide. |
|
|
Directory path to the generated files |
|
|
|
|
|
|
|
|
|
|
|
If is |
|
|
Opaque control parameters. For example, to execute three times, set
|
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.
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;
Table 152-21 EXPORT_SQL_TESTCASE_DIR_BY_TXT Function Parameters
| Parameter | Description |
|---|---|
|
|
Incident ID containing the offending SQL |
|
|
Directory to store the various generated files |
|
|
Text of the SQL statement to explain |
|
|
Name of the user schema to use to parse the SQL, defaults to |
|
|
|
|
|
|
|
|
|
|
|
If is |
|
|
Opaque control parameters. For example, to execute three times, set
|
This function loads a sql_setrow from the trace file associated to an the given incident ID.
This procedure imports a SQL test case into a schema.
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);
Table 152-24 IMPORT_SQL_TESTCASE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Directory containing test case files |
|
|
Name of a file containing an XML document describing the SQL test case |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Opaque control parameters, of which only
|
|
|
|
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)
a README.txt file that explain the usage of the TCB
the outlines used by the statement (ol.xml)
a list of parameters set in the exporting db/env (prmimp.sql)
a SQL monitor report, if any (smrpt.html)
an AWR report, if any (awrrpt.html)
a list of binds used in this statement (bndlst.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.
The capture value used when invoking the EXPORT_SQL_TESTCASE Procedures must be used when calling this procedure.
This procedure initializes a sql_setrow from an incident ID.
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);
Table 152-25 INCIDENTID_2_SQL Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Identifier of the incident |
|
|
Resulting SQL |
|
|
Tentative type of SQL problem (currently among |
|
|
Error code if any otherwise it is set to |
|
|
Error message if any otherwise it is set to |
This function loads a SQLSET from a Test Case Builder file.
DBMS_SQLDIAG.LOAD_SQLSET_FROM_TCB (
directory IN VARCHAR2,
filename IN VARCHAR2,
sqlset_name IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
This procedure packs SQL patches into the staging table created by a call to the CREATE_STGTAB_SQLPATCH Procedure.
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);
Table 152-28 UPPACK_STGTAB_SQLPATCH Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of patch to pack (% wildcards acceptable, case-sensitive) |
|
|
Category to which to pack patches (% wildcards acceptable, case-insensitive) |
|
|
(Mandatory) Name of the table to use (case-sensitive) |
|
|
Schema where the table resides, or |
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.
This procedure automates the reproduction of the SQL Test Case.
DBMS_SQLDIAG.REPLAY_SQL_TESTCASE ( directory IN VARCHAR2, filename IN VARCHAR2, ctrlOptions IN VARCHAR2 := NULL, format IN VARCHAR2 := 'TEXT'); DBMS_SQLDIAG.REPLAY_SQL_TESTCASE ( directory IN VARCHAR2, sqlTestCase IN CLOB, ctrlOptions IN VARCHAR2 := NULL, format IN VARCHAR2 := 'TEXT');
Table 152-29 REPLAY_SQL_TESTCASE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Directory containing test case files |
|
|
Name of a file containing an XML document describing the SQL test case |
|
|
Opaque control parameters. For example, to execute three times, set
|
|
|
SQL test case |
|
|
Format of the replay report. Possible formats are: |
TCB Replay Mode: Execute
SELECT /* tcbdynpl_1 */ /*+ gather_plan_statistics */ * FROM (SELECT * FROM emp where emp.sal > 100) emp, dept WHERE emp.deptno = dept.deptno And emp.sal > 1000 /* tcbdynpl_1 */
Explain Plan
Plan Hash Value : 2219294842
-----------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 |
| * 1 | HASH JOIN | | 13 |
| 2 | NESTED LOOPS | | |
| 3 | NESTED LOOPS | | 13 |
| 4 | STATISTICS COLLECTOR | | |
| 5 | TABLE ACCESS FULL | DEPT | 4 |
| * 6 | INDEX RANGE SCAN | EMP_IDX_DEPTNO | |
| * 7 | TABLE ACCESS BY INDEX ROWID | EMP | 3 |
| * 8 | TABLE ACCESS FULL | EMP | 13 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
* 6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
* 7 - filter("EMP"."SAL">1000)
* 8 - filter("EMP"."SAL">1000)
Runtime Plan
Plan Hash Value : 2219294842
-------------------------------------------------------
| Id | Operation | Name | E-Card | A-Card |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | | 0 |
| * 1 | HASH JOIN | | 13 | 0 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 0 |
| * 3 | TABLE ACCESS FULL | EMP | 13 | 0 |
-------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
* 3 - filter("EMP"."SAL">1000)
REPLAY Note:
-----------
- Replay used dynamic sampling
- Replay forced Dynamic plan
This function reports on a diagnostic task. It returns a CLOB containing the desired report.
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;
Table 152-30 REPORT_DIAGNOSIS_TASK Function Parameters
| Parameter | Description |
|---|---|
|
|
Name of task to report |
|
|
Type of the report. Possible values are: TEXT, HTML, XML (see Table 152-4, "DBMS_SQLDIAG Constants - Report Type (possible values) Constants"). |
|
|
Format of the recommendations. Possible values are TYPICAL, BASIC, ALL (Table 152-5, "DBMS_SQLDIAG Constants - Report Level (possible values) Constants"). |
|
|
Particular section in the report. Possible values are: SUMMARY, FINDINGS, PLAN, INFORMATION, ERROR, ALL (Table 152-6, "DBMS_SQLDIAG Constants - Report Section (possible values) Constants"). |
|
|
Identifier of the advisor framework object that represents a given statement in a SQL Tuning Set (STS). |
|
|
Number of statements in a STS for which the report is generated |
|
|
Name of the task execution to use. If |
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)
DBMS_SQLDIAG.SET_DIAGNOSIS_TASK_PARAMETER (
taskname IN VARCHAR2,
parameter IN VARCHAR2, value IN NUMBER);
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).
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);
Table 152-34 UPPACK_STGTAB_SQLPATCH Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of patch to unpack (% wildcards acceptable, case-sensitive) |
|
|
Category from which to unpack patches (% wildcards acceptable, case-insensitive) |
|
|
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. |
|
|
(Mandatory) Name of the table to use (case-sensitive) |
|
|
Schema where the table resides, or |
Requires: ADMINISTER SQL MANAGEMENT OBJECT privilege and SELECT or READ 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.