SQL>SET SERVEROUT ON SIZE UNL; SQL>REM SQL>REM $Header: coe_xfr_sql_profile.sql 2020/03/10 carlos.sierra $ SQL>REM SQL>REM AUTHOR SQL>REM Carlos Sierra SQL>REM SQL>REM SCRIPT SQL>REM coe_xfr_sql_profile.sql SQL>REM SQL>REM DESCRIPTION SQL>REM This script generates another that contains the commands to SQL>REM create a manual custom SQL Profile out of a known plan from SQL>REM memory or AWR. The manual custom profile can be implemented SQL>REM into the same SOURCE system where the plan was retrieved, SQL>REM or into another similar TARGET system that has same schema SQL>REM objects referenced by the SQL that generated the known plan. SQL>REM SQL>REM PRE-REQUISITES SQL>REM 1. Oracle Tuning Pack license. SQL>REM SQL>REM PARAMETERS SQL>REM 1. SQL_ID (required) SQL>REM 2. Plan Hash Value for which a manual custom SQL Profile is SQL>REM needed (required). A list of known plans is presented. SQL>REM You may choose from list provided or enter a valid phv SQL>REM from a version of the SQL modified with Hints. SQL>REM SQL>REM EXECUTION SQL>REM 1. Connect into SQL*Plus as user with access to data dictionary. SQL>REM Do not use SYS. SQL>REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and SQL>REM plan hash value (parameters can be passed inline or until SQL>REM requested). SQL>REM SQL>REM EXAMPLE SQL>REM # sqlplus system SQL>REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE]; SQL>REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663; SQL>REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm; SQL>REM SQL> START coe_xfr_sql_profile.sql; SQL>REM SQL>REM NOTES SQL>REM 1. For possible errors see coe_xfr_sql_profile.log SQL>REM 2. If SQLT is installed in SOURCE, you can use instead: SQL>REM sqlt/utl/sqltprofile.sql SQL>REM 3. Be aware that using DBMS_SQLTUNE requires a license for SQL>REM Oracle Tuning Pack. SQL>REM 4. Use a DBA user but not SYS. SQL>REM 5. If you get "ORA-06532: Subscript outside of limit, ORA-06512: at line 1" SQL>REM Then you may consider this change (only in a test and disposable system): SQL>REM create or replace TYPE sys.sqlprof_attr AS VARRAY(5000) of VARCHAR2(500); SQL>REM SQL>SET TERM ON ECHO OFF; Parameter 1: SQL_ID (required) AVG_ET_SECS_MEM AVG_ET_SECS_AWR PLAN_HASH_VALUE EXECUTIONS_MEM EXECUTIONS_AWR --------------- --------------- --------------- -------------- -------------- 0.001152 0.001152 1509575125 2 2 Parameter 2: PLAN_HASH_VALUE (required) Values passed to coe_xfr_sql_profile: SQL_ID : "9t7jvx0gm0m95" PLAN_HASH_VALUE: "1509575125" SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE; SQL> SQL>-- trim parameters SQL>COL sql_id NEW_V sql_id FOR A30; SQL>COL plan_hash_value NEW_V plan_hash_value FOR A30; SQL>SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL; SQL_ID PLAN_HASH_VALUE ------------------------------ ------------------------------ 9t7jvx0gm0m95 1509575125 SQL> SQL>VAR sql_text CLOB; SQL>VAR sql_text2 CLOB; SQL>VAR other_xml CLOB; SQL>EXEC :sql_text := NULL; SQL>EXEC :sql_text2 := NULL; SQL>EXEC :other_xml := NULL; SQL> SQL>-- get sql_text from memory SQL>DECLARE 2 l_sql_text VARCHAR2(32767); 3 BEGIN -- 10g see bug 5017909 4 FOR i IN (SELECT DISTINCT piece, sql_text 5 FROM gv$sqltext_with_newlines 6 WHERE sql_id = TRIM('&&sql_id.') 7 ORDER BY 1, 2) 8 LOOP 9 IF :sql_text IS NULL THEN 10 DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE); 11 DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE); 12 END IF; 13 -- removes NUL characters 14 l_sql_text := REPLACE(i.sql_text, CHR(00), ' '); 15 -- adds a NUL character at the end of each line 16 DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00)); 17 END LOOP; 18 -- if found in memory then sql_text is not null 19 IF :sql_text IS NOT NULL THEN 20 DBMS_LOB.CLOSE(:sql_text); 21 END IF; 22 EXCEPTION 23 WHEN OTHERS THEN 24 DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM); 25 :sql_text := NULL; 26 END; 27 / SQL> SQL>SELECT :sql_text FROM DUAL; :SQL_TEXT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select * from t1 where i=5000 SQL> SQL>-- get sql_text from awr SQL>DECLARE 2 l_sql_text VARCHAR2(32767); 3 l_clob_size NUMBER; 4 l_offset NUMBER; 5 BEGIN 6 IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN 7 SELECT sql_text 8 INTO :sql_text2 9 FROM dba_hist_sqltext 10 WHERE sql_id = TRIM('&&sql_id.') 11 AND sql_text IS NOT NULL 12 AND ROWNUM = 1; 13 END IF; 14 -- if found in awr then sql_text2 is not null 15 IF :sql_text2 IS NOT NULL THEN 16 l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0); 17 l_offset := 1; 18 DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE); 19 DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE); 20 -- store in clob as 64 character pieces plus a NUL character at the end of each piece 21 WHILE l_offset < l_clob_size 22 LOOP 23 IF l_clob_size - l_offset > 64 THEN 24 l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' '); 25 ELSE -- last piece 26 l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' '); 27 END IF; 28 DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00)); 29 l_offset := l_offset + 64; 30 END LOOP; 31 DBMS_LOB.CLOSE(:sql_text); 32 END IF; 33 EXCEPTION 34 WHEN OTHERS THEN 35 DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM); 36 :sql_text := NULL; 37 END; 38 / SQL> SQL>SELECT :sql_text2 FROM DUAL; :SQL_TEXT2 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL>SELECT :sql_text FROM DUAL; :SQL_TEXT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select * from t1 where i=5000 SQL> SQL>-- validate sql_text SQL>SET TERM ON; 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> SQL>-- get other_xml from memory SQL>BEGIN 2 FOR i IN (SELECT other_xml 3 FROM gv$sql_plan 4 WHERE sql_id = TRIM('&&sql_id.') 5 AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) 6 AND other_xml IS NOT NULL 7 ORDER BY 8 child_number, id) 9 LOOP 10 :other_xml := i.other_xml; 11 EXIT; -- 1st 12 END LOOP; 13 EXCEPTION 14 WHEN OTHERS THEN 15 DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM); 16 :other_xml := NULL; 17 END; 18 / SQL> SQL>-- get other_xml from awr SQL>BEGIN 2 IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN 3 FOR i IN (SELECT other_xml 4 FROM dba_hist_sql_plan 5 WHERE sql_id = TRIM('&&sql_id.') 6 AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) 7 AND dbid = (SELECT dbid FROM v$database) 8 AND other_xml IS NOT NULL 9 ORDER BY 10 id) 11 LOOP 12 :other_xml := i.other_xml; 13 EXIT; -- 1st 14 END LOOP; 15 END IF; 16 EXCEPTION 17 WHEN OTHERS THEN 18 DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM); 19 :other_xml := NULL; 20 END; 21 / SQL> SQL>-- get other_xml from memory from modified SQL SQL>BEGIN 2 IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN 3 FOR i IN (SELECT other_xml 4 FROM gv$sql_plan 5 WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) 6 --WHERE full_plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) 7 AND other_xml IS NOT NULL 8 ORDER BY 9 child_number, id) 10 LOOP 11 :other_xml := i.other_xml; 12 EXIT; -- 1st 13 END LOOP; 14 END IF; 15 EXCEPTION 16 WHEN OTHERS THEN 17 DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM); 18 :other_xml := NULL; 19 END; 20 / SQL> SQL>-- get other_xml from awr from modified SQL SQL>BEGIN 2 IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN 3 FOR i IN (SELECT other_xml 4 FROM dba_hist_sql_plan 5 WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) 6 --WHERE full_plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) 7 AND dbid = (SELECT dbid FROM v$database) 8 AND other_xml IS NOT NULL 9 ORDER BY 10 id) 11 LOOP 12 :other_xml := i.other_xml; 13 EXIT; -- 1st 14 END LOOP; 15 END IF; 16 EXCEPTION 17 WHEN OTHERS THEN 18 DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM); 19 :other_xml := NULL; 20 END; 21 / SQL> SQL>SELECT :other_xml FROM DUAL; :OTHER_XML -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- yes19.0.0.01975401683150957512519754016830 SQL> SQL>-- validate other_xml SQL>SET TERM ON; 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; SQL> SQL>-- generates script that creates sql profile in target system: SQL>SET ECHO OFF; coe_xfr_sql_profile_9t7jvx0gm0m95_1509575125.sql.