程序博客网 > 男款汉服款式知乎

SQL Profiles-PartII

来源：互联网 发布：男款汉服款式知乎 编辑：程序博客网 时间：2024/05/01 18:15

在上一篇《SQL Profiles-Part I》，我向大家介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。那么在今天，将向大家介绍如何手工创建SQL Profiles（即不通过SQL Tuning Advisor)来达成2个目的：

  • 锁定或者说稳定SQL执行计划。
  • 在不能修改应用的SQL的情况下，来改变或者说是强制使SQL使用我们指定的执行计划，即使原始的SQL包含了Hints。

那么，这里最关键的一点是，如何来手工创建SQL Profiles?
答案是，正如上一篇中有朋友的留言，使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程。

view plaincopy to clipboardprint?
  1. SQL> desc dbms_sqltune  
  2. ...  
  3. PROCEDURE IMPORT_SQL_PROFILE  
  4. 参数名称                       类型                    输入/输出默认值?  
  5. ------------------------------ ----------------------- ------ --------  
  6.  SQL_TEXT                       CLOB                    IN  
  7.  PROFILE                        SQLPROF_ATTR            IN  
  8.  NAME                           VARCHAR2                IN     DEFAULT  
  9.  DESCRIPTION                    VARCHAR2                IN     DEFAULT  
  10.  CATEGORY                       VARCHAR2                IN     DEFAULT  
  11.  VALIDATE                       BOOLEAN                 IN     DEFAULT  
  12.  REPLACE                        BOOLEAN                 IN     DEFAULT  
  13.  FORCE_MATCH                    BOOLEAN                 IN     DEFAULT  
  14. ...  

这个过程其名字与实际功能有所差异，其实可以理解为CREATE OR REPLACE SQL_PROFILE。过程中的PROFILE参数为SYS.SQLPROF_ATTR，这种类型其实就是VARCHAR2的集合类型(COLLECTION)：

view plaincopy to clipboardprint?
  1. SQL> select text from dba_source where name='SQLPROF_ATTR' and owner='SYS';  
  2.   
  3. TYPE     sqlprof_attr  
  4.  AS VARRAY(2000) of VARCHAR2(500)  

下面我们就用这个过程来创建SQL PROFILE：
为避免干扰，将上一篇测试中生成的SQL Profile删除掉，同时恢复T1表的统计信息中的表行数：

view plaincopy to clipboardprint?
  1. SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000');  
  2.   
  3. PL/SQL 过程已成功完成。  
  4.   
  5. SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>49953);  
  6.   
  7. PL/SQL 过程已成功完成。  

现在我们手工创建一个SQL Profile：

view plaincopy to clipboardprint?
  1. SQL> declare  
  2.   2    v_hints sys.sqlprof_attr;  
  3.   3  begin  
  4.   4    v_hints:=sys.sqlprof_attr('USE_NL(T1 T2)','INDEX(T2)');  
  5.   5    dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',  
  6.   6                v_hints,'SQLPROFILE_NAME1',force_match=>true);  
  7.   7  end;  
  8.   8  /  
  9.   
  10. PL/SQL 过程已成功完成。  
  11.   
  12. SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b  
  13.   2  where a.signature = b.signature  
  14.   3  and a.name='SQLPROFILE_NAME1';  
  15.   
  16. ATTR_VAL  
  17. ----------------------------------------  
  18. USE_NL(T1 T2)  
  19. INDEX(T2)  

下面执行SQL Profiles对应的SQL：

view plaincopy to clipboardprint?
  1. SQL> select  t1.*,t2.owner  
  2.   2       from t1,t2  
  3.   3       where t1.object_name like '%T1%'  
  4.   4       and t1.object_id=t2.object_id;  
  5.   
  6. 已选择29行。  
  7.   
  8. 执行计划  
  9. ----------------------------------------------------------  
  10. Plan hash value: 1838229974  
  11.   
  12. ---------------------------------------------------------------------------  
  13. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  14. ---------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT   |      |  2498 | 99920 |   219   (4)| 00:00:03 |  
  16. |*  1 |  HASH JOIN         |      |  2498 | 99920 |   219   (4)| 00:00:03 |  
  17. |*  2 |   TABLE ACCESS FULL| T1   |  2498 | 72442 |    59   (6)| 00:00:01 |  
  18. |   3 |   TABLE ACCESS FULL| T2   | 49954 |   536K|   159   (2)| 00:00:02 |  
  19. ---------------------------------------------------------------------------  
  20.   
  21. Predicate Information (identified by operation id):  
  22. ---------------------------------------------------  
  23.   
  24.    1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
  25.    2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
  26.   
  27. Note  
  28. -----  
  29.    - SQL profile "SQLPROFILE_NAME1" used for this statement  
  30.   
  31. 统计信息  
  32. ----------------------------------------------------------  
  33.           0  recursive calls  
  34.           0  db block gets  
  35.         933  consistent gets  
  36.           

