定制执行计划

在Oracle数据库中，执行计划是树形结构，因此我们可以利用树形查询来定制执行计划。
我们打开PLSQL dev SQL窗口，登录示例账户Scott并且运行如下SQL。

explain plan for select /*+ use_hash(a,dept) */ *
  from emp a, dept
 where a.deptno = dept.deptno
   and a.sal > 3000;

然后执行下面的脚本，结果如图3-2所示。

select case
         when (filter_predicates is not null or
              access_predicates is not null) then
          '*'
         else
          ' '
       end || id as "Id",
       lpad(' ', level) || operation || ' ' || options "Operation",
       object_name "Name",
       cardinality as "Rows",
       filter_predicates "Filter",
       access_predicates "Access"
  from plan_table 
 start with id = 0
connect by prior id = parent_id;

Picture 1
图3-2
我们曾在1.2节中提到，只有大表才会产生性能问题，因此可以将表的段大小添加到定制执行计划中，这样我们在用定制执行计划优化SQL的时候，可以很方便地知道表大小，从而更快地判断该步骤是否可能是性能瓶颈。下面脚本添加表的段大小以及索引段大小到定制执行计划中，结果如图3-3所示。

select case
         when (filter_predicates is not null or
              access_predicates is not null) then
          '*'
         else
          ' '
       end || id as "Id",
       lpad(' ', level) || operation || ' ' || options "Operation",
       object_name "Name",
       cardinality as "Rows",
       b.size_mb "Size_Mb",
       filter_predicates "Filter",
       access_predicates "Access"
  from plan_table a,
       (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb
          from dba_segments
         group by owner, segment_name) b
 where a.object_owner = b.owner(+)
   and a.object_name = b.segment_name(+)
 start with id = 0
connect by prior id = parent_id;

如图3-3所示，Size_Mb显示表的段大小，单位是MB。
Picture 1
图3-3
我们曾在1.4节中提到建立组合索引避免回表或者建立合适的组合索引减少回表次数。如果一个SQL只访问了某个表的极少部分列，那么我们可以将这些被访问的列联合在一起，从而建立组合索引。下面脚本将添加表的总字段数以及被访问字段数量到定制执行计划中，结果如图3-4所示。

select case
         when access_predicates is not null or filter_predicates is not null then
          '*' || id
         else
          ' ' || id
       end as "Id",
       lpad(' ', level) || operation || ' ' || options "Operation",
       object_name "Name",
       cardinality "Rows",
       b.size_mb "Mb",
       case
         when object_type like '%TABLE%' then
          REGEXP_COUNT(a.projection, ']') || '/' || c.column_cnt
       end as "Column",
       access_predicates "Access",
       filter_predicates "Filter",
       case
         when object_type like '%TABLE%' then
          projection
       end as "Projection"
  from plan_table a,
       (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb
          from dba_segments
         group by owner, segment_name) b,
       (select owner, table_name, count(*) column_cnt
          from dba_tab_cols
         group by owner, table_name) c
 where a.object_owner = b.owner(+)
   and a.object_name = b.segment_name(+)
   and a.object_owner = c.owner(+)
   and a.object_name = c.table_name(+)
 start with id = 0
connect by prior id = parent_id;

Picture 4
图3-4
如图3-4中所示，Column表示访问了表多少列/表一共有多少列。Projection显示了具体的访问列信息，限于书本宽度，图中没有显示Projection列信息。
限于书本限制，定制执行计划本书不做进一步讨论，有兴趣的读者请自行添加其余定制信息到定制执行计划中。