查看统计信息_脚本

我们要去 check 为啥过期了
----看一下 为啥 统计信息过期了，简化了，自己考虑复杂情况 ----

select *
  from all_tab_modifications
 where table_owner in (select  object_owner from plan_table)
   and table_name in (select  object_name from plan_table )
   and (inserts > 0 or updates > 0 or deletes > 0)
 order by 5 desc,6 desc ,7 desc;


select owner,
      table_name,
      partition_name,
      subpartition_name,
      stats_update_time,
      stats_update_time - lag(stats_update_time, 1, null) over(partition by owner, table_name order by stats_update_time) interval
 from DBA_TAB_STATS_HISTORY
where owner = 'SCOTT'
  and table_name = 'TEST'
order by owner, table_name, stats_update_time desc;   
这个脚本 会检查 统计信息 收集的 时间间隔


explain plan for select * from test where owner='SCOTT';

---检查过期了没----

exec dbms_stats.flush_database_monitoring_info;

with tab as
 (select object_name
    from plan_table
   where object_type = 'TABLE'
  union
  select table_name
    from dba_indexes
   where owner in (select object_owner from plan_table)
     and index_name in (select object_name from plan_table)),
owner as
 (select object_owner
    from plan_table
   where object_type = 'TABLE'
  union
  select table_owner
    from dba_indexes
   where owner in (select object_owner from plan_table)
     and index_name in (select object_name from plan_table))
select owner, table_name name, object_type, stale_stats, last_analyzed
  from dba_tab_statistics
 where table_name in (select object_name from tab)
   and owner in (select object_owner from owner)
   and (stale_stats = 'YES' or last_analyzed is null);
   
   select owner, table_name name, object_type, stale_stats, last_analyzed
  from dba_tab_statistics
 where table_name='TEST';