Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?
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.
dbms_sqldiag_internal.do_menu(
reportlist IN sys.dbms_sqldiag_internal.reporttable,
report_type IN VARCHAR2,
int_report_level IN NUMBER,
sqlid_in_memory IN BOOLEAN,
write_directory IN BOOLEAN,
report_clob IN OUT CLOB);
dbms_sqldiag_internal.i_accept_sql_patch(
task_name IN VARCHAR2,
object_id IN NUMBER,
name IN VARCHAR2,
description IN VARCHAR2,
category IN VARCHAR2,
task_owner IN VARCHAR2,
replace IN BOOLEAN,
force_match IN BOOLEAN,
origin IN BINARY_INTEGER)
RETURN VARCHAR2;
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);
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
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;
-- 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;
/
PL/SQL procedure successfully completed.
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;
/
PL/SQL procedure successfully completed.
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;
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;
dbms_sqldiag_internal.i_get_aem_incinfo_for_sqlid(
sqlid IN VARCHAR2,
exactsig OUT NUMBER,
forcesig OUT NUMBER,
incid OUT NUMBER,
probkey OUT VARCHAR2)
RETURN BOOLEAN;
BANNER_FULL
----------------------------------------------------------------
Oracle Database 23ai EE High Perf Release 23.0.0.0 - Production
Version 23.4.0.24.05
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;
/
*
ORA-24801: illegal parameter value in OCI lob function
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;
/
*
ORA-24801: illegal parameter value in OCI lob function
dbms_sqldiag_internal.i_record_mitigation_info(
sqlid IN VARCHAR2,
exactsig IN NUMBER,
forcesig IN NUMBER,
incid IN NUMBER,
probkey IN VARCHAR2,
starttime IN TIMESTAMP WITH TIME ZONE,
endtime IN TIMESTAMP WITH TIME ZONE);
dbms_sqldiag_internal.i_sql_aem_apply_sql_patch(
sql_id IN VARCHAR2,
incident_id IN NUMBER,
user_id IN NUMBER,
user_name IN VARCHAR2,
force_match IN BOOLEAN)
RETURN NUMBER;
TBD
I_SQL_AEM_DIAGNOSE_AND_REPAIR (new 23ai)
Undocumented
dbms_sqldiag_internal.i_sql_aem_diagnse_and_repair(
sql_id IN VARCHAR2,
sql_text IN CLOB,
bind_list IN sys.sql_binds,
time_limit IN NUMBER,
problem_type IN NUMBER,
parsing_schema IN VARCHAR2,
user_id IN NUMBER,
flags IN NUMBER)
RETURN NUMBER;
TBD
I_UPDATE_MITIGATION_INFO (new 23ai)
Undocumented
dbms_sqldiag_internal.i_update_mitigation_info(
sqlid IN VARCHAR2,
patch_name IN VARCHAR2,
force_match IN BOOLEAN);
