首页 » ORACLE 9i-23c » Autotask(Optimizer statistics gathering) not working in 11.2.0.4(未自动收集统计信息)

Autotask(Optimizer statistics gathering) not working in 11.2.0.4(未自动收集统计信息)

10G时的统计信息收集使用的scheduler job

SQL>select job_name,comments from dba_scheduler_jobs

JOB_NAME                       COMMENTS
------------------------------ --------------------------------------------------------------------------------
AUTO_SPACE_ADVISOR_JOB         auto space advisor maintenance job
GATHER_STATS_JOB               Oracle defined automatic optimizer statistics collection job
FGR$AUTOPURGE_JOB              file group auto-purge job
PURGE_LOG                      purge log job
MGMT_CONFIG_JOB                Configuration collection job.
MGMT_STATS_CONFIG_JOB          OCM Statistics collection job.

11G 发生了变化,引入了autotask, 从schedurer job中移除

SQL> select job_name,comments from dba_scheduler_jobs

JOB_NAME                       COMMENTS
------------------------------ --------------------------------------------------------------------------------
XMLDB_NFS_CLEANUP_JOB
SM$CLEAN_AUTO_SPLIT_MERGE      auto clean job for auto split merge
RSE$CLEAN_RECOVERABLE_SCRIPT   auto clean job for recoverable script
FGR$AUTOPURGE_JOB              file group auto-purge job
BSLN_MAINTAIN_STATS_JOB        Oracle defined automatic moving window baseline statistics computation job
DRA_REEVALUATE_OPEN_FAILURES   Reevaluate open failures for DRA
HM_CREATE_OFFLINE_DICTIONARY   Create offline dictionary in ADR for DRA name translation
ORA$AUTOTASK_CLEAN             Delete obsolete AUTOTASK repository data
FILE_WATCHER                   File watcher job
PURGE_LOG                      purge log job
MGMT_STATS_CONFIG_JOB          OCM Statistics collection job.
MGMT_CONFIG_JOB                Configuration collection job.
RLM$SCHDNEGACTION
RLM$EVTCLEANUP

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

可以使用OEM或dbms_auto_task_admin package 修改autotask 的属性。11G中的autotask 可能包括:

  • Optimizer statistics gathering
    Automatic Segment Advisor
    SQL Tuning Advisor

当系统参数statistics_level 是TYPICAL 或ALL时,表示table monitor功能是eanble的,SMON后台进程会监控表上的修改如insert,delete,update,间隔几分钟后一起把这些信息从内存刷新到基表,当表上的修改超过10%时,表原来的统计信息就表示为老化,下次收集统计信息的作业运行时,会收集统计信息STALE_STATS 为NULL 或 YES的表信息,查看table statistic信息老化可以通过xxx_tab_statistics 视图,当然更详细的详细还是要参考官方文档,下面来一个我生产环境中遇到的一个问题。

Question:
有一些表数据发生了很大变化,但是统计信息从初次导入后从没有自动收集过统计信息?手动收集是成功的,oracle 11.2.0.4 RAC ON AIX

Answer:

先检查收集统计信息的Autotask是否已启用。

SQL>   SELECT col
  2           , val
  3        FROM ( SELECT *
  4                 FROM DBA_AUTOTASK_CLIENT
  5                WHERE CLIENT_NAME = 'auto optimizer stats collection'
  6             )
  7     UNPIVOT (    val                            
  8              FOR col                              
  9               IN ( CLIENT_NAME
 10                  , STATUS
 11                  , CONSUMER_GROUP
 12                  , CLIENT_TAG
 13                  , PRIORITY_OVERRIDE
 14                  , ATTRIBUTES
 15                  , WINDOW_GROUP
 16                  , SERVICE_NAME
 17                  )
 18             );

COL               VAL
----------------- --------------------------------------------------------------------------------
CLIENT_NAME       auto optimizer stats collection
STATUS            ENABLED
CONSUMER_GROUP    ORA$AUTOTASK_STATS_GROUP
CLIENT_TAG        OS
PRIORITY_OVERRIDE INVALID
ATTRIBUTES        ON BY DEFAULT, VOLATILE, SAFE TO KILL
WINDOW_GROUP      ORA$AT_WGRP_OS

SQL> SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;

WINDOW_GROUP_NAME               ENABL NUMBER_OF_WINDOWS NEXT_START_DATE                              COMMENTS
-------------------------       ----- ----------------- -------------------------------------------- ------------------------------
MAINTENANCE_WINDOW_GROUP        TRUE                  7 18-NOV-14 10.00.00.000000 PM ASIA/SHANGHAI   Window group for Automated Maintenance                   

