在 Oracle 11g 升级后无法运行自动优化器统计信息收集作业
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13142721/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot get auto optimizer stats collection job to run after Oracle 11g upgrade
提问by Steve Broberg
We recently upgraded our 10g server to 11g (11.2.0.3.0), and I see that stats gathering should be handled via the Automated Maintenance Tasks feature - specifically the "auto optimizer stats collection" job.
我们最近将 10g 服务器升级到 11g (11.2.0.3.0)，我发现应该通过自动维护任务功能来处理统计信息收集——特别是“自动优化器统计信息收集”工作。
However, since our upgrade (5 days ago), I have not seen any of these jobs run in the scheduler, and the Automated Maintenance Tasks interface in OEM does not show the jobs having been run. DBA_AUTOTASK_JOB_HISTORY has no rows, and I see no history of any stat job running in the scheduler tab or OEM, yet as far as I can tell, the system is configured correctly:
但是，自从我们升级（5 天前）以来，我没有看到任何这些作业在调度程序中运行，并且 OEM 中的自动维护任务界面没有显示已运行的作业。DBA_AUTOTASK_JOB_HISTORY 没有行，我在调度程序选项卡或 OEM 中看不到任何统计作业运行的历史记录，但据我所知，系统配置正确：
SELECT col
, val
FROM ( SELECT *
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'auto optimizer stats collection'
)
UNPIVOT ( val
FOR col
IN ( CLIENT_NAME
, STATUS
, CONSUMER_GROUP
, CLIENT_TAG
, PRIORITY_OVERRIDE
, ATTRIBUTES
, WINDOW_GROUP
, SERVICE_NAME
)
);
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_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
------------------------------ --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW 01-NOV-12 10.00.00.000000 AM AMERICA/NEW_YORK FALSE ENABLED ENABLED DISABLED ENABLED DISABLED
TUESDAY_WINDOW 01-NOV-12 10.00.00.000000 AM AMERICA/NEW_YORK FALSE ENABLED ENABLED DISABLED ENABLED DISABLED
WEDNESDAY_WINDOW 01-NOV-12 10.00.00.000000 AM AMERICA/NEW_YORK FALSE ENABLED ENABLED DISABLED ENABLED DISABLED
THURSDAY_WINDOW 01-NOV-12 10.00.00.000000 AM AMERICA/NEW_YORK FALSE ENABLED ENABLED DISABLED ENABLED DISABLED
FRIDAY_WINDOW 01-NOV-12 10.00.00.000000 AM AMERICA/NEW_YORK FALSE ENABLED ENABLED DISABLED ENABLED DISABLED
SATURDAY_WINDOW 01-NOV-12 10.00.00.000000 AM AMERICA/NEW_YORK FALSE ENABLED ENABLED DISABLED ENABLED DISABLED
SUNDAY_WINDOW 01-NOV-12 10.00.00.000000 AM AMERICA/NEW_YORK FALSE ENABLED ENABLED DISABLED ENABLED DISABLED
Have I missed something?
我错过了什么吗？
采纳答案by Sujit
Cause
原因
Just want to share the solution for all who will face this problem in the future.
只是想为所有将来会遇到此问题的人分享解决方案。
This problem is caused when you shutdown the database for maintenance etc while one of the scheduler window is active and corresponding auto maintenance job is running. So when the database is shutdown, corresponding job is killed by the status of maintenance window remains active.
当您在调度程序窗口之一处于活动状态且相应的自动维护作业正在运行时关闭数据库进行维护等会导致此问题。所以当数据库关闭时，相应的作业被杀死，维护窗口的状态保持活动。
This can be confirmed by following query.
这可以通过以下查询来确认。
SQL> select window_name,active from DBA_SCHEDULER_WINDOWS;
WINDOW_NAME ACTIV
------------------------------ -----
WEEKNIGHT_WINDOW FALSE
WEEKEND_WINDOW FALSE
MONDAY_WINDOW FALSE
TUESDAY_WINDOW FALSE
WEDNESDAY_WINDOW FALSE
THURSDAY_WINDOW FALSE
FRIDAY_WINDOW FALSE
SATURDAY_WINDOW TRUE <-- This is still active.
SUNDAY_WINDOW FALSE
Following query is showing that currently there are no auto stats job is running.
以下查询显示当前没有正在运行的自动统计作业。
SQL> select CLIENT_NAME,JOB_NAME,JOB_SCHEDULER_STATUS
2 from DBA_AUTOTASK_CLIENT_JOB
3 where client_name = 'auto optimizer stats collection';
no rows selected
未选择任何行
Solution
解决方案
close the active window.
关闭活动窗口。
SQL> EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW ('SATURDAY_WINDOW');
PL/SQL procedure successfully completed.
PL/SQL 过程成功完成。
SQL> select window_name,active from DBA_SCHEDULER_WINDOWS;
WINDOW_NAME ACTIV
------------------------------ -----
WEEKNIGHT_WINDOW FALSE
WEEKEND_WINDOW FALSE
MONDAY_WINDOW FALSE
TUESDAY_WINDOW FALSE
WEDNESDAY_WINDOW FALSE
THURSDAY_WINDOW FALSE
FRIDAY_WINDOW FALSE
SATURDAY_WINDOW FALSE
SUNDAY_WINDOW FALSE
Next time your job will run just fine.
下次你的工作会运行得很好。
回答by Sujit
See the following My Oracle Support (MOS) Article: Scheduled Maintenance Auto Tasks Not Working After 11.2.0.3 Upgrade [ID 1452836.1]
请参阅以下 My Oracle Support (MOS) 文章：计划维护自动任务在 11.2.0.3 升级后不起作用 [ID 1452836.1]
