Oracle DBMS_SQLDIAG_INTERNAL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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 univers: 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 V$INSTANCE
DBMS_SMB DBMS_SQLTUNE V$PARAMETER
DBMS_SMB_INTERNAL DBMS_SQLTUNE_INTERNAL V$SESSION_FIX_CONTROL
DBMS_SQLDIAG SQLPROF_ATTR V$SQLAREA_PLAN_HASH
DBMS_SQLDIAG_LIB SQLSET_ROW V$SQL_OPTIMIZER_ENV
Documented No
First Available 12.1.0
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtsqld.plb
Subprograms
 
I_CREATE_HINTSET
Undocumented dbms_sqldiag_internal.i_create_hint_set(
sql_text    IN CLOB,
hint_text   IN VARCHAR2,
name        IN VARCHAR2,
description IN VARCHAR2,
category    IN VARCHAR2,
validate    IN BOOLEAN);
TBD
 
I_CREATE_PATCH (rewritten in 12.2)
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 ID number dbms_sqldiag_internal.i_get_incidentid(id IN VARCHAR2) RETURN NUMBER;
TBD
 
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);
TBD

Related Topics
DBMS_SQLDIAG
DBMS_SPD
DBMS_SPM
DBMS_SQLTUNE
Packages
System Stats
What's New In 12cR1
What's New In 12cR2

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