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

This article will explain in detail how to modify the task time of Oracle 11g automatic collection of statistical information. Xiaobian thinks it is quite practical, so share it with you as a reference. I hope you can gain something after reading this article.

After Oracle 11g is deployed, statistics are automatically collected from 22:00 to-2: 00 for a total of 4 hours.

However, the traffic is often peak during this time period, which brings a greater burden to the already strained system resources. So, we try to shift the automatic execution time to idle time.

Get the execution time of the current automatic collection statistics

col REPEAT_INTERVAL for a70

select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2

where t1.window_name=t2.window_name and t2.window_group_name in ('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_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00

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 04:00:00

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

--WINDOW_NAME: Task name

--REPEAT_INTERVAL: Task repetition interval time

--DURATION: Duration

One, we're going to increase the duration to eight hours.

BEGIN

dbms_scheduler.disable(

name => 'MONDAY_WINDOW');

dbms_scheduler.set_attribute(

name => 'MONDAY_WINDOW',

attribute => 'DURATION',

value => numtodsinterval(8, 'hour'));

dbms_scheduler.enable(

name => 'MONDAY_WINDOW');

END;

/

So we've adjusted the automatic statistics collection for Monday to eight hours. Then adjust the time from Tuesday to Sunday according to this method.

The same method (broken down into various steps)

1. stop task

BEGIN

DBMS_SCHEDULER.DISABLE(

name=>'"SYS". "FRIDAY_WINDOW"',

force=>TRUE);

END;

2. Modify the duration of the task in minutes

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>'"SYS". "FRIDAY_WINDOW"',

attribute=>'DURATION',

value=>numtodsinterval(480, 'minute'));

END;

3. Start execution time, BYHOUR=22, means start execution at 22:00

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>'"SYS". "FRIDAY_WINDOW"',

attribute=>'REPEAT_INTERVAL',

value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=22;BYMINUTE=0;BYSECOND=0');

END;

4. Start Mission

BEGIN

DBMS_SCHEDULER.ENABLE(

name=>'"SYS". "FRIDAY_WINDOW"');

END;

Adjust the remaining days separately according to the decomposition steps.

after the adjustment

col REPEAT_INTERVAL for a70

select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2

where t1.window_name=t2.window_name and t2.window_group_name in ('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 08:00:00

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

WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 08:00:00

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

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

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

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

About "Oracle 11g automatic collection of statistical information task time how to modify" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people 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.