我们要去 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';
----看一下 为啥 统计信息过期了,简化了,自己考虑复杂情况 ----
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';