程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 固定執行計劃-使用coe_xfr_sql_profile，coexfrsqlprofile

固定執行計劃-使用coe_xfr_sql_profile，coexfrsqlprofile

編輯：Oracle教程

固定執行計劃-使用coe_xfr_sql_profile，coexfrsqlprofile

一、歷史執行計劃固定

歷史的執行計劃找到一個合理的執行計劃進行綁定

1. 存在多個執行計劃的語句，按照索引是比較合適的，FULL SCAN不合適

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))

SQL_ID  4hpk08j31nm7y, child number 0
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 1404472509
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     6 |   228 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_EMP_DEPTNO |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=30)
 
SQL_ID  4hpk08j31nm7y, child number 1
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     6 |   228 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTNO"=30)

存在兩個執行計劃，使之後的SQL語句都走Plan hash value: 1404472509 處理模

2、運行coe_xfr_sql_profile腳本來綁定

sys@GULL> @coe_xfr_sql_profile.SQL

Parameter 1:
SQL_ID (required)

輸入 1 的值:  4hpk08j31nm7y


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1404472509        .002
     3956160932        .015

Parameter 2:
PLAN_HASH_VALUE (required)

輸入 2 的值:  1404472509

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "4hpk08j31nm7y"
PLAN_HASH_VALUE: "1404472509"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

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


COE_XFR_SQL_PROFILE completed.

sys@GULL> @coe_xfr_sql_profile_4hpk08j31nm7y_1404472509.sql
sys@GULL> REM
sys@GULL> REM $Header: 215187.1 coe_xfr_sql_profile_4hpk08j31nm7y_1404472509.sql 11.4.3.5 2016/06/20 carlos.sierra $
sys@GULL> REM
sys@GULL> REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
sys@GULL> REM
sys@GULL> REM AUTHOR
sys@GULL> REM   [email protected]
sys@GULL> REM
sys@GULL> REM SCRIPT
sys@GULL> REM   coe_xfr_sql_profile_4hpk08j31nm7y_1404472509.sql
sys@GULL> REM
sys@GULL> REM DESCRIPTION
sys@GULL> REM   This script is generated by coe_xfr_sql_profile.sql
sys@GULL> REM   It contains the SQL*Plus commands to create a custom
sys@GULL> REM   SQL Profile for SQL_ID 4hpk08j31nm7y based on plan hash
sys@GULL> REM   value 1404472509.
sys@GULL> REM   The custom SQL Profile to be created by this script
sys@GULL> REM   will affect plans for SQL commands with signature
sys@GULL> REM   matching the one for SQL Text below.
sys@GULL> REM   Review SQL Text and adjust accordingly.
sys@GULL> REM
sys@GULL> REM PARAMETERS
sys@GULL> REM   None.
sys@GULL> REM
sys@GULL> REM EXAMPLE
sys@GULL> REM   SQL> START coe_xfr_sql_profile_4hpk08j31nm7y_1404472509.sql;
sys@GULL> REM
sys@GULL> REM NOTES
sys@GULL> REM   1. Should be run as SYSTEM or SYSDBA.
sys@GULL> REM   2. User must have CREATE ANY SQL PROFILE privilege.
sys@GULL> REM   3. SOURCE and TARGET systems can be the same or similar.
sys@GULL> REM   4. To drop this custom SQL Profile after it has been created:
sys@GULL> REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_4hpk08j31nm7y_1404472509');
sys@GULL> REM   5. Be aware that using DBMS_SQLTUNE requires a license
sys@GULL> REM      for the Oracle Tuning Pack.
sys@GULL> REM
sys@GULL> WHENEVER SQLERROR EXIT SQL.SQLCODE;
sys@GULL> REM
sys@GULL> VAR signature NUMBER;
sys@GULL> REM
sys@GULL> DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6  select * from  scott.emp  where deptno=30
  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.3')]',
 12  q'[DB_VERSION('11.2.0.3')]',
 13  q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
 14  q'[ALL_ROWS]',
 15  q'[OUTLINE_LEAF(@"SEL$1")]',
 16  q'[INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))]',
 17  q'[END_OUTLINE_DATA]');
 18  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 19  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 20  sql_text    => sql_txt,
 21  profile     => h,
 22  name        => 'coe_4hpk08j31nm7y_1404472509',
 23  description => 'coe 4hpk08j31nm7y 1404472509 '||:signature||'',
 24  category    => 'DEFAULT',
 25  validate    => TRUE,
 26  replace     => TRUE,
 27  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 28  END;
 29  /

PL/SQL 過程已成功完成。

sys@GULL> WHENEVER SQLERROR CONTINUE
sys@GULL> SET ECHO OFF;

            SIGNATURE
