294 lines
18 KiB
Plaintext
294 lines
18 KiB
Plaintext
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
|
|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
<other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0</info><info type="parse_schema"><![CDATA["ADMINPDB"]]></info><info type="plan_hash_full">1975401683</info><info type="plan_hash">1509575125</info><info type="plan_hash_2">1975401683</info><stats type="compilation"><stat name="bg">0</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."I"))]]></hint><hint><![CDATA[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")]]></hint></outline_data></other_xml>
|
|
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.
|