使用coe

使用coe

历史的执行计划找到一个合理的执行计划进行绑定

1. 存在多个执行计划的语句，按照索引是比较合适的，FULL SCAN不合适

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

select  * from   p  where  deptno=30 select  * from  table (dbms_xplan.display_cursor( '4hpk08j31nm7y' , null )) SQL_ID  4hpk08j31nm7y, child number 0 ------------------------------------- select  * from   p  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   p  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脚本来绑定

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130

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   carlos.sierra@oracle 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  p  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

执行.htm?id=41222768202

3、再此重新执行语句

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

select  * from   p  where  deptno=30 select  * from  table (dbms_xplan.display_cursor( null , null )) SQL_ID  4hpk08j31nm7y, child number 2 ------------------------------------- select  * from   p  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已经绑定上，执行计划已这个为最佳，为止绑定处理 .htm?id=41222768202

 

二、自己来构造合理的执行计划

1、构造执行计划

以下例子中sql语句走的是全表扫描，没有走索引，构造一个走索引的语句，来替换全表扫描执行计划

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23

alter  session set  optimizer_index_cost_adj=500 select  * from   p  where  deptno=30 select  * from  table (dbms_xplan.display_cursor( null , null )) SQL_ID  4hpk08j31nm7y, child number 0 ------------------------------------- select  * from   p  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

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

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

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23

select  /*+index(emp index_emp_deptno)*/  * from   p  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   p  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

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

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

查看.htm?id=41222768202信息，需要替换的是这段内容

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 这个脚本

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86

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   carlos.sierra@oracle 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  p  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、再次语句查看执行计划

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

select  * from   p  where  deptno=30 select  * from  table (dbms_xplan.display_cursor( null , null )) SQL_ID  4hpk08j31nm7y, child number 0 ------------------------------------- select  * from   p  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