Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1) Part Number B28419-01 |
|
|
View PDF |
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:
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
.
The DBMS_SQLDIAG package uses the constants shown in the following tables:
Table 123-1, "DBMS_SQLDIAG Constants - SQLDIAG Advisor Name" describes the name of SQL repair advisor as seen by the advisor framework
Table 123-2, "DBMS_SQLDIAG Constants - SQLDIAG Advisor Task Scope Parameter Values" describes SQLDIAG advisor task scope parameter values
Table 123-3, "DBMS_SQLDIAG Constants - SQLDIAG Advisor time_limit Constants" describes SQLDIAG advisor time_limit
constants
Table 123-4, "DBMS_SQLDIAG Constants - Report Type (possible values) Constants" describes possible formats for a report
Table 123-5, "DBMS_SQLDIAG Constants - Report Level (possible values) Constants" describes possible levels of detail in the report
Table 123-6, "DBMS_SQLDIAG Constants - Report Section (possible values) Constants" describes possible report sections (comma delimited)
Table 123-7, "DBMS_SQLDIAG Constants - Problem Type Constants" describes possible values for the problem_type
parameter of the CREATE_DIAGNOSIS_TASK Functions
Table 123-8, "DBMS_SQLDIAG Constants - Findings Filter Constants" describes possible values for the _sql_findings_mode
parameter
Table 123-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 123-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 123-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 123-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 123-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 123-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 123-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 |
Table 123-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 |
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 via 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 123-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_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_SQL Function |
Imports a SQL test case |
INCIDENTID_2_SQL Procedure |
Initializes a sql_setrow from an incident ID |
INTERRUPT_DIAGNOSIS_TASK Procedure |
Interrupts a diagnostic task |
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 |
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 123-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
PATCH
privilege
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 123-11 ALTER_SQL_PATCH Procedure Parameters
Parameter | Description |
---|---|
name |
Name of SQL patch to alter. |
attribute_name |
Name of SQL patch to alter. Possible values:
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
This procedure cancels a diagnostic task.
Syntax
DBMS_SQLDIAG.CANCEL_DIAGNOSIS_TASK ( taskname IN VARCHAR2);
Parameters
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 123-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:
|
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:
|
sqlset_owner |
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.
Syntax
DBMS_SQLDIAG.CREATE_STGTAB_SQLPATCH ( table_name IN VARCHAR2, schema_name IN VARCHAR2 := NULL, tablespace_name IN VARCHAR2 := NULL);
Parameters
Table 123-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) |
This procedure drops a diagnostic task.
Syntax
DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK ( taskname IN VARCHAR2);
Parameters
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 123-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
This procedure executes a diagnostic task.
Syntax
DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK ( taskname IN VARCHAR2);
Parameters
Table 123-17 EXECUTE_DIAGNOSIS_TASK Procedure Parameters
Parameter | Description |
---|---|
taskname |
Name of task |
This procedure explains a SQL test case.
Syntax
DBMS_SQLDIAG.EXPLAIN_SQL_TESTCASE ( sqlTestCase IN CLOB) RETURN CLOB;
Parameters
Table 123-18 EXPLAIN_SQL_TESTCASE Procedure Parameters
Parameter | Description |
---|---|
sqlTestCase |
XML document describing the SQL test case |
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 := 'SYS', bind_list IN sql_binds := NULL, exportEnvironment IN BOOLEAN := TRUE, exportMetadata IN BOOLEAN := TRUE, exportData IN BOOLEAN := TRUE, samplingPercent IN NUMBER := 100, ctrlOptions IN VARCHAR2 := NULL, timeLimit IN NUMBER := 0, testcase_name IN VARCHAR2 := NULL, testcase IN OUT NOCOPY CLOB);
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 := TRUE, samplingPercent IN NUMBER := 100, ctrlOptions IN VARCHAR2 := NULL, timeLimit IN NUMBER := 0, testcase_name IN VARCHAR2 := NULL, testcase IN OUT NOCOPY CLOB);
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.BUILD_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, samplingPercent IN NUMBER := 100, ctrlOptions IN VARCHAR2 := NULL, timeLimit IN NUMBER := 0, testcase_name IN VARCHAR2 := NULL, testcase IN OUT NOCOPY CLOB);
Parameters
Table 123-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 |
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 |
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 |
testcaseMetadata |
Resulting test case |
incident_id |
Incident ID containing the offending SQL |
sql_id |
Identifier of the statement in the cursor cache |
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 123-20 EXPORT_SQL_TESTCASE_DIR_BY_INC Function Parameters
Parameter | Description |
---|---|
incident_id |
Incident ID containing the offending SQL |
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 |
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 123-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 |
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 123-22 GET_SQL Function Parameters
Parameter | Description |
---|---|
incident_id |
Identifier of the incident |
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 123-23 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 |
This procedure interrupts a diagnostic task.
Syntax
DBMS_SQLDIAG.INTERRUPT_DIAGNOSIS_TASK ( taskname IN VARCHAR2);
Parameters
Table 123-24 INTERRUPT_DIAGNOSIS_TASK Procedure Parameters
Parameter | Description |
---|---|
taskname |
Name of task |
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 123-25 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.
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 123-26 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 123-4, "DBMS_SQLDIAG Constants - Report Type (possible values) Constants"). |
level |
Format of the recommendations. Possible values are TYPICAL, BASIC, ALL (Table 123-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 123-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. |
This procedure resets a diagnostic task.
Syntax
DBMS_SQLDIAG.RESET_DIAGNOSIS_TASK ( taskname IN VARCHAR2);
Parameters
This procedure resumes a diagnostic path.
Syntax
DBMS_SQLDIAG.RESUME_DIAGNOSIS_TASK ( taskname IN VARCHAR2);
Parameters
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 123-29 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 |
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 123-30 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.