Resource Manager SQLs on 12c Non-CDB

Helpful SQLs

--if no rows return Resoruce Manager is not managing the CPU 
select name from v$rsrc_plan where is_top_plan='TRUE' and cpu_managed='ON';

select plan, cpu_method, status from dba_rsrc_plans order by 1;

select consumer_group,cpu_method, status from dba_rsrc_consumer_groups order by 1;

select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4, status
from dba_rsrc_plan_directives where plan= 'DEFAULT_MAINTENANCE_PLAN' order by 1,2,3,4,5,6 desc;

select * from dba_rsrc_plan_directives where plan= 'DEFAULT_MAINTENANCE_PLAN' order by 1,2,3,4,5,6 desc;

select * from dba_rsrc_plan_directives where plan= 'ORA$AUTOTASK_PLAN' order by 1,2,3,4,5,6 desc;


-- check any resoruce manager plan applied on the database instace 
select * from v$parameter where name like '%manager%';

-Monitoring Instance caging 
--The throttling effect of Resource Manager can be displayed using the CONSUMED_CPU_TIME and CPU_WAIT_TIME columns of the following views.

SELECT name, consumed_cpu_time, cpu_wait_time FROM v$rsrc_consumer_group;

select begin_time, consumer_group_name,cpu_consumed_time,cpu_wait_time from v$rsrcmgrmetric_history order by begin_time desc;

SELECT * FROM v$rsrc_consumer_group;

-- here are the resource plans available
SELECT * FROM dba_rsrc_plans;

-- apply a plan to the database 
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = DEFAULT_MAINTENANCE_PLAN;

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_maintenance_plan;

-- Apply a Newly created plan 
alter system set resource_manager_plan='AUTOSTATS_CPU_SWITCH';

-- check default or current resource consumer group of DB users 
select username, initial_rsrc_consumer_group from dba_users;

--disable resource manager on fly and set back to defaults

alter system set resource_manager_plan='';

SELECT group_or_subplan,
 cpu_p1,
 mgmt_p1,
 mgmt_p2,
 mgmt_p3,
 mgmt_p4,
 mgmt_p5,
 mgmt_p6,
 mgmt_p7,
 mgmt_p8,
 max_utilization_limit
 FROM dba_rsrc_plan_directives
 WHERE plan = (SELECT name
 FROM v$rsrc_plan
 WHERE is_top_plan = 'TRUE');

-- last 60 min log

SELECT TO_CHAR (m.begin_time, 'HH:MI') time,
 m.consumer_group_name,
 m.cpu_consumed_time / 60000 avg_running_sessions,
 m.cpu_wait_time / 60000 avg_waiting_sessions,
 d.mgmt_p1
 * (SELECT VALUE
 FROM v$parameter
 WHERE name = 'cpu_count')
 / 100
 allocation
 FROM v$rsrcmgrmetric_history m, dba_rsrc_plan_directives d, v$rsrc_plan p
 WHERE m.consumer_group_name = d.group_or_subplan AND p.name = d.plan
ORDER BY m.begin_time, m.consumer_group_name;