Oracle Stats Gathering During Automated Maintenance Tasks Window

Problem Background
A few days back I faced a problem when I received slowness complaints from end users who use an application based on our 11.1.0.7 database. While investigating, I came to know that at 10:00 AM, a stats gathering job started which lasted for 45 minutes and during this window,
database's response was quite slow (buffer hit ratio was down to 73% because of full table scans during stats calculation on different tables).
Since I monitored it for quite some time, I saw that during stats collection job, DB FILE SCATTERED READ wait event is at the top which is probably because of full table scans by the stats collection procedure; as it needs to read the full table(s) for stats collection. DBMS_SCHEDULER module; running DBMS_STATS was also at the top in my AWR report; for elapsed time. Because of this FULL TABLE SCANS (db file scattered read), my buffer cache hit ratio became poor (because full table scans means that oracle has to store these big tables' blocks in the cache and kicking out other frequently accessed block from the table required by my application running at that time, and if application needs those blocks again, it will require a physical read to fetch them back in the cache). This also means that the stats job data placement in the cache, and my application, both were fighting to put their blocks in the cache.
So I though that this is the time when I should make changes to Oracle's default maintenance windows because there was no low-peak window for my this production database.

How Automated Maintenance Tasks Work

Oracle introduced automatic stats collection on tables/indexes etc. in 10g by having a default scheduler job "GATHER_STATS_JOB". In 11g, Oracle made amendments to this and introduced Automated Maintenance Tasks. These tasks include 1) Optimizer stats gathering, 2) Segment advisor, 3) SQL tuning advisor, to be run during maintenance windows.
7 maintenance windows are there for every day of the week. During week days, window starts at 22:00 and ends at 02:00 (total 4 hours duration). During week-ends, SATURDAY_WINDOW and SUNDAY_WINDOW start at 06:00 and ends next day 02:00 (20 hours).
So what actually happens during week-end windows is that Optimizer stats gathering task and Segment Advisor task run every 4 hours (06:00, 10:00, 14:00, 18:00, 22:00) during the window (and this was the reason which initiated stats calculation at 10:00 AM and my production started performing poorly).

Any DBA can face this problem at any given time because normally DBAs don't want to touch default settings of these automated maintenance tasks, but, sooner or later, the time comes to make adjustments.

Solution
In my case, I just changed the week-end windows (SATURDAY_WINDOW, SUNDAY_WINDOW) so that no automated maintenance task gets initiated during peak hours. I changed start time of these week end windows to 22:00 and duration to 9 hours. So maintenance tasks will be initiated at 22:00, 02:00, 06:00 only.

Check Current Settings
select window_name, repeat_interval, duration from dba_scheduler_windows
where window_name='SATURDAY_WINDOW';

select window_name, repeat_interval, duration from dba_scheduler_windows
where window_name='SUNDAY_WINDOW';

Change Windows Settings


execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0');

exec DBMS_SCHEDULER.set_attribute( name => 'SYS.SATURDAY_WINDOW',  attribute => 'DURATION',  value => numtodsinterval(540, 'minute'));

execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=22;byminute=0;bysecond=0');

exec DBMS_SCHEDULER.set_attribute( name => 'SYS.SUNDAY_WINDOW',  attribute => 'DURATION',  value => numtodsinterval(540, 'minute'));

Check Settings Again

select window_name, repeat_interval, duration from dba_scheduler_windows
where window_name='SATURDAY_WINDOW';

select window_name, repeat_interval, duration from dba_scheduler_windows
where window_name='SATURDAY_WINDOW';

Conclusion
Now my week-end maintenance windows start and end time is during my off peak hours and my application does not suffer any slowness because of these maintenance tasks.
Every DBA should proactively look into these maintenance windows and should adjust according to his own environment.