SELECT
p.sql_id
,p.plan_hash_value
,p.child_number
,t.phv2
FROM
v$sql_plan p
,xmltable('for $i in /other_xml/info
where $i/@type eq "plan_hash_2"
return $i'
passing xmltype(p.other_xml)
columns phv2 number path '/') t
WHERE p.sql_id = '&sql_id'
AND p.other_xml is not null;check sql profile exists or not
define sql_id='XXXXXX'
set lines 1000 pages 9999
col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150
col signature for 9999999999999999999999999
select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created, prof.task_exec_name,prof.FORCE_MATCHING, prof.status, prof.SIGNATURE,prof.SQL_TEXT
from
dba_sql_profiles prof,
gv$sql sql
where sql.sql_id in ('&sql_id')
order by
created;
SELECT NAME,SIGNATURE,CREATED,STATUS,SQL_TEXT FROM DBA_SQL_PROFILES
where 1=1
and name like '%XXXXXXX%'
order by created ;
Disable the SQL Profiles BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE( name => 'SYS_SQLPROF_789tfag56hjli0004', attribute_name => 'STATUS', value => 'DISABLED'); END; /
Drop the SQL Profile
exec dbms_sqltune.drop_sql_profile('coe_XXXXXX');
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Drop all profiles in a database,
don't run !!!!!!!!!!!!!!
declare
cursor c1 is select name from dba_sql_profiles;
begin
for r1 in c1 loop
dbms_sqltune.drop_sql_profile(r1.name);
end loop;
end;
/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
then create profile
[oracle@localhost ~]$ sqlplus system/system@//localhost:1521/free
*****************************************************************************************************************
--- coe_xfr_sql_profile.sql
--- coe_xfr_sql_profile.sql dg8duxp7zprxp PLAN_HASH_VALUE
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
-- Transfer (copy) a SQL Profile from PDBx on CDBa into PDBy on CDBb
SPO coe_xfr_sql_profile.log;
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SET SERVEROUT ON SIZE UNL;
REM
REM $Header: coe_xfr_sql_profile.sql 2020/03/10 carlos.sierra $
REM
REM AUTHOR
REM Carlos Sierra
REM
REM SCRIPT
REM coe_xfr_sql_profile.sql
REM
REM DESCRIPTION
REM This script generates another that contains the commands to
REM create a manual custom SQL Profile out of a known plan from
REM memory or AWR. The manual custom profile can be implemented
REM into the same SOURCE system where the plan was retrieved,
REM or into another similar TARGET system that has same schema
REM objects referenced by the SQL that generated the known plan.
REM
REM PRE-REQUISITES
REM 1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM 1. SQL_ID (required)
REM 2. Plan Hash Value for which a manual custom SQL Profile is
REM needed (required). A list of known plans is presented.
REM You may choose from list provided or enter a valid phv
REM from a version of the SQL modified with Hints.
REM
REM EXECUTION
REM 1. Connect into SQL*Plus as user with access to data dictionary.
REM Do not use SYS.
REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
REM plan hash value (parameters can be passed inline or until
REM requested).
REM
REM EXAMPLE
REM # sqlplus system
REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
REM SQL> START coe_xfr_sql_profile.sql;
REM
REM NOTES
REM 1. For possible errors see coe_xfr_sql_profile.log
REM 2. If SQLT is installed in SOURCE, you can use instead:
REM sqlt/utl/sqltprofile.sql
REM 3. Be aware that using DBMS_SQLTUNE requires a license for
REM Oracle Tuning Pack.
REM 4. Use a DBA user but not SYS.
REM 5. If you get "ORA-06532: Subscript outside of limit, ORA-06512: at line 1"
REM Then you may consider this change (only in a test and disposable system):
REM create or replace TYPE sys.sqlprof_attr AS VARRAY(5000) of VARCHAR2(500);
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id = '&1.';
UNDEF 1;
PRO
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND dbid = (SELECT dbid FROM v$database)
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs,
SUM(executions) executions
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_delta)/SUM(executions_delta) avg_et_secs,
SUM(executions_delta) executions
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
AND executions_delta > 0
GROUP BY
plan_hash_value )
SELECT
TO_CHAR(ROUND(m.avg_et_secs/1e6, 6), '999,990.000000') avg_et_secs_mem,
TO_CHAR(ROUND(a.avg_et_secs/1e6, 6), '999,990.000000') avg_et_secs_awr,
p.plan_hash_value,
m.executions executions_mem,
a.executions executions_awr
--TO_CHAR(ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 6), '999,990.000000') avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
NVL(m.avg_et_secs, a.avg_et_secs) NULLS LAST, a.avg_et_secs;
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
PRO
DEF plan_hash_value = '&2';
UNDEF 2;
PRO
PRO Values passed to coe_xfr_sql_profile:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO SQL_ID : "&&sql_id."
PRO PLAN_HASH_VALUE: "&&plan_hash_value."
PRO
SET TERM OFF ECHO ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
-- trim parameters
COL sql_id NEW_V sql_id FOR A30;
COL plan_hash_value NEW_V plan_hash_value FOR A30;
SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;
VAR sql_text CLOB;
VAR sql_text2 CLOB;
VAR other_xml CLOB;
EXEC :sql_text := NULL;
EXEC :sql_text2 := NULL;
EXEC :other_xml := NULL;
-- get sql_text from memory
DECLARE
l_sql_text VARCHAR2(32767);
BEGIN -- 10g see bug 5017909
FOR i IN (SELECT DISTINCT piece, sql_text
FROM gv$sqltext_with_newlines
WHERE sql_id = TRIM('&&sql_id.')
ORDER BY 1, 2)
LOOP
IF :sql_text IS NULL THEN
DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
END IF;
-- removes NUL characters
l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
-- adds a NUL character at the end of each line
DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
END LOOP;
-- if found in memory then sql_text is not null
IF :sql_text IS NOT NULL THEN
DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
:sql_text := NULL;
END;
/
SELECT :sql_text FROM DUAL;
-- get sql_text from awr
DECLARE
l_sql_text VARCHAR2(32767);
l_clob_size NUMBER;
l_offset NUMBER;
BEGIN
IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
SELECT sql_text
INTO :sql_text2
FROM dba_hist_sqltext
WHERE sql_id = TRIM('&&sql_id.')
AND sql_text IS NOT NULL
AND ROWNUM = 1;
END IF;
-- if found in awr then sql_text2 is not null
IF :sql_text2 IS NOT NULL THEN
l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0);
l_offset := 1;
DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
-- store in clob as 64 character pieces plus a NUL character at the end of each piece
WHILE l_offset < l_clob_size
LOOP
IF l_clob_size - l_offset > 64 THEN
l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' ');
ELSE -- last piece
l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' ');
END IF;
DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
l_offset := l_offset + 64;
END LOOP;
DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
:sql_text := NULL;
END;
/
SELECT :sql_text2 FROM DUAL;
SELECT :sql_text FROM DUAL;
-- validate sql_text
SET TERM ON;
BEGIN
IF :sql_text IS NULL THEN
RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
END IF;
END;
/
SET TERM OFF;
-- get other_xml from memory
BEGIN
FOR i IN (SELECT other_xml
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
child_number, id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from awr
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND dbid = (SELECT dbid FROM v$database)
AND other_xml IS NOT NULL
ORDER BY
id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from memory from modified SQL
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM gv$sql_plan
WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
--WHERE full_plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
child_number, id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from awr from modified SQL
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM dba_hist_sql_plan
WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
--WHERE full_plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND dbid = (SELECT dbid FROM v$database)
AND other_xml IS NOT NULL
ORDER BY
id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
:other_xml := NULL;
END;
/
SELECT :other_xml FROM DUAL;
-- validate other_xml
SET TERM ON;
BEGIN
IF :other_xml IS NULL THEN
RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
END IF;
END;
/
SET TERM OFF;
-- generates script that creates sql profile in target system:
SET ECHO OFF;
PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.
SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;
SET SERVEROUT ON SIZE UNL FOR WOR;
SPO OFF;
SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;
DECLARE
l_pos NUMBER;
l_clob_size NUMBER;
l_offset NUMBER;
l_sql_text VARCHAR2(32767);
l_len NUMBER;
l_hint VARCHAR2(32767);
BEGIN
DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');
DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.4.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' carlos.sierra $');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM AUTHOR');
DBMS_OUTPUT.PUT_LINE('REM carlos.sierra@oracle.com');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
DBMS_OUTPUT.PUT_LINE('REM coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
DBMS_OUTPUT.PUT_LINE('REM This script is generated by coe_xfr_sql_profile.sql');
DBMS_OUTPUT.PUT_LINE('REM It contains the SQL*Plus commands to create a custom');
DBMS_OUTPUT.PUT_LINE('REM SQL Profile for SQL_ID &&sql_id. based on plan hash');
DBMS_OUTPUT.PUT_LINE('REM value &&plan_hash_value..');
DBMS_OUTPUT.PUT_LINE('REM The custom SQL Profile to be created by this script');
DBMS_OUTPUT.PUT_LINE('REM will affect plans for SQL commands with signature');
DBMS_OUTPUT.PUT_LINE('REM matching the one for SQL Text below.');
DBMS_OUTPUT.PUT_LINE('REM Review SQL Text and adjust accordingly.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
DBMS_OUTPUT.PUT_LINE('REM None.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
DBMS_OUTPUT.PUT_LINE('REM SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM NOTES');
DBMS_OUTPUT.PUT_LINE('REM 1. Should be run as SYSTEM or SYSDBA.');
DBMS_OUTPUT.PUT_LINE('REM 2. User must have CREATE ANY SQL PROFILE privilege.');
DBMS_OUTPUT.PUT_LINE('REM 3. SOURCE and TARGET systems can be the same or similar.');
DBMS_OUTPUT.PUT_LINE('REM 4. To drop this custom SQL Profile after it has been created:');
DBMS_OUTPUT.PUT_LINE('REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');
DBMS_OUTPUT.PUT_LINE('REM 5. Be aware that using DBMS_SQLTUNE requires a license');
DBMS_OUTPUT.PUT_LINE('REM for the Oracle Tuning Pack.');
DBMS_OUTPUT.PUT_LINE('REM 6. If you modified a SQL putting Hints in order to produce a desired');
DBMS_OUTPUT.PUT_LINE('REM Plan, you can remove the artifical Hints from SQL Text pieces below.');
DBMS_OUTPUT.PUT_LINE('REM By doing so you can create a custom SQL Profile for the original');
DBMS_OUTPUT.PUT_LINE('REM SQL but with the Plan captured from the modified SQL (with Hints).');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');
DBMS_OUTPUT.PUT_LINE('VAR signaturef NUMBER;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('DECLARE');
DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');
DBMS_OUTPUT.PUT_LINE('h SYS.SQLPROF_ATTR;');
DBMS_OUTPUT.PUT_LINE('PROCEDURE wa (p_line IN VARCHAR2) IS');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);');
DBMS_OUTPUT.PUT_LINE('END wa;');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);');
DBMS_OUTPUT.PUT_LINE('-- SQL Text pieces below do not have to be of same length.');
DBMS_OUTPUT.PUT_LINE('-- So if you edit SQL Text (i.e. removing temporary Hints),');
DBMS_OUTPUT.PUT_LINE('-- there is no need to edit or re-align unmodified pieces.');
l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text), 0);
l_offset := 1;
WHILE l_offset < l_clob_size
LOOP
l_pos := DBMS_LOB.INSTR(:sql_text, CHR(00), l_offset);
IF l_pos > 0 THEN
l_len := l_pos - l_offset;
ELSE -- last piece
l_len := l_clob_size - l_pos + 1;
END IF;
l_sql_text := DBMS_LOB.SUBSTR(:sql_text, l_len, l_offset);
/* cannot do such 3 replacement since a line could end with a comment using "--"
l_sql_text := REPLACE(l_sql_text, CHR(10), ' '); -- replace LF with SP
l_sql_text := REPLACE(l_sql_text, CHR(13), ' '); -- replace CR with SP
l_sql_text := REPLACE(l_sql_text, CHR(09), ' '); -- replace TAB with SP
*/
l_offset := l_offset + l_len + 1;
IF l_len > 0 THEN
IF INSTR(l_sql_text, '''[') + INSTR(l_sql_text, ']''') = 0 THEN
l_sql_text := '['||l_sql_text||']';
ELSIF INSTR(l_sql_text, '''{') + INSTR(l_sql_text, '}''') = 0 THEN
l_sql_text := '{'||l_sql_text||'}';
ELSIF INSTR(l_sql_text, '''<') + INSTR(l_sql_text, '>''') = 0 THEN
l_sql_text := '<'||l_sql_text||'>';
ELSIF INSTR(l_sql_text, '''(') + INSTR(l_sql_text, ')''') = 0 THEN
l_sql_text := '('||l_sql_text||')';
ELSIF INSTR(l_sql_text, '''"') + INSTR(l_sql_text, '"''') = 0 THEN
l_sql_text := '"'||l_sql_text||'"';
ELSIF INSTR(l_sql_text, '''|') + INSTR(l_sql_text, '|''') = 0 THEN
l_sql_text := '|'||l_sql_text||'|';
ELSIF INSTR(l_sql_text, '''~') + INSTR(l_sql_text, '~''') = 0 THEN
l_sql_text := '~'||l_sql_text||'~';
ELSIF INSTR(l_sql_text, '''^') + INSTR(l_sql_text, '^''') = 0 THEN
l_sql_text := '^'||l_sql_text||'^';
ELSIF INSTR(l_sql_text, '''@') + INSTR(l_sql_text, '@''') = 0 THEN
l_sql_text := '@'||l_sql_text||'@';
ELSIF INSTR(l_sql_text, '''#') + INSTR(l_sql_text, '#''') = 0 THEN
l_sql_text := '#'||l_sql_text||'#';
ELSIF INSTR(l_sql_text, '''%') + INSTR(l_sql_text, '%''') = 0 THEN
l_sql_text := '%'||l_sql_text||'%';
ELSIF INSTR(l_sql_text, '''$') + INSTR(l_sql_text, '$''') = 0 THEN
l_sql_text := '$'||l_sql_text||'$';
ELSE
l_sql_text := CHR(96)||l_sql_text||CHR(96);
END IF;
DBMS_OUTPUT.PUT_LINE('wa(q'''||l_sql_text||''');');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CLOSE(sql_txt);');
DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');
FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint
FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)
LOOP
l_hint := i.hint;
WHILE NVL(LENGTH(l_hint), 0) > 0
LOOP
IF LENGTH(l_hint) <= 500 THEN
DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
l_hint := NULL;
ELSE
l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
l_hint := ' '||SUBSTR(l_hint, l_pos);
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');
DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');
DBMS_OUTPUT.PUT_LINE(':signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);');
DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
DBMS_OUTPUT.PUT_LINE('sql_text => sql_txt,');
DBMS_OUTPUT.PUT_LINE('profile => h,');
DBMS_OUTPUT.PUT_LINE('name => ''coe_&&sql_id._&&plan_hash_value.'',');
DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'' ''||:signaturef||'''',');
DBMS_OUTPUT.PUT_LINE('category => ''DEFAULT'',');
DBMS_OUTPUT.PUT_LINE('validate => TRUE,');
DBMS_OUTPUT.PUT_LINE('replace => TRUE,');
DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.FREETEMPORARY(sql_txt);');
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('/');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');
DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');
DBMS_OUTPUT.PUT_LINE('PRINT signature');
DBMS_OUTPUT.PUT_LINE('PRINT signaturef');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');
DBMS_OUTPUT.PUT_LINE('SPO OFF;');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');
END;
/
SPO OFF;
SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;
SET SERVEROUT OFF;
PRO
PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
PRO on TARGET system in order to create a custom SQL Profile
PRO with plan &&plan_hash_value linked to adjusted sql_text.
PRO
UNDEFINE 1 2 sql_id plan_hash_value
CL COL
PRO
PRO COE_XFR_SQL_PROFILE completed.
**********************************************************
set linesize window
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
set lines 300
col sql_sql_text head SQL_TEXT format a50 word_wrap
col sql_child_number head CH# for 9999
column EXACT_MATCHING_SIGNATURE format 99999999999999999999
column force_matching_signature format 99999999999999999999
select
s.sql_id,
s.hash_value,
s.plan_hash_value,
s.force_matching_signature,
s.EXACT_MATCHING_SIGNATURE,
t.phv2,
s.sql_text sql_sql_text
from
v$sql s,
v$sql_plan p
,xmltable('for $i in /other_xml/info
where $i/@type eq "plan_hash_2"
return $i'
passing xmltype(p.other_xml)
columns phv2 number path '/'
) t
where
s.sql_id=p.sql_id
AND
s.plan_hash_value=p.plan_hash_value
AND
s.child_number=p.CHILD_NUMBER
AND
p.other_xml is not null
-- AND s.sql_text like 'sql_text%'
and s.sql_id in ('dg8duxp7zprxp','29v6w386wqnzt')
order by 3 desc
coe_xfr_sql_profile.sql dg8duxp7zprxp 1288918533
coe_xfr_sql_profile.sql dg8duxp7zprxp PLAN_HASH_VALUE
SQL> ed coe_xfr_sql_profile
SQL> @coe_xfr_sql_profile
Parameter 1:
SQL_ID (required)
Enter value for 1: dg8duxp7zprxp
AVG_ET_SECS_MEM AVG_ET_SECS_AWR PLAN_HASH_VALUE EXECUTIONS_MEM EXECUTIONS_AWR
--------------- --------------- --------------- -------------- --------------
0.017320 1130139812 1
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1130139812
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "dg8duxp7zprxp"
PLAN_HASH_VALUE: "1130139812"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql
on TARGET system in order to create a custom SQL Profile
with plan 1130139812 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
##########################
SQL>!grep -i force coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
SQL>
SQL>@coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql 11.4.4.4 2025/01/03 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID dg8duxp7zprxp based on plan hash
SQL>REM value 1130139812.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_dg8duxp7zprxp_1130139812');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[with s1 as (
15 select
16 a.n2 n2,
17 a.vc vc
18 from
19 t1 a
20 )]');
21 wa(q'[,
22 s2 as (
23 select
24 b.n4 n4,
25 b.vd vd
26 from
27 t2 b
28 )
29 se]');
30 wa(q'[lect
31 s1.n2,
32 s1.vc,
33 s2.n4,
34 s2.vd,
35 s1.n2||s2.n4 as N
36 fro]');
37 wa(q'[m
38 s1,
39 s2
40 where
41 s1.n2 = s2.n4
42 and s1.vc = 'xY'
43 AND s2]');
44 wa(q'[.n4 = 0]');
45 DBMS_LOB.CLOSE(sql_txt);
46 h := SYS.SQLPROF_ATTR(
47 q'[BEGIN_OUTLINE_DATA]',
48 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
49 q'[OPTIMIZER_FEATURES_ENABLE('23.1.0')]',
50 q'[DB_VERSION('23.1.0')]',
51 q'[ALL_ROWS]',
52 q'[OUTLINE_LEAF(@"SEL$18780C30")]',
53 q'[MERGE(@"SEL$1" >"SEL$3")]',
54 q'[MERGE(@"SEL$2" >"SEL$3")]',
55 q'[OUTLINE(@"SEL$3")]',
56 q'[OUTLINE(@"SEL$1")]',
57 q'[OUTLINE(@"SEL$2")]',
58 q'[FULL(@"SEL$18780C30" "B"@"SEL$2")]',
59 q'[INDEX_RS_ASC(@"SEL$18780C30" "A"@"SEL$1" ("T1"."N2"))]',
60 q'[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$18780C30" "A"@"SEL$1")]',
61 q'[LEADING(@"SEL$18780C30" "B"@"SEL$2" "A"@"SEL$1")]',
62 q'[USE_MERGE_CARTESIAN(@"SEL$18780C30" "A"@"SEL$1")]',
63 q'[END_OUTLINE_DATA]');
64 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
65 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
66 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
67 sql_text => sql_txt,
68 profile => h,
69 name => 'coe_dg8duxp7zprxp_1130139812',
70 description => 'coe dg8duxp7zprxp 1130139812 '||:signature||' '||:signaturef||'',
71 category => 'DEFAULT',
72 validate => TRUE,
73 replace => TRUE,
74 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
75 DBMS_LOB.FREETEMPORARY(sql_txt);
76 END;
77 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
5336375310114799588
SIGNATUREF
---------------------
14577638555275281096
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_dg8duxp7zprxp_1130139812 completed
col name for a35
col created for a30
col FORCE_MATCHING for a25
SELECT
--s.sql_id,
-- s.plan_hash_value,
p.name,
p.CREATED,
-- p.LAST_MODIFIED,
p.STATUS,
p.FORCE_MATCHING
FROM
dba_sql_profiles p,
v$sqlstats s
where
p.signature = s.FORCE_MATCHING_SIGNATURE
AND s.sql_id='dg8duxp7zprxp';
set linesize 300
col name for a35
col created for a30
col FORCE_MATCHING for a25
SELECT
--s.sql_id,
-- s.plan_hash_value,
p.name,
p.CREATED,
-- p.LAST_MODIFIED,
p.STATUS,
p.FORCE_MATCHING
FROM
dba_sql_profiles p ;
NAME CREATED STATUS FORCE_MATCHING
----------------------------------- ------------------------------ -------- -------------------------
coe_dg8duxp7zprxp_1130139812 03-JAN-25 02.30.19.728687 PM ENABLED NO
====
set linesize 300
col name for a35
col created for a30
col FORCE_MATCHING for a25
col STATUS for a12
SELECT
CON_ID,
p.name,
p.CREATED,
-- p.LAST_MODIFIED,
p.STATUS,
p.FORCE_MATCHING
FROM
CDB_SQL_PROFILES p
where 1=1
--and p.CREATED >sysdate -7
;
to drop ---
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','ENABLE');
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','DISABLED');
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('PROFILE_NAME');
set pages 9999 lines 200 autotrace off trimspool on echo on
column name format a30
column category format a10
column created format a30
column last_modified format a30
column task_exec_name format a20
column signature format 99999999999999999999
column description format a20
set linesize 700
col DESCRIPTION for a20
col PROFILE_NAME for a20
col SQL_TEXT for a50 wrap
col TASK_EXEC_NAME for a15
col CATEGORY for a15
col LAST_MODIFIED for a20
col SIGNATURE for 9999999999999999999999
SELECT *
FROM DBA_SQL_PROFILES prof,
DBMSHSXP_SQL_PROFILE_ATTR attr
WHERE prof.NAME=attr.PROFILE_NAME
and prof.CREATED >sysdate -7
;
define sql_id=' '
set linesize 500 pagesize 300
col PARSE_USR for A14
col SQLTEXT for a50
col SQL_PROFILE for a27
col FORCE_MATCHING_SIGNATURE for 9999999999999999999999999
break on parse_usr
prompt ========== from v$sql ==========
select
con_id,
parsing_schema_name parse_usr
,lpad(plan_hash_value,10,' ')||' '||
case when sql_profile is not null then 'P'
when sql_plan_baseline is not null then 'B'
else ' '
end phashp
--,is_bind_sensitive||is_bind_aware||is_shareable pas
--,optimizer_mode m
--,is_obsolete o
,users_executing ue
,substr(object_status,1,1)||':'||lpad(child_number,5,' ') s_cn
--,to_char(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS'),'MMDD HH24MI') lltime
,to_char(last_active_time,'MMDD HH24:MI') latime
,(sysdate-last_active_time)*1440 min_ago
,decode(executions,0,1,executions) exec
,cpu_time/decode(executions,0,1,executions)/1000 cpx
,elapsed_time/decode(executions,0,1,executions)/1000 elpx
,(cpu_time/elapsed_time)*100 cpct
,buffer_gets/decode(executions,0,1,executions) bgpx
,user_io_wait_time/decode(executions,0,1,executions)/1000 iowpx
,(user_io_wait_time/elapsed_time)*100 ipct
,disk_reads/decode(executions,0,1,executions) drpx
,application_wait_time/decode(executions,0,1,executions)/1000 apwpx
,concurrency_wait_time/decode(executions,0,1,executions)/1000 conw
,rows_processed/decode(executions,0,1,executions) rwpx
,sorts/decode(executions,0,1,executions) sortx
,fetches/decode(executions,0,1,executions) fetchx
,module module
,SQL_PROFILE
,FORCE_MATCHING_SIGNATURE
,replace(sql_text,chr(13)) sqltext
from v$sql
where sql_id = '&&sql_id'
--and last_active_time > sysdate-3
order by parsing_schema_name
,last_active_time
;
===
define sql_id=' '
col apwpx format 99999.999 head 'AppWms|PerX'
col bgpx format 999999999 head 'BGets|PerX'
col conw format 99999.999 head 'Conwms|PerX'
col cpx format 99999999.999 head 'CPUms|PerX'
col drpx format 999999999999.99 head 'DReads|PerX'
col fetchx format 99999 head 'Fetchs|PerX'
col sortx format 999 head 'Sorts|PerX'
col elpx format 99999999.999 head 'Elapms|PerX'
col exec format 999999999999 head 'Execs'
col iowpx format 99999999.999 head 'IOWms|PerX'
col latime format a11 head 'Last Active'
col lltime format a09 head 'Last Load'
col maxsnapid format 999999 head 'Max|SnapId'
col m format a01 trunc
col minsnapid format 999999 head 'Min|SnapId'
col module format a14 trunc head 'Module'
col o format a01 head 'O' trunc
col opt_mode format a08 trunc head 'Opt|Mode' trunc
col parse_usr format a08 head 'ParsUser' trunc
col phash format 9999999999 head 'PlanHash'
col phashp format a12 head 'PlanHash P'
col rwpx format 9999999999.99 head 'RwsP|PerX'
col s_cn format a07 head 'S:Child' trunc
col sql_id format a15 head 'SQL Id'
col sqltext format a12 trunc head 'Sql Text'
col ue format 999
col cpct format 999 head 'CPU|Pct' trunc
col ipct format 999 head 'IO|Pct' trunc
col btime format a11 head 'Begin Time'
col smem format 99999 head 'ShrMem|KB'
col FORCE_MATCHING_SIGNATURE for 999999999999999999999999999999
break on sql_id on opt_mode on parse_usr skip 1
prompt
prompt ===== from dba_hist_sqlstat =====
select parsing_schema_name parse_usr
,plan_hash_value phash
,optimizer_mode opt_mode
,min(snap_id) minsnapid
,max(snap_id) maxsnapid
,avg(sharable_mem/1024) smem
,sum(decode(executions_delta,0,1,executions_delta)) exec
,sum(cpu_time_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 cpx
,sum(elapsed_time_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 elpx
,(sum(cpu_time_delta)/sum(decode(elapsed_time_delta,0,1,elapsed_time_delta)))*100 cpct
,sum(buffer_gets_delta)/sum(decode(executions_delta,0,1,executions_delta)) bgpx
,sum(iowait_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 iowpx
,(sum(iowait_delta)/sum(decode(elapsed_time_delta,0,1,elapsed_time_delta)))*100 ipct
,sum(disk_reads_delta)/sum(decode(executions_delta,0,1,executions_delta)) drpx
,sum(apwait_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 apwpx
,sum(ccwait_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 conw
,sum(rows_processed_delta)/sum(decode(executions_delta,0,1,executions_delta)) rwpx
,sum(sorts_delta)/sum(decode(executions_delta,0,1,executions_delta)) sortx
,sum(end_of_fetch_count_delta)/sum(decode(executions_delta,0,1,executions_delta)) fetchx
,module module
,sql_id
,FORCE_MATCHING_SIGNATURE
,SQL_PROFILE
from dba_hist_sqlstat ss
where sql_id = '&&sql_id'
group by sql_id
,parsing_schema_name
,plan_hash_value
,optimizer_mode
,module
,FORCE_MATCHING_SIGNATURE
,SQL_PROFILE
having sum(decode(executions_delta,0,1,executions_delta)) >0
order by min(ss.snap_id),parsing_schema_name
;