ORA$AT_WGRP_OS                  TRUE                  7 18-NOV-14 10.00.00.000000 PM ASIA/SHANGHAI   auto optimizer stats collection
ORA$AT_WGRP_SA                  TRUE                  7 18-NOV-14 10.00.00.000000 PM ASIA/SHANGHAI   auto space advisor
ORA$AT_WGRP_SQ                  TRUE                  7 18-NOV-14 10.00.00.000000 PM ASIA/SHANGHAI   sql tuning advisor

SQL> select window_group_name, window_name 
  from dba_scheduler_wingroup_members
  where window_group_name = 'ORA$AT_WGRP_OS';

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
ORA$AT_WGRP_OS                 MONDAY_WINDOW
ORA$AT_WGRP_OS                 TUESDAY_WINDOW
ORA$AT_WGRP_OS                 WEDNESDAY_WINDOW
ORA$AT_WGRP_OS                 THURSDAY_WINDOW
ORA$AT_WGRP_OS                 FRIDAY_WINDOW
ORA$AT_WGRP_OS                 SATURDAY_WINDOW
ORA$AT_WGRP_OS                 SUNDAY_WINDOW

–check the actual status of your task

SQL>select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME        WINDOW_NEXT_TIME                                    WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
---------------    --------------------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW      24-NOV-14 10.00.00.000000 PM ASIA/SHANGHAI          FALSE DISABLED ENABLED  ENABLED  ENABLED  DISABLED
TUESDAY_WINDOW     18-NOV-14 10.00.00.000000 PM ASIA/SHANGHAI          FALSE DISABLED ENABLED  ENABLED  ENABLED  DISABLED
WEDNESDAY_WINDOW    19-NOV-14 10.00.00.000000 PM ASIA/SHANGHAI         FALSE DISABLED ENABLED  ENABLED  ENABLED  DISABLED                   
THURSDAY_WINDOW    20-NOV-14 10.00.00.000000 PM ASIA/SHANGHAI          FALSE DISABLED ENABLED  ENABLED  ENABLED  DISABLED
FRIDAY_WINDOW      21-NOV-14 10.00.00.000000 PM ASIA/SHANGHAI          FALSE DISABLED ENABLED  ENABLED  ENABLED  DISABLED
SATURDAY_WINDOW    22-NOV-14 06.00.00.000000 AM ASIA/SHANGHAI          FALSE DISABLED ENABLED  ENABLED  ENABLED  DISABLED
SUNDAY_WINDOW      23-NOV-14 06.00.00.000000 AM ASIA/SHANGHAI          FALSE DISABLED ENABLED  ENABLED  ENABLED  DISABLED

SQL> select window_name,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    AUTOTASK
------------------------------ --------
MONDAY_WINDOW                  DISABLED
TUESDAY_WINDOW                 DISABLED
WEDNESDAY_WINDOW               DISABLED
THURSDAY_WINDOW                DISABLED
FRIDAY_WINDOW                  DISABLED
SATURDAY_WINDOW                DISABLED
SUNDAY_WINDOW                  DISABLED

Note:
从DBA_AUTOTASK_WINDOW_CLIENTS视图查询的AUTOTASK_STATUS的状态和DBA_AUTOTASK_CLIENT视图中的status是不一致的,DBA_AUTOTASK_WINDOW_CLIENTS是实际的状态。从

DBA_AUTOTASK_WINDOW_CLIENTS.AUTOTASK_STATUS is DISABLED  可以看出autotask是禁用的, 并且时间维护窗口是正常状态。

SQL> select count(*) from dba_scheduler_job_log where additional_info like '%GATHER_STATS_PROG%';
# no rows

TIP:
无运行日志。

MOS上有篇日志记录的很像,但是要重建时间空口,没有测试环境暂时不考虑。
Scheduled Maintenance Auto Tasks Not Working After 11.2.0.3 Upgrade (文档 ID 1452836.1)有

Solution:
下面这种方式经尝试也可以解决本案例问题,用dbms_auto_task_admin.enable 不带参数启动所有autotask,如果需要再禁用个别task; 如果使用enable带参数client name的方式运行,DBA_AUTOTASK_WINDOW_CLIENTS.AUTOTASK_STATUS的值不会改变,实际autotask也不会运行。

--enable Autotask 
SQL> exec dbms_auto_task_admin.enable;

PL/SQL procedure successfully completed.
--check the status again
SQL>  select window_name,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    AUTOTASK
------------------------------ --------
MONDAY_WINDOW                  ENABLED
TUESDAY_WINDOW                 ENABLED
WEDNESDAY_WINDOW               ENABLED
THURSDAY_WINDOW                ENABLED
FRIDAY_WINDOW                  ENABLED
SATURDAY_WINDOW                ENABLED
SUNDAY_WINDOW                  ENABLED

7 rows selected.

-- disable sql tuning advisor
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL, 
    window_name => NULL);
END;

-- disable auto space advisor
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL, 
    window_name => NULL);
END;

SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME                         STATUS
----------------------------------- --------
auto optimizer stats collection     ENABLED
auto space advisor                  DISABLED
sql tuning advisor                  DISABLED

