Chinaunix首页 | 论坛 | 博客

沧海拾贝brjl.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

brjl

  • 博客访问： 3539998
  • 博文数量： 715
  • 博客积分： 1860
  • 博客等级： 上尉
  • 技术积分： 7745
  • 用 户 组： 普通用户
  • 注册时间： 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文（715）

文章存档

2023年（75）

2022年（134）

2021年（238）

2020年（115）

2019年（11）

2018年（9）

2017年（9）

2016年（17）

2015年（7）

2014年（4）

2013年（1）

2012年（11）

2011年（27）

2010年（35）

2009年（11）

2008年（11）

我的朋友
最近访客
推荐博文
相关博文
自动收集统计信息没有执行，如何诊断

分类： Oracle

2022-02-27 22:28:18

都19c年代了，有些数据库还是没有自动收集统计信息。
如何诊断？

先确认一下库里业务表的统计信息是否是新的：

  1.
  2. COL OWNER FOR A20
  3. COL TABLE_NAME FOR A30
  4. COL LAST_ANALYZED FOR A20
  5. SET LIN 200 PAGES 200
  6. --19c
  7. 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
  1. 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打开文件数、内存不足、空间不足等）

  1. 参数检查
  2. col name for a32
  3. col value for a10
  4. 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

  1. 隐含参数 _optimizer_autostats_job
  2. col name for a28
  3. col descript for a42
  4.  select a.ksppinm name,b.ksppstvl value,a.ksppdesc descript
  5.   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; 
然后重启实例

  1. 基线维护作业状态
  2. col owner for a8
  3. col job_name for a25
  4. col PROGRAM_NAME for a30
  5. col STATE for a12
  6. 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') 

  1. 维护作业组状态
  2. col group_name for a32
    SELECT group_name, enabled FROM dba_scheduler_groups;

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

  1. BEGIN
  2.   DBMS_SCHEDULER.disable(
  3.     name => 'SYS.MAINTENANCE_WINDOW_GROUP',
  4.     force => TRUE);

  6.   DBMS_SCHEDULER.enable(
  7.     name => 'SYS.MAINTENANCE_WINDOW_GROUP');
  8. END;
  9. /

  1. 自动任务窗口（非常重要）

  3. col WINDOW_NEXT_TIME for a20
  4. col window_name for a20
  5. 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。

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


如果OPTIMIZER_STATS 列不是ENABLED 那么需要执行：
  1. BEGIN
  2.        dbms_auto_task_admin.disable(
  3.        client_name => 'auto optimizer stats collection',
  4.        operation => NULL,
  5.        window_name => NULL);
  6. END;
  7. /
这里的client_name 参数可以通过dba_autotask_client得到。

  1. 调度窗口

  3. COL REPEAT_INTERVAL FOR A55
  4. col NEXT_START_DATE for a22
  5. col LAST_START_DATE for a22
  6. col window_name for a20
  7. col duration for a20
  8. 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小时，如果不是，则修改：

  1. --将周一窗口延长为6小时
  2. BEGIN
  3.   dbms_scheduler.disable(
  4.     name => 'MONDAY_WINDOW');
  5.   dbms_scheduler.set_attribute(
  6.     name => 'MONDAY_WINDOW',
  7.     attribute => 'DURATION',
  8.     value => numtodsinterval(6, 'hour'));
  9.   dbms_scheduler.enable(
  10.     name => 'MONDAY_WINDOW');
  11. END;
  12. /

  1. 将周五的窗口改为23点开始，持续8小时
  2. begin
  3.   dbms_scheduler.disable (NAME => '"SYS"."FRIDAY_WINDOW"', FORCE => TRUE);
  4.   
  5.   dbms_scheduler.set_attribute (name => '"SYS"."FRIDAY_WINDOW"',attribute => 'repeat_interval',value=> 'freq=weekly;byday=fri;byhour=23;byminute=0;bysecond=0');
  6.   dbms_scheduler.set_attribute (name => '"SYS"."FRIDAY_WINDOW"',attribute => 'duration',value=> '0 08:00:00');

  8.   dbms_scheduler.enable (name => '"SYS"."FRIDAY_WINDOW"');
  9. end;
  10. /

  1. 控制不同的任务在不同的窗口执行
  2. BEGIN
  3.   DBMS_AUTO_TASK_ADMIN.disable(
  4.     client_name => 'auto optimizer stats collection',
  5.     operation => NULL,
  6.     window_name => 'MONDAY_WINDOW');

  8.   DBMS_AUTO_TASK_ADMIN.disable(
  9.     client_name => 'auto space advisor',
  10.     operation => NULL,
  11.     window_name => 'MONDAY_WINDOW');

  13.   DBMS_AUTO_TASK_ADMIN.disable(
  14.     client_name => 'sql tuning advisor',
  15.     operation => NULL,
  16.     window_name => 'MONDAY_WINDOW');
  17. END;
  18. /

  1. 手工关闭或打开窗口
  2. EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW ('THURSDAY_WINDOW');
  3. 打开窗口
  4. EXECUTE DBMS_SCHEDULER.OPEN_WINDOW ('MONDAY_WINDOW','');
