How to disable auto optimizer stats collection

book

Article ID: 160273

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

On installation, Oracle will automatically start a stats collection job that does precisely the same thing as the refreshCBOStats job, the difference being Oracle runs its stats collection only once a day.  This is how to disable this feature

Resolution

All commands are run from SQLPlus logged in as sys as sysdba:

To disable:

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => NULL);

END;

/

If you want to enable it:

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => NULL);

END;

/