Manual类型的SQL Profile

实验目的：Manual类型稳定执行计划，相对AUTOMATIC类型更稳定 关键词： dbms_stats.gather_table_stats、 DBMS_SQLTUNE.DROP_SQL_PROFILE、 coe_xfr_sql_profile.sql   一、创建表，收集表统计信息 SQL> create  table t1( n number); 表已创建。 SQL> declare   2     begin   3      for  i in 1 .. 10000   4      loop   5      insert  into t1 values(i);   6      commit;   7      end loop;   8      end;   9      / PL/SQL 过程已成功完成。  SQL>   select  count(*)  from  t1;   COUNT(*) ----------      10000 SQL> create index  idx_t1  on  t1(n); 索引已创建。     SQL> exec dbms_stats.gather_table_stats( ownname =>'TEST' , tabname =>'T1' , method_opt =>'for all columns size 1', CASCADE => TRUE); PL/SQL 过程已成功完成。 二是查找到SQL_PROFILE,并将相应的SQL_PROFILE删除 SQL> SET LONG 9000 SQL> SET LONGCHUNKSIZE  1000 SQL> SET LINESIZE  2000 SQL> SELECT NAME ,SQL_TEXT, TYPE,STATUS,FORCE_MATCHING FROM  DBA_SQL_PROFILES  WHERE  SQL_TEXT LIKE 'SELECT  /*+  NO_INDEX(T1 IDX_T1) */  *   FROM  T1 WHERE%'; NAME                                                         SQL_TEXT                                                                                               TYPE       STATUS       FORCE_ ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------- ---------------- ------ SYS_SQLPROF_0162663bdb700000                                 SELECT  /*+  NO_INDEX(T1 IDX_T1) */  *   FROM  T1 WHERE N=1                                                                                                       MANUAL         ENABLED          NO SYS_SQLPROF_01626643a6130001                                 SELECT  /*+  NO_INDEX(T1 IDX_T1) */  *   FROM  T1 WHERE N=1                                                                                                       MANUAL         ENABLED          YES SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_01626643a6130001'); PL/SQL 过程已成功完成。 SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_0162663bdb700000'); PL/SQL 过程已成功完成。 三是调用coe_xfr_sql_profile.sql，产生Manual类型的SQL PROFILE脚本 SQL> SELECT  /*+  NO_INDEX(T1 IDX_T1) */  *   FROM  T1 WHERE N=1 ;          N ----------          1 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID  gn8zuq00kd86g, child number 0 ------------------------------------- SELECT  /*+  NO_INDEX(T1 IDX_T1) */  *   FROM  T1 WHERE N=1 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          | |*  1 |  TABLE ACCESS FULL| T1   |     1 |     4 |     7   (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1 Outline Data -------------   /*+ PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "T1"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ---------------------------------------------------    1 - filter("N"=1) Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - "N"[NUMBER,22] 已选择42行。 SQL> SELECT /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3;          Nse ----------          3 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID  866w0nx37z5kg, child number 0 ------------------------------------- SELECT /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3 Plan hash value: 1369807930 --------------------------------------------------------------------------- | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |        |       |       |     1 (100)|          | |*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1 Outline Data -------------   /*+ PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ---------------------------------------------------    1 - access("N"=3) Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - "N"[NUMBER,22] 已选择42行。 SQL> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%N=3%'; SQL_TEXT -------------------------------------------------------------------------------- SQL_ID                     VERSION_COUNT -------------------------- ------------- SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%N=3%' 9bvng6dz8ct9z                          1 SELECT /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3 866w0nx37z5kg                          1 SQL> SELECT PLAN_HASH_VALUE FROM V$SQL WHERE SQL_ID='866w0nx37z5kg'; PLAN_HASH_VALUE ---------------      1369807930 SQL> @F:\oracle\脚本\coe_xfr_sql_profile.sql   Parameter 1: SQL_ID (required) 输入 1 的值:   866w0nx37z5kg    --条件为 N=3 的SQL_ID PLAN_HASH_VALUE AVG_ET_SECS --------------- -----------      1369807930        .001 Parameter 2: PLAN_HASH_VALUE (required) 输入 2 的值:   1369807930 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID         : "866w0nx37z5kg" PLAN_HASH_VALUE: "1369807930" 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_866w0nx37z5kg_1369807930.sql on TARGET system in order to create a custom SQL Profile with plan 1369807930 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed.   SQL> @F:\oracle\脚本\coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) 输入 1 的值:   gn8zuq00kd86g   --条件为 N=1的SQL_ID PLAN_HASH_VALUE AVG_ET_SECS --------------- -----------      3617692013        .002 Parameter 2: PLAN_HASH_VALUE (required) 输入 2 的值:   3617692013 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID         : "gn8zuq00kd86g" PLAN_HASH_VALUE: "3617692013" 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_gn8zuq00kd86g_3617692013.sql on TARGET system in order to create a custom SQL Profile with plan 3617692013 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. 四、将coe_xfr_sql_profile_866w0nx37z5kg_1369807930.sql中HINT组合 h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', q'[DB_VERSION('11.2.0.4')]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]', q'[END_OUTLINE_DATA]'); 替换 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', q'[DB_VERSION('11.2.0.4')]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[FULL(@"SEL$1" "T1"@"SEL$1")]', q'[END_OUTLINE_DATA]'); :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);    并将 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql中的参数 FORCE_MATCH 的值由 FALSE 替换 TRUE 五、执行脚本，调整执行计划 SQL> @C:\Users\YX\coe_xfr_sql_profile_gn8zuq00kd86g_3617692013（修改后）.sql SQL>REM SQL>REM $Header: 215187.1 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql 11.4.3.5 2018/03/28 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.com SQL>REM SQL>REM SCRIPT SQL>REM   coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.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 gn8zuq00kd86g based on plan hash SQL>REM   value 3617692013. 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_gn8zuq00kd86g_3617692013.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_gn8zuq00kd86g_3617692013'); 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     /*+  NO_INDEX(T1 IDX_T1)   7  */  *      FROM  T1 WHERE N=1   8  ]';   9  h := SYS.SQLPROF_ATTR( 10   q'[BEGIN_OUTLINE_DATA]', 11  q'[IGNORE_OPTIM_EMBEDDED_HINTS]', 12  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', 13  q'[DB_VERSION('11.2.0.4')]', 14  q'[ALL_ROWS]', 15  q'[OUTLINE_LEAF(@"SEL$1")]', 16  q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]', 17  q'[END_OUTLINE_DATA]'); 18  DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( 19  sql_text    => sql_txt, 20  profile     => h, 21  name        => 'coe_gn8zuq00kd86g_3617692013', 22  description => 'coe gn8zuq00kd86g 3617692013 '||:signature||'', 23  category    => 'DEFAULT', 24  validate    => TRUE, 25  replace     => TRUE, 26  force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); 27  END; 28  / PL/SQL 过程已成功完成。 SQL>WHENEVER SQLERROR CONTIN UE SQL>SET ECHO OFF;             SIGNATURE --------------------- ... manual custom SQL Profile has been created COE_XFR_SQL_PROFILE_gn8zuq00kd86g_3617692013 completed 六、查看执行计划 SQL> SELECT  /*+  NO_INDEX(T1 IDX_T1) */  *   FROM  T1 WHERE N=1;          N ----------          1 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID  gn8zuq00kd86g, child number 0 ------------------------------------- SELECT  /*+  NO_INDEX(T1 IDX_T1) */  *   FROM  T1 WHERE N=1 Plan hash value: 1369807930 --------------------------------------------------------------------------- | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |        |       |       |     1 (100)|          | |*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1 Outline Data -------------   /*+ PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ---------------------------------------------------    1 - access("N"=1) Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - "N"[NUMBER,22] Note ----- PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------    - SQL profile coe_gn8zuq00kd86g_3617692013 used for this statement 已选择46行。