Oracle 通过sql profile为sql语句加hint

sql profile最大的优点是在不修改sql语句和会话执行环境的情况下去优化sql的执行效率,适合无法在应用程序中修改sql时.
sql profile最常用方法大概是:
--创建产生sql tuning advisor任务
DECLARE
  tuning_task varchar2(100);
  l_sql_id    v$session.prev_sql_id%TYPE;
BEGIN
  l_sql_id    := ‘6w02d3ggsj4xb‘;
  tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
  dbms_sqltune.execute_tuning_task(tuning_task);
  dbms_output.put_line(tuning_task);
END;
--查看任务内容
SELECT dbms_sqltune.report_tuning_task(‘tuning_task‘) FROM dual;
--应用任务,产生sql profile
begin
  dbms_sqltune.accept_sql_profile(task_name  => ‘tuning_task‘,
                                  replace    => TRUE,
                                  force_match => true);
end;
但上述方法主要是依赖sql tuning advisor,如果它无法生成你想要的执行计划.你还可以通过手动的方式,
通过sql profile把hint加进去.这里主要用到coe_xfr_sql_profile.sql这个sql,来产生原语句的outline data和
加hint语句的outline data,然后替换对应的SYS.SQLPROF_ATTR,最后执行生成的sql就可以了.
以下是测试(测试环境11.1.0.7):
1.建立测试表和数据
--建表
create table scott.test as select * from dba_objects;
--建索引
create index scott.idx_test_01 on scott.test(object_id);
--收集统计信息
exec dbms_stats.gather_table_stats(‘scott‘,‘test‘,cascade=>true);
--更新数据,使用数据分布不均匀
update scott.test set object_id=10 where object_id>10;
commit;

2.执行查询语句
--执行原有的查询语句,查看执行计划发现走索引,实现这时表中大部分行的object_id都已经被更新为10,所以走索引是不合理的.
select * from scott.test where object_id=10;
--执行现有加hint的查询语句,使其走全表扫.
select /*+ full(test)*/* from scott.test where object_id=10;

3.查询上面两个语句的sql_id,plan_hash_value
--查询原语句的sql_id,plan_hash_value
select sql_text,sql_fulltext,sql_id,plan_hash_value from v$sql
where sql_text like ‘select * from scott.test where object_id=10%‘;
/*    sql_id          plan_hash_value  */    
/* cpk9jsg2qt52r      2317948335*/

--查询加hint后语句的sql_id,plan_hash_value
select sql_text,sql_fulltext,sql_id,plan_hash_value from v$sql
where sql_text like ‘select /*+ full(test)*/* from scott.test where object_id=10%‘;
/*    sql_id          plan_hash_value  */ 
/* 06c2mucgn6t5g      1357081020*/

4.把coe_xfr_sql_profile.sql放在$Oracle_HOME/rdbms/admin下
coe_xfr_sql_profile.sql的内容在文档最后

5.对上面的两个sql产生outline data的sql.
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
--原语句
SQL> @coe_xfr_sql_profile.sql cpk9jsg2qt52r 2317948335

----加hint后的语句
SQL>@coe_xfr_sql_profile.sql 06c2mucgn6t5g      1357081020

6.替换coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql中的SYS.SQLPROF_ATTR,把它更改为
coe_xfr_sql_profile_06c2mucgn6t5g_1357081020.sql中产生的SYS.SQLPROF_ATTR

----coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql的SYS.SQLPROF_ATTR
h := SYS.SQLPROF_ATTR(
q‘[BEGIN_OUTLINE_DATA]‘,
q‘[IGNORE_OPTIM_EMBEDDED_HINTS]‘,
q‘[OPTIMIZER_FEATURES_ENABLE(‘11.1.0.7‘)]‘,
q‘[DB_VERSION(‘11.1.0.7‘)]‘,
q‘[ALL_ROWS]‘,
q‘[OUTLINE_LEAF(@"SEL$1")]‘,
q‘[INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))]‘,
q‘[END_OUTLINE_DATA]‘);

----coe_xfr_sql_profile_06c2mucgn6t5g_1357081020.sql的SYS.SQLPROF_ATTR
h := SYS.SQLPROF_ATTR(
q‘[BEGIN_OUTLINE_DATA]‘,
q‘[IGNORE_OPTIM_EMBEDDED_HINTS]‘,
q‘[OPTIMIZER_FEATURES_ENABLE(‘11.1.0.7‘)]‘,
q‘[DB_VERSION(‘11.1.0.7‘)]‘,
q‘[ALL_ROWS]‘,
q‘[OUTLINE_LEAF(@"SEL$1")]‘,
q‘[FULL(@"SEL$1" "TEST"@"SEL$1")]‘,
q‘[END_OUTLINE_DATA]‘);

7.执行替换过SYS.SQLPROF_ATTR的coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql
sqlplus / as sysdba
SQL> @coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql

8.查看产生的sql profile,此时原语句在不加hint的情况下也走全表扫了
select * from dba_sql_profiles;

注意:
1.这个测试只是为了演示通过coe_xfr_sql_profile.sql实现手动加hint的方法,实际上面的语句问题的处理最佳的方法应该是重新
收集scott.test的统计信息才对.
2.当一条sql既有sql profile又有stored outline时,优化器优先选择stored outline.
3.force_match参数,TRUE:FORCE (match even when different literals in SQL),FALSE:EXACT (similar to CURSOR_SHARING).
4.通过sql profile手动加hint的方法很简单,而为sql添加最合理的hint才是关键.
5.测试完后,可以通过 exec dbms_sqltune.drop_sql_profile(name =>‘coe_6w02d3ggsj4xb_2317948335‘ );删除这个sql profile.

-----coe_xfr_sql_profile.sql
    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: 215187.1 coe_xfr_sql_profile.sql 11.4.3.5 2011/08/10 carlos.sierra $
    REM
    REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
    REM
    REM AUTHOR
    REM carlos.sierra@oracle.com
    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
    REM EXECUTION
    REM 1. Connect into SQL*Plus as SYSDBA or user with access to
    REM data dictionary.
    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
    SET TERM ON ECHO OFF;
    PRO
    PRO Parameter 1:
    PRO SQL_ID (required)
    PRO
    DEF sql_id = ‘&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 other_xml IS NOT NULL ),
    m AS (
    SELECT plan_hash_value,
          SUM(elapsed_time)/SUM(executions) avg_et_secs
      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_total)/SUM(executions_total) avg_et_secs
      FROM dba_hist_sqlstat
    WHERE sql_id = TRIM(‘&&sql_id.‘)
      AND executions_total > 0
    GROUP BY
          plan_hash_value )
    SELECT p.plan_hash_value,
          ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) 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
          avg_et_secs NULLS LAST;
    PRO
    PRO Parameter 2:
    PRO PLAN_HASH_VALUE (required)
    PRO
    DEF plan_hash_value = ‘&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 other_xml CLOB;
    EXEC :sql_text := 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;
        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;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(‘getting sql_text from memory: ‘||SQLERRM);
        :sql_text := NULL;
    END;
    /

    -- get sql_text from awr
    BEGIN
      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 = TRIM(‘&&sql_id.‘)
          AND sql_text IS NOT NULL
          AND ROWNUM = 1;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(‘getting sql_text from awr: ‘||SQLERRM);
        :sql_text := NULL;
    END;
    /

    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;

    -- to avoid errors when sql_text lacks LFs and is more than 2000 bytes
    BEGIN
      :sql_text := REPLACE(:sql_text, ‘)‘, ‘)‘||CHR(10));
      :sql_text := REPLACE(:sql_text, ‘,‘, ‘,‘||CHR(10));
      -- remove consecutive LFs
      :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10)||CHR(10)||CHR(10), CHR(10));
      :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10), CHR(10));
      :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10), CHR(10));
    END;
    /

    SELECT :sql_text FROM DUAL;

    -- 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 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_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.3.5 ‘||TO_CHAR(SYSDATE, ‘YYYY/MM/DD‘)||‘ carlos.sierra $‘);
      DBMS_OUTPUT.PUT_LINE(‘REM‘);
      DBMS_OUTPUT.PUT_LINE(‘REM Copyright (c) 2000-2011, 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‘);
      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(‘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(‘BEGIN‘);
      DBMS_OUTPUT.PUT_LINE(‘sql_txt := q‘‘[‘);
      WHILE NVL(LENGTH(:sql_text), 0) > 0
      LOOP
        l_pos := INSTR(:sql_text, CHR(10));
        IF l_pos > 0 THEN
          DBMS_OUTPUT.PUT_LINE(SUBSTR(:sql_text, 1, l_pos - 1));
          :sql_text := SUBSTR(:sql_text, l_pos + 1);
        ELSE
          DBMS_OUTPUT.PUT_LINE(:sql_text);
          :sql_text := NULL;
        END IF;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(‘]‘‘;‘);
      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(‘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||‘‘‘‘,‘);
      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(‘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(‘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.

原文：http://www.cnblogs.com/SUN-PH/p/4953881.html