统计信息 - 检查统计信息是否过期 - 《SQL优化入门》

收集完表的统计信息之后，如果表中有大量数据发生变化，这时表的统计信息就过期了，我们需要重新收集表的统计信息，如果不重新收集，可能会导致执行计划走偏。

以 T_STATS 为例，我们先在 owner 列上创建一个索引。

create index idx_t_stats_owner on t_stats(owner);

Index created.

我们收集 owner 列的直方图信息。

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname         => 'SCOTT',
                                tabname          => 'T_STATS',
                                 estimate_percent => 100,
                                 method_opt       => 'for columns owner size skewonly',
                                 no_invalidate    => FALSE,
                                degree           => 1,
                                  cascade          => TRUE);
  END;

PL/SQL procedure successfully completed.

我们执行下面 SQL 并且查看执行计划（为了方便排版，省略了执行计划中的 Time 列）。

select * from t_stats where owner='SCOTT';

122 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3912915053
-----------------------------------------------------------------------------------
| Id |Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|  0 |SELECT STATEMENT            |                   |   122 | 11834 |     5   (0)|
|  1 | TABLE ACCESS BY INDEX ROWID| T_STATS           |   122 | 11834 |     5   (0)|
|* 2 | INDEX RANGE SCAN           | IDX_T_STATS_OWNER |   122 |       |     1   (0)|
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         26  consistent gets
          0  physical reads
          0  redo size
      13440  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        122  rows processed

SQL 的过滤条件是where owner='SCOTT'，因为收集了 owner 列的直方图统计，优化器能准确地估算出 SQL 返回 122 行数据，该 SQL 走的是索引范围扫描，执行计划是正确的。

现在我们更新表中的数据，将object_id<=10000的 owner 更新为 ‘SCOTT’。

update t_stats set owner='SCOTT' where object_id<=10000;

9709 rows updated.

commit;

Commit complete.

我们再次执行 SQL 并且查看执行计划。

select * from t_stats where owner='SCOTT';

9831 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3912915053
-----------------------------------------------------------------------------------
| Id | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                   |   122 | 11834 |     5   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID| T_STATS           |   122 | 11834 |     5   (0)|
|* 2 |   INDEX RANGE SCAN          | IDX_T_STATS_OWNER |   122 |       |     1   (0)|
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1502  consistent gets
          0  physical reads
       3236  redo size
    1005607  bytes sent via SQL*Net to client
       7625  bytes received via SQL*Net from client
        657  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9831  rows processed

从执行计划中可以看到，SQL 一共返回了 9 831 行数据，但是优化器评估只返回 122 行数据，因为优化器评估where owner='SCOTT'只返回 122 行数据，所以执行计划走了索引，但是实际上应该走全表扫描。

为什么优化器会评估where owner='SCOTT'只返回 122 行数据呢？原因在于表中有大量数据发生了变化，但是统计信息没有得到及时更新，优化器还是采用的老的（过期的）统计信息来估算返回行数。

我们可以使用下面方法检查表统计信息是否过期，先刷新数据库监控信息。

begin
    dbms_stats.flush_database_monitoring_info;
  end;

PL/SQL procedure successfully completed.

然后我们执行下面查询。

select owner, table_name , object_type, stale_stats, last_analyzed
   from dba_tab_statistics
   where owner = 'SCOTT'
     and table_name = 'T_STATS';

OWNER      TABLE_NAME      OBJECT_TYPE     STALE_STATS     LAST_ANALYZED
---------- --------------- --------------- --------------- -------------
SCOTT      T_STATS         TABLE           YES             24-MAY-17

STALE_STATS 显示为 YES 表示表的统计信息过期了。如果 STALE_STATS 显示为 NO，表示表的统计信息没有过期。

我们可以通过下面查询找出统计信息过期的原因。

select table_owner, table_name, inserts, updates, deletes, timestamp
    from all_tab_modifications
   where table_owner = 'SCOTT'
       and table_name = 'T_STATS';

TABLE_OWNER     TABLE_NAME         INSERTS    UPDATES    DELETES TIMESTAMP
--------------- --------------- ---------- ---------- ---------- ---------
SCOTT           T_STATS                  0       9709          0 24-MAY-17

从查询结果我们可以看到，从上一次收集统计信息到现在，表被更新了 9 709 行数据，所以表的统计信息过期了。

现在我们重新收集表的统计信息。

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname         => 'SCOTT',
                                 tabname          => 'T_STATS',
                                 estimate_percent => 100,
                                method_opt       => 'for columns owner size skewonly',
                                 no_invalidate    => FALSE,
                                 degree           => 1,
                                 cascade          => TRUE);
  END;

PL/SQL procedure successfully completed.

