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.
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='48vf4pg4g5510'
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)
