Shulou(Shulou.com)05/31 Report--

This article is about what to do if Oracle 11g cannot collect statistics automatically. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

SQL > select client_name,task_name,operation_name,status from dba_autotask_task;-- query the dba_autotask_task view with no results returned at all.

No rows selected

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 select WINDOW_NAME, WINDOW_NEXT_TIME, WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME;-- the state of each window is also fine.

WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE

-

TUESDAY_WINDOW 30-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED

WEDNESDAY_WINDOW 31-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED

THURSDAY_WINDOW 01-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED

FRIDAY_WINDOW 02-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED

SATURDAY_WINDOW 03-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED

SUNDAY_WINDOW 04-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED

MONDAY_WINDOW 05-FEB-18 03.00.00.000000 PM PRC FALSE ENABLED

SQL > select client_name,status from dba_autotask_client;-- strangely enough, querying this view does automatically turn on the settings for collecting statistics.

CLIENT_NAME STATUS

Auto optimizer stats collection ENABLED

Auto space advisor ENABLED

Sql tuning advisor ENABLED

It is also normal for SQL > select window_name,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;-- to automatically invoke the window.

WINDOW_NAME AUTOTASK

MONDAY_WINDOW ENABLED

TUESDAY_WINDOW ENABLED

WEDNESDAY_WINDOW ENABLED

THURSDAY_WINDOW ENABLED

FRIDAY_WINDOW ENABLED

SATURDAY_WINDOW ENABLED

SUNDAY_WINDOW ENABLED

At this point, the question is clearer. My settings for automatic collection of statistics in 11g are normal, but why not collect statistics automatically? That is to say, why does the query dba_autotask_task view have no task to execute?

Finally, the problem was solved by asking for help from all parties. The reasons are as follows:

SQL > select window_name,active from DBA_SCHEDULER_WINDOWS

WINDOW_NAME ACTIV

MONDAY_WINDOW FALSE

TUESDAY_WINDOW FALSE

WEDNESDAY_WINDOW FALSE

THURSDAY_WINDOW TRUE

FRIDAY_WINDOW FALSE

SATURDAY_WINDOW FALSE

SUNDAY_WINDOW FALSE

WEEKNIGHT_WINDOW FALSE

WEEKEND_WINDOW FALSE

Normally all windows should be in the false state. But we now have a true, maybe for this reason. This window is the window for Thursday, and then find out when the last time statistics were collected automatically.

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 EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW (' THURSDAY_WINDOW')

Inquire again

SQL > select window_name,active from DBA_SCHEDULER_WINDOWS

WINDOW_NAME ACTIV

MONDAY_WINDOW FALSE

TUESDAY_WINDOW FALSE

WEDNESDAY_WINDOW FALSE

THURSDAY_WINDOW FALSE

FRIDAY_WINDOW FALSE

SATURDAY_WINDOW FALSE

SUNDAY_WINDOW FALSE

WEEKNIGHT_WINDOW FALSE

WEEKEND_WINDOW FALSE

It's all flase.

Today is Monday. Now 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

-

MONDAY_WINDOW 29-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED

TUESDAY_WINDOW 30-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED

WEDNESDAY_WINDOW 31-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED

THURSDAY_WINDOW 01-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED

FRIDAY_WINDOW 02-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED

SATURDAY_WINDOW 03-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED

SUNDAY_WINDOW 04-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED

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

Modified successfully

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

-

MONDAY_WINDOW 29-JAN-18 03.00.00.000000 PM PRC FALSE ENABLED

TUESDAY_WINDOW 30-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED

WEDNESDAY_WINDOW 31-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED

THURSDAY_WINDOW 01-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED

FRIDAY_WINDOW 02-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED

SATURDAY_WINDOW 03-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED

SUNDAY_WINDOW 04-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED

7 rows selected.

Check again after running.

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 select client_name,task_name,operation_name,status from dba_autotask_task

CLIENT_NAME TASK_NAME OPERATION_NAME STATUS

Sql tuning advisor AUTO_SQL_TUNING_PROG automatic sql tuning task ENABLED

Auto space advisor auto_space_advisor_prog auto space advisor job ENABLED

Auto optimizer stats collection gather_stats_prog auto optimizer stats job ENABLED

By querying the relevant information [http://blog.itpub.net/235507/viewspace-1137629/] on the Internet, write to Oracle 11g statistics to automatically collect the task to run the process of task call:

1. First, dba_autotask_task-- > dba_autotask_client sets up the automatic execution task.

2. Set up automatic execution jobs according to the time window and resource group

Dba_autotask_client-- > dba_scheduler_window_groups-- > dba_scheduler_windows-- > dba_scheduler_jobs

Dba_autotask_client-- > dba_scheduler_job_classes

Thank you for reading! This is the end of the article on "what to do if Oracle 11g cannot collect statistics automatically". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.