可以看到，SQL使用了SQL Profile，不过没有达到我们预期的效果。

看起来是SQL Profile使用的Hints有问题。我们重新设置SQL Profile的Hints，在Hints中加上“Query Block Name”。这一次在执行IMPORT_SQL_PROFILE过程时，将REPLACE参数设置为TRUE，以替换现有的SQL Profile：

view plaincopy to clipboardprint?
  1. SQL> declare  
  2.   2    v_hints sys.sqlprof_attr;  
  3.   3  begin  
  4.   4    v_hints:=sys.sqlprof_attr('USE_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)');  
  5.   5    dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',  
  6.   6                v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true);  
  7.   7  end;  
  8.   8  /  
  9.   
  10. PL/SQL 过程已成功完成。  
  11.   
  12. SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b  
  13.   2  where a.signature = b.signature  
  14.   3  and a.name='SQLPROFILE_NAME1';  
  15.   
  16. ATTR_VAL  
  17. ----------------------------------------  
  18. USE_NL(T1@SEL$1 T2@SEL$1)  
  19. INDEX(T2@SEL$1)  

再次执行下面的SQL：

view plaincopy to clipboardprint?
  1. SQL> select  t1.*,t2.owner  
  2.   2       from t1,t2  
  3.   3       where t1.object_name like '%T1%'  
  4.   4       and t1.object_id=t2.object_id;  
  5.   
  6. 已选择29行。  
  7.   
  8. 执行计划  
  9. ----------------------------------------------------------  
  10. Plan hash value: 3787413387  
  11.   
  12. --------------------------------------------------------------------------------------  
  13. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  14. --------------------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT            |        |  2498 | 99920 |  5061   (1)| 00:01:01 |  
  16. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
  17. |   2 |   NESTED LOOPS              |        |  2498 | 99920 |  5061   (1)| 00:01:01 |  
  18. |*  3 |    TABLE ACCESS FULL        | T1     |  2498 | 72442 |    59   (6)| 00:00:01 |  
  19. |*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |  
  20. --------------------------------------------------------------------------------------  
  21.   
  22. Predicate Information (identified by operation id):  
  23. ---------------------------------------------------  
  24.   
  25.    3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
  26.    4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
  27.   
  28. Note  
  29. -----  
  30.    - SQL profile "SQLPROFILE_NAME1" used for this statement  
  31.   
  32. 统计信息  
  33. ----------------------------------------------------------  
  34.           0  recursive calls  
  35.           0  db block gets  
  36.         294  consistent gets  

这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。
那么我们再一次手工修改T1表的统计信息，看看结果如何：

view plaincopy to clipboardprint?
  1. SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>5000000);  
  2.   
  3. PL/SQL 过程已成功完成。  
  4.   
  5. SQL> select  t1.*,t2.owner  
  6.   2       from t1,t2  
  7.   3       where t1.object_name like '%T1%'  
  8.   4       and t1.object_id=t2.object_id;  
  9.   
  10. 已选择29行。  
  11.   
  12. 执行计划  
  13. ----------------------------------------------------------  
  14. Plan hash value: 3787413387  
  15. --------------------------------------------------------------------------------------  
  16. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  17. --------------------------------------------------------------------------------------  
  18. |   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
  19. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
  20. |   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
  21. |*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |  
  22. |*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |  
  23. --------------------------------------------------------------------------------------  
  24. Predicate Information (identified by operation id):  
  25. ---------------------------------------------------  
  26.    3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
  27.    4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
  28.   
  29. Note  
  30. -----  
  31.    - SQL profile "SQLPROFILE_NAME1" used for this statement  
  32.   
  33. 统计信息  
  34. ----------------------------------------------------------  
  35.           0  recursive calls  
  36.           0  db block gets  
  37.         294  consistent gets  

可以看到，Oracle优化器评估表T1经过Like条件过滤后返回的行数虽然很大，但是这里的执行计划仍然与未修改统计信息之前一样，使用range scan+ nested loop join。

