当前位置: 首页>数据库>正文

Oracle之统计信息收集整理

自动收集统计信息

查看自动统计是否开启

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

https://www.xamrdz.com/database/6z61849059.html

相关文章: