Disabling automatic statistics gathering in 10g and 11g

Hi,

Well, If your database environment has well established statistics gathering mechanism or your application has specific requirement of statistics gathering, you may need to disable the automatic statistics gathering, this is a bit different in 10g and 11g.

All of us aware starting 10g automatic statistics gather job will kicked off every night 10PM and runs for an 8 hours and then stopped.

Further, starting from 11g along with automatic statistics gathering few additional jobs also run as follows,

Oracle 11g includes three automated database maintenance tasks:

But if you want to disable the same, in 10g and 11g here are the ways.

10g:-

SQL> exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

or

SQL> exec dbms_stats.set_param('AUTOSTATS_TARGET', 'ORACLE');

Find more documentation here.
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1048566

11g:-

Note:- the dbms_stats.set_param has been deprecated and dbms_stats.set_***_prefs has been introduced in 11g.
note if you keep AUTO instead of Oracle you are enabling the stats.

SQL> exec dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE'); 

or

Note:- this will disable all three maintainance tasks for all windows
(since 11g the maintainance scheduled tasks has been differentiated as windows)

SQL> EXEC DBMS_AUTO_TASK_ADMIN.disable;

or

Note:- (the window_name and client name can be found in DBA_AUTOTASK_WINDOW_CLIENTS, here we are disabling only monday window)

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

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

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

SYS on 07-AUG-12 at orcl >select * from DBA_AUTOTASK_WINDOW_CLIENTS where WINDOW_NAME = 'MONDAY_WINDOW';
WINDOW_NAME                    WINDOW_NEXT_TIME                                                         WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
------------------------------ --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW                  08-AUG-12 10.00.00.000000 PM ASIA/CALCUTTA                               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
1 rows selected.
SYS on 07-AUG-12 at orcl >BEGIN
2    DBMS_AUTO_TASK_ADMIN.disable(
3      client_name => 'auto optimizer stats collection',
4      operation   => NULL,
5      window_name => 'MONDAY_WINDOW');
6  END;
7  /
PL/SQL procedure successfully completed.
SYS on 07-AUG-12 at orcl >select * from DBA_AUTOTASK_WINDOW_CLIENTS where WINDOW_NAME = 'MONDAY_WINDOW';
WINDOW_NAME                    WINDOW_NEXT_TIME                                                         WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
------------------------------ --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW                  08-AUG-12 10.00.00.000000 PM ASIA/CALCUTTA                               FALSE ENABLED  DISABLED ENABLED  ENABLED  DISABLED
1 rows selected.
SYS on 07-AUG-12 at orcl >

Find More:- http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41448

Hope this helps
-Thanks
Geek DBA