通过以上的测试，我们明白了DBMS_SQLTUNE.IMPORT_SQL_PROFILE的使用，同时也验证了这种方式的有效性，SQL Profiles能够像Outlines一样，能够稳定SQL的执行计划。

接下来我们需要完成两个任务。
任务一：对现有的SQL稳定其执行计划。
这里的问题是：稳定一条SQL语句的Hints从哪里来？简单的sql，没问题，我们可以手工构造，但是复杂的SQL，手工构造相对比较复杂，同时手工构造的Hints不一定能够保证SQL的执行计划就会稳定。从10g开始，v$sql_plan中就包括了SQL语句OUTLINE数据，也就是稳定执行计划的Hints。从下面可以看到：

view plaincopy to clipboardprint?
  1. SQL> select  t1.*,t2.owner  
  2.   2       from t1,t2  
  3.   3       where t1.object_name like '%T1%'  
  4.   4       and t1.object_id=t2.object_id;  
  5.   
  6. 已选择29行。  
  7.   
  8. SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));  
  9.   
  10. PLAN_TABLE_OUTPUT  
  11. ----------------------------------------------------------------------------------------  
  12. SQL_ID  6m45w7r0xgdfj, child number 0  
  13. -------------------------------------  
  14. select  t1.*,t2.owner      from t1,t2      where t1.object_name like '%T1%'  
  15.    and t1.object_id=t2.object_id  
  16.   
  17. Plan hash value: 3787413387  
  18.   
  19. --------------------------------------------------------------------------------------  
  20. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  21. --------------------------------------------------------------------------------------  
  22. |   0 | SELECT STATEMENT            |        |       |       |  5061 (100)|          |  
  23. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
  24. |   2 |   NESTED LOOPS              |        |  2498 | 99920 |  5061   (1)| 00:01:01 |  
  25. |*  3 |    TABLE ACCESS FULL        | T1     |  2498 | 72442 |    59   (6)| 00:00:01 |  
  26. |*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |  
  27. --------------------------------------------------------------------------------------  
  28.   
  29. Outline Data  
  30. -------------  
  31.   
  32.   /*+  
  33.       BEGIN_OUTLINE_DATA  
  34.       IGNORE_OPTIM_EMBEDDED_HINTS  
  35.       OPTIMIZER_FEATURES_ENABLE('10.2.0.1')  
  36.       ALL_ROWS  
  37.       OUTLINE_LEAF(@"SEL$1")  
  38.       FULL(@"SEL$1" "T1"@"SEL$1")  
  39.       INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))  
  40.       LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")  
  41.       USE_NL(@"SEL$1" "T2"@"SEL$1")  
  42.       END_OUTLINE_DATA  
  43.   */  
  44.   
  45. Predicate Information (identified by operation id):  
  46. ---------------------------------------------------  
  47.   
  48.    3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
  49.    4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
  50.   
  51. Note  
  52. -----  
  53.    - SQL profile "SQLPROFILE_NAME1" used for this statement  

上面所显示的“Outline Data”即是我们稳定SQL执行计划需要的Hints(我们甚至可以将这些Hints直接写到我们的SQL中）。对需要稳定执行计划的SQL，我们所要做的就是如前面所示，将Hints与SQL文本一起创建一个SQL Profile。这里不得不提到一个SQL脚本，来自MOS。”SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]“，在这篇文章中，可以下载到sqlt.zip，这个压缩文件内有一个文件：coe_xfr_sql_profile.sql。这个脚本可以用于从shared pool、awr中提取指定的SQL ID的Outline Data并创建SQL Profile。下面是示例：

view plaincopy to clipboardprint?
  1. SQL> select /*+ proftest1 */ t1.*,t2.owner  
  2.   2       from t1,t2  
  3.   3       where t1.object_name like '%T1%'  
  4.   4       and t1.object_id=t2.object_id;  
  5.   
  6. --在shared pool中查找刚刚执行的SQL，其ID为b4zvp712np1bp--  
  7.   
  8. SQL> @coe_xfr_sql_profile.sql  
  9.   
  10. Parameter 1:  
  11. SQL_ID (required)  
  12.   
  13. 输入 1 的值:  b4zvp712np1bp  
  14.   
  15. PLAN_HASH_VALUE AVG_ET_SECS  
  16. --------------- -----------  
  17.      2959412835        .112  
  18.   
  19. Parameter 2:  
  20. PLAN_HASH_VALUE (required)  
  21.   
  22. 输入 2 的值:  2959412835  
  23.   
  24. Values passed:  
  25. ~~~~~~~~~~~~~  
  26. SQL_ID         : "b4zvp712np1bp"  
  27. PLAN_HASH_VALUE: "2959412835"  
  28.   
  29. Execute coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql  
  30. on TARGET system in order to create a custom SQL Profile  
  31. with plan 2959412835 linked to adjusted sql_text.  
  32.   
  33. COE_XFR_SQL_PROFILE completed.  
  34. SQL>@coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql  
  35.   
  36. PL/SQL 过程已成功完成。  
  37.   
  38. SQL>WHENEVER SQLERROR CONTINUE  
  39. SQL>SET ECHO OFF;  
  40.   
  41.             SIGNATURE  
  42. ---------------------  
  43.   6058051510930011685  
  44.   
  45. ... manual custom SQL Profile has been created  
  46.   
  47. COE_XFR_SQL_PROFILE_b4zvp712np1bp_2959412835 completed  
  48.   
  49. SQL>select attr_val from sys.sqlprof$attr where signature=6058051510930011685;  
  50.   
  51. ATTR_VAL  
  52. --------------------------------------------------  
  53. BEGIN_OUTLINE_DATA  
  54. IGNORE_OPTIM_EMBEDDED_HINTS  
  55. OPTIMIZER_FEATURES_ENABLE('10.2.0.1')  
  56. ALL_ROWS  
  57. OUTLINE_LEAF(@"SEL$1")  
  58. FULL(@"SEL$1" "T2"@"SEL$1")  
  59. FULL(@"SEL$1" "T1"@"SEL$1")  
  60. LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")  
  61. USE_HASH(@"SEL$1" "T1"@"SEL$1")  
  62. END_OUTLINE_DATA  

coe_xfr_sql_profile.sql这个脚本首先要求输入sql id，然后从shared pool、awr中获取sql执行的各个执行计划的统计信息（执行计划不稳定的SQL通常会有多个不同的执行计划），然后输入你认为是正确的、需要稳定的执行计划的hash value，脚本就会生成另一个脚本，这里为coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql，然后运行这个脚本，就会创建出稳定执行计划所需要的SQL Profile，SQL Profile的名字为：coe+sql_id+plan_hash_value，这里为coe_b4zvp712np1bp_2959412835。注意，这里创建的SQL Profile，force match默认为FALSE，我们可以手工修改脚本将其改为TRUE，同时我们也可以按意愿来修改生成的脚本的其他内容。

除了上面提到的脚本，http://kerryosborne.oracle-guy.com这个BLOG里面也有许多与SQL Profiles相关的脚本。其中create_sql_profile.sql可完成类似的功能，只不过功能相对简单，只能从shared pool中生成SQL Profile，因此也更方便。

任务二：在不能修改SQL的情况下改变并固定SQL的执行计划，即使原始的SQL使用了Hints。
常常遇到这样的情况，SQL语句其执行计划有问题，或者是SQL使用了错误的Hints（比如 /*+ RULE */）导致SQL性能较差，但是应用又不能修改或者时间内不能修改，那么我们怎么来改变SQL的执行计划呢。有3种办法，一种是调整统计信息，这个不建议使用，因为比较复杂、不稳定可靠（统计信息可能会重新收集），影响面广（会影响其他访问此对象的SQL）。第二种是使用OUTLINE，这种方法比较复杂。第三种就是我们今天要介绍的使用SQL Profiles了。

使用SQL Profiles来改变SQL的执行计划，其本质上就是使用Hints来改变SQL的执行计划。对于简单的SQL，我们同样可以像前面一样手工构造Hints然后再使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE来实现。但是这种方法还是略显烦琐。那么通常的方法就是”乾坤大挪移“了：

  • 取得原始SQL的文本（如有可能还包括sql id)
  • 构造一个与原始SQL在逻辑上、结构上完全相同的SQL。这里强制逻辑上和结构上相同，SQL解析的用户名、SQL中引用对象的用户名甚至是一些predicate条件都可以不同。当然能够与原始SQL完全一样就更省事。
  • 执行我们构造的SQL，并取得构造的SQL的Outline Data。
  • 使用原始SQL的文本和构造的SQL的Outline Data创建SQL Profile。

下面我们来演示一下整个过程。我们这里要修改执行计划的SQL是：

