Oracle DBMS_SQLDIAG_INTERNAL
Version 18.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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_SQLTUNE SQLSET_ROW
DBMS_ASSERT DBMS_SQLTUNE_INTERNAL V$DIAG_INCIDENT
DBMS_LOB DBMS_STANDARD V$INSTANCE
DBMS_SMB DBMS_SYS_ERROR V$PARAMETER
DBMS_SMB_INTERNAL PLITBLM V$SESSION_FIX_CONTROL
DBMS_SQLDIAG SQLPROF_ATTR V$SQLAREA_PLAN_HASH
DBMS_SQLDIAG_LIB SQLSET_ROW V$SQL_OPTIMIZER_ENV
DBMS_SQLTCB_INTERNAL V$DIAG_INCIDENT  
Documented No
Exceptions
Error Code Reason
ORA-13796 Invalid value 5 specified for problem type
First Available 12.1.0
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtsqld.plb
Subprograms
 
CLOB_TO_SQLPROF_ATTR (new 18c)
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 (new 18c)
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.
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 maria.colgan@oracle.com (@SQLMaria)


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

Overload 1
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 (new 18c)
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 (new 18c)
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 (new 18c)
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_VALIDATE_PROBLEM_TYPE (new 18c)
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;
SQL> SELECT dbms_sqldiag_internal.i_validate_problem_type(2)
  2  FROM dual;

DBMS_SQLDIAG_INTERNAL.I_VALIDATE_PROBLEM_TYPE(2)
------------------------------------------------
                                               2

SQL> SELECT dbms_sqldiag_internal.i_validate_problem_type(99)
  2  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'

Related Topics
Built-in Functions
Built-in Packages
DBMS_SQLDIAG
DBMS_SPD
DBMS_SPM
DBMS_SQLTUNE
Packages
System Stats
What's New In 18c
What's New In 19c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx