Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, prashant.

Asked: August 16, 2022 - 9:21 am UTC

Last updated: June 07, 2023 - 7:17 am UTC

Version: 19c

Viewed 100+ times

You Asked

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?

and Connor said...

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 :-)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.