Oracle DBMS_SQLDIAG
Version 11.2.0.3

General Information
AUTHID CURRENT_USER
Constants
Name Data Type Value
Advisor Name
ADV_SQL_DIAG_NAME VARCHAR2(18) 'SQL Repair Advisor'
SQLDIAG advisor task scope parameter values
SCOPE_LIMITED VARCHAR2(7) 'LIMITED'
SCOPE_COMPREHENSIVE VARCHAR2(13) 'COMPREHENSIVE'
SQLDIAG advisor time_limit constants
TIME_LIMIT_DEFAULT NUMBER 1800
Report Types
TYPE_HTML VARCHAR2(4) 'HTML'
TYPE_TEXT VARCHAR2(4) 'TEXT'
TYPE_XML VARCHAR2(3) 'XML'
Report Levels
LEVEL_ALL VARCHAR2(3) 'ALL'
LEVEL_BASIC VARCHAR2(5) 'BASIC'
LEVEL_TYPICAL VARCHAR2(7) 'TYPICAL'
Report Sections
SECTION_ALL VARCHAR2(3) 'ALL'
SECTION_ERRORS VARCHAR2(6) 'ERRORS'
SECTION_FINDINGS VARCHAR2(8) 'FINDINGS'
SECTION_INFORMATION VARCHAR2(11) 'INFORMATION'
SECTION_PLANS VARCHAR2(5) 'PLANS'
SECTION_SUMMARY VARCHAR2(7) 'SUMMARY'
Script Sections
REC_TYPE_ALL VARCHAR2(3) 'ALL'
REC_TYPE_INDEXES VARCHAR2(7) 'INDEXES'
REC_TYPE_SQL_PROFILES VARCHAR2(8) 'PROFILES'
REC_TYPE_STATS VARCHAR2(10) 'STATISTICS'
Capture Sections
MODE_ACCUMULATE_STATS NUMBER 2
MODE_REPLACE_OLD_STATS NUMBER 1
Problem Types
PROBLEM_TYPE_ALT_PLAN_GEN NUMBER 5
PROBLEM_TYPE_COMPILATION_ERROR NUMBER 3
PROBLEM_TYPE_EXECUTION_ERROR NUMBER 4
PROBLEM_TYPE_PERFORMANCE NUMBER 1
PROBLEM_TYPE_WRONG_RESULTS NUMBER 2
Findings Filters
SQLDIAG_FINDINGS_ALL NUMBER 1
SQLDIAG_FINDINGS_VALIDATION NUMBER 2
SQLDIAG_FINDINGS_FEATURES NUMBER 3
SQLDIAG_FINDINGS_FILTER_PLANS NUMBER 4
SQLDIAG_FINDINGS_CR_DIFF NUMBER 5
SQLDIAG_FINDINGS_MASK_VARIANT NUMBER 6
SQLDIAG_FINDINGS_OBJ_FEATURES NUMBER 7
SQLDIAG_FINDINGS_BASIC_INFO NUMBER 8
Mask Mode for Filtering Findings
SQLDIAG_MASK_COST NUMBER 2
SQLDIAG_MASK_NONE NUMBER 1
Dependencies
ANYDATA DBMS_SQLTCB_INTERNAL DUAL
DBMS_ADVISOR DBMS_SQLTUNE PLITBLM
DBMS_ASSERT DBMS_SQLTUNE_INTERNAL PRVT_SQLADV_INFRA
DBMS_LOB DBMS_SQLTUNE_UTIL0 PRVT_SQLPROF_INFRA
DBMS_MANAGEMENT_PACKS DBMS_SQLTUNE_UTIL1 SQLSET_ROW
DBMS_OUTPUT DBMS_SQLTUNE_UTIL2 SQL_BINDS
DBMS_PIPE DBMS_STATS V$SQL
DBMS_SMB DBMS_STATS_INTERNAL XMLSEQUENCE
DBMS_SMB_INTERNAL DBMS_SYS_ERROR XMLSEQUENCETYPE
DBMS_SQLDIAG_INTERNAL DBMS_XPLAN XMLTYPE
First Available 11.1.0.6
Security Model Owned by SYS with EXECUTE granted to PUBLIC

ALTER ANY SQL PATCH, CREATE ANY SQL PATCH, and/or DROP ANY SQL PATCH must be granted to utilize the corresponding functionality.
Source {ORACLE_HOME}/rdbms/admin/dbmsdiag.sql
Subprograms
ACCEPT_SQL_PATCH EXPORT_SQL_TESTCASE LOAD_SQLSET_FROM_TCB
ALTER_SQL_PATCH EXPORT_SQL_TESTCASE_DIR_BY_INC PACK_STGTAB_SQLPATCH
CANCEL_DIAGNOSIS_TASK EXPORT_SQL_TESTCASE_DIR_BY_TXT REPORT_DIAGNOSIS_TASK
CREATE_DIAGNOSIS_TASK GET_FIX_CONTROL RESET_DIAGNOSIS_TASK
CREATE_STGTAB_SQLPATCH GETSQL RESUME_DIAGNOSIS_TASK
DROP_DIAGNOSIS_TASK IMPORT_SQL_TESTCASE SET_DIAGNOSIS_TASK_PARAMETER
DROP_SQL_PATCH INCIDENTID_2_SQL UNPACK_STGTAB_SQLPATCH
DUMP_TRACE INTERRUPT_DIAGNOSIS_TASK Demo
EXECUTE_DIAGNOSIS_TASK    
 
ACCEPT_SQL_PATCH
This procedure accepts a SQL patch as recommended by the specified SQL tuning task

Overload 1		 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;
TBD
Overload 2 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);
TBD
 
ALTER_SQL_PATCH
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);
TBD
 
CANCEL_DIAGNOSIS_TASK
Cancels a diagnostic task dbms_sqldiag.cancel_diagnosis_task(task_name IN VARCHAR2);
exec dbms_sqldiag.cancel_diagnosis_task('ERROR_TASK');
 
CREATE_DIAGNOSIS_TASK
Creates a diagnostic task in order to diagnose a single SQL statement

Overload 1		 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;
See Demo below
Overload 2 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;
TBD
Overload 3 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;
TBD
 
CREATE_STGTAB_SQLPATCH
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);
TBD
 
DROP_DIAGNOSIS_TASK
Drops a diagnostic task dbms_sqldiag.drop_diagnosis_task(task_name IN VARCHAR2);
TBD
 
DROP_SQL_PATCH
This procedure drops the named SQL patch from the database dbms_sqldiag.drop_sql_patch(
name   IN VARCHAR2,
ignore IN BOOLEAN := FALSE);
TBD
 
DUMP_TRACE (new in 11.2.0.1)
Dump Optimizer Trace dbms_sqldiag.dump_trace(
p_sql_id       IN VARCHAR2,
p_child_number IN NUMBER   DEFAULT 0,
p_component    IN VARCHAR2 DEFAULT 'Optimizer',
p_file_id      IN VARCHAR2 DEFAULT NULL);
SELECT /* DUMP_TRACE */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%DUMP_TRACE%';

exec dbms_sqldiag.dump_trace('gk3bbazk1jj3h', 0);

-- the file was dumped as:
/app/oracle/product/diag/rdbms/orabeta/orabeta/trace/orabeta_dbrm_18833.trc
 
EXECUTE_DIAGNOSIS_TASK
Executes a diagnostic task dbms_sqldiag.execute_diagnosis_task(task_name IN VARCHAR2);
See Demo below
 
EXPORT_SQL_TESTCASE (new parameters in 11.2.0.2)
Export a SQL test case to a directory. This variant of the API has to be provided with the SQL information

Overload 1		 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,
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');
TBD
Export a SQL test case to a directory. This API extract the SQL information from an incident file.

Overload 2		 dbms_sqldiag.export_sql_testcase(
directory              IN     VARCHAR2,
incident_id            IN     VARCHAR2,
exportEnvironment      IN     BOOLEAN  := TRUE,
exportMetadata         IN     BOOLEAN  := TRUE,
exportData             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');
TBD
Export a SQL test case to a directory. This API allow the SQL Testcase to be generated from a cursor present in the cursor cache.
Use v$sql to get the SQL identifier and the SQL hash value.

Overload 3		 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  := 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');
TBD
 
EXPORT_SQL_TESTCASE_DIR_BY_INC (new parameters in 11.2.0.2)
Export a test case to a directory dbms_sqldiag.export_sql_testcase_dir_by_inc(
incident_id           IN NUMBER,
directory             IN VARCHAR2,
samplingPercent       IN VARCHAR2 := '0',
exportEnvironment     IN BOOLEAN := TRUE,
exportMetadata        IN BOOLEAN := TRUE,
exportPkgbody         IN BOOLEAN := FALSE,
preserveSchemaMapping IN BOOLEAN := FALSE,
version               IN VARCHAR2 := 'COMPATIBLE')
RETURN BOOLEAN;
-- go to $ORACLE_BASE/diag/rdbms/orabeta/orabeta/incident and view subdirectory incdir_3809

set serveroutput on

DECLARE
 v_inc NUMBER := 3809;
 v_dir VARCHAR2(30) := 'CTEMP';
BEGIN
  IF sys.dbms_sqldiag.export_sql_testcase_dir_by_inc(v_inc, v_dir) THEN
    dbms_output.put_line('Package Created');
  ELSE
    dbms_output.put_line('Package Creation Failure');
  END IF;
END;
/

SQL> DECLARE
2 v_inc NUMBER := 3809;
3 v_dir VARCHAR2(30) := 'CTEMP';
4 BEGIN
5 IF sys.dbms_sqldiag.export_sql_testcase_dir_by_inc(v_inc, v_dir) THEN
6 dbms_output.put_line('Package Created');
7 ELSE
8 dbms_output.put_line('Package Creation Failure');
9 END IF;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-39087: directory name TCBORA$PIPE$008C07960001 is invalid
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SQLDIAG", line 92
ORA-06512: at "SYS.DBMS_SQLDIAG", line 312
ORA-06512: at line 5

Note these are not the same as ADRCI indicent numbers
The docs are terrible on this
 
EXPORT_SQL_TESTCASE_DIR_BY_TXT (new parameters in 11.2.0.2)
Generates a SQL Test Case corresponding to the SQL 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 := NULL,
samplingPercent       IN VARCHAR2 := '0',
exportEnvironment     IN BOOLEAN := TRUE,
exportMetadata        IN BOOLEAN := TRUE,
exportPkgbody         IN BOOLEAN := FALSE,
preserveSchemaMapping IN BOOLEAN := FALSE,
version               IN VARCHAR2 := 'COMPATIBLE')
RETURN BOOLEAN;
TBD
 
GET_FIX_CONTROL (new in 11.2.0.1)
Returns the value of fix control for a given bug number dbms_sqldiag.get_fix_control(bug_number IN NUMBER) RETURN NUMBER;
TBD
 
GETSQL
Load a sql_setrow from the trace file associated with an incident ID dbms_sqldiag.getsql(incident_id IN VARCHAR2) RETURN SQLSET_ROW;
SELECT num_incident, check_name, name, timeout
FROM gv$hm_run;

desc sqlset_row

set serveroutput on

DECLARE
 z   VARCHAR2(100);
 ssr sqlset_row;
BEGIN
  ssr := dbms_sqldiag.getsql(1);
  dbms_output.put_line(z);
END;
/
 
IMPORT_SQL_TESTCASE (new parameters in 11.2.0.2)
Import a SQL Test case into a schema from a directory and a file name

Overload 1		 dbms_sqldiag.import_sql_testcase(
directory             IN VARCHAR2,
sqlTestCase           IN CLOB,
importEnvironment     IN BOOLEAN  := TRUE,
importMetadata        IN BOOLEAN  := TRUE,
importData            IN BOOLEAN  := FALSE,
importDiagnosis       IN BOOLEAN  := TRUE,
ignoreStorage         IN BOOLEAN  := TRUE,
ctrlOptions           IN VARCHAR2 := NULL,
preserveSchemaMapping IN BOOLEAN  := FALSE);
TBD
Initialize a sql_setrow from an incident ID. Given a valid incident ID this function parses the trace file and extract as much information as possible about the SQL that causes
the generation of this incident (SQL text, user name, binds, etc...).

Overload 2		 dbms_sqldiag.import_sql_testcase(
directory             IN VARCHAR2,
filename              IN VARCHAR2,
importEnvironment     IN BOOLEAN  := TRUE,
importMetadata        IN BOOLEAN  := TRUE,
importData            IN BOOLEAN  := FALSE,
importDiagnosis       IN BOOLEAN  := TRUE,
ignoreStorage         IN BOOLEAN  := TRUE,
ctrlOptions           IN VARCHAR2 := NULL,
preserveSchemaMapping IN BOOLEAN  := FALSE);
TBD
 
INCIDENTID_2_SQL
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);
TBD
 
