1 Resource Manager & MIXED_WORKLOAD_PLAN

List all the existing resource plan: DBA_RSRC_PLANS

select  * from DBA_RSRC_PLANS

Show the current resource plan

show parameter resource_manager_plan

 

Change the resource plan

Here are couple ways to change the resource

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';

DBMS_RESOURCE_MANAGER.SWITCH_PLAN

The keyword “FORCE“ and the DBMS_RESOURCE_MANAGER.SWITCH_PLAN  are enforce  the resource plan can not be changed unless sysdba manually use alter system to change the resource plan.

 

Disable the resource plan

To complete disable the resource plan, just se the resource_manager_plan to empty string.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

 

 

MIXED_WORKLOAD_PLAN

Oracle includes the MIXED_WORKLOAD_PLAN which has 2 resource group interactive_group and batch_group. Base on document here and blog post , I should able to simulate the group switch when the user process run over 60 secs.

Despite of many effort, I can not simulate that group switch. I suspect it is because I am running  the oracle on the VM.