Home > Oracle > To manually enable/disable automatic statistics collection

To manually enable/disable automatic statistics collection

Generally, it would not be a good idea to shut this function off, but if, for example, you are running tests and don’t want this process to interfere with performance, here’s how you would do it.

SQL> select client_name,status,consumer_group,mean_job_duration from dba_autotask_client;

CLIENT_NAME                                                      STATUS
—————————————————————- ——–
CONSUMER_GROUP
——————————
MEAN_JOB_DURATION
—————————————————————————
auto optimizer stats collection                                  DISABLED
ORA$AUTOTASK_STATS_GROUP
+000000000 00:01:24.582222222

auto space advisor                                               ENABLED
ORA$AUTOTASK_SPACE_GROUP
+000000000 00:00:28.309734513

CLIENT_NAME                                                      STATUS
—————————————————————- ——–
CONSUMER_GROUP
——————————
MEAN_JOB_DURATION
—————————————————————————

sql tuning advisor                                               ENABLED
ORA$AUTOTASK_SQL_GROUP
+000000000 00:00:48.896226415
3 rows selected.

To enable:
SQL> begin
  dbms_auto_task_admin.enable(client_name => ‘auto optimizer stats collection’,
  operation => NULL, window_name => NULL);
  END;
  /

To disable:
 begin
  dbms_auto_task_admin.disable(client_name => ‘auto optimizer stats collection’,
  operation => NULL, window_name => NULL);
  END;
  /

select client_name,status,consumer_group,mean_job_duration from dba_autotask_client;

Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: