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.

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