`
jayghost
  • 浏览: 440393 次
  • 性别: 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 语句的执行过程，从而优化数据库性能。这一特性自 MySQL 5.0.37 版本引入，为用户提供了比 `EXPLAIN` 更深入的洞察，包括 ...

    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 ...

    使用SQL PROFILE固定SHARED POOL中的执行计划

    - `SELECT * FROM DBA_SQL_PROFILES;` 来列出所有SQL Profile。 - `EXPLAIN PLAN FOR SELECT...;` 和 `SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);` 可以查看SQL的执行计划。 - `ALTER SYSTEM FLUSH SHARED_POOL;` ...

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

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

    HIVE-SQL操作语句

    ### HIVE-SQL操作语句详解 #### 一、创建表 (CREATE TABLE) 在Hive中，`CREATE TABLE` 语句用于创建新的表。与传统的关系型数据库类似，但在Hive中有其特殊之处。 ##### 示例 ```sql hive&gt; CREATE TABLE pokes ...

    Python库 | dbt_sqlserver-1.0.0rc1-py3-none-any.whl

    **Python库dbt_sqlserver-1.0.0rc1-py3-none-any.whl详解** dbt（数据构建和转型）是一个强大的开源工具，专为数据分析师和工程师设计，用于将数据转换过程转变为可重复、可靠且文档化的流程。在Python环境中，dbt...

    Mysql-使用show profiles分析你的SQL

    “Copying to tmp table on disk”是内存中临时表无法容纳数据，转而存储到磁盘上，这会严重影响性能；而“locked”状态则可能表示出现了死锁。遇到这些情况时，通常需要重构查询、优化索引或者调整数据结构以提高...

    Eclipse连接SQL+Server+2005数据库

    2. 在"Connection Profiles"区域，点击右下角的"+"图标，选择"SQL Server"。 3. 输入"Profile Name"，如"MySQL Server 2005 Connection"，然后点击"Next"。 4. 填写连接属性，包括： - "Database Hostname/IP ...

    MySQL调优利器【show profiles】

    MySQL中的`show profiles`是一个非常实用的工具，用于分析SQL语句执行时的资源消耗，这对于数据库性能调优至关重要。在默认情况下，`show profiles`功能是关闭的，但可以通过设置变量来启用它，以便获取关于SQL语句...

    Oracle 与 SQL Server在SQL使用差异.pdf

    在安全性方面，Microsoft SQL Server提供了基于角色的服务器安全、数据库和应用程序的配置文件（profiles）、集成的安全审计，能够追踪多种安全事件，支持SSL、Kerberos等加密技术，适合企业级的安全需求。...

    TabularSemanticParsing论文: 使用BERT模型将自然语言问题转换为SQL

    例如，一个简单的SQL查询可以是：“SELECT Name, COUNT(*) FROM User_Profiles GROUP BY Partition ID”，这对应于自然语言问题：“列出每个分区ID的用户数量和他们的名字。”BRIDGE模型可以理解这个问题并生成相应...

    SQL优化-系列文档

    11. **资源管理**：探讨如何通过SQL profiles、SQL plan baselines和Resource Manager来控制SQL执行的资源消耗。 12. **监控和诊断工具**：了解TKPROF、10046事件、ASH和GATHER_SYSTEM_STATISTICS等工具的使用，以...

    oracle 数据库的学习资料， sql语句的详细学习资料

    文档可能还会介绍Oracle的SQL优化器，如CBO（Cost-Based Optimizer）的工作原理，以及如何通过SQL Profiles和SQL Plan Baselines来稳定高效的执行计划。 其他如“9.pdf”、“3.pdf”、“4.pdf”等文件，很可能是对...

    MySQL性能分析show profiles详解（csdn）————程序.pdf

    `SHOW PROFILES`是MySQL提供的一种工具，用于分析SQL语句在执行过程中的资源消耗情况，帮助我们定位性能瓶颈。本篇将深入讲解`SHOW PROFILES`的使用方法和解析结果。 首先，`SHOW PROFILES`默认是关闭的，我们需要...

    Apress Oracle SQL Tuning with Oracle SQLTXPLAIN 2013

    第六章探讨了如何通过配置SQL配置文件（Profiles）来强制执行特定的执行计划。这对于那些容易受到统计信息或CBO变化影响的查询来说尤其有用，可以保证执行计划的一致性。 第七章介绍了适应性游标共享（Adaptive ...

    PB与SQL Server数据库的连接.pdf

    1. 在PB开发环境中使用专用接口：在PB的Database画板的Objects子窗口中，或者Database Profiles画板中，右击MSS Microsoft SQL Server项，在弹出菜单中选择New Profiles项，直接设置Profile名，选择数据库服务器及欲...

    组态王与SQL数据库通讯1定义ODBC数据源.pdf

    用户DSN（Data Source Name）是指在当前用户的 profiles 中定义的数据源信息。这些信息只对当前用户可见，且只能用于当前机器上。当用户想要在多个机器上使用同一个数据源时，需要在每台机器上单独定义数据源。 ...

    常用数据库、开发、sql总结

    - `dba_profiles`：展示所有概要文件的资源参数。 - `user_password_limits`：通过概要文件为用户设置的密码相关限制。 以上内容涵盖了数据库开发中常见的操作和技术要点，包括数据清洗、数据导入、表结构管理、...

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