如果一个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%'));