自动收集统计信息没有执行，如何诊断-凯发app官方网站

col owner for a20
col table_name for a30
col last_analyzed for a20
set lin 200 pages 200
--19c
select * from (select owner,t.table_name,t.num_rows,t.blocks,to_char(t.last_analyzed,'yyyy-mm-dd hh24:mi:ss')last_analyzed from dba_tables t where owner not in (select username from dba_users where oracle_maintained='y') and num_rows>100 and last_analyzed is not null order by last_analyzed desc ) where rownum<101;

--11g num_rows为0说明可能被trunc

select * from (select owner,t.table_name,t.num_rows,t.blocks,to_char(t.last_analyzed,'yyyy-mm-dd hh24:mi:ss')last_analyzed from dba_tables t where owner not in (select username from dba_users where trunc(created)=(select trunc(created) from dba_users where username='sys')) and num_rows>100 and last_analyzed is not null order by last_analyzed desc ) where rownum<101;;

看看last_analyzed 列是否是最新日期，通常应该是昨天22:00，如果太旧说明没有更新。

统计信息收集是使用自动优化器统计信息收集维护任务实现的。任务的名称是“自动优化器统计集合”。该任务计划在维护窗口期间运行，并且应该收集有关具有过时或缺少统计信息的对象的统计信息。

自动收集统计信息受以下因素影响：
参数、任务、窗口、过程、表统计信息锁定属性、数据变化量、采样百分比、数据加载自动更新、imp导入元数据导致锁定、其他（最大进程数、最大游标数、os打开文件数、内存不足、空间不足等）

参数检查
col name for a32
col value for a10
select name,value from v$parameter where name in ('job_queue_processes','aq_tm_processes','statistics_level');

job_queue_processes 应该大于100
statistics_level 应该是 typical 或者 all
aq_tm_processes 应该是1

隐含参数 _optimizer_autostats_job
col name for a28
col descript for a42
select a.ksppinm name,b.ksppstvl value,a.ksppdesc descript
from x$ksppi a,x$ksppcv b where a.indx=b.indx and a.ksppinm like '%_optimizer_autostats_job%';

应该是 true ，如果是 false ，那么修改
alter system set "_optimizer_autostats_job"=true scope=spfile;
然后重启实例

基线维护作业状态
col owner for a8
col job_name for a25
col program_name for a30
col state for a12
select owner,job_name,program_name,enabled,state from dba_scheduler_jobs where job_name='bsln_maintain_stats_job';

enabled列应该是true，如果不是用以下方法激活：
exec dbms_scheduler.enable('bsln_maintain_stats_job')

维护作业组状态
col group_name for a32
select group_name, enabled from dba_scheduler_groups;

enabled列应该都是true，如果不是的话，禁用、启用方法：

begin
dbms_scheduler.disable(
name => 'sys.maintenance_window_group',
force => true);
dbms_scheduler.enable(
name => 'sys.maintenance_window_group');
end;
/

自动任务窗口（非常重要）
col window_next_time for a20
col window_name for a20
select window_name,to_char(window_next_time,'yyyy-mm-dd hh24:mi:ss')window_next_time,window_active,autotask_status,optimizer_stats,segment_advisor,sql_tune_advisor from dba_autotask_window_clients;

如果autotask_status列不是enabled 那么需要执行exec dbms_auto_task_admin.enable。

exec dbms_auto_task_admin.enable (client_name => 'auto optimizer stats collection',operation => null,window_name => null)

如果optimizer_stats 列不是enabled 那么需要执行：

begin
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => null,
window_name => null);
end;
/

这里的client_name 参数可以通过dba_autotask_client得到。

调度窗口
col repeat_interval for a55
col next_start_date for a22
col last_start_date for a22
col window_name for a20
col duration for a20
select window_name,repeat_interval,duration,to_char(next_start_date,'yyyy-mm-dd hh24:mi:ss')next_start_date,to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss')last_start_date,enabled from dba_scheduler_windows where window_name not like 'week%';

enabled列应该都是true，并且平时22点开始4小时，周末6点开始20小时，如果不是，则修改：

--将周一窗口延长为6小时
begin
dbms_scheduler.disable(
name => 'monday_window');
dbms_scheduler.set_attribute(
name => 'monday_window',
attribute => 'duration',
value => numtodsinterval(6, 'hour'));
dbms_scheduler.enable(
name => 'monday_window');
end;
/

