SQL plan directives
2016-01-20 12:45 abce 阅读(1300) 评论(0) 编辑 收藏 举报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>
