Oracle | DBMS

Oracle DBMS_SQLDIAG Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides an API to the SQL Diagnosability functionality used by the OEM Diagnostic Pack

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
SQLDiag Advisor Name
ADV_SQL_DIAG_NAME	VARCHAR2(18)	'SQL Repair Advisor'
Task Scopes
SCOPE_LIMITED	VARCHAR2(7)	'LIMITED'
SCOPE_COMPREHENSIVE	VARCHAR2(13)	'COMPREHENSIVE'
Advisor Time Limit
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(7)	'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_REPLACE_OLD_STATS	NUMBER	1
MODE_ACCUMULATE_STATS	NUMBER	2
Problem Types
PROBLEM_TYPE_PERFORMANCE	NUMBER	1
PROBLEM_TYPE_WRONG_RESULTS	NUMBER	2
PROBLEM_TYPE_COMPILATION_ERROR	NUMBER	3
PROBLEM_TYPE_EXECUTION_ERROR	NUMBER	4
PROBLEM_TYPE_ALT_PLAN_GEN	NUMBER	5
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_NONE	NUMBER	1
SQLDIAG_MASK_COST	NUMBER	2

Dependencies

ALL_USERS	DBMS_SQLDIAG_INTERNAL	DBMS_XPLAN
ANYDATA	DBMS_SQLPA	DBMS_XPLAN_INTERNAL
DBMS_ADVISOR	DBMS_SQLTCB_INTERNAL	DUAL
DBMS_ASSERT	DBMS_SQLTUNE	PLITBLM
DBMS_AUTO_INDEX_INTERNAL	DBMS_SQLTUNE_INTERNAL	PRVT_ADVISOR
DBMS_LOB	DBMS_SQLTUNE_UTIL0	PRVT_SQLADV_INFRA
DBMS_MANAGEMENT_PACKS	DBMS_SQLTUNE_UTIL1	PRVT_SQLPROF_INFRA
DBMS_PDB	DBMS_SQLTUNE_UTIL2	SQLSET_ROW
DBMS_PDB_CHECK_LOCKDOWN	DBMS_STANDARD	SQL_BINDS
DBMS_REDEFINITION	DBMS_STATS	V_$SQL
DBMS_SMB	DBMS_STATS_INTERNAL	XMLSEQUENCE
DBMS_SMB_INTERNAL	DBMS_STATS_INTERNAL_AGG	XMLTYPE
DBMS_SPM	DBMS_SYS_ERROR	XQSEQUENCE

Documented Yes

Exceptions

Error Code	Reason
ORA-13830	SQL profile or patch with category DEFAULT already exists for this SQL statement

First Available 11.1

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);

Security Model Owned by SYS with EXECUTE granted to PUBLIC (a grant that is a clear violation of the Principle of Least Privilege).

Use of this package requires the ADVISOR system privilege.

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	EXPLAIN_SQL_TESTCASE	PACK_STGTAB_SQLPATCH
ALTER_SQL_PATCH	EXPORT_SQL_TESTCASE	REPLAY_SQL_TESTCASE
CANCEL_DIAGNOSIS_TASK	EXPORT_SQL_TESTCASE_DIR_BY_INC	REPORT_DIAGNOSIS_TASK
CREATE_DIAGNOSIS_TASK	EXPORT_SQL_TESTCASE_DIR_BY_TXT	RESET_DIAGNOSIS_TASK
CREATE_SQL_PATCH	GETSQL	RESUME_DIAGNOSIS_TASK
CREATE_STGTAB_SQLPATCH	GET_FIX_CONTROL	SET_DIAGNOSIS_TASK_PARAMETER
DROP_DIAGNOSIS_TASK	IMPORT_SQL_TESTCASE	SET_TCB_TRACING
DROP_SQL_PATCH	INCIDENTID_2_SQL	SQL_DIAGNOSE_AND_REPAIR
DUMP_TRACE	INTERRUPT_DIAGNOSIS_TASK	UNPACK_STGTAB_SQLPATCH
EXECUTE_DIAGNOSIS_TASK	LOAD_SQLSET_FROM_TCB	Demo

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_SQL_PATCH

Creates a SQL patch based on a set of user specified hints for specific statements identified by the SQL text. A SQL patch is usually created automatically by the SQL Repair Advisor to prevent any errors during the compilation or execution of a SQL statement. This procedure provides a way to manually create a SQL patch based on a set of hints that resolves the issue.

Overload 1 dbms_sqldiag.create_sql_patch( sql_text IN CLOB, hint_text IN CLOB, name IN VARCHAR2 := NULL, decription IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL, validate IN BOOLEAN := TRUE) RETURN VARCHAR2;