将周五的窗口改为23点开始，持续8小时
begin
dbms_scheduler.disable (name => '"sys"."friday_window"', force => true);
dbms_scheduler.set_attribute (name => '"sys"."friday_window"',attribute => 'repeat_interval',value=> 'freq=weekly;byday=fri;byhour=23;byminute=0;bysecond=0');
dbms_scheduler.set_attribute (name => '"sys"."friday_window"',attribute => 'duration',value=> '0 08:00:00');
dbms_scheduler.enable (name => '"sys"."friday_window"');
end;
/

控制不同的任务在不同的窗口执行
begin
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => null,
window_name => 'monday_window');
dbms_auto_task_admin.disable(
client_name => 'auto space advisor',
operation => null,
window_name => 'monday_window');
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => null,
window_name => 'monday_window');
end;
/

手工关闭或打开窗口
execute dbms_scheduler.close_window ('thursday_window');
打开窗口
execute dbms_scheduler.open_window ('monday_window','');

检查自动任务执行历史

select client_name,window_name,jobs_created,jobs_started,jobs_completed from dba_autotask_client_history where client_name like '%stats%';

查看调度作业历史

set lin 200 pages 100
col owner for a4
col log_date for a20
col job_name for a23
col operation for a15
col status for a12
select *
from (select log_id,
to_char(log_date, 'yyyy-mm-dd hh24:mi:ss') log_date,
owner,
job_name,
operation,
status
from dba_scheduler_job_log
where job_name like 'ora$at_os_opt%'
order by log_id desc)
where rownum < 21
order by log_id;

看收集统计信息作业执行细节

col run_duration for a15
col job_name for a23
col error# for 99
col status for a10
col inst_id for 9
col actual_start_date for a20
col cpu_used for a16
select *
from (select log_id,
owner,
job_name,
status,
error#,
to_char(actual_start_date, 'yyyy-mm-dd hh24:mi:ss') actual_start_date,
run_duration,
instance_id inst_id,
cpu_used
from dba_scheduler_job_run_details
where job_name like 'ora$at_os_opt%'
order by log_id desc)
where rownum < 11
order by log_id;

看状态应该都是succeeded，执行时间不太长，没有错误。

如果这些维护作业或任务有问题了可以考虑重新初始化（ 1945512.1）

重新初始化 maintenance jobs/tasks
@?/rdbms/admin/catmwin.sql

自动任务
col client_name for a32
select client_name , status from dba_autotask_client order by 1;

至少第一个status应该是enabled，如果是禁止的，则启用

begin
dbms_auto_task_admin.enable(
client_name => 'auto optimizer stats collection',
operation => null,
window_name => null);
end;
/

下一个方向是统计信息本身

查表统计信息历史
select table_name,to_char(stats_update_time,'yyyy-mm-dd hh24:mi:ss') stats_update_time from dba_tab_stats_history where owner='scott' and table_name='emp';
可能没有，那就比较一下
set long 2000000
set pagesize 1000
select * from table(dbms_stats.diff_table_stats_in_history(
ownname => 'scott',
tabname => 'emp',
time1 => systimestamp,
time2 => to_timestamp('2022-02-27 17:47:19 pm','yyyy-mm-dd hh12:mi:ss am'),
pctthreshold => 0));
看看历史记录
col savtime for a25
select to_char(savtime,'yyyy-mm-dd hh24:mi:ss') savtime, rowcnt, blkcnt, avgrln,samplesize
from wri$_optstat_tab_history
where obj#=(select object_id from dba_objects where owner='scott' and object_name='emp')order by 1;

检查对象当前统计信息
--表
col owner for a25
col last_analyzed for a25
select owner,table_name,num_rows,blocks,avg_space,avg_row_len,sample_size,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed from dba_tables where table_name='&table_name' and owner='&owner';
--索引
select owner,table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,sample_size,last_analyzed from dba_indexes where table_name='&table_name' and owner='&owner';
--分区
select table_owner,table_name,partition_name,num_rows,blocks,avg_space,avg_row_len,sample_size,last_analyzed from dba_tab_partitions where table_name= '&table_name' and owner='&owner';
--数据变化量
col table_owner for a25
col partition_name for a22
col subpartition_name for a22
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from (select * from dba_tab_modifications where table_owner='&owner' order by timestamp desc) where rownum<21;

