Oracle DBMS_SQLDIAG_INTERNAL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Internal utilities supporting the functionality of the DBMS_DIAG and other built-in packages
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
Patches SQL statements inside the CBO by inserting a hint 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 Patach
-- Author: Maria Colgan maria.colgan@oracle.com (@SQLMaria)


conn sh/sh@pdborcl

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@pdborcl 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@pdborcl

-- 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@pdborcl 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@pdborcl

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

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