原文地址:https://www.2cto.com/database/201412/357198.html
近期发现个别11G数据库无法自动收集统计信息,部分视图查询结果如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL> select client_name,status from dba_autotask_client where client_name =
'auto optimizer stats collection'
;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
SQL>
SQL> select client_name,status from dba_autotask_task;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
SQL>
SQL> select window_name,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME AUTOTASK_STATUS
------------------------------ ---------------
WEDNESDAY_WINDOW DISABLED
FRIDAY_WINDOW DISABLED
SATURDAY_WINDOW DISABLED
THURSDAY_WINDOW DISABLED
TUESDAY_WINDOW DISABLED
SUNDAY_WINDOW DISABLED
MONDAY_WINDOW DISABLED
7
rows selected
|
发现作业全部DISABLED,经测试发现和数据库安装时未勾选“启用自动维护任务”的现象一致(默认勾选),可能是之前DBA安装时取消勾选,如下图:
由于未勾选“启用自动维护任务“,数据安装会执行如下命令,导致作业disable:
execute DBMS_AUTO_TASK_ADMIN.disable();
以后数据库如果需要启动自动维护任务,只需要执行如下命令:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> execute DBMS_AUTO_TASK_ADMIN.enable();
PL/SQL procedure successfully completed
SQL> select window_name,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME AUTOTASK_STATUS
------------------------------ ---------------
WEDNESDAY_WINDOW ENABLED
FRIDAY_WINDOW ENABLED
SATURDAY_WINDOW ENABLED
THURSDAY_WINDOW ENABLED
TUESDAY_WINDOW ENABLED
SUNDAY_WINDOW ENABLED
MONDAY_WINDOW ENABLED
7
rows selected
|