--统计信息历史
col object_name for a25
col subobject_name for a25
col object_type for a15
select ob.owner,ob.object_name,ob.subobject_name,
ob.object_type,obj#,
to_char(savtime,'yyyy-mm-dd hh24:mi:ss') savtime,
flags,rowcnt,
blkcnt,avgrln,samplesize,analyzetime,
cachedblk,cachehit,logicalread
from sys.wri$_optstat_tab_history, dba_objects ob
where owner = upper('&owner')
and object_name = upper('&table_name')
and object_type in ('table')
and object_id = obj#;
抓取索引历史索引统计信息
select * from wri$_optstat_ind_history where obj#=&obj_id;

手工刷新，验证是否能够正常
exec dbms_stats.flush_database_monitoring_info

检查dba_tab_modifications 中是否有变化数据
检查表的统计信息是否锁定
select owner,table_name,stattype_locked from dba_tab_statistics
where table_name='%table_name';

如果关注的是非业务用户对象统计信息，那么如下逐个检查：

系统统计信息收集
exec dbms_stats.gather_system_stats
select * from sys.aux_stats$;
数据字典统计信息
exec dbms_stats.gather_dictionary_stats
select * from (
select table_name, last_analyzed from dba_tables where owner='sys' order by last_analyzed, table_name) where rownum<21;
固定对象的
exec dbms_stats.gather_fixed_objects_stats
select table_name，last_analyzed
from dba_tab_statistics
where owner = 'sys' and table_name like 'x$%' order by last_analyzed asc;

检查cjq0、mmon进程的trc日志和数据库告警日志中22点是否有异常信息。

下一个方向是bug

ora$autotask_clean job执行'sys.ora$age_autotask_data'程序，底层调用dbms_autotask_prvt.age包清理了31天以前的ket$_client_tasks
如果窗口和作业都正常，在22点后会自动填充dba_autotask_task
窗口设置太短，可能导致dba_autotask_task为空 (doc id 1610257.1)
mmon 参与填充 dba_autotask_client_job 和 dba_autotask_task
如果mmon异常（例如ora-1000达到最大游标数）可能导致这两个视图空 (doc id 2097721.1)
如果task名有问题，例如数据库alert中出现如下报错：
ora-12012: error on auto execute of job "sys"."ora$at_os_opt_sy_936"
ora-20001: statistics advisor: invalid task name for the current user
ora-06512: at "sys.dbms_stats", line 47207
ora-06512: at "sys.dbms_stats_advisor", line 882
ora-06512: at "sys.dbms_stats_internal", line 20059
ora-06512: at "sys.dbms_stats_internal", line 22201
ora-06512: at "sys.dbms_stats", line 47197
就需要考虑初始化相关对象 (doc id 2420581.1)
exec dbms_stats.init_package()
确认方法：
select name, ctime, how_created from sys.wri$_adv_tasks
where owner_name = 'sys' and name in ('auto_stats_advisor_task','individual_stats_advisor_task');
imp 时 rows=n先导入metadata，然后导入数据
if rows=n, then statistics for all imported tables will be locked after the import operation is finished.
参考：
新的 11g 默认作业(doc id 755838.1)
11g：调度程序维护任务或自动任务(doc id 1526120.1)
常见问题解答：自动收集统计信息(doc id 1233203.1)
dba_autotask_client_job和dba_autotask_task为空(doc id 2097721.1)
为什么自动统计信息收集可能显示为"卡住"且无法运行(doc id 1320246.1)
如何收集系统统计信息(doc id 149560.1)

以下不用执行

查看task状态（可能为空或与dba_autotask_client不一致）
col client_name for a32
col task_name for a25
select client_name,task_name, status from dba_autotask_task;

自动收集任务与程序关联关系
col client_name for a32
col task_name for a25
col program_action for a42
col status for a10
select client_name, task_name, status, program_action
from dba_autotask_task, dba_scheduler_programs
where upper(task_name)=upper(program_name)
and client_name='auto optimizer stats collection';

应该是如果没有，那么有可能是dba_autotask_task内容不正常。

阅读(1781) | 评论(0) | 转发(1) |

上一篇：dba_autotask_task 和 dba_autotask_client 的区别

下一篇：ora-04021诊断

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们