多个表关联或者有视图套视图，快速检查SQL语句中所有的表统计信息是否过期

多个表关联或者有视图套视图，快速检查SQL语句中所有的表统计信息是否过期

现有如下SQL：

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

 

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