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

现有如下SQL。

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

刷新监控信息

SQL> begin
  2    dbms_stats.flush_database_monitoring_info;
  3  end;
  4  /

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

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

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

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%'));