Thursday, September 29, 2011

Enable-Disable automatic statistics


Automatic statistics collection is enabled by default. If you’ve disabled automatic statistics collection, you can enable it again.

First see if automatic stats collection is enabled or disabled


SQL> select client_name,status from dba_autotask_client;

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



Based on the output of the earlier SQL, notice that 'sql tunning advisor' is disabled.

Lets enable it


SQL> begin 
           dbms_auto_task_admin.enable(
           client_name=>'sql tuning advisor',
           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                                  ENABLED
auto space advisor                                                  ENABLED
sql tuning advisor                                                    ENABLED

If you want to disable it back, run the same block that you used to enable the stats but this time use 'disable' procedure of the dbms_auto_task package as below

SQL> begin 
          dbms_auto_task_admin.disable(
          client_name=>'sql tuning advisor',
          operation=>NULL,
          window_name=>NULL);
          end;
          /

 PL/SQL procedure successfully completed.



No comments:

Post a Comment