Oracle How to Create a SQL Patch to add Hints ?
How to Create a SQL Patch to add Hints to Application SQL Statements (Doc ID 1931944.1) want use sql patch on below sql for hint select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/ select d.deptno, d.dname, max(sal) from emp e , dept d where e.deptno = d.deptno and d.deptno> 10 group by d.deptno,d.dname; set linesize 400 col sql_text for a50 select SQL_ID,SQL_TEXT from gv$sql where 1=1 and SQL_TEXT like '%and d.deptno> 10%'; SQL_ID SQL_TEXT ------------- -------------------------------------------------- fzsf6kw7q2vxt select d.deptno, d.dname, max(sal) from emp e , de pt d where e.deptno = d.deptno and d.deptno> 10 gr oup by d.deptno,d.dname from web !! declare v1 varchar2(128); begin v1 := dbms_sqldiag.create_sql_patch( sql_id => 'g2z10tbxyz6b0', name => 'validate_fk', -- hint_text => 'ignore_optim_embedded_hints' -- hint_text => 'parallel(a@sel$1 8)' -- worked -- hint_text => 'parallel(8)' -- worked -- hint_text => q'{opt_param('_fast_full_scan_enabled' 'false')}' -- worked hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}' ); dbms_output.put_line(v1); end; / SET SERVEROUTPUT ON DECLARE v_sql_id VARCHAR2 (13) := ''; v_patch_name VARCHAR2 (30); v_hint VARCHAR2 (4096) := 'NO_QUERY_TRANSFORMATION(@"SEL$1")'; BEGIN v_patch_name := DBMS_SQLDIAG.create_sql_patch (sql_id => v_sql_id, hint_text => v_hint); DBMS_OUTPUT.put_line (v_patch_name); END; / === set serveroutput on declare v1 varchar2(128); begin v1 := dbms_sqldiag.create_sql_patch( sql_id => 'fzsf6kw7q2vxt', name => 'optimizer_extended_cursor_sharing_rel', hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}' ); dbms_output.put_line(v1); end; / set linesize 400 col sql_text for a50 col name for a37 select name, status, created, sql_text from dba_sql_patches where name='optimizer_extended_cursor_sharing_rel'; set linesize 400 set numf 99999999999999999999999999 select SQL_ID,CHILD_NUMBER,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE,plan_hash_value plan_hash,EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'fzsf6kw7q2vxt'; SQL_ID CHILD_NUMBER SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE PLAN_HASH EXACT_MATCHING_SIGNATURE ------------- ------------ --------------- ------------------------------------- ------------------------------------- --------------------------- --------------------------- fzsf6kw7q2vxt 0 2006461124 9282672672555810008 fzsf6kw7q2vxt 2 optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 2006461124 9282672672555810008 col OUTLINE_HINTS for a40 select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints from xmltable('/outline_data/hint' passing (select xmltype(comp_data) xml from sys.sqlobj$data where signature = 9282672672555810008) ) x; OUTLINE_HINTS ---------------------------------------- opt_param('_optimizer_extended_cursor_sh aring_rel' 'none') If needed patch can be disabled using EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('optimizer_extended_cursor_sharing_rel', 'STATUS', 'DISABLED'); If you want to drop the patch, EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name=> 'optimizer_extended_cursor_sharing_rel'); === Test ---- declare v1 varchar2(128); primary:sys@IBRAC-ibrac2 sqlplus> 2 3 begin v1 := dbms_sqldiag.create_sql_patch( 4 5 sql_id => 'fzsf6kw7q2vxt', 6 name => 'optimizer_extended_cursor_sharing_rel', 7 hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}' 8 ); 9 dbms_output.put_line(v1); 10 end; 11 / optimizer_extended_cursor_sharing_rel PL/SQL procedure successfully completed.
hint_text => 'opt_param(''_OPTIMIZER_USE_FEEDBACK'',''FALSE'') OPT_PARAM(''_optimizer_extended_cursor_sharing'' ''none'') OPT_PARAM(''_optimizer_extended_cursor_sharing_rel'' ''none'')',DECLARE
l_patch_name VARCHAR2(32767);
BEGIN
l_patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
sql_id => 'x88dcq0rbj6g9',
hint_text => 'opt_param(''_OPTIMIZER_USE_FEEDBACK'',''FALSE'') OPT_PARAM(''_optimizer_extended_cursor_sharing'' ''none'') OPT_PARAM(''_optimizer_extended_cursor_sharing_rel'' ''none'')',
name => 'x88dcq0rbj6g9_sql_patch');
END;
/
hint_text => 'opt_param(''_OPTIMIZER_USE_FEEDBACK'',''FALSE'') OPT_PARAM(''_optimizer_extended_cursor_sharing'' ''none'') OPT_PARAM(''_optimizer_extended_cursor_sharing_rel'' ''none'')', name => 'x88dcq0rbj6g9_sql_patch');
END; /select * from table(dbms_xplan.display_cursor('sql_id',0,'OUTLINE'))SQL> select EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'c33dcq0rbjuuu0';
EXACT_MATCHING_SIGNATURE
----------------------------------
8311823694834541849
SQL>
select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
from xmltable('/outline_data/hint'
passing (select xmltype(comp_data) xml
from sys.sqlobj$data
where signature = 8311823694834541849)) x;
hint info
OUTLINE_HINTS
-------------------------------------------------------------------------------------------------
opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE')
set linesize 400 col sql_text for a50 col SQL_PROFILE for a15 col SQL_PATCH for a40 col SQL_PLAN_BASELINE for a35 select SQL_ID,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE,SQL_TEXT from v$sql where 1=1 --and SQL_TEXT like '%and d.deptno> 10%' and sql_id='fzsf6kw7q2vxt' ; SQL_ID SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE SQL_TEXT ------------- --------------- ---------------------------------------- ----------------------------------- -------------------------------------------------- fzsf6kw7q2vxt select d.deptno, d.dname, max(sal) from emp e , de pt d where e.deptno = d.deptno and d.deptno> 10 gr oup by d.deptno,d.dname fzsf6kw7q2vxt optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 select d.deptno, d.dname, max(sal) from emp e , de pt d where e.deptno = d.deptno and d.deptno> 10 gr set linesize 400 col sql_text for a50 col name for a37 col CATEGORY for a15 select name, status, created,category, sql_text from dba_sql_patches where name='optimizer_extended_cursor_sharing_rel'; NAME STATUS CREATED SQL_TEXT -------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------- -------------------------------------------------- optimizer_extended_cursor_sharing_rel ENABLED 13-FEB-24 02.57.04.000000 AM select d.deptno, d.dname, max(sal) from emp e , dept d where e.deptno = d.deptno and d.deptno> 10 group by d.deptno,d.dname SELECT CAST (EXTRACTVALUE (VALUE (x), '/hint') AS VARCHAR2 (500)) AS outline_hints FROM XMLTABLE ( '/outline_data/hint' PASSING (SELECT xmltype (comp_data) xml FROM sys.sqlobj$data WHERE signature = (SELECT signature FROM dba_sql_patches WHERE name = 'optimizer_extended_cursor_sharing_rel'))) x; OUTLINE_HINTS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ opt_param('_optimizer_extended_cursor_sharing_rel' 'none') set pagesize 300 select sql_id,address, hash_value ,count(*) from v$sql where 1=1 and SQL_ID='fzsf6kw7q2vxt' group by sql_id,address, hash_value --having count(*)>20 ; SQL_ID ADDRESS HASH_VALUE COUNT(*) ------------- ---------------- ---------- ---------- fzsf6kw7q2vxt 00000000CC66CA60 258043833 2 You can view all sql patches: select name,category,status,sql_text from dba_sql_patches; define sql_id='fzsf6kw7q2vxt' set numf 99999999999999999.99 linesize 400 col CHILD_NUMBER for 99999 col SQL_PATCH for a37 col SQL_PLAN_BASELINE for a37 select --SQL_ID sql_id1, SQL_ID,CHILD_NUMBER,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE, plan_hash_value plan_hash, executions, PX_SERVERS_EXECUTIONS pxe, cpu_time/1000 cpu_ms, elapsed_time/1000 ela_ms, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_ela, IO_INTERCONNECT_BYTES/1024/1024/1024 io_inter_gb, PHYSICAL_READ_BYTES/1024/1024/1024 PHYSICAL_READ_GB, PHYSICAL_READ_BYTES/1024/1024/decode(nvl(executions,0),0,1,executions) PIO_MB_PE, buffer_gets/decode(nvl(executions,0),0,1,executions) LIOS_PE, disk_reads/decode(nvl(executions,0),0,1,executions) PIOS_PE from gv$sql where 1=1 and sql_id = ('&sql_id') order BY inst_id, sql_id, hash_value, child_number,SQL_ID,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE ; SQL_ID CHILD_NUMBER SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE PLAN_HASH EXECUTIONS PXE CPU_MS ELA_MS AVG_ELA IO_INTER_GB PHYSICAL_READ_GB PIO_MB_PE LIOS_PE PIOS_PE ------------- --------------------- --------------- ------------------------------------- ------------------------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- fzsf6kw7q2vxt .00 2006461124.00 1.00 .00 28.01 32.25 .03 .00 .00 .01 208.00 1.00 fzsf6kw7q2vxt 2.00 optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 2006461124.00 1.00 .00 12.58 13.70 .01 .00 .00 .02 5.00 2.00 set numformat 99999999999999999999999999 select EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'fzsf6kw7q2vxt'; EXACT_MATCHING_SIGNATURE --------------------------- 2640606212120450132 Then pass the signature to below query to get the hints: col OUTLINE_HINTS for a40 select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints from xmltable('/outline_data/hint' passing (select xmltype(comp_data) xml from sys.sqlobj$data where signature = 9282672672555810008) ) x; col outline_hints for a40 select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints from xmltable('/outline_data/hint' passing (select xmltype(comp_data) xml from sys.sqlobj$data where signature = 9282672672555810008) ) x; define sql_id='fzsf6kw7q2vxt' set numf 99999999999999999.99 linesize 400 col CHILD_NUMBER for 99999 col SQL_PATCH for a37 col SQL_PLAN_BASELINE for a37 select --SQL_ID sql_id1, SQL_ID,CHILD_NUMBER,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE, plan_hash_value plan_hash, executions, PX_SERVERS_EXECUTIONS pxe, cpu_time/1000 cpu_ms, elapsed_time/1000 ela_ms, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_ela, IO_INTERCONNECT_BYTES/1024/1024/1024 io_inter_gb, PHYSICAL_READ_BYTES/1024/1024/1024 PHYSICAL_READ_GB, PHYSICAL_READ_BYTES/1024/1024/decode(nvl(executions,0),0,1,executions) PIO_MB_PE, buffer_gets/decode(nvl(executions,0),0,1,executions) LIOS_PE, disk_reads/decode(nvl(executions,0),0,1,executions) PIOS_PE --,SQL_ID, --SQL_PROFILE, --SQL_PATCH,SQL_PLAN_BASELINE from gv$sql where 1=1 and sql_id = ('&sql_id') order BY inst_id, sql_id, hash_value, child_number,SQL_ID,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE ; SQL_ID CHILD_NUMBER SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE PLAN_HASH EXECUTIONS PXE CPU_MS ELA_MS AVG_ELA IO_INTER_GB PHYSICAL_READ_GB PIO_MB_PE LIOS_PE PIOS_PE ------------- ------------ --------------- ------------------------------------- ------------------------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- fzsf6kw7q2vxt 0 2006461124.00 1.00 .00 28.01 32.25 .03 .00 .00 .01 208.00 1.00 fzsf6kw7q2vxt 2 optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 2006461124.00 1.00 .00 12.58 13.70 .01 .00 .00 .02 5.00 2.00 define sql_id='fzsf6kw7q2vxt' col PLAN_TABLE_OUTPUT for a200 SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 2, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID fzsf6kw7q2vxt, child number 2 ------------------------------------- select d.deptno, d.dname, max(sal) from emp e , dept d where e.deptno = d.deptno and d.deptno> 10 group by d.deptno,d.dname Plan hash value: 2006461124 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5 (100)| | | | | | 1 | HASH GROUP BY | | 1 | 20 | 5 (20)| 00:00:01 | 1088K| 1088K| 1/0/0| |* 2 | HASH JOIN | | 14 | 280 | 4 (0)| 00:00:01 | 1743K| 1743K| 1/0/0| |* 3 | TABLE ACCESS FULL| DEPT | 3 | 39 | 2 (0)| 00:00:01 | | | | | 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 | | | | -------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / D@SEL$1 4 - SEL$1 / E@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "D"@"SEL$1") FULL(@"SEL$1" "E"@"SEL$1") LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1") USE_HASH(@"SEL$1" "E"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("E"."DEPTNO"="D"."DEPTNO") 3 - filter("D"."DEPTNO">10) Note ----- - SQL patch "optimizer_extended_cursor_sharing_rel" used for this statement <<<<<<<<<<<<<<<<<<< sql is using sql patch - SQL plan baseline SQL_PLAN_81npdmnqyng6s61f3d804 used for this statement - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 57 rows selected.
====
set linesize 150 pagesize 300 define sql_id='9s45v5rhut05y' define plan_hash_value=3134758917 --SET LINESIZE 200 PAGESIZE 100 SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'&sql_id',plan_hash_value=>&plan_hash_value ,format=>'ALL +ALIAS +COST +BYTES +predicate +note +adaptive +report +outline'));
====
===========================================
below hint for Bind mismatch(33)? and test
/*+ opt_param('_optimizer_use_feedback' 'false')================================================
https://github.com/tanelpoder/tpt-oracle/blob/master/nonshared2.sql define 2='48vf4pg4g55 10' set linesize 200 col REASON_XML for a40 col REASON for a20 define 1=100 COL nonshared_sql_id HEAD SQL_ID FOR A13 COL nonshared_child HEAD CHILD# FOR A10 COL nonshared_reason_and_details HEAD REASON FOR A60 WORD_WRAP COL reason_xml FOR A100 WORD_WRAP &1 col REASON for a20 BREAK ON nonshared_sql_id SELECT '&2' nonshared_sql_id , EXTRACTVALUE(VALUE(xs), '/ChildNode/ChildNumber') nonshared_child , EXTRACTVALUE(VALUE(xs), '/ChildNode/reason') || ': ' || EXTRACTVALUE(VALUE(xs), '/ChildNode/details') nonshared_reason_and_details , VALUE(xs) reason_xml FROM TABLE ( SELECT XMLSEQUENCE(EXTRACT(d, '/Cursor/ChildNode')) val FROM ( SELECT --XMLElement("Cursor", XMLAgg(x.extract('/doc/ChildNode'))) -- the XMLSERIALIZE + XMLTYPE combo is included for avoiding a crash in qxuageag() XML aggregation function XMLTYPE (XMLSERIALIZE( DOCUMENT XMLElement("Cursor", XMLAgg(x.extract('/doc/ChildNode')))) ) d FROM v$sql_shared_cursor c , TABLE(XMLSEQUENCE(XMLTYPE('<doc>'||c.reason||'</doc>'))) x WHERE c.sql_id = '&2' and c.child_number < 5 ) ) xs / Bug 31211220 – High version count (cursor leaks) due to bind_equiv_failure (Doc ID 31211220.8) SQL's Are Not Getting Shared due to BIND_EQUIV_FAILURE in 12.2 (Doc ID 2635456.1)
====
example from Web
-- purge_cursor.sql
DECLARE
l_name VARCHAR2(64);
l_sql_text CLOB;
BEGIN
-- get address, hash_value and sql text
SELECT address||','||hash_value, sql_fulltext
INTO l_name, l_sql_text
FROM v$sqlarea
WHERE sql_id = '&&sql_id.';
-- not always does the job
SYS.DBMS_SHARED_POOL.PURGE (
name => l_name,
flag => 'C',
heaps => 1
);
-- create fake sql patch
SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH (
sql_text => l_sql_text,
hint_text => 'NULL',
name => 'purge_&&sql_id.',
description => 'PURGE CURSOR',
category => 'DEFAULT',
validate => TRUE
);
-- drop fake sql patch
SYS.DBMS_SQLDIAG.DROP_SQL_PATCH (
name => 'purge_&&sql_id.',
ignore => TRUE
);
END;
/
declare
v1 varchar2(128);
begin
v1 := dbms_sqldiag.create_sql_patch(
sql_id => 'g2z10tbxyz6b0',
name => 'validate_fk',
hint_text => 'ignore_optim_embedded_hints'
-- hint_text => 'parallel(a@sel$1 8)' -- worked
-- hint_text => 'parallel(8)' -- worked
-- hint_text => q'{opt_param('_fast_full_scan_enabled' 'false')}' -- worked
);
dbms_output.put_line(v1);
end;
/
