【SQL Profile】coe_xfr_sql_profile.sql內容

lhrbest發表於2017-06-28

【SQL Profile】coe_xfr_sql_profile.sql內容






點選(此處)摺疊或開啟

  1. SPO coe_xfr_sql_profile.log;
  2. 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>;
  3. SET SERVEROUT ON SIZE UNL;
  4. REM
  5. REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.5.5 2013/03/01 carlos.sierra $
  6. REM
  7. REM Copyright (c) 2000-2013, Oracle Corporation. All rights reserved.
  8. REM
  9. REM AUTHOR
  10. REM carlos.sierra@oracle.com
  11. REM
  12. REM SCRIPT
  13. REM coe_xfr_sql_profile.sql
  14. REM
  15. REM DESCRIPTION
  16. REM This script generates another that contains the commands to
  17. REM create a manual custom SQL Profile out of a known plan from
  18. REM memory or AWR. The manual custom profile can be implemented
  19. REM into the same SOURCE system where the plan was retrieved,
  20. REM or into another similar TARGET system that has same schema
  21. REM objects referenced by the SQL that generated the known plan.
  22. REM
  23. REM PRE-REQUISITES
  24. REM 1. Oracle Tuning Pack license.
  25. REM
  26. REM PARAMETERS
  27. REM 1. SQL_ID (required)
  28. REM 2. Plan Hash Value for which a manual custom SQL Profile is
  29. REM needed (required). A list of known plans is presented.
  30. REM You may choose from list provided or enter a valid phv
  31. REM from a version of the SQL modified with Hints.
  32. REM
  33. REM EXECUTION
  34. REM 1. Connect into SQL*Plus as user with access to data dictionary.
  35. REM Do not use SYS.
  36. REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
  37. REM plan hash value (parameters can be passed inline or until
  38. REM requested).
  39. REM
  40. REM EXAMPLE
  41. REM # sqlplus system
  42. REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
  43. REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
  44. REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
  45. REM SQL> START coe_xfr_sql_profile.sql;
  46. REM
  47. REM NOTES
  48. REM 1. For possible errors see coe_xfr_sql_profile.log
  49. REM 2. If SQLT is installed in SOURCE, you can use instead:
  50. REM sqlt/utl/sqltprofile.sql
  51. REM 3. Be aware that using DBMS_SQLTUNE requires a license for
  52. REM Oracle Tuning Pack.
  53. REM 4. Use a DBA user but not SYS.
  54. REM 5. If you get "ORA-06532: Subscript outside of limit, ORA-06512: at line 1"
  55. REM Then you may consider this change (only in a test and disposable system):
  56. REM create or replace TYPE sys.sqlprof_attr AS VARRAY(5000) of VARCHAR2(500);
  57. REM
  58. SET TERM ON ECHO OFF;
  59. PRO
  60. PRO Parameter 1:
  61. PRO SQL_ID (required)
  62. PRO
  63. DEF sql_id = '&1';
  64. PRO
  65. WITH
  66. p AS (
  67. SELECT plan_hash_value
  68.   FROM gv$sql_plan
  69.  WHERE sql_id = TRIM('&&sql_id.')
  70.    AND other_xml IS NOT NULL
  71.  UNION
  72. SELECT plan_hash_value
  73.   FROM dba_hist_sql_plan
  74.  WHERE sql_id = TRIM('&&sql_id.')
  75.    AND other_xml IS NOT NULL ),
  76. m AS (
  77. SELECT plan_hash_value,
  78.        SUM(elapsed_time)/SUM(executions) avg_et_secs
  79.   FROM gv$sql
  80.  WHERE sql_id = TRIM('&&sql_id.')
  81.    AND executions > 0
  82.  GROUP BY
  83.        plan_hash_value ),
  84. a AS (
  85. SELECT plan_hash_value,
  86.        SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
  87.   FROM dba_hist_sqlstat
  88.  WHERE sql_id = TRIM('&&sql_id.')
  89.    AND executions_total > 0
  90.  GROUP BY
  91.        plan_hash_value )
  92. SELECT p.plan_hash_value,
  93.        ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
  94.   FROM p, m, a
  95.  WHERE p.plan_hash_value = m.plan_hash_value(+)
  96.    AND p.plan_hash_value = a.plan_hash_value(+)
  97.  ORDER BY
  98.        avg_et_secs NULLS LAST;
  99. PRO
  100. PRO Parameter 2:
  101. PRO PLAN_HASH_VALUE (required)
  102. PRO
  103. DEF plan_hash_value = '&2';
  104. PRO
  105. PRO Values passed to coe_xfr_sql_profile:
  106. PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  107. PRO SQL_ID : "&&sql_id."
  108. PRO PLAN_HASH_VALUE: "&&plan_hash_value."
  109. PRO
  110. SET TERM OFF ECHO ON;
  111. WHENEVER SQLERROR EXIT SQL.SQLCODE;

  112. -- trim parameters
  113. COL sql_id NEW_V sql_id FOR A30;
  114. COL plan_hash_value NEW_V plan_hash_value FOR A30;
  115. SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;

  116. VAR sql_text CLOB;
  117. VAR sql_text2 CLOB;
  118. VAR other_xml CLOB;
  119. EXEC :sql_text := NULL;
  120. EXEC :sql_text2 := NULL;
  121. EXEC :other_xml := NULL;

  122. -- get sql_text from memory
  123. DECLARE
  124.   l_sql_text VARCHAR2(32767);
  125. BEGIN -- 10g see bug 5017909
  126.   FOR i IN (SELECT DISTINCT piece, sql_text
  127.               FROM gv$sqltext_with_newlines
  128.              WHERE sql_id = TRIM('&&sql_id.')
  129.              ORDER BY 1, 2)
  130.   LOOP
  131.     IF :sql_text IS NULL THEN
  132.       DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
  133.       DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
  134.     END IF;
  135.     -- removes NUL characters
  136.     l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
  137.     -- adds a NUL character at the end of each line
  138.     DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
  139.   END LOOP;
  140.   -- if found in memory then sql_text is not null
  141.   IF :sql_text IS NOT NULL THEN
  142.     DBMS_LOB.CLOSE(:sql_text);
  143.   END IF;
  144. EXCEPTION
  145.   WHEN OTHERS THEN
  146.     DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
  147.     :sql_text := NULL;
  148. END;
  149. /

  150. SELECT :sql_text FROM DUAL;

  151. -- get sql_text from awr
  152. DECLARE
  153.   l_sql_text VARCHAR2(32767);
  154.   l_clob_size NUMBER;
  155.   l_offset NUMBER;
  156. BEGIN
  157.   IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
  158.     SELECT sql_text
  159.       INTO :sql_text2
  160.       FROM dba_hist_sqltext
  161.      WHERE sql_id = TRIM('&&sql_id.')
  162.        AND sql_text IS NOT NULL
  163.        AND ROWNUM = 1;
  164.   END IF;
  165.   -- if found in awr then sql_text2 is not null
  166.   IF :sql_text2 IS NOT NULL THEN
  167.     l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0);
  168.     l_offset := 1;
  169.     DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
  170.     DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
  171.     -- store in clob as 64 character pieces plus a NUL character at the end of each piece
  172.     WHILE l_offset < l_clob_size
  173.     LOOP
  174.       IF l_clob_size - l_offset > 64 THEN
  175.         l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' ');
  176.       ELSE -- last piece
  177.         l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' ');
  178.       END IF;
  179.       DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
  180.       l_offset := l_offset + 64;
  181.     END LOOP;
  182.     DBMS_LOB.CLOSE(:sql_text);
  183.   END IF;
  184. EXCEPTION
  185.   WHEN OTHERS THEN
  186.     DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
  187.     :sql_text := NULL;
  188. END;
  189. /

  190. SELECT :sql_text2 FROM DUAL;
  191. SELECT :sql_text FROM DUAL;

  192. -- validate sql_text
  193. SET TERM ON;
  194. BEGIN
  195.   IF :sql_text IS NULL THEN
  196.     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).');
  197.   END IF;
  198. END;
  199. /
  200. SET TERM OFF;

  201. -- get other_xml from memory
  202. BEGIN
  203.   FOR i IN (SELECT other_xml
  204.               FROM gv$sql_plan
  205.              WHERE sql_id = TRIM('&&sql_id.')
  206.                AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
  207.                AND other_xml IS NOT NULL
  208.              ORDER BY
  209.                    child_number, id)
  210.   LOOP
  211.     :other_xml := i.other_xml;
  212.     EXIT; -- 1st
  213.   END LOOP;
  214. EXCEPTION
  215.   WHEN OTHERS THEN
  216.     DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
  217.     :other_xml := NULL;
  218. END;
  219. /

  220. -- get other_xml from awr
  221. BEGIN
  222.   IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
  223.     FOR i IN (SELECT other_xml
  224.                 FROM dba_hist_sql_plan
  225.                WHERE sql_id = TRIM('&&sql_id.')
  226.                  AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
  227.                  AND other_xml IS NOT NULL
  228.                ORDER BY
  229.                      id)
  230.     LOOP
  231.       :other_xml := i.other_xml;
  232.       EXIT; -- 1st
  233.     END LOOP;
  234.   END IF;
  235. EXCEPTION
  236.   WHEN OTHERS THEN
  237.     DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
  238.     :other_xml := NULL;
  239. END;
  240. /

  241. -- get other_xml from memory from modified SQL
  242. BEGIN
  243.   IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
  244.     FOR i IN (SELECT other_xml
  245.                 FROM gv$sql_plan
  246.                WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
  247.                  AND other_xml IS NOT NULL
  248.                ORDER BY
  249.                      child_number, id)
  250.     LOOP
  251.       :other_xml := i.other_xml;
  252.       EXIT; -- 1st
  253.     END LOOP;
  254.   END IF;
  255. EXCEPTION
  256.   WHEN OTHERS THEN
  257.     DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
  258.     :other_xml := NULL;
  259. END;
  260. /

  261. -- get other_xml from awr from modified SQL
  262. BEGIN
  263.   IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
  264.     FOR i IN (SELECT other_xml
  265.                 FROM dba_hist_sql_plan
  266.                WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
  267.                  AND other_xml IS NOT NULL
  268.                ORDER BY
  269.                      id)
  270.     LOOP
  271.       :other_xml := i.other_xml;
  272.       EXIT; -- 1st
  273.     END LOOP;
  274.   END IF;
  275. EXCEPTION
  276.   WHEN OTHERS THEN
  277.     DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
  278.     :other_xml := NULL;
  279. END;
  280. /

  281. SELECT :other_xml FROM DUAL;

  282. -- validate other_xml
  283. SET TERM ON;
  284. BEGIN
  285.   IF :other_xml IS NULL THEN
  286.     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).');
  287.   END IF;
  288. END;
  289. /
  290. SET TERM OFF;

  291. -- generates script that creates sql profile in target system:
  292. SET ECHO OFF;
  293. PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.
  294. SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;
  295. SET SERVEROUT ON SIZE UNL FOR WOR;
  296. SPO OFF;
  297. SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;
  298. DECLARE
  299.   l_pos NUMBER;
  300.   l_clob_size NUMBER;
  301.   l_offset NUMBER;
  302.   l_sql_text VARCHAR2(32767);
  303.   l_len NUMBER;
  304.   l_hint VARCHAR2(32767);
  305. BEGIN
  306.   DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');
  307.   DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');
  308.   DBMS_OUTPUT.PUT_LINE('REM');
  309.   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 $');
  310.   DBMS_OUTPUT.PUT_LINE('REM');
  311.   DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.');
  312.   DBMS_OUTPUT.PUT_LINE('REM');
  313.   DBMS_OUTPUT.PUT_LINE('REM AUTHOR');
  314.   DBMS_OUTPUT.PUT_LINE('REM carlos.sierra@oracle.com');
  315.   DBMS_OUTPUT.PUT_LINE('REM');
  316.   DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
  317.   DBMS_OUTPUT.PUT_LINE('REM coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');
  318.   DBMS_OUTPUT.PUT_LINE('REM');
  319.   DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
  320.   DBMS_OUTPUT.PUT_LINE('REM This script is generated by coe_xfr_sql_profile.sql');
  321.   DBMS_OUTPUT.PUT_LINE('REM It contains the SQL*Plus commands to create a custom');
  322.   DBMS_OUTPUT.PUT_LINE('REM SQL Profile for SQL_ID &&sql_id. based on plan hash');
  323.   DBMS_OUTPUT.PUT_LINE('REM value &&plan_hash_value..');
  324.   DBMS_OUTPUT.PUT_LINE('REM The custom SQL Profile to be created by this script');
  325.   DBMS_OUTPUT.PUT_LINE('REM will affect plans for SQL commands with signature');
  326.   DBMS_OUTPUT.PUT_LINE('REM matching the one for SQL Text below.');
  327.   DBMS_OUTPUT.PUT_LINE('REM Review SQL Text and adjust accordingly.');
  328.   DBMS_OUTPUT.PUT_LINE('REM');
  329.   DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
  330.   DBMS_OUTPUT.PUT_LINE('REM None.');
  331.   DBMS_OUTPUT.PUT_LINE('REM');
  332.   DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
  333.   DBMS_OUTPUT.PUT_LINE('REM SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');
  334.   DBMS_OUTPUT.PUT_LINE('REM');
  335.   DBMS_OUTPUT.PUT_LINE('REM NOTES');
  336.   DBMS_OUTPUT.PUT_LINE('REM 1. Should be run as SYSTEM or SYSDBA.');
  337.   DBMS_OUTPUT.PUT_LINE('REM 2. User must have CREATE ANY SQL PROFILE privilege.');
  338.   DBMS_OUTPUT.PUT_LINE('REM 3. SOURCE and TARGET systems can be the same or similar.');
  339.   DBMS_OUTPUT.PUT_LINE('REM 4. To drop this custom SQL Profile after it has been created:');
  340.   DBMS_OUTPUT.PUT_LINE('REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');
  341.   DBMS_OUTPUT.PUT_LINE('REM 5. Be aware that using DBMS_SQLTUNE requires a license');
  342.   DBMS_OUTPUT.PUT_LINE('REM for the Oracle Tuning Pack.');
  343.   DBMS_OUTPUT.PUT_LINE('REM 6. If you modified a SQL putting Hints in order to produce a desired');
  344.   DBMS_OUTPUT.PUT_LINE('REM Plan, you can remove the artifical Hints from SQL Text pieces below.');
  345.   DBMS_OUTPUT.PUT_LINE('REM By doing so you can create a custom SQL Profile for the original');
  346.   DBMS_OUTPUT.PUT_LINE('REM SQL but with the Plan captured from the modified SQL (with Hints).');
  347.   DBMS_OUTPUT.PUT_LINE('REM');
  348.   DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');
  349.   DBMS_OUTPUT.PUT_LINE('REM');
  350.   DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');
  351.   DBMS_OUTPUT.PUT_LINE('VAR signaturef NUMBER;');
  352.   DBMS_OUTPUT.PUT_LINE('REM');
  353.   DBMS_OUTPUT.PUT_LINE('DECLARE');
  354.   DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');
  355.   DBMS_OUTPUT.PUT_LINE('h SYS.SQLPROF_ATTR;');
  356.   DBMS_OUTPUT.PUT_LINE('PROCEDURE wa (p_line IN VARCHAR2) IS');
  357.   DBMS_OUTPUT.PUT_LINE('BEGIN');
  358.   DBMS_OUTPUT.PUT_LINE('DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);');
  359.   DBMS_OUTPUT.PUT_LINE('END wa;');
  360.   DBMS_OUTPUT.PUT_LINE('BEGIN');
  361.   DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);');
  362.   DBMS_OUTPUT.PUT_LINE('DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);');
  363.   DBMS_OUTPUT.PUT_LINE('-- SQL Text pieces below do not have to be of same length.');
  364.   DBMS_OUTPUT.PUT_LINE('-- So if you edit SQL Text (i.e. removing temporary Hints),');
  365.   DBMS_OUTPUT.PUT_LINE('-- there is no need to edit or re-align unmodified pieces.');
  366.   l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text), 0);
  367.   l_offset := 1;
  368.   WHILE l_offset < l_clob_size
  369.   LOOP
  370.     l_pos := DBMS_LOB.INSTR(:sql_text, CHR(00), l_offset);
  371.     IF l_pos > 0 THEN
  372.       l_len := l_pos - l_offset;
  373.     ELSE -- last piece
  374.       l_len := l_clob_size - l_pos + 1;
  375.     END IF;
  376.     l_sql_text := DBMS_LOB.SUBSTR(:sql_text, l_len, l_offset);
  377.     /* cannot do such 3 replacement since a line could end with a comment using "--"
  378.     l_sql_text := REPLACE(l_sql_text, CHR(10), ' '); -- replace LF with SP
  379.     l_sql_text := REPLACE(l_sql_text, CHR(13), ' '); -- replace CR with SP
  380.     l_sql_text := REPLACE(l_sql_text, CHR(09), ' '); -- replace TAB with SP
  381.     */
  382.     l_offset := l_offset + l_len + 1;
  383.     IF l_len > 0 THEN
  384.       IF INSTR(l_sql_text, '''[') + INSTR(l_sql_text, ']''') = 0 THEN
  385.         l_sql_text := '['||l_sql_text||']';
  386.       ELSIF INSTR(l_sql_text, '''{') + INSTR(l_sql_text, '}''') = 0 THEN
  387.         l_sql_text := '{'||l_sql_text||'}';
  388.       ELSIF INSTR(l_sql_text, '''<') + INSTR(l_sql_text, '>''') = 0 THEN
  389.         l_sql_text := '<'||l_sql_text||'>';
  390.       ELSIF INSTR(l_sql_text, '''(') + INSTR(l_sql_text, ')''') = 0 THEN
  391.         l_sql_text := '('||l_sql_text||')';
  392.       ELSIF INSTR(l_sql_text, '''"') + INSTR(l_sql_text, '"''') = 0 THEN
  393.         l_sql_text := '"'||l_sql_text||'"';
  394.       ELSIF INSTR(l_sql_text, '''|') + INSTR(l_sql_text, '|''') = 0 THEN
  395.         l_sql_text := '|'||l_sql_text||'|';
  396.       ELSIF INSTR(l_sql_text, '''~') + INSTR(l_sql_text, '~''') = 0 THEN
  397.         l_sql_text := '~'||l_sql_text||'~';
  398.       ELSIF INSTR(l_sql_text, '''^') + INSTR(l_sql_text, '^''') = 0 THEN
  399.         l_sql_text := '^'||l_sql_text||'^';
  400.       ELSIF INSTR(l_sql_text, '''@') + INSTR(l_sql_text, '@''') = 0 THEN
  401.         l_sql_text := '@'||l_sql_text||'@';
  402.       ELSIF INSTR(l_sql_text, '''#') + INSTR(l_sql_text, '#''') = 0 THEN
  403.         l_sql_text := '#'||l_sql_text||'#';
  404.       ELSIF INSTR(l_sql_text, '''%') + INSTR(l_sql_text, '%''') = 0 THEN
  405.         l_sql_text := '%'||l_sql_text||'%';
  406.       ELSIF INSTR(l_sql_text, '''$') + INSTR(l_sql_text, '$''') = 0 THEN
  407.         l_sql_text := '$'||l_sql_text||'$';
  408.       ELSE
  409.         l_sql_text := CHR(96)||l_sql_text||CHR(96);
  410.       END IF;
  411.       DBMS_OUTPUT.PUT_LINE('wa(q'''||l_sql_text||''');');
  412.     END IF;
  413.   END LOOP;
  414.   DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CLOSE(sql_txt);');
  415.   DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
  416.   DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');
  417.   FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
  418.                    SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint
  419.               FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)
  420.   LOOP
  421.     l_hint := i.hint;
  422.     WHILE NVL(LENGTH(l_hint), 0) > 0
  423.     LOOP
  424.       IF LENGTH(l_hint) <= 500 THEN
  425.         DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
  426.         l_hint := NULL;
  427.       ELSE
  428.         l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
  429.         DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
  430.         l_hint := ' '||SUBSTR(l_hint, l_pos);
  431.       END IF;
  432.     END LOOP;
  433.   END LOOP;
  434.   DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');
  435.   DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');
  436.   DBMS_OUTPUT.PUT_LINE(':signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);');
  437.   DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
  438.   DBMS_OUTPUT.PUT_LINE('sql_text => sql_txt,');
  439.   DBMS_OUTPUT.PUT_LINE('profile => h,');
  440.   DBMS_OUTPUT.PUT_LINE('name => ''coe_&&sql_id._&&plan_hash_value.'',');
  441.   DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'' ''||:signaturef||'''',');
  442.   DBMS_OUTPUT.PUT_LINE('category => ''DEFAULT'',');
  443.   DBMS_OUTPUT.PUT_LINE('validate => TRUE,');
  444.   DBMS_OUTPUT.PUT_LINE('replace => TRUE,');
  445.   DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
  446.   DBMS_OUTPUT.PUT_LINE('DBMS_LOB.FREETEMPORARY(sql_txt);');
  447.   DBMS_OUTPUT.PUT_LINE('END;');
  448.   DBMS_OUTPUT.PUT_LINE('/');
  449.   DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');
  450.   DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');
  451.   DBMS_OUTPUT.PUT_LINE('PRINT signature');
  452.   DBMS_OUTPUT.PUT_LINE('PRINT signaturef');
  453.   DBMS_OUTPUT.PUT_LINE('PRO');
  454.   DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');
  455.   DBMS_OUTPUT.PUT_LINE('PRO');
  456.   DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');
  457.   DBMS_OUTPUT.PUT_LINE('SPO OFF;');
  458.   DBMS_OUTPUT.PUT_LINE('PRO');
  459.   DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');
  460. END;
  461. /
  462. SPO OFF;
  463. 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>;
  464. SET SERVEROUT OFF;
  465. PRO
  466. PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
  467. PRO on TARGET system in order to create a custom SQL Profile
  468. PRO with plan &&plan_hash_value linked to adjusted sql_text.
  469. PRO
  470. UNDEFINE 1 2 sql_id plan_hash_value
  471. CL COL
  472. PRO
  473. PRO COE_XFR_SQL_PROFILE completed.




About Me

...............................................................................................................................

● 本文作者:小麥苗,只專注於資料庫的技術,更注重技術的運用

● 本文在itpub(http://blog.itpub.net/26736162)、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址:http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群:230161599     微信群:私聊

● 聯絡我請加QQ好友(646634621),註明新增緣由

● 於 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解

● 版權所有,歡迎分享本文,轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號:xiaomaimiaolhr,掃描右邊的二維碼加入小麥苗的QQ群,學習最實用的資料庫技術。

【SQL Profile】coe_xfr_sql_profile.sql內容
DBA筆試面試講解
歡迎與我聯絡

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2141322/,如需轉載,請註明出處,否則將追究法律責任。

相關文章