The database cannot automatically collect statistics.

The database cannot automatically collect statistics.

I posted a question post before, and you may not have encountered it. I have solved this problem recently. So I 'd like to share with you. Http://www.cndba.cn/549974293/problem/48select client_name, task_name, operation_name, status from dba_autotask_task; -- Querying this view has no result returned at all. No rows selectedselect job_name, actual_start_date, status from (select * from dba_scheduler_job_run_details where job_name like 'ora $ AT_ OS _OPT % 'order by log_date desc) where rownum <4; -- the time for the last successful execution to automatically collect statistics is. JOB_NAME ACTUAL_START_DATE STATUS when there are too many ORA $ pai26-sep-12 10.0000007.902100 pm prc runtime $ pai25-sep-12 runtime pm prc runtime $ pai24-sep-12 runtime pm prc runtime WINDOW_NAME, WINDOW_NEXT_TIME, WINDO W_ACTIVE, OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME; -- the status of each window is also normal. WINDOW_NAME WINDOW_NEXT_TIME windo optimize invalid certificate ----- -------- TUESDAY_WINDOW 30-JAN-18 when pm prc false when 31-JAN-18 when pm prc false when 01-FEB-18 when pm prc false when 02-FEB-18 when PM PRC FALS E release 03-FEB-18 release am prc false ENABLEDSUNDAY_WINDOW 04-FEB-18 release am prc false ENABLEDMONDAY_WINDOW 05-FEB-18 release pm prc false enabledsql> select client_name, status from release; -- it is strange to query this view, it is true that the settings for collecting statistics are automatically enabled. CLIENT_NAME STATUS indication -------- auto optimizer stats collection ENABLEDauto space advisor ENABLEDsql tuning advisor ENABLEDselect window_name, autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS; -- the automatic call window is also normal. WINDOW_NAME AUTOTASK certificate -------- MONDAY_WINDOW ENABLEDTUESDAY_WINDOW ENABLEDWEDNESDAY_WINDOW ENABLEDTHURSDAY_WINDOW ENABLEDFRIDAY_WINDOW ENABLEDSATURDAY_WINDOW ENABLEDSUNDAY_WINDOW ENABLED. In 11g, my settings for automatic statistics collection are normal, but why not I automatically collect statistics. -- You may wish to think for yourself. ------ at last, the problem was finally solved by asking for help from all parties. The reason is as follows: SQL> select window_name, active from nation; WINDOW_NAME ACTIV nation ----- MONDAY_WINDOW returns FALSETHURSDAY_WINDOW TRUEFRIDAY_WINDOW FALSESATURDAY_WINDOW FALSESUNDAY_WINDOW returns FALSE. Normally, all windows should be in the false state. However, one of the existing values is true, probably because of this reason. This window looks like a Thursday window, and then looks for the last time the statistics are automatically collected. SQL> select job_name, actual_start_date, status from (select * from dba_scheduler_job_run_details where job_name like 'ora $ AT_ OS _OPT % 'order by log_date desc) where rownum <4; JOB_NAME ACTUAL_START_DATE STATUS when running ------------------------------ ORA $ AT_ OS _OPT_SY_2606 26-SEP-12 10.0020.7.902100 PM PRC SUCCEED EDORA $ pai25-sep-12 10.0000007.829792 pm prc succeededora $ AT_ OS _OPT_SY_2566 24-SEP-12 10.0000007.154019 PM PRC SUCCEEDED was found on February 12, September 26. Query the Perpetual calendar. On the 26th, Wednesday. Thursday turned to true, probably because the database was not properly shut down when collecting statistics. Change the value of true to false. Note that if your production environment has not automatically collected statistics for a long time, it is best to collect statistics in the testing environment for a spa report. Otherwise, production may be affected. EXECUTE optimize ('Thursday _ window'); query SQL again> select window_name, active from nation; WINDOW_NAME ACTIV =----- MONDAY_WINDOW returns FALSETHURSDAY_WINDOW FALSEFRIDAY_WINDOW returns FALSESUNDAY_WINDOW returns FALSE to flase. Today is Monday. Change the time for collecting statistics on Monday. SQL> select WINDOW_NAME, WINDOW_NEXT_TIME, WINDOW_ACTIVE, OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME; WINDOW_NAME WINDOW_NEXT_TIME windo optimize ---------------------------- certificate ----- -------- MONDAY_WINDOW 29-JAN-18 10.00.00.000000 pm prc false ENABLEDTUESDAY_WINDOW 30-JAN-18 10.00.00.000000 Prc false values 31-JAN-18 values pm prc false values 01-FEB-18 values pm prc false values 02-FEB-18 values pm prc false values 03-FEB-18 values am prc false values 04-FEB-18 values am prc false ENABLED7 rows selected. modify the automatic collection time window. SQL> begin 2 dbms_scheduler.disable (name => '"SYS ". "MONDAY_WINDOW" ', force => true); 3 end; 4/PL/SQL procedure successfully completed. SQL> begin 2 dbms_scheduler.set_attribute (name => '"SYS ". "MONDAY_WINDOW" ', attribute => 'Repeat _ interval', value => 'freq = daily; byday = mon; byhour = 15; byminute = 0; bysecond = 0 '); 3 end; 4/PL/SQL procedure successfully completed. SQL> begin 2 dbms_scheduler.enable (name => '" SYS ". "MONDAY_WINDOW" '); 3 end; 4/PL/SQL procedure successfully completed. succeeded in modifying SQL> select WINDOW_NAME, WINDOW_NEXT_TIME, WINDOW_ACTIVE, OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME; WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE limit ----- -------- MONDAY_WINDOW 29-JAN-18 When pm prc false when 30-JAN-18 10.00.00.000000 pm prc false when 31-JAN-18 when pm prc false when 01-FEB-18 when pm prc false ENABLEDFRIDAY_WINDOW 02-FEB-18 when pm prc false when 03-FEB-18 then AM prc false ENABLEDSUNDAY_WINDOW 04-FEB-18 06.00.00.000000 AM PRC FALSE ENA BLED7 rows selected. query again after running. Select job_name, actual_start_date, status from (select * from dba_scheduler_job_run_details where job_name like 'ora $ AT_ OS _OPT % 'order by log_date desc) where rownum <4; JOB_NAME ACTUAL_START_DATE STATUS when starting ------------------------------ ORA $ AT_ OS _OPT_SY_2626 29-JAN-18 03.00.00.732062 PM PRC STOPPEDORA $ T_ OS _OPT_SY_2606 26-SEP-12 10.0000007.902100 pm prc succeededora $ AT_ OS _OPT_SY_2586 25-SEP-12 10.0000007.829792 PM PRC SUCCEEDED is indeed because of this window problem. The normal status of DBA_SCHEDULER_WINDOWS should be "false". When the system calls the DBA_SCHEDULER_WINDOWS, it turns to "true". Because the database is not properly closed, the window has not changed. Therefore, all database collection jobs are disconnected. Query again: select client_name, task_name, operation_name, status from dba_autotask_task; CLIENT_NAME TASK_NAME OPERATION_NAME STATUS already exist -------- SQL tuning advisor AUTO_ SQL _TUNING_PROG automatic IC SQL tuning task ENABLEDauto space advi Sor auto_space_advisor_prog auto space advisor job ENABLEDauto optimizer stats collection gather_stats_prog auto optimizer stats job ENABLED saw someone on the internet writing the process of calling a job at 11g. First, dba_autotask_task --> dba_autotask_client creates an automatic execution task, and then creates an automatic execution job dba_autotask_client --> schedule --> dba_scheduler_windows --> schedule --> Schedule. At first, I did not notice that, as a dba, it was still very far away. Continue to work hard in the future.