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.
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;
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;
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’,”);