INTERRUPT_DIAGNOSIS_TASK
Interrupts a diagnostic task dbms_sqldiag.interrupt_diagnosis_task(task_name IN VARCHAR2);
TBD
 
LOAD_SQLSET_FROM_TCB
Loads a SQLSET from Test Case Builder (TCB) file dbms_sqldiag.interrupt_diagnosis_task(task_name IN VARCHAR2);
TBD
 
PACK_STGTAB_SQLPATCH
SQL patches into the staging table created by the CREATE_STGTAB_SQLPATCH Procedure dbms_sqldiag.pack_stgtab_sqlpatch(
patch_name IN VARCHAR2 := '%',
patch_category IN VARCHAR2 := 'DEFAULT',
staging_table_name IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);
TBD
 
REPORT_DIAGNOSIS_TASK
Reports on a diagnostic task dbms_sqldiag.report_diagnosis_task(
task_name    IN VARCHAR2,
type         IN VARCHAR2 := TYPE_TEXT,
level        IN VARCHAR2 := LEVEL_ALL,
section      IN VARCHAR2 := SECTION_ALL,
object_id    IN NUMBER   := NULL,
result_limit IN NUMBER   := NULL,
owner_name   IN VARCHAR2 := NULL)
RETURN CLOB;
See Demo Below
 
RESET_DIAGNOSIS_TASK
Resets a diagnostic task dbms_sqldiag.reset_diagnosis_task(task_name IN VARCHAR2);
TBD
 
RESUME_DIAGNOSIS_TASK
Resume a diagnostic task dbms_sqldiag.resume_diagnosis_task(task_name IN VARCHAR2);
TBD
 
SET_DIAGNOSIS_TASK_PARAMETER
Sets a diagnosis task parameter

Renamed from SET_DIAGNOSIS_PARAMETER in 1.1.0.7		 dbms_sqldiag.set_diagnosis_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN NUMBER);
TBD
 
UNPACK_STGTAB_SQLPATCH
Unpacks from the staging table populated by a call to PACK_STGTAB_SQLPATCH, using the patch data stored in the staging table to create patches on this system dbms_sqldiag.unpack_stgtab_sqlpatch(
patch_name IN VARCHAR2 := '%',
patch_category IN VARCHAR2 := '%',
replace IN BOOLEAN,
staging_table_name IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);
TBD
 
Demo
DBMS_SQLDIAG Demo conn uwclass/uwclass

CREATE TABLE t (
a  VARCHAR2(3),
b  VARCHAR2(3),
c  VARCHAR2(3),
d  VARCHAR2(3));

INSERT INTO t VALUES ('a', 'b', 'c', 'd');
INSERT INTO t VALUES ('u', 'v', 'w', 'd');
INSERT INTO t VALUES ('a', 'b', 'c', 'z');
INSERT INTO t VALUES ('w', 'x', 'y', 'd');

SELECT * FROM t;

-- a critical error occurs
DELETE FROM uwclass.t t1
WHERE t1.a = 'a'
AND ROWID <> (
  SELECT MAX(ROWID)
  FROM uwclass.t t2
  WHERE t1.a= t2.a
  AND t1.b = t2.b AND t1.d=t2.d);

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
ORDER BY 1;

set long 1000000
set serveroutput on

DECLARE
 rep_out CLOB;
 t_id    VARCHAR2(50);
BEGIN
  -- create a diagnosis task
  t_id := dbms_sqldiag.create_diagnosis_task(
  sql_text => 'DELETE FROM uwclass.t t1 WHERE t1.a = ''a'' AND ROWID <>
  (SELECT   MAX(ROWID) FROM uwclass.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);

  -- run the diagnosis task
  dbms_sqldiag.execute_diagnosis_task(t_id);

  -- output the report
  rep_out := dbms_sqldiag.report_diagnosis_task(t_id,
  dbms_sqldiag.type_text);

  dbms_output.put_line('Report : ' || rep_out);
END;
/

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
ORDER BY 1;

-- apply the recommended patch
exec dbms_sqldiag.accept_sql_patch(task_name => 'error_task', task_owner => 'SYS', replace => TRUE);

-- test the patch
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);

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
ORDER BY 1;

dbms_sqltune.drop_tuning_task (task_name => 'error_task');

Related Topics
DBMS_ADVISOR
DBMS_SQLTUNE

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved