Tag Archives: Force fully used

Managed Resource Manager in Oracle

Managed Resource Manager in Oracle

Resource Manager is used for resource allocation among the many concurrent database sessions as needed. Different job need different resource allocation like ETL job, OLTP server etc
You can set at session level when particular job begins.

Terms used in Resource Manager Configuration
RESOURCE CONSUMER GROUP: Collection of user sessions that are grouped together based on resource requirements. It allocate resources on Resources consumer group not individual session. Default group present in database : SYS_GROUP (session started by SYS or SYSTEM user), DEFAULT_CONSUMER_GROUP (sessions started by user accounts), OTHER_GROUPS (sessions that belong to a consumer group that is not part of active plan)
RESOURCE PLAN: Resource Manage allocate resources to resource plan. One resource plan active at a time. child resource plan directives controls resource allocation for a different consumer group. You can create your own resource plan.
RESOURCE PLAN DIRECTIVE: Resource Manager allocates resources to consumer groups according to the set of resource plan directives.A directive can limit resource allocation to consumer group like CPU, session etc. Each directive references one consumer group.

Example of Resource plan as SALES_PLAN
One active plan SALES_PLAN, its directive having 75% CPU for OLTP Consumer group, 20% for reporting consumer group and 5% CPU for others.

Resouceplanexplain

Handle Privileges for Resource Manager

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(GRANTEE_NAME => 'SCOTT', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', ADMIN_OPTION => FALSE);

Creating a Resource plan

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'SIMPLE_PLAN1',
CONSUMER_GROUP1 => 'MYGROUP1', GROUP1_PERCENT => 75,
CONSUMER_GROUP2 => 'MYGROUP2', GROUP2_PERCENT => 25);
END;
/

It will create the following resource plan:

Consumer Group	Level 1	Level 2	Level 3
--------------  ------- ------- -------
SYS_GROUP	    100%	-	    -
MYGROUP1	    -	    75%	    -
MYGROUP2	    -	    25%	    -
OTHER_GROUPS	-	    -	    100%

Check the current resource plan

SQL> show parameter resource

                                                                                                                       
NAME                                 TYPE        VALUE                                                                      
------------------------------------ ----------- ------------------------------                                             
resource_limit                       boolean     TRUE                                                                       
resource_manager_cpu_allocation      integer     6                                                                          
resource_manager_plan                string      SCHEDULER[0x4446]:DEFAULT_MAINTENANCE_PLAN

Force fully use your Resource plan until you manually change it not effected by scheduler

--Disabling Plan Switches by Oracle Scheduler Windows use force keyword it will not effected by scheduler until you manually change it.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';

Check list and meaning of jobs

SELECT plan,plan_id,status,comments from DBA_RSRC_PLANS;

View displays the currently active resource plan and its subplans.

SELECT name, is_top_plan FROM v$rsrc_plan;

NAME                             IS_TO 
-------------------------------- ----- 
DEFAULT_MAINTENANCE_PLAN         TRUE  

Check timing from the history of Resource plan

SELECT sequence# seq, name plan_name,
to_char(start_time, 'DD-MON-YY HH24:MM') start_time,
to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name
FROM v$rsrc_plan_history;

SEQ PLAN_NAME                START_TIME      END_TIME        WINDOW_NAME        
--- ------------------------ --------------- --------------- -------------------
  1 DEFAULT_PLAN             02-AUG-18 02:08 02-AUG-18 22:08                                                                                                                            
  2 DEFAULT_MAINTENANCE_PLAN 02-AUG-18 22:08                 THURSDAY_WINDOW

Check the running status with SID and resource consumer group

SELECT se.sid sess_id, co.name consumer_group,
se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
FROM v$rsrc_session_info se, v$rsrc_consumer_group co
WHERE se.current_consumer_group_id = co.id;

Check which directives are defined for plans in your database

select plan, group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3
from DBA_RSRC_PLAN_DIRECTIVES
where plan like ('DEFAULT_%')
order by plan, mgmt_p1 desc, mgmt_p2 desc, mgmt_p3 desc;

PLAN                     GROUP_OR_SUBPLAN      MGMT_P1 MGMT_P2 MGMT_P3
------------------------ --------------------- ------- ------- -------
DEFAULT_MAINTENANCE_PLAN SYS_GROUP             75      0       0
DEFAULT_MAINTENANCE_PLAN OTHER_GROUPS          0       50      0
DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK_SUB_PLAN 0       25      0
DEFAULT_MAINTENANCE_PLAN ORA$DIAGNOSTICS       0       25      0
DEFAULT_PLAN             SYS_GROUP             75      0       0
DEFAULT_PLAN             OTHER_GROUPS          0       90      0
DEFAULT_PLAN             ORA$AUTOTASK_SUB_PLAN 0       5       0
DEFAULT_PLAN             ORA$DIAGNOSTICS       0       5       0


Note: DEFAULT_PLAN they are allowed 90% and 5% respectively & DEFAULT_MAINTENANCE_PLAN allowed 75% and 25%

Check in Resource plan Scheduler

select window_name,RESOURCE_PLAN from DBA_SCHEDULER_WINDOWS;

Update a Resource Manager PLAN

Exec DBMS_RESOURCE_MANAGER.UPDATE_PLAN(PLAN => 'DAYTIME',NEW_COMMENT => '50% more CPU resources');

Delete a Resource manager plan

EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'OLTP_PLAN')

Disable the resource manager

1. Set the current resource manager plan to null

alter system set resource_manager_plan='' scope=both

2. Change the active windows to use the null resource manager plan

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

execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

Advertisements