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';
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 = '';
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.