from web https://connor-mcdonald.com/2020/08/07/modifying-scheduler-windows/
set linesize 500 pagesize 400
col WINDOW_NAME for a25
col REPEAT_INTERVAL for a70
col DURATION for a25
col SCHEDULE_OWNER for a14
select SCHEDULE_OWNER ,window_name, repeat_interval, duration from dba_scheduler_windows
--where 1=1
order by WINDOW_NAME
;
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 *
9 rows selected.
declare
x sys.odcivarchar2list := sys.odcivarchar2list('SATURDAY');
BEGIN
for i in 1 .. x.count
loop
DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(60, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
end loop;
END;
/
output !!!
declare
x sys.odcivarchar2list := sys.odcivarchar2list('SATURDAY');
BEGIN
SQL> SQL> 2 3 4 5 for i in 1 .. x.count
6
7 loop
8 DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
9
10 DBMS_SCHEDULER.set_attribute(
11 name => 'SYS.'||x(i)||'_WINDOW',
12 attribute => 'REPEAT_INTERVAL',
13 value => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(60, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
end loop;
END;
/ 14 15 16 17 18 19 20 21 22 23
PL/SQL procedure successfully completed.
======================================================================
from
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
to
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
SATURDAY_WINDOW FREQ=WEEKLY;BYDAY=SAT;BYHOUR=03;BYMINUTE=0;BYSECOND=0 +000 01:00:00
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.
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;
below hint for Bind mismatch(33)? and test
/*+ opt_param('_optimizer_use_feedback' 'false')
================================================
set pagesize 60
set linesize 300
set trimspool on
column sql_text format a40
column plan_name format a30
column signature format 999999999999999999999
column hint format a50 wrap word
select
prf.plan_name,
prf.sql_text,
prf.signature,
extractvalue(value(hnt),'.') hint
from (
select
so.name plan_name,
so.signature,
so.category,
so.obj_type,
so.plan_id,
st.sql_text,
sod.comp_data
from
sqlobj$ so,
sqlobj$data sod,
sql$text st
where
sod.signature = so.signature
and st.signature = so.signature
and st.signature = sod.signature
and sod.category = so.category
and sod.obj_type = so.obj_type
and sod.plan_id = so.plan_id
and so.obj_type = 3
-- and so.name = 'SQL_Patch_xxxxxxxx'
order by signature, obj_type, plan_id
) prf,
table ( select xmlsequence(
extract(xmltype(prf.comp_data),'/outline_data/hint')
)
from dual ) hnt;
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)
====
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
