Automatic Statistics and maintenance

For checking status of auto task operations like gathering stats

SELECT client_name, status FROM dba_autotask_operation;

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


For schedules
SELECT * FROM dba_autotask_schedule;

WINDOW_NAME          START_TIME    DURATION  
-------------------- ------------- -----------
MONDAY_WINDOW        08/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0
MONDAY_WINDOW        15/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0
MONDAY_WINDOW        22/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0
...
SUNDAY_WINDOW        21/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0
SUNDAY_WINDOW        28/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0

You can disable any of the tasks by using the DBMS_AUTO_TASK_ADMIN package. If you want to disable all the automated tasks, issue the following command::
Oracle PL/SQL


EXEC dbms_auto_task_admin.disable;
If you want to enable all the automated tasks, issue the following command:
Oracle PL/SQL


EXEC dbms_auto_task_admin.enable;
You can also disable (or enable) a specific task. For example, you can issue the following command to disable “gather_stats_prog” task:
Oracle PL/SQL

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


SELECT * FROM DBA_AUTOTASK_JOB_HISTORY

for job history

SELECT client_name, status FROM dba_autotask_operation;

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

EXEC dbms_auto_task_admin.enable( 'auto optimizer stats collection',
NULL, NULL );

SELECT client_name, status FROM dba_autotask_operation;

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


You can also use the “Automated Maintenance Tasks” screen of Enterprise Manager to manage these automated tasks.For disabling tasks

Begin
dbms_stats.gather_schema_stats ('SYS');
end;
/
begin
dbms_stats.gather_dictionary_stats;
dbms_stats.gather_fixed_objects_stats;
end;
/


SELECT 'exec dbms_stats.gather_table_stats( ownname => '''||OWNER||''', tabname => '''||TABLE_NAME||''', degree => 6) ;'
  FROM dba_tables
 WHERE owner not in ('ANONYMOUS','APPQOSSYS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TOAD','WMSYS','XDB','XS$NULL')
ORDER BY 1;



'CAARS_DM_USER','DM_USER','DW_USER','ETL','HARTS_DM_USER','HDD_DM_USER','HMA_CONTROL','HMA_CRM_USER','HMA_DM_INCENTIVES','HMA_DM_MARKETING','HMA_DM_SALES','HMA_DM_SERVICE','HMA_DM_SSBI',','HMA_LOOKUP','HMA_ODS','HMA_PROD','HMA_STAGE','HMA_STG_MV_USER','INCENT_DM_USER','LMRS_DM_USER','MKTG_DM_USER','QIS_DM_USER','SALES_DM_USER','SCORE_DM_USER','SED_DM_USER','SSBI_DM_USER','VOC_DM_USER','WIS_DM_USER'


SELECT 'exec dbms_stats.gather_schema_stats( ownname => '''||USERNAME||''') ;' FROM dba_users
 WHERE USERNAME  in ('CAARS_DM_USER','DM_USER','DW_USER','ETL','HARTS_DM_USER','HDD_DM_USER','HMA_CONTROL','HMA_CRM_USER','HMA_DM_INCENTIVES','HMA_DM_MARKETING','HMA_DM_SALES','HMA_DM_SERVICE','HMA_DM_SSBI','HMA_LOOKUP','HMA_ODS','HMA_PROD','HMA_STAGE','HMA_STG_MV_USER','INCENT_DM_USER','LMRS_DM_USER','MKTG_DM_USER','QIS_DM_USER','SALES_DM_USER','SCORE_DM_USER','SED_DM_USER','SSBI_DM_USER','VOC_DM_USER','WIS_DM_USER'
)
ORDER BY 1;


exec dbms_stats.gather_schema_stats( ownname => 'OPACES') ;