view plaincopy to clipboardprint?
  1. select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner     
  2.      from t1,t2     
  3.      where t1.object_name like '%T1%'     
  4.      and t1.object_id=t2.object_id;   

我们首先需要执行这一条SQL，然后取得的SQL ID为gmvb9bp7f9kqd：

view plaincopy to clipboardprint?
  1. SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner  
  2.   2       from t1,t2  
  3.   3       where t1.object_name like '%T1%'  
  4.   4       and t1.object_id=t2.object_id;  
  5.   
  6. 已选择29行。  
  7.   
  8. 执行计划  
  9. ----------------------------------------------------------  
  10. Plan hash value: 2959412835  
  11.   
  12. -----------------------------------------------------------------------------------  
  13. | Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
  14. -----------------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT   |      |   250K|  9765K|       |   998  (25)| 00:00:12 |  
  16. |*  1 |  HASH JOIN         |      |   250K|  9765K|  1128K|   998  (25)| 00:00:12 |  
  17. |   2 |   TABLE ACCESS FULL| T2   | 49954 |   536K|       |   159   (2)| 00:00:02 |  
  18. |*  3 |   TABLE ACCESS FULL| T1   |   250K|  7080K|       |   288  (81)| 00:00:04 |  
  19. -----------------------------------------------------------------------------------   

然后我们构造一条SQL，让这条SQL按我们希望的执行计划运行，构造的SQL其ID为cymak300cycmd：

view plaincopy to clipboardprint?
  1. SQL> select /*+ modify_sql index(t1) use_nl(t1 t2) */ t1.*,t2.owner  
  2.   2       from t1,t2  
  3.   3       where t1.object_name like '%T1%'  
  4.   4       and t1.object_id=t2.object_id;  
  5.   
  6. 已选择29行。  
  7.   
  8. 执行计划  
  9. ----------------------------------------------------------  
  10. Plan hash value: 3787413387  
  11.   
  12. --------------------------------------------------------------------------------------  
  13. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  14. --------------------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
  16. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
  17. |   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
  18. |*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |  
  19. |*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |  
  20. --------------------------------------------------------------------------------------  
  21.   
  22. Predicate Information (identified by operation id):  
  23. ---------------------------------------------------  
  24.   
  25.    3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
  26.    4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  

然后使用coe_xfr_sql_profile.sql脚本来提取我们构造的SQL的Outline Data，生成的结果为coe_xfr_sql_profile_cymak300cycmd_3787413387.sql，打开结果文件，可以看到有这么一段：

view plaincopy to clipboardprint?
  1. h := SYS.SQLPROF_ATTR(  
  2. q'[BEGIN_OUTLINE_DATA]',  
  3. q'[IGNORE_OPTIM_EMBEDDED_HINTS]',  
  4. q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.1')]',  
  5. q'[ALL_ROWS]',  
  6. q'[OUTLINE_LEAF(@"SEL$1")]',  
  7. q'[FULL(@"SEL$1" "T1"@"SEL$1")]',  
  8. q'[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',  
  9. q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',  
  10. q'[USE_NL(@"SEL$1" "T2"@"SEL$1")]',  
  11. q'[END_OUTLINE_DATA]');  

再针对gmvb9bp7f9kqd使用coe_xfr_sql_profile.sql，生成的结果文件为coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql。手工修改这个文件，将里面h := SYS.SQLPROF_ATTR…那一段替换成我们之前得到的那一段。这一次我们将这个文件中的force_match从FALSE改成TRUE。
最后我们运行coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql这个脚本文件：

view plaincopy to clipboardprint?
  1. SQL>@coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql  
  2.   
  3. PL/SQL 过程已成功完成。  
  4.   
  5. SQL>WHENEVER SQLERROR CONTINUE  
  6. SQL>SET ECHO OFF;  
  7.   
  8.             SIGNATURE  
  9. ---------------------  
  10.  15409905709853673912  
  11.   
  12. ... manual custom SQL Profile has been created  
  13.   
  14. COE_XFR_SQL_PROFILE_gmvb9bp7f9kqd_2959412835 completed  

这样就完成了我们所需要的SQL Profile的创建。下面再看看原来的SQL执行情况（这里我故意将like条件改了一下，以查看force match是否起作用）：

