欢迎光临
我们一直在努力

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行。

赞(0)
【声明】:本博客不参与任何交易,也非中介,仅记录个人感兴趣的主机测评结果和优惠活动,内容均不作直接、间接、法定、约定的保证。访问本博客请务必遵守有关互联网的相关法律、规定与规则。一旦您访问本博客,即表示您已经知晓并接受了此声明通告。