TBD

Creates a SQL patch based on a set of user specified hints for specific statements identified by the SQL id. A SQL patch is usually created automatically by the SQL Repair Advisor to prevent any errors during the compilation or execution of a SQL statement. This procedure provides a way to manually create a SQL patch based on a set of hints that resolves the issue.

Overload 2 dbms_sqldiag.create_sql_patch( sql_id IN VARCHAR2, hint_text IN CLOB, name IN VARCHAR2 := NULL, decription IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL, validate IN BOOLEAN := TRUE) RETURN VARCHAR2;

SQL> variable srvrid number;

      SQL> exec :srvrid := 12

      

      PL/SQL procedure successfully completed.

      

      SELECT /* CREATE_PATCH1 */ COUNT(*), MAX(siid)

      FROM uwclass.serv_inst

      WHERE srvr_id = :srvrid;

      

        COUNT(*) MAX(SIID)

      ---------- ----------

              22   3714862

      

      SQL> SELECT sql_id, sql_text

        2  FROM v$sqlarea

        3  WHERE sql_fulltext LIKE '%CREATE_PATCH%';

      

      SQL_ID        SQL_TEXT

      ------------- -----------------------------------------------

      0taz20gu81tvd
      SELECT /* CREATE_PATCH1 */ COUNT(*), MAX(siid) 

                    FROM uwclass.serv_inst WHERE srvr_id = :srvrid

      

      SELECT is_bind_aware

      FROM v$sql

      WHERE sql_id = '0taz20gu81tvd';

      

      I

      -

      N

      

      DECLARE

       stxt   CLOB := 'SELECT /* CREATE_PATCH2 */ COUNT(*), MAX(siid) ' ||

                      'FROM uwclass.serv_inst WHERE srvr_id = :srvrid';

       htxt   CLOB := 'BIND_AWARE';

       retVal VARCHAR2(60);

      BEGIN

        retVal := sys.dbms_sqldiag.create_sql_patch(stxt, htxt);

        dbms_output.put_line(retVal);

      END;

      /

      SYS_SQLPTCH_0161abef311f0000

      

      PL/SQL procedure successfully completed.

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

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', -- alt. value 'Compiler' 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

EXPLAIN_SQL_TESTCASE

Explains a SQL test case. The docs say the input should be an XML document but give no indication of where it is supposed to come from. dbms_sqldiag.explain_sql_testcase(sqlTestCase IN CLOB) RETURN CLOB;

TBD

EXPORT_SQL_TESTCASE

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

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

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

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;

      /

GET_FIX_CONTROL

Returns the value of fix control for a given bug number dbms_sqldiag.get_fix_control(bug_number IN NUMBER) RETURN NUMBER;

TBD

IMPORT_SQL_TESTCASE

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

REPLAY_SQL_TESTCASE

Replays a SQL test case

Overload 1 dbms_sqldiag.explain_sql_testcase( directory IN VARCHAR2, sqlTestCase IN CLOB, ctrlOptions IN VARCHAR2 := NULL, format IN VARCHAR2 := 'TEXT') RETURN CLOB;

TBD

Overload 2 dbms_sqldiag.explain_sql_testcase( directory IN VARCHAR2, filename IN VARCHAR2, ctrlOptions IN VARCHAR2 := NULL, format IN VARCHAR2 := 'TEXT') RETURN CLOB;

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 dbms_sqldiag.set_diagnosis_task_parameter( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN NUMBER);

TBD

SET_TCB_TRACING

Enables/disables TCB tracing (for Oracle Support/Development use only) dbms_sqldiag.set_tcb_tracing(status IN BOOLEAN := TRUE);

exec dbms_sqldiag.set_tcb_tracing(FALSE);

SQL_DIAGNOSE_AND_REPAIR

Diagnose a given SQL statement for the given problem type.
-- It creates an incident, populate incident metadata with
-- required information like, sqlid, sql text, compilation env etc,
-- creates a diagnostic task, executes it and accepts SQL PATCH
-- recommendation for a given SQL statement.

Overload 1 dbms_sqldiag.sql_diagnose_and_repair( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE, auto_apply_patch IN VARCHAR2 := 'YES') RETURN NUMBER;

Overload 2 dbms_sqldiag.sql_diagnose_and_repair( sql_id IN CLOB, bind_list IN sql_binds := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE, auto_apply_patch IN VARCHAR2 := 'YES') RETURN NUMBER;

Overload 3 dbms_sqldiag.sql_diagnose_and_repair( incident_id IN NUMBER, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE, auto_apply_patch IN VARCHAR2 := 'YES') RETURN NUMBER;

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