Oracle | DBMS_SQLDIAG

Oracle DBMS_SQLDIAG_INTERNAL Version 21c

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 Internal utilities supporting the functionality of the DBMS_DIAG and other built-in packages. This built-in package contains one of the most valuable objects in the Oracle database and while it is a shame that it is not fully documented at docs.oracle.com it has been blogged about by one of the most respected resources in our universe: Maria Colgan. Look below at the demo of I_CREATE_PATCH and do not underestimate its value.

AUTHID DEFINER

Dependencies

AUX_STATS$	DBMS_SQLTCB_INTERNAL	SQLSET_ROW
DBMS_ASSERT	DBMS_SQLTUNE	V$DIAG_INCIDENT
DBMS_LOB	DBMS_SQLTUNE_INTERNAL	V$INSTANCE
DBMS_SMB	DBMS_STANDARD	V$PARAMETER
DBMS_SMB_INTERNAL	DBMS_SYS_ERROR	V$SESSION_FIX_CONTROL
DBMS_SQLDIAG	PLITBLM	V$SQLAREA_PLAN_HASH
DBMS_SQLDIAG_LIB	SQLPROF_ATTR	V$SQL_OPTIMIZER_ENV

Documented No

Exceptions

Error Code	Reason
ORA-13796	Invalid value 5 specified for problem type

First Available 12.1

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvtsqld.plb

Subprograms

CLOB_TO_SQLPROF_ATTR	I_GENERATE_SS_IMPORT	I_INCIDENTID_2_SQL
I_CREATE_HINTSET	I_GET_DBVERSION	I_REMOVE_INCIDENT
I_CREATE_INCIDENT	I_GET_INCIDENTID	I_SET_TRACEFILE_IDENTIFIER
I_CREATE_PATCH	I_GET_INCIDENT_FOR_SQLID	I_VALIDATE_PROBLEM_TYPE
I_GENERATE_PARAM_IMPORT	I_GET_INCIDENT_TYPE

CLOB_TO_SQLPROF_ATTR

Loads the sqlprof_attr varray with the contents of a properly constructed CLOB dbms_sqldiag_internal.clob_to_sqlprof_attr(cl IN CLOB) RETURN sys.sqlprof_attr;

TBD

I_CREATE_HINTSET

Undocumented

Overload 1 dbms_sqldiag_internal.i_create_hint_set( sql_text IN CLOB, hint_text IN CLOB, creator IN VARCHAR2, name IN VARCHAR2, description IN VARCHAR2, category IN VARCHAR2, validate IN BOOLEAN);

TBD

Overload 2 dbms_sqldiag_internal.i_create_hint_set( sql_text IN VARCHAR2, hint_text IN CLOB, creator IN VARCHAR2, name IN VARCHAR2, description IN VARCHAR2, category IN VARCHAR2, validate IN BOOLEAN);

TBD

I_CREATE_INCIDENT

Given an incident type provides an incident_id as output dbms_sqldiag_internal.i_create_incident( incident_id OUT NUMBER, incident_type IN VARCHAR2);

DECLARE

       outVal NUMBER;

      BEGIN

        dbms_sqldiag_internal.i_create_incident(outVal, 'Query_Performance');

        dbms_output.put_line(outVal);

      END;

      /

      92171

I_CREATE_PATCH

Patches SQL statements inside the CBO by inserting a hint

This syntax is valid in 12.1.0.2 but not in 12.2.0.1 which is the two overloads below.

Overload 1, Example 1 dbms_sqldiag_internal.i_create_patch( sql_text IN CLOB, hint_text IN VARCHAR2, name IN VARCHAR2, description IN VARCHAR2, category IN VARCHAR2, validate IN BOOLEAN);

