Hi,
Env details :- RHEL 7 , 12.2.0.1 Jul 2018 RU, 2 Node RAC
We had observed a behavior post implementation that multiple calls to stats
during the maintenance window which was due to bug (*Bug 26724511
<https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=2387466.1&id=26724511.8>
:
AUTO OPTIMIZER STATS RUN MULTIPLE JOBS DURING MAINTENANCE WINDOWS*). Hence
we decided to disable auto gather stats and space advisor jobs and set up
manual scheduler job for gather stats on daily basis. Please find code below
--Disable auto optimizer stats collection and auto space advisor
begin
DBMS_AUTO_TASK_ADMIN.disable(client_name=>'auto space
advisor',operation=>NULL,window_name=>NULL);
end;
/
begin
DBMS_AUTO_TASK_ADMIN.disable(client_name=>'auto optimizer stats
collection',operation=>NULL,window_name=>NULL);
end;
/
-- create weekdays schedule
Begin
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'MANUALSTATS_WEEKDAYS_SCHEDULE',
start_date => NULL, -- If start_date is null, then the date that the
job or window is enabled is used.
repeat_interval => 'freq=daily;byhour=22;byminute=0;bysecond=0',
end_date => NULL,
comments => 'starts every day at 10:00 pm');
End;
/
-- create weekdays window
Begin
DBMS_SCHEDULER.CREATE_WINDOW (
window_name => 'MANUALSTATS_WEEKDAYS_WINDOW',
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
schedule_name => 'MANUALSTATS_WEEKDAYS_SCHEDULE',
duration => '0 06:00:00',
window_priority => 'HIGH',
comments => 'window opens every day at 10:00 pm and ends at 04:00
am');
End;
/
-- create program that runs the stored procedure
Begin
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'STATS_MAUNAL',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC; END;',
number_of_arguments => 0,
enabled => TRUE,
comments => 'This is the program unit that calls the
concerned stored procedure');
End;
/
-- create job
Begin
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'PMP_MANUAL_STATS',
program_name => 'STATS_MAUNAL',
schedule_name => 'MANUALSTATS_WEEKDAYS_SCHEDULE',
enabled => TRUE,
auto_drop => FALSE,
Comments => 'Job that gathers DB stats everyday at 10:00
pm to 04:00 am',
job_style => 'REGULAR');
End;
/
begin
DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'PMP_MANUAL_STATS'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => TRUE);
END;
/
Although we have set stop_on_window_close to true, the job is running
beyond the maintenance window.
*OWNER*
*WINDOW_NAME*
*RESOURCE_PLAN*
*SCHEDULE_OWNER*
*SCHEDULE_NAME*
*SCHEDULE_TYPE*
*START_DATE*
*REPEAT_INTERVAL*
*END_DATE*
*DURATION*
*WINDOW_PRIORITY*
*NEXT_START_DATE*
*LAST_START_DATE*
*ENABLED*
*ACTIVE*
*MANUAL_OPEN_TIME*
*MANUAL_DURATION*
*COMMENTS*
SYS
MONDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
CALENDAR
freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0
+00 04:00:00.000000
LOW
3/30/2020 10:00:00.000000 PM -04:00
3/23/2020 11:59:58.270880 PM -04:00
TRUE
FALSE
Monday window for maintenance tasks
SYS
TUESDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
CALENDAR
freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0
+00 04:00:00.000000
LOW
3/24/2020 10:00:00.000000 PM -04:00
3/17/2020 11:59:58.196259 PM -04:00
TRUE
FALSE
Tuesday window for maintenance tasks
SYS
WEDNESDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
CALENDAR
freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0
+00 04:00:00.000000
LOW
3/25/2020 10:00:00.000000 PM -04:00
3/18/2020 11:59:58.390856 PM -04:00
TRUE
FALSE
Wednesday window for maintenance tasks
SYS
THURSDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
CALENDAR
freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0
+00 04:00:00.000000
LOW
3/26/2020 10:00:00.000000 PM -04:00
3/19/2020 11:59:58.574353 PM -04:00
TRUE
FALSE
Thursday window for maintenance tasks
SYS
FRIDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
CALENDAR
freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0
+00 04:00:00.000000
LOW
3/27/2020 10:00:00.000000 PM -04:00
3/20/2020 11:59:58.093932 PM -04:00
TRUE
FALSE
Friday window for maintenance tasks
SYS
SATURDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
CALENDAR
freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0
+00 20:00:00.000000
LOW
3/28/2020 6:00:00.000000 AM -04:00
3/21/2020 11:59:58.237351 PM -04:00
TRUE
FALSE
Saturday window for maintenance tasks
SYS
SUNDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
CALENDAR
freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0
+00 20:00:00.000000
LOW
3/29/2020 6:00:00.000000 AM -04:00
3/22/2020 11:59:58.729725 PM -04:00
TRUE
FALSE
Sunday window for maintenance tasks
SYS
WEEKNIGHT_WINDOW
CALENDAR
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+00 08:00:00.000000
LOW
10/16/2018 10:00:00.000000 PM -04:00
FALSE
FALSE
Weeknight window - for compatibility only
SYS
WEEKEND_WINDOW
CALENDAR
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
+02 00:00:00.000000
LOW
10/20/2018 12:00:00.000000 AM -04:00
FALSE
FALSE
Weekend window - for compatibility only
SYS
MANUALSTATS_WEEKDAYS_WINDOW
DEFAULT_MAINTENANCE_PLAN
SYS
MANUALSTATS_WEEKDAYS_SCHEDULE
NAMED
+00 06:00:00.000000
HIGH
3/24/2020 10:00:00.000000 PM +00:00
3/24/2020 10:00:00.295488 PM +00:00
TRUE
TRUE
window opens every day at 10:00 pm and ends at 04:00 am
*LOG_DATE*
*OWNER*
*JOB_NAME*
*JOB_SUBNAME*
*STATUS*
*ERROR#*
*REQ_START_DATE*
*ACTUAL_START_DATE*
*RUN_DURATION*
*INSTANCE_ID*
3/24/2020 6:14:34.583018 AM -04:00
SYS
PMP_MANUAL_STATS
SUCCEEDED
0
3/23/2020 10:00:00.732552 PM -04:00
3/23/2020 10:00:01.101570 PM -04:00
+00 08:14:33.000000
1
3/23/2020 12:00:35.121660 AM -04:00
SYS
PMP_MANUAL_STATS
SUCCEEDED
0
3/22/2020 10:00:00.868242 PM -04:00
3/22/2020 10:00:01.730078 PM -04:00
+00 02:00:33.000000
1
3/21/2020 11:36:47.174682 PM -04:00
SYS
PMP_MANUAL_STATS
SUCCEEDED
0
3/21/2020 10:00:00.471529 PM -04:00
3/21/2020 10:00:00.866283 PM -04:00
+00 01:36:46.000000
2
3/21/2020 12:44:57.617524 AM -04:00
SYS
PMP_MANUAL_STATS
SUCCEEDED
0
3/20/2020 10:00:00.047892 PM -04:00
3/20/2020 10:00:00.470036 PM -04:00
+00 02:44:57.000000
2
3/20/2020 2:26:56.067705 PM -04:00
SYS
PMP_MANUAL_STATS
SUCCEEDED
0
3/19/2020 10:00:00.268441 PM -04:00
3/19/2020 10:00:01.045376 PM -04:00
+00 16:26:55.000000
1
3/19/2020 11:47:48.701722 AM -04:00
SYS
PMP_MANUAL_STATS
SUCCEEDED
0
3/18/2020 10:00:00.912440 PM -04:00
3/18/2020 10:00:01.259249 PM -04:00
+00 13:47:47.000000
2
Could you please let me know if I need to make changes to any of the above
scheduler job creation steps?
Thanks,
Mani
