PLSQL_性能优化系列19_Oracle Explain Plan解析计划通过Profile绑定

简介: 20150529 Created By BaoXinjian   一、摘要   1. 应用场景 当运行很久的Job突然出现性能问题时,并排除数据量突然变大,可能原因有执行的脚本的某些对应的SQL的解析计划变更,在Oracle 10g中可以通过绑定profile,在11g中可以通过baseline进行绑定 2.

20150529 Created By BaoXinjian

 

一、摘要 


 1. 应用场景

当运行很久的Job突然出现性能问题时,并排除数据量突然变大,可能原因有执行的脚本的某些对应的SQL的解析计划变更,在Oracle 10g中可以通过绑定profile,在11g中可以通过baseline进行绑定

2. 解决步骤

Step1. 通过对应的User或者其他信息查找Session ID

Step2. 通过Session_ID查看该SQL_ID历史的解析计划变换 

Step3. 如果最近发生解析计划变更,并导致效率变慢,通过Profile绑定之前的解析计划 

Step4. 确定解析计划是否绑定 

Step5. 如果脚本还在运行,让客户停掉脚本,并重新运行,以测试绑定的新的解析计划是否满足期望 

Step6. 如果绑定的解析计划并不能解决性能问题,验证后将其删除

 

二、步骤


Step1. 通过对应的User或者其他信息查找Session ID

select sql_id, username, osuser, machine, program
  from v$session
 where username = 'gavin'

Step2. 查看该SQL_ID历史的解析计划变换

复制代码
select distinct a.instance_number,
                trunc(b.begin_interval_time, 'mi'), sql_id, plan_hash_value from dba_hist_sqlstat a, dba_hist_snapshot b where a.snap_id = b.snap_id and sql_id = '089dbukv1aanh' order by 2, 1
复制代码

Step3. 如果最近发生解析计划变更,并导致效率变慢,通过Profile绑定之前的解析计划 (脚本coe_xfr_sql_profile.sql)

SQL>@D:/Gavin/coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 6abthk1u14yb7


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2848324471        .002

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2848324471

Values passed:
~~~~~~~~~~~~~
SQL_ID         : "6abthk1u14yb7"
PLAN_HASH_VALUE: "2848324471"


Execute coe_xfr_sql_profile_6abthk1u14yb7_2848324471.sql
on TARGET system in order to create a custom SQL Profile
with plan 2848324471 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

Step4. 运行产生的sql script脚本(coe_xfr_sql_profile_6abthk1u14yb7_2848324471.sql) 

SQL> @coe_xfr_sql_profile_6abthk1u14yb7_2848324471.sql
SQL> REM
SQL> REM $Header: 215187.1 coe_xfr_sql_profile_6abthk1u14yb7_2848324471.sql 11.4.1.4 2015/05/31 csie
SQL> REM
SQL> REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
SQL> REM
SQL> REM AUTHOR
SQL> REM   carlos.sierra@oracle.com
SQL> REM
SQL> REM SCRIPT
SQL> REM   coe_xfr_sql_profile_6abthk1u14yb7_2848324471.sql
SQL> REM
SQL> REM DESCRIPTION
SQL> REM   This script is generated by coe_xfr_sql_profile.sql
SQL> REM   It contains the SQL*Plus commands to create a custom
SQL> REM   SQL Profile for SQL_ID 6abthk1u14yb7 based on plan hash
SQL> REM   value 2848324471.
SQL> REM   The custom SQL Profile to be created by this script
SQL> REM   will affect plans for SQL commands with signature
SQL> REM   matching the one for SQL Text below.
SQL> REM   Review SQL Text and adjust accordingly.
SQL> REM
SQL> REM PARAMETERS
SQL> REM   None.
SQL> REM
SQL> REM EXAMPLE
SQL> REM   SQL> START coe_xfr_sql_profile_6abthk1u14yb7_2848324471.sql;
SQL> REM
SQL> REM NOTES
SQL> REM   1. Should be run as SYSTEM or SYSDBA.
SQL> REM   2. User must have CREATE ANY SQL PROFILE privilege.
SQL> REM   3. SOURCE and TARGET systems can be the same or similar.
SQL> REM   4. To drop this custom SQL Profile after it has been created:
SQL> REM         EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_6abthk1u14yb7_2848324471');
SQL> REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL> REM         for the Oracle Tuning Pack.
SQL> REM
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL> REM
SQL> VAR signature NUMBER;
SQL> REM
SQL> DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6  SELECT VERSION FROM V$INSTANCE
  7  ]';
  8  h := SYS.SQLPROF_ATTR(
  9  q'[BEGIN_OUTLINE_DATA]',
 10  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 11  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
 12  q'[DB_VERSION('11.2.0.1')]',
 13  q'[ALL_ROWS]',
 14  q'[OUTLINE_LEAF(@"SEL$5C160134")]',
 15  q'[MERGE(@"SEL$335DD26A")]',
 16  q'[OUTLINE(@"SEL$1")]',
 17  q'[OUTLINE(@"SEL$335DD26A")]',
 18  q'[MERGE(@"SEL$3")]',
 19  q'[OUTLINE(@"SEL$2")]',
 20  q'[OUTLINE(@"SEL$3")]',
 21  q'[FULL(@"SEL$5C160134" "KS"@"SEL$3")]',
 22  q'[FULL(@"SEL$5C160134" "KV"@"SEL$3")]',
 23  q'[FULL(@"SEL$5C160134" "QU"@"SEL$3")]',
 24  q'[LEADING(@"SEL$5C160134" "KS"@"SEL$3" "KV"@"SEL$3" "QU"@"SEL$3")]',
 25  q'[USE_MERGE_CARTESIAN(@"SEL$5C160134" "KV"@"SEL$3")]',
 26  q'[USE_MERGE_CARTESIAN(@"SEL$5C160134" "QU"@"SEL$3")]',
 27  q'[END_OUTLINE_DATA]');
 28  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 29  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 30  sql_text    => sql_txt,
 31  profile     => h,
 32  name        => 'coe_6abthk1u14yb7_2848324471',
 33  description => 'coe 6abthk1u14yb7 2848324471 '||:signature||'',
 34  category    => 'DEFAULT',
 35  validate    => TRUE,
 36  replace     => TRUE,
 37  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (si
 38  END;
 39  /

PL/SQL procedure successfully completed.

SQL> WHENEVER SQLERROR CONTINUE
SQL> SET ECHO OFF;

            SIGNATURE
---------------------
  6854104167049572323


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_6abthk1u14yb7_2848324471 completed
SQL> 

Step5. 确定Profile是否创建

select name, sql_text, created, description, type, status
  from dba_sql_profiles
where description like '%6abthk1u14yb7%'

 

Step6. 确定Profile是否绑定

select sql_id, sql_profile, plan_hash_value
  from v$sql
 where sql_id = '6abthk1u14yb7';

 

Step7. 如果脚本还在运行,让客户停掉脚本,并重新运行,以测试绑定的新的解析计划是否满足期望

alter system disconnect session '102,102' immediate; 

Step8. 如果绑定的解析计划并不能解决性能问题,验证后将其删除

--Disable Profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(Name => '<SQL PROFILE>', Attribute_Name => 'STATUS', Value => 'DISABLED');
--Drop Profile 
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '<SQL PROFILE>');

 

