Oracle DBMS_SQLDIAG_INTERNAL
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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 18cR3
What's New In 19cR2

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