Worked in Database technology for fixed the issues faced in daily activities in Oracle, Sql Server, MySQL etc.

Tag Archives: DEFAULT_MAINTENANCE_PLAN

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 ConfigurationRESOURCE 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.

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;