TIPS: 快速建立OUTLINE

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2010-03-15 02:46:03

分享到 

OUTLINE是用于保证语句的执行计划稳定性的一种手段。有的时候，我们也需要借助OUTLINES将一个环境下的语句的执行计划在另外一个环境下（如生产环境下发现的TOP SQL，在测试环境中无法直接重现时）重现，以进行进一步优化。       实际上，HINT是构成一个OUTLINE的重要组成，包含了环境参数的设置、对象的选择、访问顺序及JOIN方式等等能影响该语句的执行计划的因素。   SQL代码 HELLODBA.COM>select * from demo.t_test1 where object_name like 'OUT%';    ...    7 rows selected.       HELLODBA.COM>select sql_id, hash_value, child_number from v$sql where sql_text like 'select * from demo.t_test1 %';       SQL_ID        HASH_VALUE CHILD_NUMBER    ------------- ---------- ------------    fraa4pqvh5au9 3070405449            0       HELLODBA.COM>exec DBMS_OUTLN.create_outline(3070405449, 0, 'OUTLN_TEST');       PL/SQL procedure successfully completed.       HELLODBA.COM>select name from DBA_OUTLINES where category='OUTLN_TEST';       NAME   ------------------------------    SYS_OUTLINE_10031509315351401       HELLODBA.COM>select to_char(hint) from dba_outline_hints where name = 'SYS_OUTLINE_10031509315351401';       TO_CHAR(HINT)    --------------------------------------------------------------------------------------------------------------------    INDEX(@"SEL$1" "T_TEST1"@"SEL$1" ("T_TEST1"."OBJECT_NAME"))    OUTLINE_LEAF(@"SEL$1")    OPT_PARAM('optimizer_index_cost_adj' 60)    OPTIMIZER_FEATURES_ENABLE('10.2.0.1')    IGNORE_OPTIM_EMBEDDED_HINTS        而10g以后，这些OUTLINE的内容在生成一个语句的查询计划时已经包含在其中（v$sql_plan的other_xml字段）。通过DBMS_XPLAN，我们可以看到这些由BEGIN_OUTLINE_DATA .. END_OUTLINE_DATA所包括的这些提示，   SQL代码 HELLODBA.COM>select * from table(dbms_xplan.display_cursor('fraa4pqvh5au9',0,'ADVANCED'));       PLAN_TABLE_OUTPUT    ------------------------------------------------------------------------------------------------       SQL_ID  fraa4pqvh5au9, child number 0    -------------------------------------    select * from demo.t_test1 where object_name like 'OUT%'      Plan hash value: 3207259996       -------------------------------------------------------------------------------------------    | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    -------------------------------------------------------------------------------------------    |   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |    |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |     2 |   198 |     2   (0)| 00:00:03 |    |*  2 |   INDEX RANGE SCAN          | T_TEST_IDX6 |     2 |       |     1   (0)| 00:00:02 |    -------------------------------------------------------------------------------------------       Query Block Name / Object Alias (identified by operation id):    -------------------------------------------------------------          1 - SEL$1 / T_TEST1@SEL$1       2 - SEL$1 / T_TEST1@SEL$1       Outline Data    -------------         /*+          BEGIN_OUTLINE_DATA          IGNORE_OPTIM_EMBEDDED_HINTS          OPTIMIZER_FEATURES_ENABLE('10.2.0.1')          OPT_PARAM('optimizer_index_cost_adj' 60)          OUTLINE_LEAF(@"SEL$1")          INDEX(@"SEL$1" "T_TEST1"@"SEL$1" ("T_TEST1"."OBJECT_NAME"))          END_OUTLINE_DATA      */    ...        通过比较dba_outline_hints中查询到的数据，可以看到这些HINT正是outline中的HINT。将这些HINT加入到语句当中，我们就可以快速的重现该语句的查询计划。   SQL代码 HELLODBA.COM>explain plan for select /*+      2        BEGIN_OUTLINE_DATA      3        IGNORE_OPTIM_EMBEDDED_HINTS      4        OPTIMIZER_FEATURES_ENABLE('10.2.0.1')      5        OPT_PARAM('optimizer_index_cost_adj' 60)      6        OUTLINE_LEAF(@"SEL$1")      7        INDEX(@"SEL$1" "T_TEST1"@"SEL$1" ("T_TEST1"."OBJECT_NAME"))      8        END_OUTLINE_DATA      9    */* from demo.t_test1 where object_name like 'OUT%';       Explained.       HELLODBA.COM>select * from table(dbms_xplan.display(null,null,'ADVANCED'));       PLAN_TABLE_OUTPUT    -----------------------------------------------------------------------------------------------------       Plan hash value: 3207259996       -------------------------------------------------------------------------------------------    | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    -------------------------------------------------------------------------------------------    |   0 | SELECT STATEMENT            |             |     2 |   198 |     2   (0)| 00:00:02 |    |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |     2 |   198 |     2   (0)| 00:00:02 |    |*  2 |   INDEX RANGE SCAN          | T_TEST_IDX6 |     2 |       |     1   (0)| 00:00:02 |    -------------------------------------------------------------------------------------------       Query Block Name / Object Alias (identified by operation id):    -------------------------------------------------------------          1 - SEL$1 / T_TEST1@SEL$1       2 - SEL$1 / T_TEST1@SEL$1       Outline Data    -------------         /*+          BEGIN_OUTLINE_DATA          INDEX(@"SEL$1" "T_TEST1"@"SEL$1" ("T_TEST1"."OBJECT_NAME"))          OUTLINE_LEAF(@"SEL$1")          OPT_PARAM('optimizer_index_cost_adj' 60)          OPTIMIZER_FEATURES_ENABLE('10.2.0.1')          IGNORE_OPTIM_EMBEDDED_HINTS          END_OUTLINE_DATA      */    ...        注意：当参数_plan_outline_data设为FLASE时（默认为TRUE），other_xml中不会存储outline数据。       --- Fuyuncat ---