Thanks for the question, oracle.
Asked: April 21, 2017 - 5:03 am UTC
Last updated: April 22, 2017 - 1:57 pm UTC
Version: 11.2.0
Viewed 1000+ times
You Asked
Hi team,
Every Saturday DB Server CPU Become very high after to restart the db also it become high i checked awr where i found "cpu quantum" event then i check
SQL> select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
I disabled this
SQL> select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor DISABLED
sql tuning advisor DISABLED
But again some days later same issue faced So, i checked again where i found resource_manager_plan is scheduled my doubt is
I have disbaled above three scheduer then why resource_manager_plan is running and what it does??
then i changed the resource manager plan
SQL> show parameter resource_manager
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_cpu_allocation integer 24
resource_manager_plan string SCHEDULER[0x3008]:DEFAULT_MAIN
TENANCE_PLAN
then i disabled -
SQL> alter system set resource_manager_plan='' scope=both;
System altered.
And it works fine now we are not facing same issue.
My questions are -
1. I have disabled above three scheduers then why same issue came just because resource manager plan was scheduled If yes then they are disabled how they works ?
2. I have disabled resource_manager plan will it impact on db ?
3. Tuesday i checked alert log where i seen
Thu Apr 20 22:00:00 2017
Setting Resource Manager plan SCHEDULER[0x3006]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Fri Apr 21 02:00:00 2017
Clearing Resource Manager plan via parameter
Why it is showing like this ?
SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
resource_manager_cpu_allocation integer 24
resource_manager_plan string
Thanks
and Connor said...
There is a difference between the *tasks* that will run in the maintenance window, and the maintenance window itself. The windows exist even if there are no tasks defined (or all are disabled)
SQL> select * from DBA_SCHEDULER_WINDOWS
2 @pr
==============================
OWNER : SYS
WINDOW_NAME : MONDAY_WINDOW
RESOURCE_PLAN : DEFAULT_MAINTENANCE_PLAN
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : CALENDAR
START_DATE :
REPEAT_INTERVAL : freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0
END_DATE :
DURATION : +000 04:00:00
WINDOW_PRIORITY : LOW
NEXT_START_DATE : 24-APR-17 10.00.00.000000 PM AUSTRALIA/PERTH
LAST_START_DATE : 17-APR-17 10.00.00.054000 PM AUSTRALIA/PERTH
ENABLED : TRUE
ACTIVE : FALSE
MANUAL_OPEN_TIME :
MANUAL_DURATION :
COMMENTS : Monday window for maintenance tasks
==============================
OWNER : SYS
WINDOW_NAME : TUESDAY_WINDOW
RESOURCE_PLAN : DEFAULT_MAINTENANCE_PLAN
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : CALENDAR
START_DATE :
REPEAT_INTERVAL : freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0
END_DATE :
DURATION : +000 04:00:00
WINDOW_PRIORITY : LOW
NEXT_START_DATE : 25-APR-17 10.00.00.000000 PM AUSTRALIA/PERTH
LAST_START_DATE : 18-APR-17 10.00.00.069000 PM AUSTRALIA/PERTH
ENABLED : TRUE
ACTIVE : FALSE
MANUAL_OPEN_TIME :
MANUAL_DURATION :
COMMENTS : Tuesday window for maintenance tasks
==============================
OWNER : SYS
WINDOW_NAME : WEDNESDAY_WINDOW
RESOURCE_PLAN : DEFAULT_MAINTENANCE_PLAN
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : CALENDAR
START_DATE :
REPEAT_INTERVAL : freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0
END_DATE :
DURATION : +000 04:00:00
WINDOW_PRIORITY : LOW
NEXT_START_DATE : 26-APR-17 10.00.00.000000 PM AUSTRALIA/PERTH
LAST_START_DATE : 19-APR-17 10.00.00.149000 PM AUSTRALIA/PERTH
ENABLED : TRUE
ACTIVE : FALSE
MANUAL_OPEN_TIME :
MANUAL_DURATION :
COMMENTS : Wednesday window for maintenance tasks
==============================
OWNER : SYS
WINDOW_NAME : THURSDAY_WINDOW
RESOURCE_PLAN : DEFAULT_MAINTENANCE_PLAN
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : CALENDAR
START_DATE :
REPEAT_INTERVAL : freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0
END_DATE :
DURATION : +000 04:00:00
WINDOW_PRIORITY : LOW
NEXT_START_DATE : 27-APR-17 10.00.00.000000 PM AUSTRALIA/PERTH
LAST_START_DATE : 20-APR-17 10.00.00.110000 PM AUSTRALIA/PERTH
ENABLED : TRUE
ACTIVE : FALSE
MANUAL_OPEN_TIME :
MANUAL_DURATION :
COMMENTS : Thursday window for maintenance tasks
==============================
OWNER : SYS
WINDOW_NAME : FRIDAY_WINDOW
RESOURCE_PLAN : DEFAULT_MAINTENANCE_PLAN
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : CALENDAR
START_DATE :
REPEAT_INTERVAL : freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0
END_DATE :
DURATION : +000 04:00:00
WINDOW_PRIORITY : LOW
NEXT_START_DATE : 28-APR-17 10.00.00.000000 PM AUSTRALIA/PERTH
LAST_START_DATE : 21-APR-17 10.00.00.014000 PM AUSTRALIA/PERTH
ENABLED : TRUE
ACTIVE : FALSE
MANUAL_OPEN_TIME :
MANUAL_DURATION :
COMMENTS : Friday window for maintenance tasks
==============================
OWNER : SYS
WINDOW_NAME : SATURDAY_WINDOW
RESOURCE_PLAN : DEFAULT_MAINTENANCE_PLAN
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : CALENDAR
START_DATE :
REPEAT_INTERVAL : freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0
END_DATE :
DURATION : +000 20:00:00
WINDOW_PRIORITY : LOW
NEXT_START_DATE : 22-APR-17 06.00.00.000000 AM AUSTRALIA/PERTH
LAST_START_DATE : 22-APR-17 06.00.00.164000 AM AUSTRALIA/PERTH
ENABLED : TRUE
ACTIVE : TRUE
MANUAL_OPEN_TIME :
MANUAL_DURATION :
COMMENTS : Saturday window for maintenance tasks
==============================
OWNER : SYS
WINDOW_NAME : SUNDAY_WINDOW
RESOURCE_PLAN : DEFAULT_MAINTENANCE_PLAN
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : CALENDAR
START_DATE :
REPEAT_INTERVAL : freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0
END_DATE :
DURATION : +000 20:00:00
WINDOW_PRIORITY : LOW
NEXT_START_DATE : 23-APR-17 06.00.00.000000 AM AUSTRALIA/PERTH
LAST_START_DATE : 16-APR-17 06.00.00.079000 AM AUSTRALIA/PERTH
ENABLED : TRUE
ACTIVE : FALSE
MANUAL_OPEN_TIME :
MANUAL_DURATION :
COMMENTS : Sunday window for maintenance tasks
==============================
OWNER : SYS
WINDOW_NAME : WEEKNIGHT_WINDOW
RESOURCE_PLAN :
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : CALENDAR
START_DATE :
REPEAT_INTERVAL : freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
END_DATE :
DURATION : +000 08:00:00
WINDOW_PRIORITY : LOW
NEXT_START_DATE : 08-MAR-17 10.00.00.000000 PM US/CENTRAL
LAST_START_DATE :
ENABLED : FALSE
ACTIVE : FALSE
MANUAL_OPEN_TIME :
MANUAL_DURATION :
COMMENTS : Weeknight window - for compatibility only
==============================
OWNER : SYS
WINDOW_NAME : WEEKEND_WINDOW
RESOURCE_PLAN :
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : CALENDAR
START_DATE :
REPEAT_INTERVAL : freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
END_DATE :
DURATION : +002 00:00:00
WINDOW_PRIORITY : LOW
NEXT_START_DATE : 11-MAR-17 12.00.00.000000 AM US/CENTRAL
LAST_START_DATE :
ENABLED : FALSE
ACTIVE : FALSE
MANUAL_OPEN_TIME :
MANUAL_DURATION :
COMMENTS : Weekend window - for compatibility only
PL/SQL procedure successfully completed.
You can adjust, shrink or remove maintenance windows using the scheduler
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tasks004.htm#ADMIN12335