三、代码


Oracle Metalink下载代码 - 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>;  
REM  
REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.1.4 2010/07/12 csierra $  
REM  
REM Copyright (c) 2000-2010, 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:  
PRO ~~~~~~~~~~~~~  
PRO SQL_ID         : "&&sql_id."  
PRO PLAN_HASH_VALUE: "&&plan_hash_value."  
PRO  
SET TERM OFF ECHO ON;  
WHENEVER SQLERROR EXIT SQL.SQLCODE;  
  
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;  
  
-- 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;  
  
-- 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;  
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.1.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' csierra $');  
  DBMS_OUTPUT.PUT_LINE('REM');  
  DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2010, 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>;  
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  
PRO  
PRO COE_XFR_SQL_PROFILE completed. 

 

Thanks and Regards

参考:Oracle Metalink

ERP技术讨论群: 288307890
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
相关文章
|
1月前
|
Linux 网络安全 网络虚拟化
Linux虚拟网络设备:底层原理与性能优化深度解析
在深入探讨Linux虚拟网络设备的底层原理之前,重要的是要理解这些设备如何在Linux内核中实现,以及它们如何与操作系统的其他部分交互以提供高效且灵活的网络功能。虚拟网络设备在现代网络架构中发挥着关键作用🔑,特别是在云计算☁️、容器化📦和网络功能虚拟化(NFV)环境中。
Linux虚拟网络设备:底层原理与性能优化深度解析
|
1月前
|
存储 缓存 固态存储
深度解析linux主机:从应用到硬盘,玩转系统I/O性能优化攻略!
深度解析linux主机:从应用到硬盘,玩转系统I/O性能优化攻略!
|
8月前
|
SQL Oracle 关系型数据库
问题出在Debezium Oracle Connector的日志解析器上
问题出在Debezium Oracle Connector的日志解析器上
79 2
|
9月前
|
Java 应用服务中间件 Maven
解析Spring Boot中的Profile:配置文件与代码的双重掌控
解析Spring Boot中的Profile:配置文件与代码的双重掌控
|
9月前
|
网络协议 Linux 应用服务中间件
2022红帽企业版网络配置--centos7配置DHCP DNS绑定域名 FTP HTTP(apache) nginx samba
2022红帽企业版网络配置--centos7配置DHCP DNS绑定域名 FTP HTTP(apache) nginx samba
170 0
|
11天前
|
存储 安全 Java
深入解析 Java 中的 Synchronized:原理、实现与性能优化
深入解析 Java 中的 Synchronized:原理、实现与性能优化
17 1
|
14天前
|
运维 网络协议 JavaScript
Serverless 应用引擎产品使用合集之绑定自定义域名是否要确定解析设置
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
|
1月前
|
存储 算法 Java
深入解析Java虚拟机(JVM):技术原理与性能优化
深入解析Java虚拟机(JVM):技术原理与性能优化
36 1
|
1月前
|
算法 编译器 PHP
PHP 8 新特性与性能优化解析
PHP 8 是最新版本的 PHP 编程语言,本文将深入探讨 PHP 8 的新特性以及针对性能进行的优化,帮助开发者更好地了解和应用这些特性,提升代码效率和性能。
41 3

推荐镜像

更多