统计信息的执行关乎着我们系统的稳定，优化器的选择。我们大部分的核心库还都是Oracle数据库，统计信息是把双刃剑，在业务敏感度较高的交易系统中，尤其重要。此前我们因为“统计信息”出现的业务感知的问题，挺多。后来也在生产环境不断打磨，形成了我们特有的统计信息壁垒手段。
1，库级基本的统计信息JOB不做调整。
2，定期与开发交流，锚定某数据库中的热点表，按表收集。
3，按用户收集统计信息。
4，面对日、月、周级的分区表，月底月初，突然有数据量进入，提前COPY统计信息。
5，所有的操作时间段，均不在业务高峰期。就这样，99%的系统都经过几年的打磨，平稳运行，上周的某一天，有个业务系统晚上凌晨跑批，跑批SQL未在正常时间内出结果。后与开发沟通，是因上游数据提前产生，灌入库内时间提前，与我们统计信息收集脚本重叠，且灌入库的逻辑，是先将已经存在的数据全部truncate，然后立马写入百万数据，再立刻运行跑批SQL，貌似没有给我们收集统计信息的时间了，而且跑批期间是多次跑批，每次跑批重复以上逻辑（truncate多次）。再梳理我们的统计信息脚本的时候，发现系统自己的统计信息job好像从来没有成功过！而没有出问题，正式因为我们上面说的2-4的手段，做了弥补。我们的参数文档、部署脚本，都是统一的。那这里为什么统计信息job没有执行呢？
SQL> select client_name ,status from DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
SQL> SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
------------------------------ --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW 14-AUG-23 10.00.00.000000 PM PRC FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
TUESDAY_WINDOW 15-AUG-23 10.00.00.000000 PM PRC FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
WEDNESDAY_WINDOW 16-AUG-23 10.00.00.000000 PM PRC FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
THURSDAY_WINDOW 17-AUG-23 10.00.00.000000 PM PRC FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
FRIDAY_WINDOW 18-AUG-23 10.00.00.000000 PM PRC FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
SATURDAY_WINDOW 19-AUG-23 06.00.00.000000 AM PRC FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
SUNDAY_WINDOW 20-AUG-23 06.00.00.000000 AM PRC FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
7 rows selected.这里的AUTOTASK为DISABLED，似乎是异常的。进行了简单的排查
SQL> show parameter job_queue_processes
SQL> show parameter statistics_level
SQL> select client_name,window_group,status from dba_autotask_client;
SQL>select window_name, enabled from dba_scheduler_windows;
SQL>show parameter "_enable_automatic_maintenance"
PS:Setting "_enable_automatic_maintenance" to 0 will prevent autotask startup's.
基表状态为空
SQL> select * from sys.KET$_CLIENT_TASKS;
no rows selected这个 DBA_AUTOTASK_TASK 的内容是几个视图关联的结果，正常的如图
重要的信息来自KET$_CLIENT_TASKS这个基表，这个里面的数据是动态变化的，如果禁用掉某个任务则会消失一条，另外，自动清理作业也可能会清空这里的信息，这里尝试做了如下操作
begin
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
end;观察一天后，还是状态不对。这里我想，那是不是装库的时候，因为DBCA建库有一步勾选EM的时候，会不会有人把统计信息automatic也给勾选掉了。
MOS文档参考：
11g Autotask Jobs Are Not Running as Scheduled. (Doc ID 2084941.1)
Automatic Maintenance Jobs Not Run Even When "auto optimizer stats collection" Is Enabled (Doc ID 2362007.1)于是做了如下的小实验，验证是不是因为装库的时候，去掉了enable automatic导致的
查询DBA_AUTOTASK_WINDOW_CLIENTS，状态也是为禁用状态，那么大概率就是因为这个原因导致的了。知道原因也就放心了，这里有两种处理手段
1，通过EM进行启动。
2，通过语句进行重启job。
17:20:40 SYS@xlmdb2(xxxx2)> EXEC DBMS_AUTO_TASK_ADMIN.disable;
PL/SQL procedure successfully completed.
17:21:22 SYS@xlmdb2(xxxx2)> EXEC DBMS_AUTO_TASK_ADMIN.enable;
PL/SQL procedure successfully completed.
15:52:09 SYS@x1(xxx1)> SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
------------------------------ --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW 14-AUG-23 10.00.00.000000 PM PRC FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
TUESDAY_WINDOW 15-AUG-23 10.00.00.000000 PM PRC FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
WEDNESDAY_WINDOW 16-AUG-23 10.00.00.000000 PM PRC FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
THURSDAY_WINDOW 17-AUG-23 10.00.00.000000 PM PRC FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
FRIDAY_WINDOW 18-AUG-23 10.00.00.000000 PM PRC FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
SATURDAY_WINDOW 19-AUG-23 06.00.00.000000 AM PRC FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
SUNDAY_WINDOW 20-AUG-23 06.00.00.000000 AM PRC FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
7 rows selected.相关查询语句sql
检查自动任务执行历史
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;
检查对象当前统计信息
--表
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';
select owner, ---所有者
table_name name, ---对象名
object_type, ---对象类型
stale_stats, ---统计信息是否过期
last_analyzed ---过期时间戳
from dba_tab_statistics
where table_name in ('T_SALESMAN_GRADE_DAY_MPOS')
and owner = 'TSS';
历史某个表的统计信息收集时间
SELECT t.object_name,
t.owner,
'HISTORY' version_type,
h.analyzetime,
h.rowcnt,
h.samplesize,
CASE WHEN h.rowcnt > 0 THEN TO_CHAR(ROUND(h.samplesize * 100 / h.rowcnt, 1), '99999990D0') END perc,
h.blkcnt,
h.avgrln
FROM dba_objects t,
sys.WRI$_OPTSTAT_TAB_HISTORY h
WHERE t.object_id = h.obj#
AND t.object_type = 'TABLE'
and t.object_name='T_BAB_PARTITION'
and t.owner='SSS';
--表上列的直方图信息
select table_name,column_name,endpoint_number,endpoint_value
from user_tab_histograms
where table_name = 'TEST'
and column_name = 'OBJECT_ID';
select table_name,column_name,endpoint_number,endpoint_value
from user_tab_histograms
where table_name in (
select table_name
from dba_tables
where owner='TEST'
);
细心维护系统，踏实保证系统稳定！
