TIPS: 快速建立OUTLINE

转自 http://www.hellodba.com/reader.php?ID=12&lang=CN

 OUTLINE是用于保证语句的执行计划稳定性的一种手段。有的时候，我们也需要借助OUTLINES将一个环境下的语句的执行计划在另外一个环境下（如生产环境下发现的TOP SQL，在测试环境中无法直接重现时）重现，以进行进一步优化。
 

    实际上，HINT是构成一个OUTLINE的重要组成，包含了环境参数的设置、对象的选择、访问顺序及JOIN方式等等能影响该语句的执行计划的因素。
 

SQL代码
  1. HELLODBA.COM>select * from demo.t_test1 where object_name like 'OUT%';   
  2. ...   
  3. 7 rows selected.   
  4.   
  5. HELLODBA.COM>select sql_id, hash_value, child_number from v$sql where sql_text like 'select * from demo.t_test1 %';   
  6.   
  7. SQL_ID        HASH_VALUE CHILD_NUMBER   
  8. ------------- ---------- ------------   
  9. fraa4pqvh5au9 3070405449            0   
  10.   
  11. HELLODBA.COM>exec DBMS_OUTLN.create_outline(3070405449, 0, 'OUTLN_TEST');   
  12.   
  13. PL/SQL procedure successfully completed.   
  14.   
  15. HELLODBA.COM>select name from DBA_OUTLINES where category='OUTLN_TEST';   
  16.   
  17. NAME  
  18. ------------------------------   
  19. SYS_OUTLINE_10031509315351401   
  20.   
  21. HELLODBA.COM>select to_char(hint) from dba_outline_hints where name = 'SYS_OUTLINE_10031509315351401';   
  22.   
  23. TO_CHAR(HINT)   
  24. --------------------------------------------------------------------------------------------------------------------   
  25. INDEX(@"SEL$1" "T_TEST1"@"SEL$1" ("T_TEST1"."OBJECT_NAME"))   
  26. OUTLINE_LEAF(@"SEL$1")   
  27. OPT_PARAM('optimizer_index_cost_adj' 60)   
  28. OPTIMIZER_FEATURES_ENABLE('10.2.0.1')   
  29. IGNORE_OPTIM_EMBEDDED_HINTS   

    而10g以后，这些OUTLINE的内容在生成一个语句的查询计划时已经包含在其中（v$sql_plan的other_xml字段）。通过DBMS_XPLAN，我们可以看到这些由BEGIN_OUTLINE_DATA .. END_OUTLINE_DATA所包括的这些提示，
 

SQL代码
  1. HELLODBA.COM>select * from table(dbms_xplan.display_cursor('fraa4pqvh5au9',0,'ADVANCED'));   
  2.   
  3. PLAN_TABLE_OUTPUT   
  4. ------------------------------------------------------------------------------------------------   
  5.   
  6. SQL_ID  fraa4pqvh5au9, child number 0   
  7. -------------------------------------   
  8. select * from demo.t_test1 where object_name like 'OUT%'  
  9.   
  10. Plan hash value: 3207259996   
  11.   
  12. -------------------------------------------------------------------------------------------   
  13. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |   
  14. -------------------------------------------------------------------------------------------   
  15. |   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |   
  16. |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |     2 |   198 |     2   (0)| 00:00:03 |   
  17. |*  2 |   INDEX RANGE SCAN          | T_TEST_IDX6 |     2 |       |     1   (0)| 00:00:02 |   
  18. -------------------------------------------------------------------------------------------   
  19.   
  20. Query Block Name / Object Alias (identified by operation id):   
  21. -------------------------------------------------------------   
  22.   
  23.    1 - SEL$1 / T_TEST1@SEL$1   
  24.    2 - SEL$1 / T_TEST1@SEL$1   
  25.   
  26. Outline Data   
  27. -------------   
  28.   
  29.   /*+   
  30.       BEGIN_OUTLINE_DATA   
  31.       IGNORE_OPTIM_EMBEDDED_HINTS   
  32.       OPTIMIZER_FEATURES_ENABLE('10.2.0.1')   
  33.       OPT_PARAM('optimizer_index_cost_adj' 60)   
  34.       OUTLINE_LEAF(@"SEL$1")   
  35.       INDEX(@"SEL$1" "T_TEST1"@"SEL$1" ("T_TEST1"."OBJECT_NAME"))   
  36.       END_OUTLINE_DATA   
  37.   */   
  38. ...   

    通过比较dba_outline_hints中查询到的数据，可以看到这些HINT正是outline中的HINT。将这些HINT加入到语句当中，我们就可以快速的重现该语句的查询计划。
 

SQL代码
  1. HELLODBA.COM>explain plan for select /*+   
  2.   2        BEGIN_OUTLINE_DATA   
  3.   3        IGNORE_OPTIM_EMBEDDED_HINTS   
  4.   4        OPTIMIZER_FEATURES_ENABLE('10.2.0.1')   
  5.   5        OPT_PARAM('optimizer_index_cost_adj' 60)   
  6.   6        OUTLINE_LEAF(@"SEL$1")   
  7.   7        INDEX(@"SEL$1" "T_TEST1"@"SEL$1" ("T_TEST1"."OBJECT_NAME"))   
  8.   8        END_OUTLINE_DATA   
  9.   9    */* from demo.t_test1 where object_name like 'OUT%';   
  10.   
  11. Explained.   
  12.   
  13. HELLODBA.COM>select * from table(dbms_xplan.display(null,null,'ADVANCED'));   
  14.   
  15. PLAN_TABLE_OUTPUT   
  16. -----------------------------------------------------------------------------------------------------   
  17.   
  18. Plan hash value: 3207259996   
  19.   
  20. -------------------------------------------------------------------------------------------   
  21. | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |   
  22. -------------------------------------------------------------------------------------------   
  23. |   0 | SELECT STATEMENT            |             |     2 |   198 |     2   (0)| 00:00:02 |   
  24. |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |     2 |   198 |     2   (0)| 00:00:02 |   
  25. |*  2 |   INDEX RANGE SCAN          | T_TEST_IDX6 |     2 |       |     1   (0)| 00:00:02 |   
  26. -------------------------------------------------------------------------------------------   
  27.   
  28. Query Block Name / Object Alias (identified by operation id):   
  29. -------------------------------------------------------------   
  30.   
  31.    1 - SEL$1 / T_TEST1@SEL$1   
  32.    2 - SEL$1 / T_TEST1@SEL$1   
  33.   
  34. Outline Data   
  35. -------------   
  36.   
  37.   /*+   
  38.       BEGIN_OUTLINE_DATA   
  39.       INDEX(@"SEL$1" "T_TEST1"@"SEL$1" ("T_TEST1"."OBJECT_NAME"))   
  40.       OUTLINE_LEAF(@"SEL$1")   
  41.       OPT_PARAM('optimizer_index_cost_adj' 60)   
  42.       OPTIMIZER_FEATURES_ENABLE('10.2.0.1')   
  43.       IGNORE_OPTIM_EMBEDDED_HINTS   
  44.       END_OUTLINE_DATA   
  45.   */   
  46. ...   

    注意：当参数_plan_outline_data设为FLASE时（默认为TRUE），other_xml中不会存储outline数据。

posted @ 2014-02-03 02:55  princessd8251  阅读(238)  评论(0)  编辑  收藏  举报