* Auto task to gather stats is not run On some of our databases, CBO stats are not regularly gathered by Oracle (nor by jobs explicitly scheduled by DBAs). This query select autotask_status from dba_autotask_window_clients; shows 'DISABLED' (even though optimizer_status is 'ENABLED'). The database version is 11.2.0.3 or 11.2.0.4. We DBAs don't recall ever disabling it. On the other hand, status of dba_autotask_client is 'ENABLED'. It baffled us how there could be inconsistency between these two views. It turns out (my understanding!) that dba_autotask_client.status is about each individual task's status, controlled by dbms_auto_task_admin.enable(client_name=>...), where you explicitly specify the "client" name. The dba_autotask_window_clients view is about the overall or general status for all tasks, controlled by dbms_auto_task_admin.enable, running *without* the client_name or any argument. That's what I did to correct our problem, i.e. exec dbms_auto_task_admin.enable Documentation at https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_autotaskadm.htm#ARPLS65448 implies that the enable procedure requires at least the client_name argument. (Ref: http://www.itpub.net/thread-1885054-1-1.html) ---------------------------------------------------------------------------------------------------------------------------------- * From job trace file to relevant views Take 'sql tuning advisor' as example: Trace file oracp41_j003_15798.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production ... *** 2013-02-28 22:25:18.953 *** SESSION ID:(4.4078) 2013-02-28 22:25:18.953 *** CLIENT ID:() 2013-02-28 22:25:18.953 *** SERVICE NAME:(SYS$USERS) 2013-02-28 22:25:18.953 *** MODULE NAME:(DBMS_SCHEDULER) 2013-02-28 22:25:18.953 *** ACTION NAME:(ORA$AT_SQ_SQL_SW_9798) 2013-02-28 22:25:18.953 Incident 64648 created, dump file: /u01/app/oracle/diag/rdbms/oracp4/oracp41/incident/incdir_64648/oracp41_j003_15798_i64648.trc ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [], [], [], [], [] Query dba_scheduler_job_log. Use ACTION NAME for job_name: SQL> select log_date, job_class, operation, additional_info from dba_scheduler_job_log where job_name = 'ORA$AT_SQ_SQL_SW_9798' order by 1; LOG_DATE JOB_CLASS OPERATION ADDITIONAL_INFO ----------------------------------- ---------------- ---------- -------------------------------------------------------------------------------- 28-FEB-13 10.00.01.330687 PM -06:00 ORA$AT_JCNRM_SQ UPDATE STOP_ON_WINDOW_CLOSE="TRUE", PREVIOUS_VALUE="TRUE" 28-FEB-13 10.00.01.365628 PM -06:00 ORA$AT_JCNRM_SQ ENABLE REASON="manually enabled" 28-FEB-13 11.01.07.801135 PM -06:00 ORA$AT_JCNRM_SQ RUN 28-FEB-13 11.01.07.821430 PM -06:00 ORA$AT_JCNRM_SQ COMPLETED REASON="Max runs reached" 28-FEB-13 11.01.07.821930 PM -06:00 ORA$AT_JCNRM_SQ DROP REASON="Auto drop job dropped", PROGRAM_NAME=""SYS"."AUTO_SQL_TUNING_PROG"", JOB Find job class comment for the job class: SQL> select comments from dba_scheduler_job_classes where job_class_name = 'ORA$AT_JCNRM_SQ'; COMMENTS ----------------------- sql tuning advisor Use the above comment as client_name; found the task's status and scheduling window: SQL> select status, attributes, mean_job_duration from dba_autotask_client where client_name = 'sql tuning advisor'; STATUS -------- ATTRIBUTES ------------------------------------------------------------------ MEAN_JOB_DURATION ------------------------------------------------------ ENABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL +000000000 00:09:45.864990689 SQL> select * from dba_scheduler_window_groups where comments = 'sql tuning advisor'; WINDOW_GROUP_NAME ENABL NUMBER_OF_WINDOWS NEXT_START_DATE ------------------------------ ----- ----------------- ------------------------------------ COMMENTS ------------------------------------------------------------------------------------------- ORA$AT_WGRP_SQ TRUE 7 04-MAR-13 10.00.00.000000 PM CST6CDT sql tuning advisor "sql tuning advisor" is one of 3 auto tasks: SQL> select client_name, count(*) from dba_autotask_client_history group by client_name; CLIENT_NAME COUNT(*) ----------------------------------- ---------- auto optimizer stats collection 30 auto space advisor 30 sql tuning advisor 30 To disable "sql tuning advisor": SQL> exec dbms_auto_task_admin.disable('sql tuning advisor', null, null) ---------------------------------------------------------------------------------------------------------------------------------- * To change one day's job schedule (done on 2013-09-05 Thurs): Set to 11pm: SQL> exec dbms_scheduler.set_attribute('thursday_window', 'start_date', '05-SEP-13 11.00.00.000000 PM CST6CDT') PL/SQL procedure successfully completed. Remember to change byhour from 22 to 23, otherwise dba_scheduler_windows.next_start_date remains as 10pm: SQL> exec dbms_scheduler.set_attribute('thursday_window', 'repeat_interval', 'freq=daily;byday=THU;byhour=23;byminute=0; bysecond=0') PL/SQL procedure successfully completed. SQL> select start_date, repeat_interval, next_start_date from dba_scheduler_windows where window_name = 'THURSDAY_WINDOW'; START_DATE -------------------------------------------------------------------- REPEAT_INTERVAL -------------------------------------------------------------------- NEXT_START_DATE ---------------------------------------------------------------- 05-SEP-13 11.00.00.000000 PM CST6CDT freq=daily;byday=THU;byhour=23;byminute=0; bysecond=0 05-SEP-13 11.00.00.000000 PM CST6CDT SQL> select * from dba_scheduler_window_groups; ---------------------------------------------------------------------------------------------------------------------------------- * Multiple DBs on one server If you run multiple databases on one server, you may consider spreading the default 10PM jobs on other times so there won't be memory and CPU usage peaks at the same time. You can change the time for a window (see above). Alternatively, Oracle Doc 2231655.1 suggests you create another window and job (gather_stats_prog as an example) and disable the auto optimizer stats collection auto task. It's a fine solution (but the job name string should be shorter for 11g). To further relieve the 10PM usage pressure, consider disabling the other two auto tasks as well unless you really use their advisories regularly: exec dbms_auto_task_admin.disable('auto space advisor',null,null) exec dbms_auto_task_admin.disable('sql tuning advisor',null,null) Note that if the timezone is not set right, the 10PM default time may be of GMT instead of your local timezone.