使用sql_profile固定执行计划（脚本分享）

       生产上经常遇到执行计划“突变”的问题，针对这种问题 常规的解决就是收集统计信息。如果相关表的segment很大，收集统计信息需要很长时间。在应急的情况下快速解决问题，只需要绑定这个SQL突变前的执行计划，即可！下面分享一个脚本 用于绑定执行计划：直接调用存储过程，传入的参数有三个 

ORIGINAL_SQL_ID           需要固定执行计划的SQL
MODIFIED_SQL_ID           用来固定执行计划的SQL(一般和第一个参数传值相同)
PLAN_HASH_VALUE1      用来固定执行计划的PLAN_HASH_VALUE

create or replace procedure p_sql_profile(ORIGINAL_SQL_ID in varchar2,
                                          MODIFIED_SQL_ID in varchar2,
                                          PLAN_HASH_VALUE1 in number) is
  sql_text  CLOB := NULL;
  other_xml CLOB := NULL;
  signature NUMBER := NULL;
  name      VARCHAR2(30) := NULL;

  l_sql_text VARCHAR2(32767);

  h           SYS.SQLPROF_ATTR := SYS.SQLPROF_ATTR();
  idx         INTEGER := 0;
  l_pos       NUMBER;
  l_hint      VARCHAR2(32767);
  description VARCHAR2(500);
  --SQLPROF_ATTR
PROCEDURE add_hint(p_hint IN VARCHAR2) IS
  BEGIN
    idx := idx + 1;

    DBMS_OUTPUT.PUT_LINE(LPAD(idx, 4, '0') || ' ' || p_hint);
    h.EXTEND;
    h(idx) := p_hint;
  END add_hint;

BEGIN
  -- get sql_text from memory
  FOR i IN (SELECT DISTINCT piece, sql_text
              FROM gv$sqltext_with_newlines
             WHERE sql_id = ORIGINAL_SQL_ID --o
             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;
    l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
    DBMS_LOB.WRITEAPPEND(sql_text, LENGTH(l_sql_text), l_sql_text);
  END LOOP;
  IF sql_text IS NOT NULL THEN
    DBMS_LOB.CLOSE(sql_text);
  END IF;
  -- get sql_text from awr
  IF sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(sql_text), 0) = 0 THEN
    SELECT REPLACE(sql_text, CHR(00), ' ')
      INTO sql_text
      FROM dba_hist_sqltext
     WHERE sql_id = ORIGINAL_SQL_ID --o
       AND sql_text IS NOT NULL
       AND ROWNUM = 1;
  END IF;

  -- get other_xml from memory
  FOR i IN (SELECT other_xml
              FROM gv$sql_plan
             WHERE sql_id = MODIFIED_SQL_ID --m
               AND plan_hash_value = PLAN_HASH_VALUE1 --p
               AND other_xml IS NOT NULL
             ORDER BY child_number, id) LOOP
    other_xml := i.other_xml;
  END LOOP;
  dbms_output.put_line('other_xml1 is' || other_xml);

  -- get other_xml from awr
  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 = MODIFIED_SQL_ID --m
                 AND plan_hash_value = PLAN_HASH_VALUE1 --p 
                 AND other_xml IS NOT NULL
               ORDER BY id) LOOP
      other_xml := i.other_xml;
    END LOOP;
  END IF;
  dbms_output.put_line('other_xml2 is' || other_xml);

  --hint
  add_hint('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
        add_hint(l_hint);
        l_hint := NULL;
      ELSE
        l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
        add_hint(SUBSTR(l_hint, 1, l_pos));
        l_hint := '   ' || SUBSTR(l_hint, l_pos);
      END IF;
    END LOOP;
  
  END LOOP;
  add_hint('END_OUTLINE_DATA');

  signature   := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_text);
  name        := UPPER(original_sql_id) || '_' || PLAN_HASH_VALUE1;
  description := UPPER('original:' || original_sql_id || ' modified:' ||
                       modified_sql_id || ' phv:' || PLAN_HASH_VALUE1 ||
                       ' signature:' || signature ||
                       ' created by coe_load_sql_profile.sql');
  -- create custom sql profile for original sql using plan from modified sql
  DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text    => sql_text, -- original sql
                                  profile     => h, -- plan from modified sql
                                  name        => name,
                                  description => description,
                                  category    => 'DEFAULT',
                                  validate    => TRUE,
                                  replace     => TRUE,
                                  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */);
  --异常处理
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('sql profile create filed');
end p_sql_profile;