我们再次查看 SQL 的执行计划。

select * from t_stats where owner='SCOTT';

9831 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1525972472
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  9831 |   931K|   187   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T_STATS |  9831 |   931K|   187   (2)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1690  consistent gets
          0  physical reads
          0  redo size
     418062  bytes sent via SQL*Net to client
       7625  bytes received via SQL*Net from client
        657  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9831  rows processed

重新收集完统计信息之后，优化器估算返回 9 831 行数据，这次 SQL 没走索引扫描而是走的全表扫描，SQL 走了正确的执行计划。

细心的读者可能会认为走索引扫描的性能高于全表扫描，因为索引扫描逻辑读为 1 502，而全表扫描逻辑读为 1 690，所以索引扫描性能高。其实这是不对的，衡量一个 SQL 的性能不能只看逻辑读，还要结合 SQL 的物理 I/O 次数综合判断。本书第 4 章会就为什么这里全表扫描性能比索引扫描性能更高给出详细解释。

Oracle 是怎么判断一个表的统计信息过期了呢？当表中有超过 10% 的数据发生变化（INSERT，UPDATE，DELETE），就会引起统计信息过期。

现在我们查看表一共有多少行数据。

select count(*) from t_stats;

  COUNT(*)
----------
     72674

删除表中 10% 的数据，然后我们查看表的统计信息是否过期。

delete t_stats where rownum<=72674*0.1+1;
commit;

7268 rows deleted.

我们刷新数据库监控信息。

begin
    dbms_stats.flush_database_monitoring_info;
  end;

PL/SQL procedure successfully completed.

我们检查表统计信息是否过期。

select owner, table_name, object_type, stale_stats, last_analyzed
    from dba_tab_statistics
   where owner = 'SCOTT'
     and table_name = 'T_STATS';

OWNER      TABLE_NAME OBJECT_TYP STALE_STATS     LAST_ANALYZED
---------- ---------- ---------- --------------- ------------------
SCOTT      T_STATS    TABLE      YES             24-MAY-17

STALE_STATS 显示为 YES，说明表的统计信息过期了。

我们查看统计信息过期原因。

select table_owner, table_name, inserts, updates, deletes, timestamp
   from all_tab_modifications
  where table_owner = 'SCOTT'
   and table_name = 'T_STATS';

TABLE_OWNE TABLE_NAME    INSERTS    UPDATES    DELETES TIMESTAMP
---------- ---------- ---------- ---------- ---------- ------------------
SCOTT      T_STATS             0          0       7268 24-MAY-17

从上面查询我们可以看到表被删除了 7 268 行数据，从而导致表的统计信息过期。

在进行 SQL 优化的时候，我们需要检查表的统计信息是否过期，如果表的统计信息过期了，要及时更新表的统计信息。

数据字典 all_tab_modifications 还可以用来判断哪些表需要定期降低高水位，比如一个表经常进行 insert、delete，那么这个表应该定期降低高水位，这个表的索引也应该定期重建。除此之外，all_tab_modifications 还可以用来判断系统中哪些表是业务核心表、表的数据每天增长量等。

如果一个 SQL 有七八个表关联或者有视图套视图等，怎么快速检查 SQL 语句中所有的表统计信息是否过期呢？

现有如下 SQL。

select * from emp e,dept d where e.deptno=d.deptno;

我们可以先用 explain plan for 命令，在 plan_table 中生成 SQL 的执行计划。

explain plan for select * from emp e,dept d where e.deptno=d.deptno;

Explained.

然后我们使用下面脚本检查 SQL 语句中所有的表的统计信息是否过期。

 select owner, table_name, object_type, stale_stats, last_analyzed
    from dba_tab_statistics
    where (owner, table_name) in
         (select object_owner, object_name
            from plan_table
           where object_type like '%TABLE%'
          union
          select table_owner, table_name
            from dba_indexes
           where (owner, index_name) in
                 (select object_owner, object_name
                    from plan_table
                   where object_type like '%INDEX%'));

OWNER      TABLE_NAME OBJECT_TYP STALE_STATS     LAST_ANALYZED
---------- ---------- ---------- --------------- ------------------
SCOTT      DEPT       TABLE      NO              05-DEC-16
SCOTT      EMP        TABLE      YES             22-OCT-16

最后我们可以使用下面脚本检查 SQL 语句中表统计信息的过期原因。

select *
  from all_tab_modifications
 where (table_owner, table_name) in
       (select object_owner, object_name
          from plan_table
         where object_type like '%TABLE%'
        union
        select table_owner, table_name
          from dba_indexes
         where (owner, index_name) in
               (select object_owner, object_name
                  from plan_table
                 where object_type like '%INDEX%'));