`
jayghost
  • 浏览: 429364 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

【转】SQL Profiles

阅读更多

转自：http://www.laoxiong.net/sql-profiles-part.html

 

Oracle 11g从发布到现在，也有几个年头了。而在国内来说，Oracle 10g仍然是主流，甚至一些电信运营商的核心系统仍然在使用9i。作为Oracle 10g的一项新特性，SQL Profiles被使用得并不太多。不管是在论坛、个人的BLOG还是其他一些地方，SQL Profiles的介绍也相对较少。对我个人来说，已经在多个优化场合中使用SQL Profiles，在这里向大家介绍SQL Profiles，就是希望能够了解Oracle数据库的这一功能。

SQL Profiles可以说是Outlines的进化。Outlines能够实现的功能SQL Profiles也完全能够实现，而SQL Profiles具有Outlines不具备的优化，个人认为最重要的有2点：

  • SQL Profiles更容易生成、更改和控制。
  • SQL Profiles在对SQL语句的支持上做得更好，也就是适用范围更广。

关于这2方面的优点，我后面会详细地阐述。

现在我在使用Outlines的场合，均使用SQL Profiles来替代。有一次准备对1条SQL语句使用Outline进行执行计划的稳定，结果使用Outline之后，系统出现大量的library cache latch的争用，不得不关闭Outline的使用，但是改用SQL Profiles不再有这个问题。这或许是个BUG，不过既然能用SQL Profiles代替，也就没再深入去研究这个问题。

使用SQL Profiles无非是两个目的：

  • 锁定或者说是稳定执行计划。
  • 在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。

那么SQL Profile到底是什么？在我看来，SQL Profile就是为某一SQL语句提供除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为SQL语句选择更适合的执行计划。这些说法显得比较枯燥，还是来看看下面的测试。

首先建2个测试表：

view plaincopy to clipboardprint?
  1. SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;  
  2.   
  3. 表已创建。  
  4.   
  5. SQL> create table t2 as select * from dba_objects;  
  6.   
  7. 表已创建。  
  8.   
  9. SQL> create index t2_idx on t2(object_id);  
  10.   
  11. 索引已创建。  
  12.   
  13. SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');  
  14.   
  15. PL/SQL 过程已成功完成。  
  16.   
  17. SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');  
  18.   
  19. PL/SQL 过程已成功完成。  

然后看看下面这一条SQL：

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

这里省略了SELECT出来的具体数据，但是我们关心的是返回的结果行数、执行计划以及逻辑读这些信息。
首先从执行计划可以看到，这条SQL语句在2个表上都是全表扫描。在第1个表T1上，有 like ‘%T1%’这样的条件，导致只能全表扫描，这没有问题。但是第2个表，也是全表扫描，这里有没有问题呢？或者说是有没有优化的余地，答案显然是肯定的。
这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1 like ‘%T1%’返回的结果行数为2498行，即T1表总行数的5%，如果2个表采用index range scan+nested loop连接，oracle评估的成本会高于full table scan+hash join。下面可以看到Oracle优化器评估的index range_scan+nested loop的成本：

view plaincopy to clipboardprint?
  1. SQL> explain plan for select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner   
  2.      from t1,t2   
  3.      where t1.object_name like '%T1%'   
  4.      and t1.object_id=t2.object_id;  
  5.   
  6. 已解释。  
  7.   
  8. SQL> @showplan  
  9.   
  10. PLAN_TABLE_OUTPUT  
  11. --------------------------------------------------------------------------------------  
  12. Plan hash value: 3787413387  
  13. --------------------------------------------------------------------------------------  
  14. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  15. --------------------------------------------------------------------------------------  
  16. |   0 | SELECT STATEMENT            |        |  2498 | 99920 |  5061   (1)| 00:01:01 |  
  17. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
  18. |   2 |   NESTED LOOPS              |        |  2498 | 99920 |  5061   (1)| 00:01:01 |  
  19. |*  3 |    TABLE ACCESS FULL        | T1     |  2498 | 72442 |    59   (6)| 00:00:01 |  
  20. |*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |  
  21. --------------------------------------------------------------------------------------  
  22. Predicate Information (identified by operation id):  
  23. ---------------------------------------------------  
  24.    3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
  25.    4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  

从执行计划可以看到Oracle优化器评估的成本为5061，远远高于原来的219。
但是实际的逻辑读是多少呢？

view plaincopy to clipboardprint?
  1. 统计信息  
  2. ----------------------------------------------------------  
  3.           0  recursive calls  
  4.           0  db block gets  
  5.         290  consistent gets  
  6.           0  physical reads  
  7.           0  redo size  
  8.        1352  bytes sent via SQL*Net to client  
  9.         385  bytes received via SQL*Net from client  
  10.           2  SQL*Net roundtrips to/from client  
  11.           0  sorts (memory)  
  12.           0  sorts (disk)  
  13.          29  rows processed  

加了HINT之后实际的逻辑读只有290，低于原始SQL的932。所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。

下面我们用Oracle的SQL Tuning Advisor来尝试这条SQL：

view plaincopy to clipboardprint?
  1. SQL> var tuning_task varchar2(100);  
  2. SQL> DECLARE  
  3.   2    l_sql_id v$session.prev_sql_id%TYPE;  
  4.   3    l_tuning_task VARCHAR2(30);  
  5.   4  BEGIN  
  6.   5    l_sql_id:='4zbqykx89yc8v';  
  7.   6    l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
  8.   7    :tuning_task:=l_tuning_task;  
  9.   8    dbms_sqltune.execute_tuning_task(l_tuning_task);  
  10.   9    dbms_output.put_line(l_tuning_task);  
  11.  10  END;  
  12.  11  /  
  13. 任务_74  
  14.   
  15. PL/SQL 过程已成功完成。  
  16.   
  17. SQL> print tuning_task;  
  18.   
  19. TUNING_TASK  
  20. ---------------------------------------------------------------------------------------------------------  
  21. 任务_74  
  22.   
  23. SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;  
  24.   
  25. DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)  
  26. --------------------------------------------------------------------------------  
  27. GENERAL INFORMATION SECTION  
  28. -------------------------------------------------------------------------------  
  29. Tuning Task Name                  : 任务_74  
  30. Tuning Task Owner                 : TEST1  
  31. Scope                             : COMPREHENSIVE  
  32. Time Limit(seconds)               : 1800  
  33. Completion Status                 : COMPLETED  
  34. Started at                        : 12/15/2010 09:56:02  
  35. Completed at                      : 12/15/2010 09:56:03  
  36. Number of SQL Profile Findings    : 1  
  37.   
  38. -------------------------------------------------------------------------------  
  39. Schema Name: TEST1  
  40. SQL ID     : 4zbqykx89yc8v  
  41. SQL Text   : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'  
  42.              and t1.object_id=t2.object_id  
  43.   
  44. -------------------------------------------------------------------------------  
  45. FINDINGS SECTION (1 finding)  
  46. -------------------------------------------------------------------------------  
  47.   
  48. 1- SQL Profile Finding (see explain plans section below)  
  49. --------------------------------------------------------  
  50.  为此语句找到了性能  
  51.   
  52.   Recommendation (estimated benefit: 46.62%)  
  53.   ------------------------------------------  
  54.   -考虑接受推荐的 SQL  
  55.     executedbms_sqltune.accept_sql_profile(task_name => '任务_74', replace =  
  56.             TRUE);  
  57.   
  58. -------------------------------------------------------------------------------  
  59. EXPLAIN PLANS SECTION  
  60. -------------------------------------------------------------------------------  
  61.   
  62. 1- Original With Adjusted Cost  
  63. ------------------------------  
  64. Plan hash value: 1838229974  
  65.   
  66. ---------------------------------------------------------------------------  
  67. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  68. ---------------------------------------------------------------------------  
  69. |   0 | SELECT STATEMENT   |      |    29 |  1160 |   219   (4)| 00:00:03 |  
  70. |*  1 |  HASH JOIN         |      |    29 |  1160 |   219   (4)| 00:00:03 |  
  71. |*  2 |   TABLE ACCESS FULL| T1   |    29 |   841 |    59   (6)| 00:00:01 |  
  72. |   3 |   TABLE ACCESS FULL| T2   | 49954 |   536K|   159   (2)| 00:00:02 |  
  73. ---------------------------------------------------------------------------  
  74.   
  75. Predicate Information (identified by operation id):  
  76. ---------------------------------------------------  
  77.   
  78.    1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
  79.    2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
  80.   
  81. 2- Using SQL Profile  
  82. --------------------  
  83. Plan hash value: 3787413387  
  84.   
  85. --------------------------------------------------------------------------------------  
  86. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  87. --------------------------------------------------------------------------------------  
  88. |   0 | SELECT STATEMENT            |        |    29 |  1160 |   117   (3)| 00:00:02 |  
  89. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
  90. |   2 |   NESTED LOOPS              |        |    29 |  1160 |   117   (3)| 00:00:02  
  91.  |  
  92. |*  3 |    TABLE ACCESS FULL        | T1     |    29 |   841 |    59   (6)| 00:00:01 |  
  93. |*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |  
  94. --------------------------------------------------------------------------------------  
  95.   
  96. Predicate Information (identified by operation id):  
  97. ---------------------------------------------------  
  98.   
  99.    3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
  100.    4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
  101.   
  102. -------------------------------------------------------------------------------  


上面代码中的sql_id是从v$sql来，对应的是没有加hint的SQL。
结果看起来非常棒，SQL Tuning Advisor为我们找到了理想的执行计划，T1表上经过谓词过滤后返回的行数评估为29，相当地精确。我们要做的就是Accept SQL Profile，接受这个SQL Profile。

view plaincopy to clipboardprint?
  1. SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);  
  2.   
  3. PL/SQL 过程已成功完成。  

那么我们再执行其他的类似SQL看看：

view plaincopy to clipboardprint?
  1. SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;  
  2.   
  3. 已选择77行。  
  4.   
  5. 执行计划  
  6. ----------------------------------------------------------  
  7. Plan hash value: 3787413387  
  8. --------------------------------------------------------------------------------------  
  9. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  10. --------------------------------------------------------------------------------------  
  11. |   0 | SELECT STATEMENT            |        |    29 |  1160 |   117   (3)| 00:00:02 |  
  12. |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |  
  13. |   2 |   NESTED LOOPS              |        |    29 |  1160 |   117   (3)| 00:00:02 |  
  14. |*  3 |    TABLE ACCESS FULL        | T1     |    29 |   841 |    59   (6)| 00:00:01 |  
  15. |*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |  
  16. --------------------------------------------------------------------------------------  
  17. Predicate Information (identified by operation id):  
  18. ---------------------------------------------------  
  19.    3 - filter("T1"."OBJECT_NAME" LIKE '%T2%')  
  20.    4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
  21. Note  
  22. -----  
  23.    - SQL profile "SYS_SQLPROF_014b39f084c88000" used for this statement  
  24.   
  25. 统计信息  
  26. ----------------------------------------------------------  
  27.           1  recursive calls  
  28.           0  db block gets  
  29.         343  consistent gets  
  30.           0  physical reads  
  31.           0  redo size  
  32.        2840  bytes sent via SQL*Net to client  
  33.         385  bytes received via SQL*Net from client  
  34.           2  SQL*Net roundtrips to/from client  
  35.           0  sorts (memory)  
  36.           0  sorts (disk)  
  37.          77  rows processed  

这一次，尽管我们更改了LIKE 后面的值，但是执行计划与SQL Tuning Advisor产生的执行计划完全一样。从执行计划的”Note“一节也可以看到，SQL Profile起作用了。SQL Profile的名字为”SYS_SQLPROF_014b39f084c88000″。

view plaincopy to clipboardprint?
  1. SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles;  
  2.   
  3. NAME                           CATEGORY                                   SIGNATURE TYPE      STATUS  FOR  
  4. ------------------------------ ------------------------------ --------------------- --------- ---------- ---  
  5. SYS_SQLPROF_014b39f084c88000   DEFAULT                          3960696072677096522 MANUAL    ENABLED    YES  

一些复杂的SQL，我经常会先通过SQL Tuning Advisor来分析一下，看能不能让Oracle自已找出一个更好的执行计划。

我们来看看，SQL Profiles实际上是些什么：

view plaincopy to clipboardprint?
  1. SQL< select * from sys.sqlprof$attr;  
  2.   
  3.             SIGNATURE CATEGORY                            ATTR# ATTR_VAL  
  4. --------------------- ------------------------------ ---------- ----------------------------------------  
  5.   3960696072677096522 DEFAULT                                 1 OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$ 
  6.                                                                 1", SCALE_ROWS=0.01161091426)  

从sys.sqlprof$attr这个数字字典里面，我们可以看到两样东西：signature和attr。
signature是什么？可以理解为与sql_id、sql_hash_value类似的值，用来标识SQL。在10g以上的版本中，查看v$sql的定义就可以发现2列：exact_matching_signature、force_matching_signature。通过下面的数据可以看出区别：

view plaincopy to clipboardprint?
  1. SQL> select rownum,a.* from  
  2.   (select exact_matching_signature,force_matching_signature,plan_hash_value,sql_text  
  3.   from v$sql where sql_text like '%/*%xjs%' and sql_text not like '%v$sql%' order by 1) a;  
  4.   
  5.     ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT  
  6. ---------- ------------------------ ------------------------ --------------- --------------------------------------------------  
  7.          1      3939730931515200254     17443893418101517951      3617692013 select /* xjs */ object_name    from T1 where obje  
  8.                                                                              ct_name='t1'  
  9.   
  10.          2     10964210455693560558     11097449316038436385      3836375644 select /* xjs */ object_name    from T1 where rown  
  11.                                                                              um<=3  
  12.   
  13.          3     10964210455693560558     11097449316038436385      3836375644 select /* xjs   */ object_name    from T1 where ro  
  14.                                                                              wnum<=3  
  15.   
  16.          4     11217690300719901571       354482119692997204      3836375644 select /* xjs */ 2 from t1 where rownum<=1  
  17.          5     11974975582747367847       354482119692997204      3836375644 select /* xjs */ 1 from t1 where rownum<=1  
  18.          6     12941882703651921406     17443893418101517951      3617692013 select /* xjs */ object_name    from T1 where obje  
  19.                                                                              ct_name='T1'  
  20.   
  21.          7     17986178357953662359     11097449316038436385      3836375644 select /* xjs */ object_name    from T1 where rown  
  22.                                                                              um<=1  
  23.   
  24.          8     17986178357953662359     11097449316038436385      3836375644 select /* xjs */ OBJECT_NAME from T1 where rownum<  
  25.                                                                              =1  
  26.   
  27.          9     17986178357953662359     11097449316038436385      3836375644 SELECT /* xjs */ object_name    from T1 where rown  
  28.                                                                              um<=1  
  29.   
  30.         10     17986178357953662359     11097449316038436385      3836375644 select /* xjs */ object_name from t1 where rownum<  
  31.                                                                              =1  

从上面的数据可以看出：

  • 第2、3条SQL的exact_matching_signature相同，第7、8、9、10条SQL的exact_matching_signature相同。
  • 第2、3条SQL的force_matching_signature相同，第4、5条SQL的force_matching_signature相同，第7、8、9、10条的SQL的force_matching_signature相同。第1、6条SQL的force_matching_signature相同

有如下的结论：对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果SQL相同，那么SQL语句的exact_matching_signature就是相同的。对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。但是例外的情况是：如果SQL中有绑定变量，force_matching_signature就会与exact_matching_signature一样的生成标准。

view plaincopy to clipboardprint?
  1. SQL> select rownum,a.* from  
  2. (select exact_matching_signature,force_matching_signature,plan_hash_value,sql_text  
  3. from v$sql where sql_text like '%/*%xjs2%' and sql_text not like '%v$sql%' order by 1) a;  
  4.   
  5.     ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT  
  6. ---------- ------------------------ ------------------------ --------------- --------------------------------------------------  
  7.          1      5363536431331905229      5363536431331905229      3836375644 select /* xjs2 */ object_name    from T1 where obj  
  8.                                                                              ect_name='T1' and rownum<=:rn  
  9.   
  10.          2      5363536431331905229      5363536431331905229      3836375644 select /* xjs2 */ object_name    from t1 where obj  
  11.                                                                              ect_name='T1' and rownum<=:rn  
  12.   
  13.          3     12992689086515482106     12992689086515482106      3836375644 select /* xjs2 */ object_name    from t1 where obj  
  14.                                                                              ect_name='T2' and rownum<=:rn  

可以看到，现在exact_matching_signature与force_matching_signature完全一样了。
从force_matching_signature的特性，我们可以想到一个用途，用于查找没有使用绑定变量的SQL语句，类似于使用plan_hash_value来查找。

回到前面，accept_sql_profile这个过程，force_match参数设为TRUE，那么dba_sql_profiles中的signature则是由SQL的force_matching_signature而来，否则便是exact_matching_signature。对于Outlines来说，则只能是exact_matching_signature。从这个角度上讲，Sql Profiles比Outlines的使用范围更广，因为Sql profiles对没有使用绑定变量的SQL也支持得很好。值得注意的是，Sql profiles的force_match属性是不能更改的，只能在创建时指定，如果要更改，则只能重新创建改Sql Profile。

下面来看看sys.sqlprof$attr数据字典。这里面没有SQL Profile的名字，而是用的sql的signature。大家从attr_val的结果发现了什么？

view plaincopy to clipboardprint?
  1. OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.01161091426)  

可以看到，SQL Profiles的attr_val实际上就是一些Hints，这跟Outlines没有本质上的区别。只是SQL Profiles中的Hint，没有指定SQL使用哪个索引，也没有指定表的连接方法和连接顺序。这里只指定了T1表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数。2498*0.01161091426正好为29。这里就是告诉Oracle优化器，T1表经过谓语过滤后返回行数应该为评估的0.01161091426倍。从这里可以看出，SQL Profiles并不会锁定SQL的执行计划，只是提供了更多、更准确的统计信息给优化器。看下面的测试：

view plaincopy to clipboardprint?
  1. SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>5000000);  
  2.   
  3. PL/SQL 过程已成功完成。  
  4. SQL> explain plan for select  t1.*,t2.owner  
  5.   2       from t1,t2  
  6.   3       where t1.object_name like '%T1%'  
  7.   4       and t1.object_id=t2.object_id;  
  8.   
  9. 已解释。  
  10.   
  11. SQL> @showplan  
  12.   
  13. PLAN_TABLE_OUTPUT  
  14. ----------------------------------------------------------------------------------  
  15. Plan hash value: 1838229974  
  16. ---------------------------------------------------------------------------  
  17. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  18. ---------------------------------------------------------------------------  
  19. |   0 | SELECT STATEMENT   |      |  2903 |   113K|   448  (53)| 00:00:06 |  
  20. |*  1 |  HASH JOIN         |      |  2903 |   113K|   448  (53)| 00:00:06 |  
  21. |*  2 |   TABLE ACCESS FULL| T1   |  2903 | 84187 |   288  (81)| 00:00:04 |  
  22. |   3 |   TABLE ACCESS FULL| T2   | 49954 |   536K|   159   (2)| 00:00:02 |  
  23. ---------------------------------------------------------------------------  
  24. Predicate Information (identified by operation id):  
  25. ---------------------------------------------------  
  26.    1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")  
  27.    2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')  
  28. Note  
  29. -----  
  30.    - SQL profile "SYS_SQLPROF_014b39f084c88000" used for this statement  

将T1表的统计信息中的表行数改为500万，Oracle就会评估为返回5000000*5%*0.01161091426=2903行。这里执行计划又变回为full scan+hash join。可以看到，虽然SQL Profile起作用了，但是并没有锁定执行计划。

小结：本文简单介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。

下一篇将会介绍如何手工来为创建、生成SQL Profile，以及如何让SQL Profile也能像Outlines一样锁定SQL的执行计划，以保持SQL执行计划的稳定性。

在上一篇《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，这里不做介绍，有兴趣的朋友请参考文档。

 

分享到：
| 使用SQL PROFILE固定SHARED POOL中的执行 ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Mysql利用profiles来查看SQL语句执行计划.doc

    Mysql利用profiles来查看SQL语句执行计划.doc

    利用profiles来查看sql 语句执行计划

    用于MYSQL数据库关键SQL语句的资源消耗的量化以及针对性地分析和优化

    Mysql利用profiles来查看SQL语句执行计划.rar

    要使用该功能，mysql的版本必须在5.0.37版本以上。否则只能使用explain 的方式来检查。 profiling 功能可以了解到cpu io 等更详细的信息。 show profile 的格式如下： SHOW PROFILE [type [, type] ......

    Mysql-使用show profiles分析你的SQL

    分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询” 慢查询日志：MySQL提供的一种日志记录，它用来记录在...

    Pro Oracle SQL

    You’ll learn when to create indexes, how to verify that they make a difference, how to use SQL Profiles to optimize SQL in packaged applications, and much more. You’ll also understand how SQL is ...

    MySQL调优利器【show profiles】

    1、查看当前版本sql是否支持show profiles mysql&gt; show variables like 'profiling%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF

    vim-sql-workbench:SQL For Vim（提供从VIM到任何DBMS的访问，例如dbext）

    教程 以下文档很长。 如果您不想通读它，这里是快速入门： 从安装SQL Workbench/J 为数据库安装jdbc驱动程序，请参见 ...如果你有CtrlP安装，你可以做CtrlP ，然后选择SQL Workbench profiles ，并选择您的个人资料

    World Factbook Country Profiles in SQL(包括Factbook .db - Single-File SQLite发行版)- Free Open Public Domain Data - Factbook / Factbook . SQL

    World Factbook Country Profiles in SQL(包括Factbook .db - Single-File SQLite发行版)- Free Open Public Domain Data - Factbook / Factbook . SQL-源码

    在线求职系统C#源码 ASP.NET2005+SQLServer2005

    Profiles 个性化 Themes 主题 Master pages 母版页 New data source controls - ObjectDataSource 新的数据源控件-objectDataSource New data bound controls – GridView and DetailsView 新的数据绑定...

    MySQL利用profile分析慢sql详解（group left join效率高于子查询）

    使用profile来分析慢sql mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项...mysql&gt; show profiles; -- 查看是否开启 Empty set, 1 warning (0.00 sec) mysql&gt; set profiling=1; -- 开启profile Query

    MySQL优化篇：show profile进行SQL分析

    看看当前的MySQL版本是否支持（2）开启功能，默认是关闭，使用前需要开启（3）运行SQL（4）查看结果，show profiles（5）诊断SQL，show profile cpu, block io for query 上一步前面的问题SQL数字号码（6）日常开发...

    查看mysql语句运行时间的2种方法

    网站运行很慢的时候，我就特别起知道为什么这么慢，所以我查啊查,数据库绝对是很重要的一部分，里面运行的sql是绝对不能放过的。平时做项目的时候，我也会注意sql语句的书写，写出一些高效的sql来，所以我会经常测试...

    Oracle 用户密码有效期的sql语句

    废话不多说了，直接给大家写sql语句了，当然写法还有很多种，我也只是给大家分享其中的一种解决办法，有不同见解的可以留言哦。 oracle的密码是存在有效期的，有时候会遇到密码到期需要重设的情况，查看当前密码有效...

    piton:针对SQL实现的Clojure数据库迁移库

    Clojure库，用于执行SQL迁移和播种。 安装 Piton有两个部分：Leiningen插件和项目库。 该插件旨在作为您的个人lein配置文件的一部分进行安装。 该库必须包含在您的项目中，才能在本地和生产环境中进行迁移和播种。 ...

    Mysql查询很慢卡在sending data的原因及解决思路讲解

    因为编写了一个Python程序，密集的操作了一个Mysql库，之前数据量不大时，没发现很慢，后来越来越慢，以为...在网上查了一些，有些是因为sql语句的问题，但是我这个完全没有用到varchar，也没用到in方法。 于是想是不是

    基于Java web的求职招聘系统（源码+数据库+部署说明）.zip

    2、在SQL server 中创建数据库，执行database目录下的HRIMS_DB.sql数据库脚本文件创建数据库SQL语句创建数据库和表及数据； 3、设置数据库连接配置，修改src/connect_DB目录下的ConnectDB.java文件，将其中的数据库...

    Mysql 进阶修行学习

    索引：结构、Btree、索引分类、索引语法、性能分析、执行频次、慢查询日志、show profiles、explain、使用规则（验证索引效率、最左前缀法则、索引失效情况、SQL提示、覆盖索引&回表查询、前缀索引、单列&联合索引、...

    Oracle Security

    Chapter 6 Profiles, Passwords, and Synonyms Profiles Passwords Synonyms Implementing Security Chapter 7 Developing a Database Security Plan About the Security Policy and Security Plan Types of...

    最完整的Toad For Oracle使用手册

    Profiles 959 Queue Tables 960 Queues 964 Recycle Bin 966 Refresh Groups 968 Resource Consumer Groups 970 Resource Plans 972 Roles 974 Rollback Segments 975 Sequences 977 Synonyms 978 System Privileges...

    mysql.docx

    MySQL性能分析，mysql max_allowed_packet，mysql show profiles 使用分析sql 性能，MySQL wait_timeout

Magicbox
Global site tag (gtag.js) - Google Analytics