Below are three Resource Manager Plan directives:
begin
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'EQ_ALLOCATION',
group_or_subplan => 'STAGE',
mgmt_p2 => 100,
parallel_degree_limit_p1 => 2);
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'EQ_ALLOCATION',
group_or_subplan => 'APPLICATION',
mgmt_p1 => 100,
parallel_degree_limit_p1 => 6);
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'EQ_ALLOCATION',
group_or_subplan => 'OTHER_GROUPS',
comment => '50% Priority for other operation on 2nd level',
mgmt_p2 => 50,
parallel_degree_limit_p1 => 1);
end;
/
below is the code to associate the resource directives with Schemas:
BEGIN
-- Assign users to consumer groups
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'USER1',
consumer_group => 'STAGE',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('USER1', 'STAGE');
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'USER2',
consumer_group => 'MART',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('USER2', 'MART');
END;
/
As per the Oracle documentation, resource available with on mgmt_p1 level with be given to mgmt_p2 processes if needed.
Questions:
1) How to check CPU count/SGA/PGA that are being used by a particular session?
2) Will Oracle resource manager work since the begining of the session or it will kick in once cpu reaches 100% utilization mark? I tried doing some POC on my local.I put a max limit on parallel degree, say 5 and executed a query using parallel(10) hint. Few executions happened with 5 threads, others happened with 10 threads though the consumer group was should the new plan and I had implemented.
3) If I mgmt_p1 process kicks in, when mgmt_p2 process are in session with 100% CPU utilization, how Oracle will make the resources available for mgmt_p1 process?
4) What can I do take the CPU utilization on 100% mark?
1) A single session cannot use more than a single CPU core. (A parallel query will be invoking multiple sessions). To see current PGA, check v$sesstat or v$mystat
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name like '%pga%'
NAME VALUE
-------------------------------------------------------------------------------- ----------
session pga memory 6769336
session pga memory max 34491064
2) It depends. By default, Resource Manager is all about slicing up resources once a machine is hitting 100% (because it makes no sense to cap the CPU if the machine has plenty in reserve). However with "utilization limits" you can restrict groups from getting more than you'd like them to
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-resources-with-oracle-database-resource-manager.html#GUID-DDE2E916-61E2-4754-B398-6AE677DFE44C In terms of when it kicks in - depends if you switched those sessions manually, or relied solely on "set_initial"
3)
If "p2" sessions have all the CPU, then as "p1" sessions come along, then as "p2" sessions continue to make calls, they'll be "toned down" to reduce their CPU. Look for "resmgr: cpu quantum" waits which shows a session being forced to "sleep" a little to allow others to come along grab their CPU slice.
4)
declare
x int;
begin
for i in 1 .. 10000000000 loop
select sqrt(i) into x from dual;
end loop;
end;
will work both SQL and PLSQL engines nice and hard :-)