定制执行计划

在 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;

图 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。

图 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;

图 3-4

如图 3-4 中所示，Column 表示访问了表多少列/表一共有多少列。Projection 显示了具体的访问列信息，限于书本宽度，图中没有显示 Projection 列信息。

限于书本限制，定制执行计划本书不做进一步讨论，有兴趣的读者请自行添加其余定制信息到定制执行计划中。