0 Replies Latest reply: Oct 16, 2012 6:48 AM by 877440 RSS

    auto optimizer stats collection enabled, but not running and not showing up

    877440
      I enabled auto optimizer stats collection days ago, but it never ran. DB version is 11gr2, OS is redhat 5. It shows enabled in dba_autotask_client, but not in autotask_task. Please help on this issue.


      SQL> select client_name, status from dba_autotask_client;

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

      SQL> select task_name, status, to_char(last_good_date, 'YYYY-MM-DD HH24:MI:SS') last_good_date, last_good_duration
      from dba_autotask_task
      where client_name = 'auto optimizer stats collection'; 2 3

      no rows selected

      SQL> select program_action, number_of_arguments, enabled
      from dba_scheduler_programs
      where owner = 'SYS'
      and program_name = 'GATHER_STATS_PROG'; 2 3 4

      PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABL

      dbms_stats.gather_database_stats_job_proc 0 TRUE

      SQL> select w.window_name, c.autotask_status, c.optimizer_stats, w.repeat_interval, w.enabled
      -- , w.duration, w.last_start_date, w.next_start_date
      2 from dba_autotask_window_clients c , dba_scheduler_windows w
      3 4 where c.window_name = w.window_name
      5 order by last_start_date desc;

      WINDOW_NAME AUTOTASK OPTIMIZE REPEAT_INTERVAL ENABL

      MONDAY_WINDOW ENABLED ENABLED freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 TRUE
      SUNDAY_WINDOW ENABLED ENABLED freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 TRUE
      SATURDAY_WINDOW ENABLED ENABLED freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 TRUE
      FRIDAY_WINDOW ENABLED ENABLED freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 TRUE
      THURSDAY_WINDOW ENABLED ENABLED freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 TRUE
      WEDNESDAY_WINDOW ENABLED ENABLED freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 TRUE
      TUESDAY_WINDOW ENABLED ENABLED freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 TRUE

      7 rows selected.

      SQL>