诡异事件之统计信息JOB啥时候执行的？

最新推荐文章于 2024-04-30 09:30:07 发布

杨俊峰的博客

最新推荐文章于 2024-04-30 09:30:07 发布

阅读量162

点赞数

本文链接：https://blog.csdn.net/yangjunfeng0604/article/details/132364492

版权

文章讲述了在维护Oracle数据库时，如何管理和优化统计信息，以确保在高业务敏感度场景下系统的稳定性。遇到自动任务（如统计信息收集job）失效的情况，作者通过排查和实验揭示了问题所在并提供了两种解决策略。

摘要由CSDN通过智能技术生成

统计信息的执行关乎着我们系统的稳定，优化器的选择。我们大部分的核心库还都是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 的内容是几个视图关联的结果，正常的如图

诡异事件之统计信息JOB啥时候执行的？_统计信息

重要的信息来自KET$_CLIENT_TASKS这个基表，这个里面的数据是动态变化的，如果禁用掉某个任务则会消失一条，另外，自动清理作业也可能会清空这里的信息，这里尝试做了如下操作

begin
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL
  );
end;

观察一天后，还是状态不对。这里我想，那是不是装库的时候，因为DBCA建库有一步勾选EM的时候，会不会有人把统计信息automatic也给勾选掉了。

诡异事件之统计信息JOB啥时候执行的？_SQL_02

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导致的

诡异事件之统计信息JOB啥时候执行的？_统计信息_03

诡异事件之统计信息JOB啥时候执行的？_SQL_04

查询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.

诡异事件之统计信息JOB啥时候执行的？_杨俊峰_05

诡异事件之统计信息JOB啥时候执行的？_统计信息_06

诡异事件之统计信息JOB啥时候执行的？_杨俊峰_07

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'
 );

细心维护系统，踏实保证系统稳定！