/* the following demo was written and published by Maria Colgan of Oracle. The Library has done some minor reformatting to match the Library standard. */

      

      -- setup script for blog post on SQL Patch

      -- Author: Maria Colgan

      

      conn sh/sh@pdbdev

      

      CREATE TABLE emp(

      empno   NUMBER,

      ename   VARCHAR2(20),

      phone   VARCHAR2(20),

      deptno  NUMBER);

      

      INSERT INTO emp

      WITH tdata AS (

        SELECT rownum empno

        FROM all_objects

        WHERE <= 1000)

      SELECT rownum, dbms_random.string ('u', 20), dbms_random.string ('u', 20),

      CASE WHEN when rownum/100000 <= 0.001 THEN

        MOD(rownum, 10) ELSE 10 END

      FROM tdata a, tdata b

      WHERE rownum <= 100000;

      

      exec dbms_stats.gather_table_stats(USER, 'EMP', METHOD_OPT=>'FOR COLUMNS DEPTNO SIZE 10', CASCADE=>TRUE);

      

      CREATE INDEX emp_i1

      ON emp(deptno);

      

      -- check histogram has been created on DEPTNO (10 buckets)

      SELECT column_name, histogram, num_buckets

      FROM user_tab_cols

      WHERE table_name='EMP';

      

      --declare a value for our bind variable :deptno for our queries

      variable deptno number;

      exec :deptno := 9

      

      -- begin with a simple query on the emp table without the BIND_AWARE hint. Cursor will not be bind aware

      SELECT COUNT(*), MAX(empno)

      FROM emp

      WHERE deptno = :deptno;

      

      -- add a BIND_AWARE hint and execute again.

      -- this time the cursor will be marked bind aware immediately

      SELECT /*+ BIND_AWARE */ COUNT(*), MAX(empno)

      FROM emp

      WHERE deptno = :deptno;

      

      -- check in v$SQL we will see two cursors one IS_BIND_AWARE and one that is not

      SELECT sql_id, substr(sql_text,1,40), executions, is_bind_aware

      FROM v$sql

      WHERE sql_id IN ('09a1uvyty82b1', '4j5y55fd5rx77');

      

      -- create a SQL patch for the query

      conn sys@pdbdev as sysdba

      

      BEGIN

        sys.dbms_sqldiag_internal.i_create_patch(

            sql_text  => 'SELECT COUNT(*), MAX(empno) FROM emp WHERE deptno = :deptno',

            hint_text => 'BIND_AWARE',

            name      => 'test_patch');

      END;

      /

      

      conn sh/sh@pdbdev

      

      -- check the SQL patch is working

      SELECT COUNT(*), MAX(empno)

      FROM emp

      WHERE deptno = :deptno;

      

      SELECT sql_id, substr(sql_text,1,40), executions, is_bind_aware

      FROM v$sql

      WHERE sql_id IN ('09a1uvyty82b1', '4j5y55fd5rx77');

      

      -- try a different SQL statement this time and add a different hint

      explain plan for

      SELECT COUNT(*), MAX(empno)

      FROM (

        SELECT *

        FROM emp

        WHERE deptno = 10);

      

      SELECT * FROM TABLE(dbms_xplan.display(format=>'basic +note'));

      

      -- the initial plan shows a FTS we want an index access so lets add a hint

      EXPLAIN PLAN FOR

      SELECT /*+ INDEX(@SEL$2 emp) */ COUNT(*), MAX(empno)

      FROM (SELECT * FROM emp WHERE  deptno = 10);

      

      SELECT * FROM TABLE(dbms_xplan.display(format=>'basic +note'));

      

      -- we have the plan we want lets create a SQL patch for it

      conn sys@pdbdev as sysdba

      

      BEGIN

        sys.dbms_sqldiag_internal.i_create_patch(

            sql_text  => 'SELECT COUNT(*), MAX(empno) FROM (SELECT * FROM emp WHERE deptno = 10)',

            hint_text => 'INDEX(@SEL$2 emp)',

            name      => 'test_patch2');

      END;

      /

      

      conn sh/sh@pdbdev

      

      --see if the SQL patch has an effect

      EXPLAIN PLAN FOR

      SELECT COUNT(*), MAX(empno)

      FROM (

        SELECT *

        FROM emp

        WHERE deptno = 10);

      

      SELECT * FROM TABLE(dbms_xplan.display(format=>'basic +note'));

With the exception of the CREATOR column appears to map to DBMS_SQLDIAG.CREATE_SQL_PATCH
overload 2, below

Overload 1, Example 2 dbms_sqldiag_internal.i_create__patch( sql_id IN VARCHAR2, hint_text IN CLOB, creator IN VARCHAR2, name IN VARCHAR2, description IN VARCHAR2, category IN VARCHAR2, validate IN BOOLEAN) RETURN VARCHAR2;

TBD

With the exception of the CREATOR column appears to map to DBMS_SQLDIAG.CREATE_SQL_PATCH overload 1

Overload 2 dbms_sqldiag_internal.i_create__patch( sql_text IN CLOB, hint_text IN CLOB, creator IN VARCHAR2 := NULL, decription IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL, validate IN BOOLEAN := TRUE) RETURN VARCHAR2;

TBD

I_GENERATE_PARAM_IMPORT

Returns the optimizer parameters for a SQL Plan Management baseline dbms_sqldiag_internal.i_generate_param_import(l_sql_id IN VARCHAR2) RETURN VARCHAR2;

SELECT DISTINCT sql_id

      FROM v$sql

      WHERE rownum < 11;

      

      SELECT * FROM TABLE(dbms_xplan.display_cursor('gd90ygn1j4026'));

      

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

      | Id | Operation                           | Name                          |

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

      |  0 | SELECT STATEMENT                    |                               |

      |  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OPT_FINDING_OBJ$              |

      |* 2 |   INDEX RANGE SCAN                  | I_OPT_FINDING_OBJ_ID_OBJ_TYPE |

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

      

      SELECT dbms_sqldiag_internal.i_generate_param_import('gd90ygn1j4026')

      FROM dual;

      

      DBMS_SQLDIAG_INTERNAL.I_GENERATE_PARAM_IMPORT('GD90YGN1J4026')

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

      alter session set "optimizer_capture_sql_plan_baselines" = true;

      alter session set "optimizer_index_caching" = 50;

      alter session set "optimizer_index_cost_adj" = 50;

      alter session set "optimizer_mode" = choose;

I_GENERATE_SS_IMPORT

