[Oracle Note] 11g中如何禁用自动统计信息收集作业

11g中如何禁用自动统计信息收集作业？

因为11g中auto stats gather job被集成到auto task中，所以与10g中的禁用方式不一样;下面是一个简短的英文介绍：

Oracle 11g includes three automated database maintenance tasks:

• Automatic Optimizer Statistics Collection - Gathers stale or missing statistics for all schema objects (more info). The task name is 'auto optimizer stats collection'.
• Automatic Segment Advisor - Identifies segments that could be reorganized to save space (more info). The task name is 'auto space advisor'.
• Automatic SQL Tuning Advisor - Identifies and attempts to tune high load SQL (more info). The task name is 'sql tuning advisor'.

  These tasks run during maintenance windows scheduled to open over night. Configuration of the maintenance tasks, their schedules and resource usage is possible using Enterprise Manager or PL/SQL APIs.

   

  The DISABLE and ENABLE procedures of the DBMS_AUTO_TASK_ADMIN package achieve the same result if they are called with no parameters.

   

  EXEC DBMS_AUTO_TASK_ADMIN.disable;
  EXEC DBMS_AUTO_TASK_ADMIN.enable;

   

     This can be done using the DBMS_AUTO_TASK_ADMIN package by specifying the task name in the CLIENT_NAME parameter of the DISABLE and ENABLE procedures.

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

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

This can be done using the DBMS_AUTO_TASK_ADMIN package by specifying the CLIENT_NAME and WINDOW_NAME parameters of the DISABLE and ENABLE procedures.

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => 'MONDAY_WINDOW');

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

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

 

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

begin
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
end;
/
PL/SQL procedure successfully completed.

SQL>  select client_name,status from DBA_AUTOTASK_CLIENT;

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

Relevant Views

The following views display information related to the automated database maintenance tasks:

• DBA_AUTOTASK_CLIENT
• DBA_AUTOTASK_CLIENT_HISTORY
• DBA_AUTOTASK_CLIENT_JOB
• DBA_AUTOTASK_JOB_HISTORY
• DBA_AUTOTASK_OPERATION
• DBA_AUTOTASK_SCHEDULE
• DBA_AUTOTASK_TASK
• DBA_AUTOTASK_WINDOW_CLIENTS
• DBA_AUTOTASK_WINDOW_HISTORY