* 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.