检查自动任务执行历史
  1. SELECT client_name,window_name,jobs_created,jobs_started,jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%';


查看调度作业历史
  1. set lin 200 pages 100
  2. col owner for a4
  3. col log_date for a20
  4. col job_name for a23
  5. col operation for a15
  6. col status for a12
  7. select *
  8.   from (select log_id,
  9.              to_char(log_date, 'yyyy-mm-dd hh24:mi:ss') log_date,
  10.                owner,
  11.                job_name,
  12.                operation,
  13.                status
  14.           from dba_scheduler_job_log
  15.          where job_name like 'ORA$AT_OS_OPT%'
  16.          order by log_id desc)
  17.  where rownum < 21
  18.  order by log_id;


看收集统计信息作业执行细节
  1. col RUN_DURATION for a15
  2. col job_name for a23
  3. col error# for 99
  4. col status for a10
  5. col inst_id for 9
  6. col ACTUAL_START_DATE for a20
  7. col cpu_used for a16
  8.  select *
  9.    from (select log_id,
  10.                 owner,
  11.                 job_name,
  12.                 status,
  13.                 error#,
  14.                 to_char(actual_start_date, 'yyyy-mm-dd hh24:mi:ss') actual_start_date,
  15.                 run_duration,
  16.                 instance_id inst_id,
  17.                 cpu_used
  18.            from dba_scheduler_job_run_details
  19.           where job_name like 'ORA$AT_OS_OPT%'
  20.           order by log_id desc)
  21.   where rownum < 11
  22.   order by log_id;

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

如果这些维护作业或任务有问题了可以考虑重新初始化（ 1945512.1）
  1. 重新初始化 maintenance jobs/tasks
  2. @?/rdbms/admin/catmwin.sql

  1. 自动任务
  2. col CLIENT_NAME for a32
  3. select client_name , status from DBA_AUTOTASK_CLIENT order by 1;

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

  1.
  2. BEGIN
  3.   DBMS_AUTO_TASK_ADMIN.enable(
  4.     client_name => 'auto optimizer stats collection',
  5.     operation => NULL,
  6.     window_name => NULL);
  7. end;
  8. /



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

  1. 查表统计信息历史
  2. 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';
  3. 可能没有，那就比较一下
  4. set long 2000000
  5. set pagesize 1000

  7. select * from table(dbms_stats.diff_table_stats_in_history(
  8.                     ownname => 'SCOTT',
  9.                     tabname => 'EMP',
  10.                     time1 => systimestamp,
  11.                     time2 => to_timestamp('2022-02-27 17:47:19 PM','YYYY-MM-DD hh12:mi:ss AM'),
  12.                     pctthreshold => 0));
  13. 看看历史记录
  14. col savtime for a25
  15. select to_char(savtime,'yyyy-mm-dd hh24:mi:ss') savtime, rowcnt, blkcnt, avgrln,samplesize
  16. from WRI$_OPTSTAT_TAB_HISTORY
  17. where obj#=(select object_id from dba_objects where owner='SCOTT' and object_name='EMP')order by 1;

  1. 检查对象当前统计信息
  2. --表
  3. col owner for a25
  4. col last_analyzed  for a25
  5. 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';

  7. --索引
  8. 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';

  10. --分区
  11. 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';

  13. --数据变化量
  14. col TABLE_OWNER for a25
  15. col PARTITION_NAME for a22
  16. col SUBPARTITION_NAME for a22
  17. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
  19. select * from (select * from dba_tab_modifications where table_owner='&OWNER' order by TIMESTAMP desc) where rownum<21;

  1. --统计信息历史
  2. col object_name for a25
  3. col SUBOBJECT_NAME for a25
  4. col OBJECT_TYPE for a15
  5. SELECT ob.owner,ob.object_name,ob.subobject_name,
  6.      ob.object_type,obj#,
  7.      to_char(savtime,'yyyy-mm-dd hh24:mi:ss') savtime,
  8.     flags,rowcnt,
  9.      blkcnt,avgrln,samplesize,analyzetime,
  10.      cachedblk,cachehit,logicalread
  11. FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
  12. WHERE owner = upper('&OWNER')
  13.  and object_name = upper('&TABLE_NAME')
  14.  and object_type in ('TABLE')
  15.  and object_id = obj#;
  16.  
  17. 抓取索引历史索引统计信息
  18. 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';