---------------------
  7148830044791940844


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_4hpk08j31nm7y_1404472509 completed

執行COE_XFR_SQL_PROFILE_4hpk08j31nm7y_1404472509

3、再此重新執行語句

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID  4hpk08j31nm7y, child number 2
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 1404472509
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |    10 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     6 |   228 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_EMP_DEPTNO |     6 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=30)
 
Note
-----
   - SQL profile coe_4hpk08j31nm7y_1404472509 used for this statement

SQL profile coe_4hpk08j31nm7y_1404472509 used for this statement，說明sql profile已經綁定上，執行計劃已這個為最佳，為止綁定處理

二、自己來構造合理的執行計劃

1、構造執行計劃

以下例子中sql語句走的是全表掃描，沒有走索引，構造一個走索引的語句，來替換全表掃描執行計劃

alter session set optimizer_index_cost_adj=500

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID  4hpk08j31nm7y, child number 0
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     6 |   228 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTNO"=30)

執行現存在的coe_xfr_sql_profile

sys@GULL> @coe_xfr_sql_profile.SQL

Parameter 1:
SQL_ID (required)

輸入 1 的值:  4hpk08j31nm7y


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3956160932        .041

Parameter 2:
PLAN_HASH_VALUE (required)

輸入 2 的值:  3956160932 

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "4hpk08j31nm7y"
PLAN_HASH_VALUE: "3956160932 "

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

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


COE_XFR_SQL_PROFILE completed.

查看構造SQL的走索引執行計劃coe_xfr_sql_profile

select /*+index(emp index_emp_deptno)*/ * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID  2hdyvqk9b09va, child number 0
-------------------------------------
select /*+index(emp index_emp_deptno)*/ * from  scott.emp  where 
deptno=30
 
Plan hash value: 1404472509
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |    10 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     6 |   228 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_EMP_DEPTNO |     6 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=30)

查看次構造SQL的coe_xfr_sql_profile

SQL>@coe_xfr_sql_profile.SQL 2hdyvqk9b09va

Parameter 1:
SQL_ID (required)



PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1404472509        .001

Parameter 2:
PLAN_HASH_VALUE (required)

輸入 2 的值:  1404472509

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "2hdyvqk9b09va"
PLAN_HASH_VALUE: "1404472509"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      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).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

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


COE_XFR_SQL_PROFILE completed.

2、替換outline data

查看coe_xfr_sql_profile_2hdyvqk9b09va_1404472509.sql信息，需要替換的是這段內容

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
q'[DB_VERSION('11.2.0.3')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
q'[OPT_PARAM('optimizer_index_cost_adj' 500)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))]',
q'[END_OUTLINE_DATA]');

把這個內容替換到coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.sql 中

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
q'[DB_VERSION('11.2.0.3')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
q'[OPT_PARAM('optimizer_index_cost_adj' 500)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "EMP"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

這段信息後，執行coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.sql 這個腳本

SQL>@coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.sql 11.4.3.5 2016/06/20 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM   [email protected]
SQL>REM
SQL>REM SCRIPT
SQL>REM   coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.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 4hpk08j31nm7y based on plan hash
SQL>REM   value 3956160932.
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_4hpk08j31nm7y_3956160932.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_4hpk08j31nm7y_3956160932');
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 * from  scott.emp  where deptno=30
  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.3')]',
 12  q'[DB_VERSION('11.2.0.3')]',
 13  q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
 14  q'[OPT_PARAM('optimizer_index_cost_adj' 500)]',
 15  q'[ALL_ROWS]',
 16  q'[OUTLINE_LEAF(@"SEL$1")]',
 17  q'[INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))]',
 18  q'[END_OUTLINE_DATA]');
 19  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 20  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 21  sql_text    => sql_txt,
 22  profile     => h,
 23  name        => 'coe_4hpk08j31nm7y_3956160932',
 24  description => 'coe 4hpk08j31nm7y 3956160932 '||:signature||'',
 25  category    => 'DEFAULT',
 26  validate    => TRUE,
 27  replace     => TRUE,
 28  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 29  END;
 30  /

PL/SQL 過程已成功完成。

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
---------------------
  7148830044791940844


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_4hpk08j31nm7y_3956160932 completed

3、再次語句查看執行計劃

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID  4hpk08j31nm7y, child number 0
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 1404472509
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |    10 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     6 |   228 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_EMP_DEPTNO |     6 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=30)
 
Note
-----
   - SQL profile coe_4hpk08j31nm7y_3956160932 used for this statement

偷梁換柱成功，固定執行so easy

提供腳本文件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 [email protected] 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 [email protected]'); 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. COE_XFR_SQL_PROFILE

參考《基於SQL的優化》