自动收集统计信息
查看自动统计是否开启
SELECT client_name, task_name, status FROM dba_autotask_task;
select client_name,status from dba_autotask_client;
查看收集策略
select t1.window_name,t1.repeat_interval,t1.duration from
dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name
and t2.window_group_name
in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
查看策略是否开启
SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
FROM dba_autotask_window_clients c, dba_scheduler_windows w
WHERE c.window_name = w.window_name
AND c.optimizer_stats = 'ENABLED';
查看收集历史
SELECT * FROM dba_autotask_client_history WHERE client_name LIKE '%stats%' order by WINDOW_START_TIME desc;
查看某个用户或者某张表的最后收集时间
select t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS, t.LAST_ANALYZED
from dba_tables t where t.owner ='SAPSR3' AND ROWNUM<10 ORDER BY LAST_ANALYZED DESC;
select t.TABLE_NAME, T.OWNER,t.NUM_ROWS, t.BLOCKS, t.LAST_ANALYZED
from dba_tables T WHERE ROWNUM<100 ORDER BY LAST_ANALYZED DESC;
select t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS, t.LAST_ANALYZED
from dba_tables t where t.TABLE_NAME ='Z_JCDDT_TEMP';
禁用自动收集
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => N