首页 » ORACLE 9i-23ai » 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–
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。