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

The editor will share with you how to modify the execution time of the task of automatically collecting statistics in the database. I hope you will gain something after reading this article. Let's discuss it together.

In general, the settings for the oracle11G database automatic statistics collection task are as follows:

1. Default settings of Oracle11g

2. User_tab_modifications tracks the modification of the record table

3. When the data of the analysis object changes by more than 10%, Oracle will re-analyze

4. Timed task gather_stats_job is responsible for scheduled scheduling and collecting statistical information.

5. In Oracle11g, the default time for automatically collecting statistics is:

# from Monday to Friday, start at 22:00 and collect for 4 hours

# Saturday, Sunday, start at 6: 00, collect for 20 hours

-time to view automatic statistics:

Sys@PROD > col WINDOW_NAME for A15

Sys@PROD > col REPEAT_INTERVAL for A60

Sys@PROD > col DURATION for A30

Sys@PROD > set linesize 120

Sys@PROD > SELECT t1.window_name, t1.repeat_interval, t1.duration

2 FROM dba_scheduler_windows t1, dba_scheduler_wingroup_members t2

3 WHERE t1.window_name = t2.window_name

4 AND t2.window_group_name IN

5 ('MAINTENANCE_WINDOW_GROUP',' BSLN_MAINTAIN_STATS_SCHED')

WINDOW_NAME REPEAT_INTERVAL DURATION

-

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

WEDNESDAY_WINDO freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

W

THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 + 000 04:00:00

SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 + 000 20:00:00

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 + 000 20:00:00

7 rows selected.

Remarks:

# freq=daily: collect every day

#; byday=SUN: the week within a week, for example, here is Sunday

# byhour=22: at the time of each day, it's 22:00 here

# byminute=0: the time of the day. Here is 0, then 1

# bysecond=0: the time point in seconds of each day. Here is 0 second, then 1 second.

# + 000 20:00:00: indicates a long time interval for collecting information, which means 20 hours.

-modify the scheduled task time for automatic collection of statistics:

-- stop the original plan first

Sys@PROD > BEGIN

2 DBMS_SCHEDULER.DISABLE (

3 name = >'"SYS". "THURSDAY_WINDOW"')

4 end

5 /

PL/SQL procedure successfully completed.

-- modify the execution time of the scheduled task:

Sys@PROD > BEGIN

2 DBMS_SCHEDULER.SET_ATTRIBUTE (

3 name = >'"SYS". "THURSDAY_WINDOW"'

4 attribute = > 'REPEAT_INTERVAL'

5 value = >

6 'freq=daily;byday=THU;byhour=22;byminute=0;bysecond=0')

7 end

8 /

PL/SQL procedure successfully completed.

-enable the execution time of the new scheduled task:

Sys@PROD > BEGIN

2 DBMS_SCHEDULER.ENABLE (

3 name = >'"SYS". "THURSDAY_WINDOW"')

4 end

5 /

PL/SQL procedure successfully completed.

After reading this article, I believe you have a certain understanding of "how to modify the execution time of automatic statistical information collection tasks in the database". If you want to know more about it, welcome to follow the industry information channel, thank you for your reading!

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.