在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列信息。
限于书本限制,定制执行计划本书不做进一步讨论,有兴趣的读者请自行添加其余定制信息到定制执行计划中。

有时需要抓取正在运行的SQL的执行计划,这时我们需要获取SQL的SQL_ID以及SQL的CHILD_NUMEBR,然后将其代入下面SQL,就能获取正在运行的SQL的执行计划。

select * from table(dbms_xplan.display_cursor('sql_id',child_number));

在一个会话中执行如下SQL。

select count(*) from a,b where a.owner=b.owner;

在另外一个会话中执行如下SQL。

select a.sid, a.event, a.sql_id, a.sql_child_number, b.sql_text
  from v$session a, v$sql b
 where a.sql_address = b.address
   and a.sql_hash_value = b.hash_value
   and a.sql_child_number = b.child_number
 order by 1 desc;

接下来我们将SQL_ID和CHILD_NUMBER代入以下SQL。

SQL> select * from table(dbms_xplan.display_cursor('czr9jwxv0xra6',0));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------

SQL_ID  czr9jwxv0xra6, child number 0
-------------------------------------
select count(*) from a,b where a.owner=b.owner

Plan hash value: 319234518

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |       |  2556 (100)|          |
|   1 |  SORT AGGREGATE     |      |     1 |    34 |       |            |          |
|*  2 |   HASH JOIN         |      |   400M|    12G|  1920K|  2556  (78)| 00:00:31 |
|   3 |    TABLE ACCESS FULL| B    | 67547 |  1121K|       |   187   (1)| 00:00:03 |
|   4 |    TABLE ACCESS FULL| A    | 77054 |  1279K|       |   187   (1)| 00:00:03 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OWNER"="B"."OWNER")

Note
-----
   - dynamic sampling used for this statement (level=2)

查看带有A-TIME的执行计划的用法如下。

alter session set statistics_level=all;

或者在SQL语句中添加hint:/+ gather_plan_statistics /
运行完SQL语句,然后执行下面的查询语句就可以获取带有A-TIME的执行计划。

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

示例(Oracle11gR2,Scott账户)如下。

SQL> select /*+ gather_plan_statistics full(test) */ count(*) from test where owner='SYS';

  COUNT(*)
----------
     30808

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

SQL_ID  fswg73p1zmvqu, child number 0
-------------------------------------
select /*+ gather_plan_statistics full(test) */ count(*) from test
where owner='SYS'

Plan hash value: 1950795681

-------------------------------------------------------------------------------------
| Id |Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------
|  0 |SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.03 |    1037 | 1033 |
|  1 | SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.03 |    1037 | 1033 |
|* 2 |  TABLE ACCESS FULL| TEST |      1 |   2518 |  30808 |00:00:00.01 |    1037 | 1033 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

20 rows selected.

Starts表示这个操作执行的次数。
E-Rows表示优化器估算的行数,就是普通执行计划中的Rows。
A-Rows表示真实的行数。
A-Time表示累加的总时间。与普通执行计划不同的是,普通执行计划中的Time是假的,而A-Time是真实的。
Buffers表示累加的逻辑读。
Reads表示累加的物理读。

下面脚本用于收集SCOTT账户下统计信息过期了或者是从没收集过统计信息的表的统计信息,采样率也根据表的段大小做出了相应调整。

declare
  cursor stale_table is
    select owner,
           segment_name,
           case
             when segment_size < 1 then
              100
             when segment_size >= 1 and segment_size <= 5 then
              50
             when segment_size > 5 then
              30
           end as percent,
           6 as degree
      from (select owner,
                   segment_name,
                   sum(bytes / 1024 / 1024 / 1024) segment_size
              from DBA_SEGMENTS
             where owner = 'SCOTT'
               and segment_name in
                   (select table_name
                      from DBA_TAB_STATISTICS
                     where (last_analyzed is null or stale_stats = 'YES')
                       and owner = 'SCOTT')
             group by owner, segment_name);
begin
  dbms_stats.flush_database_monitoring_info;
  for stale in stale_table loop
    dbms_stats.gather_table_stats(ownname          => stale.owner,
                                  tabname          => stale.segment_name,
                                  estimate_percent => stale.percent,
                                  method_opt       => 'for all columns size repeat',
                                  degree           => stale.degree,
                                  cascade          => true);
  end loop;
end;
/