--disable all Autotask 
SQL> exec dbms_auto_task_admin.disable;
PL/SQL procedure successfully completed.

--check the status again
SQL> select window_name,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    AUTOTASK
------------------------------ --------
MONDAY_WINDOW                  ENABLED
TUESDAY_WINDOW                 ENABLED
WEDNESDAY_WINDOW               ENABLED
THURSDAY_WINDOW                ENABLED
FRIDAY_WINDOW                  ENABLED
SATURDAY_WINDOW                ENABLED
SUNDAY_WINDOW                  ENABLED

-- check scheduler log

SQL>select log_id, log_date, additional_info from dba_scheduler_job_log 
where job_class in (select job_class_name from dba_scheduler_job_classes
 where comments = 'auto optimizer stats collection') and log_date > sysdate - 1 order by 1;

  LOG_ID LOG_DATE                            ADDITIONAL_INFO
---------- ----------------------------------- --------------------------------------------------------------------------------
 26062 20-NOV-14 02.00.01.790968 AM +08:00 REASON="Stop job called because associated window was closed"
     26159 20-NOV-14 02.00.01.874114 AM +08:00 REASON="Max runs reached"
     26160 20-NOV-14 02.00.01.875417 AM +08:00 REASON="Auto drop job dropped", PROGRAM_NAME=""SYS"."GATHER_STATS_PROG"", JOB_TY
                                               PE="NULL", JOB_ACTION="NULL", NUMBER_OF_ARGUMENTS="0", SCHEDULE_NAME="NULL", STA
                                               RT_DATE="NULL", REPEAT_INTERVAL="SYS"ORA$AT_WGRP_OS", QUEUE_SPEC="NULL", END_DAT
                                               E="NULL", JOB_CLASS="ORA$AT_JCNRM_OS", ENABLED="FALSE", AUTO_DROP="TRUE", RESTAR
                                               TABLE="FALSE", JOB_PRIORITY="2", MAX_RUNS="1", MAX_FAILURES="0", SCHEDULE_LIMIT=
                                               "NULL", MAX_RUN_DURATION="NULL", LOGGING_LEVEL="0", STOP_ON_WINDOW_EXIT="TRUE",
                                               INSTANCE_STICKINESS="TRUE",JOB_WEIGHT="1", NLS_ENV="", COMMENTS="automatic optim
                                               izer statistics collection job"

SQL> @printtab 'select client_name,window_start_time,window_duration,jobs_completed,window_end_time from dba_autotask_client_history'
CLIENT_NAME                   : auto optimizer stats collection
WINDOW_START_TIME             : 19-NOV-14 10.00.01.380677 PM +08:00
WINDOW_DURATION               : +000000000 03:59:59.310443
JOBS_COMPLETED                : 0
WINDOW_END_TIME               : 20-NOV-14 02.00.00.691120 AM +08:00
-----------------

NOTE:
可以看到autotask 是运行了,本案例中是因为老化的统计信息太多,4个小时的时间维护窗口内没有完成,窗口结束时关闭了任务。 这种情况可以手动收集一部分在闲时,或增加维护窗口。

禁用统计信息收集

有时因为统计信息收信可能会影响IO,或导致执行计划不稳定,禁用自动。
10g:-

SQL> exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
or
SQL> exec dbms_stats.set_param('AUTOSTATS_TARGET', 'ORACLE');

11g:-

SQL> EXEC DBMS_AUTO_TASK_ADMIN.disable;
or
SQL> exec dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE'); 
or
SQL>  begin
    DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => 'MONDAY_WINDOW');
...

修改统计信息收集窗口
可以自定议JOB的收集WINDOW ,如只允许周日0:00 – 8:00

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;by hour=1;byminute=0;bysecond=0');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'duration', '+000 08:00:00');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'resource_plan', '');
exec dbms_scheduler.enable('SUNDAY_WINDOW');
exec dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection',operation=>null,window_name=>'SUNDAY_WINDOW');

最近发现还有客户在用oracle 10g, 追加一个10g的方法. 确认scheduler job window.

SELECT job_name,
program_name,
schedule_name,
state,
stop_on_window_close
FROM dba_scheduler_jobs
WHERE job_name = 'GATHER_STATS_JOB';
SELECT *
FROM dba_scheduler_wingroup_members
WHERE window_group_name = 'MAINTENANCE_WINDOW_GROUP';

SELECT window_name,
repeat_interval,
duration
FROM dba_scheduler_windows
WHERE window_name IN ( 'WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW' );

WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; +000 08:00:00
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00

WEEKNIGHT_WINDOW : 周一到周五 每天22:00开始 ~ 次日06:00结束。
WEEKEND_WINDOW :周六0点开始,并且持续2天(到周日的23:59)。

–over–

打赏

, , ,

对不起,这篇文章暂时关闭评论。