如果关注的是非业务用户对象统计信息，那么如下逐个检查：
  1. 系统统计信息收集 
  2. exec dbms_stats.gather_system_stats

  4. select * from sys.aux_stats$;

  5.
  6. 数据字典统计信息 
  7. exec DBMS_STATS.GATHER_DICTIONARY_STATS

  8.
  9. select * from (
  10. select table_name, last_analyzed from dba_tables where owner='SYS' order by last_analyzed, table_name) where rownum<21;

  11.
  12. 固定对象的 
  13. exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

  14.
  15. SELECT TABLE_NAME，LAST_ANALYZED
  16. from DBA_TAB_STATISTICS
  17. WHERE OWNER = 'SYS' AND TABLE_NAME LIKE 'X$%' ORDER BY LAST_ANALYZED ASC;

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




下一个方向是bug

  1. ORA$AUTOTASK_CLEAN job执行'SYS.ORA$AGE_AUTOTASK_DATA'程序，底层调用dbms_autotask_prvt.age包清理了31天以前的KET$_CLIENT_TASKS
  2. 如果窗口和作业都正常，在22点后会自动填充DBA_AUTOTASK_TASK


  5. 窗口设置太短，可能导致dba_autotask_task为空 (DOC ID 1610257.1)

  7. MMON 参与填充 DBA_AUTOTASK_CLIENT_JOB 和 DBA_AUTOTASK_TASK
  8. 如果mmon异常（例如ora-1000达到最大游标数）可能导致这两个视图空 (DOC ID 2097721.1)

  10. 如果task名有问题，例如数据库alert中出现如下报错：
  11. ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_936"
  12. ORA-20001: Statistics Advisor: Invalid task name for the current user
  13. ORA-06512: at "SYS.DBMS_STATS", line 47207
  14. ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
  15. ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
  16. ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
  17. ORA-06512: at "SYS.DBMS_STATS", line 47197
  18. 就需要考虑初始化相关对象 (DOC ID 2420581.1)
  19. EXEC dbms_stats.init_package() 

  20.
  21. 确认方法：
  22. select name, ctime, how_created from sys.wri$_adv_tasks
  23. where owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');


  26. imp 时 rows=N先导入metadata，然后导入数据
  27. If ROWS=n, then statistics for all imported tables will be locked after the import operation is finished.


  30. 参考： 
  31. 新的 11g 默认作业(DOC ID 755838.1)
  32. 11g：调度程序维护任务或自动任务(DOC ID 1526120.1)
  33. 常见问题解答：自动收集统计信息(DOC ID 1233203.1)
  34. DBA_AUTOTASK_CLIENT_JOB和DBA_AUTOTASK_TASK为空(DOC ID 2097721.1) 
  35. 为什么自动统计信息收集可能显示为"卡住"且无法运行(DOC ID 1320246.1)
  36. 如何收集系统统计信息(DOC ID 149560.1)





以下不用执行

  1. 查看task状态（可能为空或与dba_autotask_client不一致）
  2. col CLIENT_NAME for a32
  3. col task_name for a25
  4. SELECT client_name,task_name, status FROM dba_autotask_task;

  1. 自动收集任务与程序关联关系

  3. col CLIENT_NAME for a32
  4. col task_name for a25
  5. col PROGRAM_ACTION for a42
  6. col status for a10
  7. select client_name, task_name, status, program_action
  8. from dba_autotask_task, dba_scheduler_programs
  9. where upper(task_name)=upper(program_name)
  10. and client_name='auto optimizer stats collection';
应该是如果没有，那么有可能是dba_autotask_task内容不正常。

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

上一篇：DBA_AUTOTASK_TASK 和 Dba_Autotask_Client 的区别

下一篇：ORA-04021诊断

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

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

16024965号-6