Oracle统计信息收集失败案例

【摘要】

有个数据库显示作业按照正常时间点执行，但是统计信息并未更新，导致数据库统计信息陈旧，产生大量错误的执行计划。

通过查询MOS，整理一下几种情况及解决方法：

1、是否存在统计信息被锁

查询是否被锁

SELECT DISTINCT A.OWNER,a.TABLE_NAME FROM DBA_TAB_STATISTICS A WHERE A.STATTYPE_LOCKED='ALL';

（1）、解锁统计信息然后重新收集：

exec dbms_stats.unlock_schema_stats(ownname => 'test');

（2）、强制收集

begin
dbms_stats.gather_schema_stats('GSMADMIN_INTERNAL', estimate_percent =>100, cascade=>true, force=>TRUE);
end;
/

2、检查收集时间duration时间是否过短 例：五分钟

select window_name,repeat_interval,dyuration from dba_scheduler_windows;

 

手动修改duration

exec dbms_scheduler.set_attribute('MONDAY_WINDOW','DURATION','+000 04:00:00');
exec dbms_scheduler.set_attribute('TUESDAY_WINDOW','DURATION','+000 04:00:00');
exec dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','DURATION','+000 04:00:00');
exec dbms_scheduler.set_attribute('THURSDAY_WINDOW','DURATION','+000 04:00:00');
exec dbms_scheduler.set_attribute('FRIDAY_WINDOW','DURATION','+000 04:00:00');
exec dbms_scheduler.set_attribute('SATURDAY_WINDOW','DURATION','+000 20:00:00');
exec dbms_scheduler.set_attribute('SUNDAY_WINDOW','DURATION','+000 20:00:00');

3、作业执行过程中数据库被shutdown，导致作业未正常关闭

• The 'auto optimizer stats collection' task is enabled in auto task
• STATISTICS_LEVEL has already been set to TYPICAL or FULL
• dba_autotask_client_history is empty
• Statistics on tables are stale.
• Some scheduler window is active and LAST_START_DATE is several days ago.
  
  SELECT window_name ,
  last_start_date,
  enabled ,
  active
  FROM dba_scheduler_windows;
   

  WINDOW_NAME       LAST_START_DATE                      ENABLE ACTIVE
  ----------------- ------------------------------------ ------ ------
  SATURDAY_WINDOW   26-Mar-11 06.00.00.019249 am +08:00 TRUE  TRUE

• In Alert log, there is a shutdown immediate after the window has opened but before the window has closed:
   

  Sat Mar 26 23:33:23 2011
  Stopping background process SMCO
  Stopping background process FBDA
  Shutting down instance: further logons disabled
  Sat Mar 26 23:33:26 2011
  Stopping background process CJQ0
  Stopping background process QMNC
  Stopping background process MMNL
  Stopping background process MMON
  Shutting down instance (immediate) <==system shutdown immediate

• The 'auto optimizer stats collection' task is assigned to a window group:
   

  select client_name,window_group
  from DBA_AUTOTASK_CLIENT
  where client_name = 'auto optimizer stats collection';
  
  CLIENT_NAME                     WINDOW_GROUP
  ------------------------------- ------------------
  auto optimizer stats collection ORA$AT_WGRP_OS

• In 11g, each day has its own window, but they are in the same window group.
   

  SELECT * FROM dba_scheduler_group_members
  where group_name='ORA$AT_WGRP_OS'
  
  OWNER GROUP_NAME     MEMBER_NAME
  ----- ----------     -------------
  SYS   ORA$AT_WGRP_OS "SYS"."MONDAY_WINDOW"
  SYS   ORA$AT_WGRP_OS "SYS"."TUESDAY_WINDOW"
  SYS   ORA$AT_WGRP_OS "SYS"."WEDNESDAY_WINDOW"
  SYS   ORA$AT_WGRP_OS "SYS"."THURSDAY_WINDOW"
  SYS   ORA$AT_WGRP_OS "SYS"."FRIDAY_WINDOW"
  SYS   ORA$AT_WGRP_OS "SYS"."SATURDAY_WINDOW"
  SYS   ORA$AT_WGRP_OS "SYS"."SUNDAY_WINDOW"

解决方法:

EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW ('SATURDAY_WINDOW');

再次查看作业状态：

select window_name,active from DBA_SCHEDULER_WINDOWS

WINDOW_NAME                    ACTIVE 
------------------------------ ------ 
MONDAY_WINDOW                  FALSE  
TUESDAY_WINDOW                 FALSE  
WEDNESDAY_WINDOW               FALSE  
THURSDAY_WINDOW                FALSE  
FRIDAY_WINDOW                  FALSE  
SATURDAY_WINDOW  FALSE  
SUNDAY_WINDOW                  FALSE  
WEEKNIGHT_WINDOW               FALSE  
WEEKEND_WINDOW                 FALSE 

下次作业启动时会自动收集统计信息。

4、catproc is invalid

SELECT comp_name, comp_id, version, status FROM dba_registry;
COMP_NAME COMP_ID VERSION Status
Oracle Database Catalog Views CATALOG 11.2.0.4.0 VALID
Oracle Database Packages and Types CATPROC 11.2.0.4.0 INVALID 

解决方法：

修复完成catproc再次执行

BEGIN
dbms_auto_task_admin.enable(
 client_name => 'auto optimizer stats collection',
 operation   => 'auto optimizer stats job',
 window_name => NULL);
END;
/

5、其它BUG导致，参考文章

Troubleshooting Scheduler Autotask Issues (文档 ID 1561498.1)