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示例

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

巴特西

SQL plan directives

最新文章

热门文章