Oracle database resource manager

Preamble

Oracle database resource manager (DBRM) helps you to manage and prioritize available resources of your server (CPU, parallelism, execution time, …). It provides more fine grain tuning that what you can implement at Operating System level (CPU is at Unix account level for example). Working on resources allocation at Oracle level allows you to prioritize resource between two Oracle accounts. In advanced configuration you can even filter by machine or program name and/or switch sessions/users from one group to another.

Database resource manager configuration

Obviously you can use the PL/SQL commands but it is quite complex and the basic graphical administration tool that provides the Java release of Enterprise Manager helps a lot in configuration.

Aim of this configuration example is to switch TEST Oracle account to another group if it comes from a particular machine or using a particular program to connect.

Let first create a Resource Consumer Group called EDAPT_GROUP:

database_resource_manager1
database_resource_manager1
database_resource_manager2
database_resource_manager2

The PL/SQL equivalence would be (!!):

BEGIN
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.create_consumer_group(consumer_group => 'EDAPT_GROUP', COMMENT => 'To flavor connection coming from a particular machine or program');
  dbms_resource_manager.submit_pending_area();
  dbms_resource_manager_privs.grant_switch_consumer_group('TEST', 'EDAPT_GROUP', FALSE);
END;

Remark:
This group will not be the default group of TEST account, we have just provided it the capability to switch into it (by default all accounts are in DEFAULT_CONSUMER_GROUP group).

This can be verified with:

SELECT * FROM dba_rsrc_consumer_group_privs;
 
GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
TEST                           EDAPT_GROUP                    NO  NO
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
YJAQUIER                       SYS_GROUP                      NO  YES
SYSTEM                         SYS_GROUP                      NO  YES
PUBLIC                         LOW_GROUP                      NO  NO

Then let’s create a Resource Consumer Plan called EDAPT_PLAN:

database_resource_manager3
database_resource_manager3

Remark:
There is a bug and before creating the plan you need to go in “Group Switching” panel and fill the “switch to group” column with same group name even if the criteria will never be verified (default value should be NULL).

I do not copy/paste the PL/SQL equivalence as damn too complex…

In this situation first CPU level is 100% allocated to SYS_GROUP (SYS, SYSTEM and all DBA accounts), the second level of CPU is equally shared between DEFAULT_CONSUMER_GROUP (all accounts) and EDAPT_GROUP (no pre-configured accounts but we have the capability to switch some sessions inside), then third CPU level is all for OTHER_GROUPS (should not be used but is a mandatory configuration i.e. “consumer group for users not included in any group in the active top-plan”).

Database resource manager testing

Initiate a connect with TEST account and sqlplus.exe tool (the text release of SQL*Plus accessible with a command prompt):

SELECT sid,
  serial#,
  machine,
  terminal,
  program,
  module,
  resource_consumer_group
FROM v$session s
WHERE username='TEST';
 
       SID    SERIAL# MACHINE    TERMINAL   PROGRAM              MODULE               RESOURCE_CONSUMER_GROUP
---------- ---------- ---------- ---------- -------------------- -------------------- --------------------------------
       127      60160            GVADT30511 sqlplus.exe          SQL*Plus             DEFAULT_CONSUMER_GROUP

As expected the account is in DEFAULT_CONSUMER_GROUP group.

Now let’s create a mapping on the client program with (all the *_submit_area procedure are mandatory):

BEGIN
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.client_program,'sqlplus.exe','EDAPT_GROUP');
  dbms_resource_manager.submit_pending_area();
END;
/
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM dba_rsrc_group_mappings;
 
ATTRIBUTE                      VALUE           CONSUMER_GROUP                 STATUS
------------------------------ --------------- ------------------------------ ------------------------------
ORACLE_USER                    SYS             SYS_GROUP
ORACLE_USER                    SYSTEM          SYS_GROUP
ORACLE_USER                    YJAQUIER        SYS_GROUP
CLIENT_PROGRAM                 SQLPLUS.EXE     EDAPT_GROUP
 
4 ROWS selected.

Let’s initiate a new connection with the same tool:

SELECT sid,
  serial#,
  machine,
  terminal,
  program,
  module,
  resource_consumer_group
FROM v$session s
WHERE username='TEST';
 
       SID    SERIAL# MACHINE    TERMINAL   PROGRAM              MODULE               RESOURCE_CONSUMER_GROUP
---------- ---------- ---------- ---------- -------------------- -------------------- --------------------------------
       127      60162            GVADT30511 sqlplus.exe          SQL*Plus             EDAPT_GROUP

As expected session moved automatically to EDAPT_GROUP group.

To delete the switching issue:

BEGIN
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.client_program,'sqlplus.exe',NULL);
  dbms_resource_manager.submit_pending_area();
END;
/

Configuration would be the same for client machine (to switch connection coming from GVADT30511, my desktop):

BEGIN
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.client_machine,'GVADT30511','EDAPT_GROUP');
  dbms_resource_manager.submit_pending_area();
END;
/

Remark:
Many tools like sqlplus.exe, sqlplusw.exe do not set the MACHINE column in V$SESSION table, making impossible the switch on the machine name. To test it it must be handle in your tool, like in SQL*Developer. With those tool when doing below SQL you get nothing:

SQL> SELECT SYS_CONTEXT('USERENV', 'HOST') AS USERENV FROM dual;
 
USERENV
--------------------------------------------------
SELECT sid,
  serial#,
  machine,
  terminal,
  program,
  module,
  resource_consumer_group
FROM v$session s
WHERE username='TEST';
 
       SID    SERIAL# MACHINE    TERMINAL   PROGRAM              MODULE               RESOURCE_CONSUMER_GROUP
---------- ---------- ---------- ---------- -------------------- -------------------- --------------------------------
       159       5739 GVADT30511 unknown    SQL Developer        SQL Developer        EDAPT_GROUP

You can also manually switch a session using:

BEGIN
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.switch_consumer_group_for_sess(140,768,'EDAPT_GROUP');
  dbms_resource_manager.submit_pending_area();
END;
/

Remark:
140 and 768 above are respectively SID and SERIAL# of the session taken from V$SESSION table.

References

  • Troubleshooting Guide: Oracle Resource Manager [ID 975455.1]
  • Creating Resource Plan Fails with ORA-29355 Using Script Generated By OEM [ID 311164.1]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>