Enabling and Disabling – auto optimizer stats collection

Here is how you can query if the Auto Optimizer Stats Collection job is running in Oracle Database 11g:

Login to database as SYSDBA:

SELECT CLIENT_NAME,
STATUS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = ‘auto optimizer stats collection’;

Below is how the job can be disabled:

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => ‘auto optimizer stats collection’,
operation => NULL,
window_name => NULL);

Below is how the job can be enabled:

exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => ‘auto optimizer stats collection’,
operation =>NULL,
window_name => NULL);