多个表关联或者有视图套视图，快速检查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;

 

然后使用下面脚本检查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%'));

 

 

最后可以使用下面脚本检查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%'));