view plaincopy to clipboardprint?
  1. SQL>select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner  
  2.   2       from t1,t2  
  3.   3       where t1.object_name like '%T2%'  
  4.   4       and t1.object_id=t2.object_id;  
  5.   
  6. 已选择77行。  
  7.   
  8. 执行计划  
  9. ----------------------------------------------------------  
  10. Plan hash value: 3787413387  
  11.   
  12. --------------------------------------------------------------------------------------  
  13. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  14. --------------------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
  16. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
  17. |   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |  
  18. |*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |  
  19. |*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |  
  20. --------------------------------------------------------------------------------------  
  21.   
  22. Predicate Information (identified by operation id):  
  23. ---------------------------------------------------  
  24.   
  25.    3 - filter("T1"."OBJECT_NAME" LIKE '%T2%')  
  26.    4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
  27.   
  28. Note  
  29. -----  
  30.    - SQL profile "coe_gmvb9bp7f9kqd_2959412835" used for this statement  
  31.   
  32. 统计信息  
  33. ----------------------------------------------------------  
  34.           0  recursive calls  
  35.           0  db block gets  
  36.         363  consistent gets  

可以看到SQL Profile起作用了。

最后一步，生成SQL Profile时稍显复杂，不过我们可以修改之前提到的create_sql_profile.sql文件来达到同样的目的，只不过前几个步骤仍然是不可省略的。将里面的代码：

view plaincopy to clipboardprint?
  1. select extractvalue(value(d), '/hint') as outline_hints bulk collect  
  2.   into ar_profile_hints  
  3.   from xmltable('/*/outline_data/hint' passing  
  4.                 (select xmltype(other_xml) as xmlval  
  5.                    from v$sql_plan  
  6.                   where sql_id = '&&sql_id'  
  7.                     and child_number = &&child_no  
  8.                     and other_xml is not null)) d;  

改为

view plaincopy to clipboardprint?
  1. select extractvalue(value(d), '/hint') as outline_hints bulk collect  
  2.   into ar_profile_hints  
  3.   from xmltable('/*/outline_data/hint' passing  
  4.                 (select xmltype(other_xml) as xmlval  
  5.                    from v$sql_plan  
  6.                   where sql_id = '&&modi_sql_id'  
  7.                     and child_number = &&modi_child_no  
  8.                     and other_xml is not null)) d;  

注意这里modi_sql_id和modi_child_no为我们构造的SQL执行后的id及child_number。同时这2个变量在文件前面需要定义，此处不再细述。

小结：本文承接上一篇，介绍了如何利用SQL Profile来稳定执行计划；如何利用SQL Profile来改变SQL的执行计划。对于SQL Profiles来说，不属于任何一个用户，比Outlines更具有操控性灵活性。对于SQL Profiles的category，这里不做介绍，有兴趣的朋友请参考文档。

--The end---

男款汉服款式知乎 男款汉服款式知乎
原创粉丝点击
热门IT博客
手机淘宝怎么查看积分手机淘宝怎么查看积分
人工合成牛胰岛素 知乎人工合成牛胰岛素 知乎
加盟农村淘宝怎么申请
淘宝项目代理
手游网络枪战游戏大全
java字符串单词反转
京东模拟登陆php代码
unity3d 足球游戏源码
做攻的感觉知乎
房屋设计图软件下载
推荐算法的选择
windows远程桌面快捷键
动力it教育怎么样
淘宝店刷销量靠谱吗
怎么添加域名为白名单
mac系统可以玩dota2吗
js框架排名
tracking.js
比较好的电台app 知乎
windows office 密钥
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 转基因大豆非转基因大豆 大豆纤维被 美国大豆 大豆亩产 大豆营养 大豆是什么 大豆的做法 大豆种子 收购大豆 大豆 黄豆 大豆种 大豆油报价 黄大豆 大豆被 转基因大豆油能吃吗 转基因大豆油 千万不要吃大豆异黄酮 大豆卵磷脂功效 大豆肚子补充句子 今日大豆价格 大豆异黄酮的危害 大豆卵磷脂不适宜人群 大豆卵磷脂适宜人群 金龙鱼大豆油 非转基因大豆 转基因大豆油危害 大豆异黄酮哪个牌子好 大豆分离蛋白 大豆价格行情 大豆油和花生油哪个好 转基因大豆危害 大豆是黄豆吗 大豆品种介绍 大豆被子的优缺点 大豆异黄酮牌子 2018年大豆价格暴涨 今日大豆收购价格 2019年国产大豆主产区价格预测 大豆的功效与作用 大豆营养成分 大豆的营养价值及功效

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里