Oracle DBA – A lifelong learning experience

Resource Manager plans during the automatic maintenance window

Posted by John Hallas on October 19, 2011

Whilst ensuring that Resource manager was working properly I noticed a problem that it seemed to be dropping out of the plan I wanted to run ( DW_PLAN) and returning to the default plan whilst in the automatic maintenance windows.

The fix for that is to set the parameter resource_manager =’FORCE:DW_PLAN’ and the DW_PLAN is retained. The reason why the default plan is started off is so that  scheduler knows it has sufficient resources to get the job done and it will not be artificially constrained. If your plan does not limit the scheduler required resources then there is no harm in making your normal plan the plan for 24*7

I had noticed that  the plan was defaulting from a couple of entries in the alert log but wanted to get an exact listing of what was happening. I used the xml logs that came with the ADR package and the X$DBGALERTEXT view.

The syntax was easy to work out and produced a much better report than that delivered by using grep against the alert log (text version)

set lines 125 pages 0
col when form a20
col message_text form a95
select substr(originating_timestamp,1,15) when , message_text from x$dbgalertext where originating_timestamp > (sysdate - 10) and  message_text like '%Resource%’; 

07-OCT-11 23.00      Setting Resource Manager plan SCHEDULER[0x2C57]:DEFAULT_MAINTENANCE_PLAN via scheduler window
07-OCT-11 23.00      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
08-OCT-11 03.00      Setting Resource Manager plan DW_PLAN via parameter
08-OCT-11 07.00      Setting Resource Manager plan SCHEDULER[0x2C58]:DEFAULT_MAINTENANCE_PLAN via scheduler window
08-OCT-11 07.00      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
09-OCT-11 03.00      Setting Resource Manager plan DW_PLAN via parameter
09-OCT-11 07.00      Setting Resource Manager plan SCHEDULER[0x2C59]:DEFAULT_MAINTENANCE_PLAN via scheduler window
09-OCT-11 07.00      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
10-OCT-11 03.00      Setting Resource Manager plan DW_PLAN via parameter
10-OCT-11 23.00      Setting Resource Manager plan SCHEDULER[0x2C53]:DEFAULT_MAINTENANCE_PLAN via scheduler window
10-OCT-11 23.00      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
11-OCT-11 03.00      Setting Resource Manager plan DW_PLAN via parameter
11-OCT-11 23.00      Setting Resource Manager plan SCHEDULER[0x2C54]:DEFAULT_MAINTENANCE_PLAN via scheduler window
11-OCT-11 23.00      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
12-OCT-11 03.00      Setting Resource Manager plan DW_PLAN via parameter
12-OCT-11 23.00      Setting Resource Manager plan SCHEDULER[0x2C55]:DEFAULT_MAINTENANCE_PLAN via scheduler window
12-OCT-11 23.00      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
13-OCT-11 03.00      Setting Resource Manager plan DW_PLAN via parameter
13-OCT-11 23.00      Setting Resource Manager plan SCHEDULER[0x2C56]:DEFAULT_MAINTENANCE_PLAN via scheduler window
13-OCT-11 23.00      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

I knew I had resolved this on the pre-production environment by submitting some scheduler attribute commands but I could not remember the format until I came across MoS note 786364.1 which reminded me how I did it last time.

Before

First of all change the scheduler windows to use the DW_PLAN (or other nonrestrictive plan) using:

execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','DW_PLAN');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','DW_PLAN');

--Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:

execute dbms_scheduler.set_attribute('<window name>',' DW_PLAN ','');
execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','DW_PLAN');
execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','DW_PLAN');
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','DW_PLAN');
execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','DW_PLAN');
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','DW_PLAN');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','DW_PLAN');
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','DW_PLAN');

 

After

 The proof is in the pudding  –  the change was made on the 18th October and the required plan stays in place

17-OCT-11 08.47      Setting Resource Manager plan DW_PLAN via parameter
17-OCT-11 23.00      Setting Resource Manager plan SCHEDULER[0x2C53]:DEFAULT_MAINTENANCE_PLAN via scheduler window
17-OCT-11 23.00      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
18-OCT-11 03.00      Setting Resource Manager plan DW_PLAN via parameter
18-OCT-11 23.00      Setting Resource Manager plan SCHEDULER[0x2C54]:DW_PLAN via scheduler window
18-OCT-11 23.00      Setting Resource Manager plan DW_PLAN via parameter
19-OCT-11 03.00      Setting Resource Manager plan DW_PLAN via parameterset

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: