SQL plan directives

郑全

SQL plan directives含有优化器产生优化的执行计划时需要的附加信息和指令。 在sql执行时，如果cardinality估计有错误，数据库就会创建sql plan directives。编译sql时，优化器会检测查询对应的directive，确认sql plan directives中是否包含额外的统计信息。

如果sql plan directive中没有相关的统计信息，优化器会使用动态统计信息。比如，没有创建列组统计信息(column group statistics)时,优化器收集使用动态统计信息。目前优化器只能监控列组的动态统计信息，不能对表达式。

SQL plan directive不是和某个指定的sql语句或者sql_id相关联。优化器可以对类似的sql使用相同的sql plan directive。因为SQL plan directive不是以sql语句为单位，而是以表达式为单位，这也就意味着优化器可以对多个不同的sql应用相同的SQL plan directive。

数据库自动管理sql plan directive。数据库一开始是在share pool中创建sql plan directive。并阶段性的把sql plan directive写到sysaux表空间中。默认情况下Oracle每15分钟会自动将内存中的SQL plan directive写入SYSAUX表空间，也可以通过DBMS_SPD包进行手动管理。  

数据库使用SQL plan directive示例

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197

$ sqlplus sh/sh@pdb2 SQL> drop table tab1 purge; SQL> create table tab1(   2  id number,   3  gender varchar2(1),   4  has_y_chromosome varchar2(1),   5  constraint tab1_pk primary key(id),   6  constraint tab1_gender_chk check (gender in ('M','F')),   7  constraint tab1_has_y_chromosome_chk check (has_y_chromosome in ('Y','N'))   8  ); Table created. SQL> insert /*+ append */ into tab1   2  select level,'M','Y'   3  from dual   4  connect by level <= 10; 10 rows created. SQL> commit; SQL> insert /*+ append */ into tab1   2  select 10+level,'F','N'   3  from dual   4  connect by level<=90; 90 rows created. SQL> commit; SQL> create index tab1_gender_idx on tab1(gender); SQL> create index tab1_has_y_chromosome_idx on tab1(has_y_chromosome); SQL> exec dbms_stats.gather_table_stats(USER,'TAB1'); #此时没有任何直方图信息 SQL> select column_id,column_name,histogram   2  from user_tab_columns   3  where table_name='TAB1'   4  order by column_id; COLUMN_ID COLUMN_NAME          HISTOGRAM ---------- -------------------- ---------------          1 ID                   NONE          2 GENDER               NONE          3 HAS_Y_CHROMOSOME     NONE SQL> 实际数据中，所有males都有Y标志，但是所有females都没有。不过优化器并不知道这点。 优化器会评估谓词的selectivity，假设数据是均衡分布的，两个列相互独立，认为25行数据既含male列又含有Y标记。 SQL> select /*+ gather_plan_statistics */ *           2  from tab1   3  where gender='M'   4  and has_y_chromosome='Y';         ID G H ---------- - -          1 M Y          2 M Y          3 M Y          4 M Y          5 M Y          6 M Y          7 M Y          8 M Y          9 M Y         10 M Y 10 rows selected. SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------- SQL_ID  dnpgrp1fvkp7t, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ *  from tab1   where gender='M' and has_y_chromosome='Y' Plan hash value: 1552452781 ----------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ----------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |                 |      1 |        |     10 |00:00:00.01 |       4 | |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1            |      1 |     25 |     10 |00:00:00.01 |       4 | |*  2 |   INDEX RANGE SCAN                  | TAB1_GENDER_IDX |      1 |     50 |     10 |00:00:00.01 |       2 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("HAS_Y_CHROMOSOME"='Y')    2 - access("GENDER"='M') 21 rows selected. SQL> 如上结果所示，发生的cardinality 的估算错误。此时如果有直方图信息、或者扩展统计信息，优化器会评估出更准确的cardinality。 查看v$sql，确认该sql计划是否还可以优化。IS_REOPTIMIZABLE=Y表示优化器已经意识到cardinality估算不准，也表示SQL plan directives已经被创建： SQL> select sql_text,is_reoptimizable   2  from v$sql   3  where sql_id='dnpgrp1fvkp7t'; SQL_TEXT                                 IS_REOPTIMIZABLE ---------------------------------------- ---------------- select /*+ gather_plan_statistics */ *   Y from tab1   where gender='M'   and has_y _chromosome='Y' SQL> 查看sql plan directives 直线以下sql查看，如果查不到结果，说明sql plan directive还没有刷新到磁盘： SQL> select to_char(d.directive_id) dir_id,o.owner,o.object_name,o.subobject_name col_name,o.object_type,d.type,d.state,d.reason   2  from dba_sql_plan_directives d,dba_sql_plan_dir_objects o   3  where d.directive_id=o.directive_id   4  and o.owner='SH'   5  order by 1,2,3,4,5; no rows selected SQL> 手动刷新 SQL> exec dbms_spd.flush_sql_plan_directive; SQL> select to_char(d.directive_id) dir_id,o.owner,o.object_name,o.subobject_name col_name,o.object_type,d.type,d.state,d.reason   2    from dba_sql_plan_directives d,dba_sql_plan_dir_objects o   3    where d.directive_id=o.directive_id   4    and o.owner='SH'   5   order by 1,2,3,4,5; DIR_ID               OWNER      OBJECT_NAM COL_NAME   OBJECT TYPE             STATE      REASON -------------------- ---------- ---------- ---------- ------ ---------------- ---------- ------------------------------------ 17805875575772415323 SH         TAB1       GENDER     COLUMN DYNAMIC_SAMPLING USABLE     SINGLE TABLE CARDINALITY MISESTIMATE 17805875575772415323 SH         TAB1                  TABLE  DYNAMIC_SAMPLING USABLE     SINGLE TABLE CARDINALITY MISESTIMATE SQL> 再次执行查询，这次查询会使用到上面创建的sql plan directive_id SQL> select /*+ gather_plan_statistics */ *           2    from tab1   3    where gender='M'   4    and has_y_chromosome='Y';         ID G H ---------- - -          1 M Y          2 M Y          3 M Y          4 M Y          5 M Y          6 M Y          7 M Y          8 M Y          9 M Y         10 M Y 10 rows selected. SQL> SET LINESIZE 200 PAGESIZE 100 SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------- SQL_ID  gj6qavway0k06, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ *   from tab1   where gender='M' and has_y_chromosome='Y' Plan hash value: 1552452781 ----------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ----------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |                 |      1 |        |     10 |00:00:00.01 |       4 | |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1            |      1 |     10 |     10 |00:00:00.01 |       4 | |*  2 |   INDEX RANGE SCAN                  | TAB1_GENDER_IDX |      1 |     10 |     10 |00:00:00.01 |       2 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("HAS_Y_CHROMOSOME"='Y')    2 - access("GENDER"='M') Note -----    - dynamic statistics used: dynamic sampling (level=2)    - 1 Sql Plan Directive used for this statement 26 rows selected. SQL>