SQL plan directives

2016-01-20 12:45 abce 阅读(1330) 评论(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示例

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> 