Returns the System Stats for for the optimizer dbms_sqldiag_internal.i_generate_ss_import RETURN VARCHAR2;

SELECT pname, pval1

      FROM aux_stats$

      WHERE sname = 'SYSSTATS_MAIN';

      

      SELECT dbms_sqldiag_internal.i_generate_ss_import

      FROM dual;

I_GET_DBVERSION

Returns the full version number of the database instance dbms_sqldiag_internal.i_get_dbversion RETURN VARCHAR2;

SELECT *

      FROM v$version;

      

      SELECT dbms_sqldiag_internal.i_get_dbversion

      FROM dual;

I_GET_INCIDENTID

Returns an Incident identifier dbms_sqldiag_internal.i_get_incidentid(id IN VARCHAR2) RETURN NUMBER;

TBD

I_GET_INCIDENT_FOR_SQLID

Generates an incident identifier dbms_sqldiag_internal.i_get_incident_for_sqlid( sql_id IN VARCHAR2, problem_type IN NUMBER, incident_id OUT NUMBER);

DECLARE

       outVal NUMBER;

      BEGIN

        dbms_sqldiag_internal.i_get_incident_for_sqlid('6ayccp4h1n07f', 1, outVal);

        dbms_output.put_line(outVal);

      END;

      /

      DECLARE

      *

      ERROR at line 1:

      ORA-24801: illegal parameter value in OCI lob function

      ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 247

      ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 303

      ORA-06512: at "SYS.DBMS_SQLDIAG", line 1329

      ORA-06512: at "SYS.DBMS_SQLDIAG", line 1364

      ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 637

      ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 637

      ORA-06512: at line 4

I_GET_INCIDENT_TYPE

Returns the incident type corresponding with a problem type dbms_sqldiag_internal.i_get_incident_type(problem_type IN NUMBER) RETURN VARCHAR2;

col incident_type format a30

      

      SELECT dbms_sqldiag_internal.i_get_incident_type(1) AS INCIDENT_TYPE

      FROM dual;

      

      INCIDENT_TYPE

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

      Query_Performance

      

      SELECT dbms_sqldiag_internal.i_get_incident_type(2) AS INCIDENT_TYPE

      FROM dual;

      

      INCIDENT_TYPE

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

      SQL_Failure

      

      SELECT dbms_sqldiag_internal.i_get_incident_type(3) AS INCIDENT_TYPE

      FROM dual;

      

      INCIDENT_TYPE

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

      SQL_Failure

      

      SELECT dbms_sqldiag_internal.i_get_incident_type(4) AS INCIDENT_TYPE

      FROM dual;

      

      INCIDENT_TYPE

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

      SQL_Failure

I_INCIDENTID_2_SQL

Undocumented dbms_sqldiag_internal.i_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, exec_user_id OUT NUMBER);

DECLARE

       sqlOut  sqlset_row;

       probOut NUMBER;

       ecode   BINARY_INTEGER;

       emesg   VARCHAR2(120);

       exuid   NUMBER;

      BEGIN

        dbms_sqldiag_internal.i_incidentid_2_sql(72130, sqlOut, probOut, ecode, emesg, exuid);

        dbms_output.put_line(probOut);

        dbms_output.put_line(ecode);

        dbms_output.put_line(emesg);

        dbms_output.put_line(exuid);

      END;

      /

      DECLARE

      *

      ERROR at line 1:

      ORA-24801: illegal parameter value in OCI lob function

      ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 247

      ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 303

      ORA-06512: at line 8

I_REMOVE_INCIDENT

Removes a diagnostic incident dbms_sqldiag_internal.i_remove_incident(incidentid IN NUMBER);

SELECT incident_id

      FROM v$diag_vincident;

      

      INCIDENT_ID

      -----------

            23393

            28609

            ....

            72130

            92171

      

      exec dbms_sqldiag_internal.i_remove_incident(92171);

      

      SELECT incident_id

      FROM v$diag_vincident;

      

      INCIDENT_ID

      -----------

            23393

            28609

            ....

            72130

I_SET_TRACEFILE_IDENTIFIER

Sets a new tracefile identifier

Tests did not demonstrate the new identifier is in use dbms_sqldiag_internal.i_set_tracefile_identifier(trace_identifier IN VARCHAR2);

exec dbms_sqldiag_internal.i_set_tracefile_identifier('MLIB_TEST');

      

      PL/SQL procedure successfully completed.

I_VALIDATE_PROBLEM_TYPE

Returns the problem_type value if it is valid, otherwise raises an ORA-13796 dbms_sqldiag_internal.i_validate_problem_type(problem_type IN NUMBER) RETURN NUMBER;

SELECT 
      dbms_sqldiag_internal.i_validate_problem_type(2)

      FROM dual;

      

      DBMS_SQLDIAG_INTERNAL.I_VALIDATE_PROBLEM_TYPE(2)

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

                                                     2

      

      SELECT dbms_sqldiag_internal.i_validate_problem_type(99)

      FROM dual;

      SELECT 
      dbms_sqldiag_internal.i_validate_problem_type(5)

      *

      ERROR at line 1:

      ORA-13796: invalid value 5 specified for problem type

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

      ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 591'