a collection of scripts to ease the day-to-day work of an Oracle DBA
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
izzy 89666adc3c resman: add example SQL to readme 3 years ago
OraResourceManager_Setup.sql adding Resource Manager helper (in resman/) 3 years ago
OraResourceManager_SetupLoadTest.sql adding Resource Manager helper (in resman/) 3 years ago
OraResourceManager_TestMappingsCreate.sql adding Resource Manager helper (in resman/) 3 years ago
OraResourceManager_TestMappingsRemove.sql adding Resource Manager helper (in resman/) 3 years ago
README.md resman: add example SQL to readme 3 years ago


Oracle 12c Resource Manager

The Resource Manager can be configured via Enterprise Manager Cloud Control:

  • Login to Cloud Control
  • Login to the target database
  • Goto Administration › Resource Manager

You will be presented with a "quick list" of actions to perform:

1. Define/Configure "Consumer Groups"

These are used to group users/applications/processes/services to manage resources for. Several groups are already pre-defined, some of them mandatory (M). The mandatory groups are used Oracle internally and thus cannot be removed. The others don't hurt if they stay, but could be removed for a "cleaner look" if wanted.

  • M: SYS_GROUP: Admins
  • (DSS_[CRITICAL_]GROUP: Decision Support System / DWH "Ad Hoc Queries")
  • (ETL_GROUP: "Extraction, Transformation, Loading")
  • INTERACTIVE_GROUP: interactive / OLTP operations?
  • (LOW_GROUP: low priority sessions)
  • M: (ORA$APPQOS_[0-7]: Application QOS)
  • M: ORA$AUTOTASK: Automated Database Maintenance Tasks (https://docs.oracle.com/database/121/ADMIN/tasks.htm#ADMIN0235)
  • BATCH_GROUP: Batch operations
  • M: DEFAULT_CONSUMER_GROUP: "Everyone else"
  • the specific group OTHER_GROUPS stands for everything not specifically "grouped" in the current plan

2. Define/Configure "Consumer Group Mappings"

This means setting up rules how to map user sessions to "consumer groups".

  • Already mapped: "Oracle User" SYS, SYSTEM ==> SYS_GROUP
  • Already defined: Several unmapped "Attributes" like "Client Program", "Client OS User", "Client Machine". None of them has a "Value" yet. Select an Attribute (radio button) and hit "Add Rule" (button) lets you define candidates and map them to "Consumer Groups"

3. Define/Configure Resource Plans

  • several plans are already available, but only one is active: INTERNAL_PLAN, which means "disable Resource Manager"
  • Selecting a radio button and hitting the appropriate button lets you Edit, Delete, Activate/Deactivate, Clone a plan or show the SQL for the existing Plan.
  • Clicking the name of a plan (link) shows the current definition of a plan, from where it can be edited, activated etc. as well
  • you can e.g. define:
    • idle time: disconnect when idle for X seconds in general, or disconnect when idly blocking another session for X seconds
    • runaway query: if a query runs too long, temporarily switch the session to a different consumer group / cancel query / kill session. "Too long" can be elapsed time / CPU time / I/O amount (MB or number of requests)
    • degree of parallelism
  • "Level" gives priority, so values may not exceed 100 (percent) vertically. A lower level only gets resources the higher level does not consume. Example: In APPQOS_PLAN, "Level 1" only holds SYS_GROUP with "75". So if a SYS_GROUP member consumes 75% resources, that leaves only 25% for levels 2-8. "Level 2" shares remaining resources between ORA$APPQOS_[1-3] according to the percentage defined, and so on.
  • Plans can be enabled manually or automatically (the latter eg. via "Scheduler Windows")
  • each plan can hold a maximum of 28 "Consumer Groups" (but one can use "Subplans" within a plan)

4. Monitor Performance Statistics

With the resource manager inactive, there's only OTHER_GROUPS and no data available :) But as soon as you've defined a plan and activated it, you can check here if it works.

Doing it manually

A confusing fact I observed: "Show DDL" doesn't seem to correspond to the settings in the GUI. See e.g. for the APPQOS_PLAN and try to find the values for "Level 2"; there is no "57", no "26", no "12", and nothing set to "5" – values that should be seen for "mgmt_p2". Still, there is "mgmt_p1 => 75" for SYS_GROUP as one would expect. And funnily, ORA$APPQOS_[0-3] show up as ORA%APPQOS_[0-3]?

I cross-checked that with a test setup, confirmed. So I'm not sure if changes made via the GUI can be trusted, if they'd run the same DDL. So you better do your definitions via PL/SQL scripts, which also makes them reproducible. Which is what the files in this directory are about. Further, to make things look clearer, they start with a "fresh plate" and use their own plan – ignoring the pre-defined examples.

So here's what the example script found in this directory is going to set up as a Resource Manager plan called ERM_PLAN:

  • "Prio 1 (DBAs, SYSOPER)" ==> SYS_GROUP (already there), 100%
    DBAs should get all the resources they need – and even take them away from some heavy-load session potentially blocking the database. What they don't need gets handed down to lower Prios – which during normal operation should be the entire 100% as they won't be active.
  • "Prio 2 (Application Admins)" ==> ERM_ADMIN_GROUP, 20%
    "Prio 2 (Applikations and Services)" ==> ERM_APPLICATION_GROUP, 80% This second level is for the application, which we want to be responsive. Guarantee the application admins a chance to get in (20% of the resources), and give all remaining resources to the applications and services. Again, during "normal operation", this means applications and services get all the resources (if they need them). As usual, what remains is handed down.
  • "Prio 3 (Batch processes) ==> ERM_BATCH_GROUP, 50%
    "Prio 3 (External users) ==> ERM_EXTERNALS_GROUP, 50%
    Batch processes have a lower priority and shouldn't "steal" resources from applications. In our example, the same applies to "external access" e.g. via database links, which have to stand back behind the applications' needs. Each of the two groups can draw on 50% of the remaining resources at the same time (or more if the other doesn't need them).
  • "Prio 4 (Others not explicitly defined)" ==> [OTHER_GROUPS] (automatically), 100%
    Everything that wasn't defined can only draw on what's left. This could e.g. apply to "personal users" accessing the database via TOAD, SQLDeveloper, SQLPlus etc. Putting them here means if someone manually fires a "rogue query" that shouldn't draw too heavily on sessions assigned to Prio 1-3.

Looking at the example code you will notice users are assigned to consumer groups by different means. The example code certainly doesn't cover all possibilities, but some are used:

  • by ORACLE USER: sessions connecting directly to that schema are covered by this.
  • by OS USER: depending on what user started the session on operating system level.
  • by CLIENT PROGRAM: depending on how the client application identifies itself at the database. You can check this in V$SESSION.PROGRAM.

Additionally, the example defines a "max idle time" in the plan for OTHER_GROUPS, e.g.:

  • "Max Idle Time (sec)": 86400 (24h) (i.e. disconnect after being idle for 24h)
  • "Max Idle Time if Blocking Another Session (sec)": 1800 (30min) (disconnect after being idle for 30min while blocking another session)

Side-Effect of this: If e.g. some "forgotten" Toad session blocks DB deployment or the application itself (by locking a resource), that session gets killed after (at latest) 30min, and the deployment can continue (without manual interference).

Example code

Take a look into OraResourceManager_Setup.sql to see the example code referenced in this document. It also has a little documentation inside to explain each single step. Some stuff not mentioned there comes here:

Simple Plan

Instead of the "little more complex" setup, you could also use the CREATE_SIMPLE_PLAN statement. Note that simple plan is purely CPU based with no extras:

  /* Create a resource plan. Note that this automatically validates and submits the pending area!
   * Specified resource groups will be created (if they do not already exist), but will have
   * no describing comment attached (you need to create them beforehand).

Change the "priority ordering" for mappings

Possibly not needed, but in case of conflicting mapping rules you can adjust them:

    EXPLICIT => 1,
    MODULE_NAME => 5,
    SERVICE_NAME => 6,
    ORACLE_USER => 7,
    CLIENT_OS_USER => 9,
    CLIENT_ID => 11

Testing how your plan works

For a simple test, take a look at OraResourceManager_SetupLoadTest.sql. Instructions are right at the top of the file. What it does is basically:

  • Create a simple "GLOBAL TEMPORARY" table
  • Create a stored procedure that inserts a bunch of rows into that table, commits and then truncates it again.
  • Creates scheduler jobs for defined users to fire in parallel, so all are competing for the resources of your database.

If you have an Enterprise Manager watching over your database (see the initial chapter of this document), you can now monitor how the Resource Manager works ("Monitor Performance Statistics").

Test mappings

Take a look at the files OraResourceManager_TestMappingsCreate.sql and OraResourceManager_TestMappingsRemove.sql to see how you can add/remove users to/from your consumer groups.

View/Inspect your configuration using SQL

If you don't have Oracle Enterprise Manager or any other graphical interface providing Resource Plan features, you might wish to investigate existing setups using a standard SQL client. You can do so: there are the DBA_RSRC_* and V$RSRC_* database views you can query, as described in the Oracle documentation e.g. here; please see there for details. Picking some examples:

Consumer Groups

Which Consumer Groups are defined, and who has access to them:

COL grantee FOR a18
COL granted_group FOR a25
SELECT * FROM dba_rsrc_consumer_group_privs;

------------------ ------------------------------ ------------ -------------
PUBLIC             DEFAULT_CONSUMER_GROUP         YES          YES
PUBLIC             LOW_GROUP                      NO           NO
SCOTT              MARKETING                      NO           NO
SCOTT              SALES                          YES          YES
SYSTEM             SYS_GROUP                      NO           YES

In short: Oracle user SCOTT always starts in the SALES group and can switch to MARKETING (direct grant) as well as DEFAULT_CONSUMER_GROUP and LOW GROUP (PUBLIC). He seems to be some manager in SALES, as he also can grant access to that group and to the DEFAULT_CONSUMER_GROUP to other users.

Current Consumer Groups for sessions:

COL username FOR a30
SELECT sid,serial#,username,resource_consumer_group
  FROM v$session
 WHERE username IS NOT NULL;

-----  -------  ------------------------  --------------------------------
   11       136 SYS                       SYS_GROUP
   13     16570 SCOTT                     SALES

USERNAME IS NOT NULL just excludes Oracle background sessions.

Resource Plans

Which plans are defined:

COL plan FOR a27
COL status FOR a8
COL comments FOR a140
SET lines 200
SET pages 50
SELECT plan,status,comments FROM dba_rsrc_plans;

PLAN                        STATUS   COMMENTS
--------------------------- -------- ----------------------------------------
DSS_PLAN                             Example plan for DSS workloads that prio...
ETL_CRITICAL_PLAN                    Example plan for DSS workloads that prio...
MIXED_WORKLOAD_PLAN                  Example plan for a mixed workload that p...
DEFAULT_MAINTENANCE_PLAN             Default plan for maintenance windows tha...
DEFAULT_PLAN                         Default, basic, pre-defined plan that pr...
INTERNAL_QUIESCE                     Plan for quiescing the database.  This p...
INTERNAL_PLAN                        Internally-used plan for disabling the r...

Shows all plans available -- not indicating which one is active. The STATUS column just shows whether a plan is part of the pending area.

Which plans are currently active:

SELECT name, is_top_plan FROM v$rsrc_plan;

NAME                             IS_TO
-------------------------------- -----
INTERNAL_PLAN                    TRUE

In this example, no user-defined plan is active. INTERNAL_PLAN is what Oracle uses when the Resource Manager was not activated. So this looks different when you have plans defined and activated the Resource Manager. In the following example, a multilevel plan is used:


System altered.

SELECT name, is_top_plan FROM v$rsrc_plan;

NAME             IS_TOP_PLAN

Plans with Consumer Groups

COL group_or_subplan FOR a30
SELECT group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3, mgmt_p4
  FROM dba_rsrc_plan_directives WHERE plan = 'DSS_PLAN';

GROUP_OR_SUBPLAN                  MGMT_P1    MGMT_P2    MGMT_P3    MGMT_P4
------------------------------ ---------- ---------- ---------- ----------
SYS_GROUP                              75          0          0          0
DSS_CRITICAL_GROUP                     18          0          0          0
DSS_GROUP                               3          0          0          0
ETL_GROUP                               1          0          0          0
BATCH_GROUP                             1          0          0          0
ORA$AUTOTASK                            1          0          0          0
OTHER_GROUPS                            1          0          0          0

DSS_PLAN is the plan pre-defined by Oracle. Replace that by which plan you want to show.


As one would expect, this can be done via the V$RSRC_* performance views.

Resources used:

SELECT name, active_sessions, queue_length, consumed_cpu_time, cpu_waits, cpu_wait_time
 FROM v$rsrc_consumer_group;

------------------ --------------- ------------ ----------------- ---------- -------------
OLTP_ORDER_ENTRY                 1            0             29690        467          6709
OTHER_GROUPS                     0            0           5982366       4089         60425
SYS_GROUP                        1            0           2420704        914         19540
DSS_QUERIES                      4            2           4594660       3004         55700

All of the statistics are cumulative from the time when the plan was activated. CPU_WAIT_TIME indicates the total time that sessions in the consumer group waited for CPU because of resource management. Not included in this measure are waits due to latch or enqueue contention, I/O waits, and so on.

Current sessions:

SELECT se.sid sess_id, co.name consumer_group, se.state, se.consumed_cpu_time
       cpu_time, se.cpu_wait_time, se.queued_time
  FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id;

------- ------------------ -------- --------- ------------- -----------
    113 OLTP_ORDER_ENTRY   WAITING     137947         28846           0
    135 OTHER_GROUPS       IDLE        785669         11126           0
    124 OTHER_GROUPS       WAITING      50401         14326           0
    114 SYS_GROUP          RUNNING        495             0           0
    102 SYS_GROUP          IDLE         88054            80           0
    147 DSS_QUERIES        WAITING     460910        512154           0

CPU_WAIT_TIME in this view has the same meaning as in the V$RSRC_CONSUMER_GROUP view in previous query, but applied to an individual session.

Resource Plan History:

COL seq FOR 9999
COL start_time FOR a15
COL end_time FOR a15
COL window_name FOR a30
SELECT sequence# seq, name plan_name,
       to_char(start_time, 'DD-MON-YY HH24:MM') start_time,
       to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name
  FROM v$rsrc_plan_history;

  SEQ PLAN_NAME                      START_TIME      END_TIME        WINDOW_NAME
----- ------------------------------ --------------- --------------- ------------------------------
    1                                05-SEP-19 14:09 05-SEP-19 22:09
    2 DEFAULT_MAINTENANCE_PLAN       05-SEP-19 22:09 06-SEP-19 02:09 THURSDAY_WINDOW
    3                                06-SEP-19 02:09 06-SEP-19 22:09
    4 DEFAULT_MAINTENANCE_PLAN       06-SEP-19 22:09 07-SEP-19 02:09 FRIDAY_WINDOW
    5                                07-SEP-19 02:09 07-SEP-19 06:09
    6 DEFAULT_MAINTENANCE_PLAN       07-SEP-19 06:09 08-SEP-19 02:09 SATURDAY_WINDOW

This allows you to see what times which plans have been active. If PLAN_NAME is NULL, this means no plan was active.

Consumer Group history:

SELECT sequence# seq, name, cpu_wait_time, cpu_waits, consumed_cpu_time
  FROM v$rsrc_cons_group_history;

----- ------------------------------ ------------- ---------- -----------------
    1 ORA$AUTOTASK                               0          0                 0
    1 OTHER_GROUPS                               0          0                 0
    1 SYS_GROUP                                  0          0                 0
    1 _ORACLE_BACKGROUND_GROUP_                  0          0                 0
    2 ORA$AUTOTASK                               0          0            428210
    2 OTHER_GROUPS                               0          0            118361
    2 SYS_GROUP                                  0          0             24123
    2 _ORACLE_BACKGROUND_GROUP_                  0          0                 0
    3 ORA$AUTOTASK                               0          0                 0
    3 OTHER_GROUPS                               0          0                 2
    3 SYS_GROUP                                  0          0                 0
    3 _ORACLE_BACKGROUND_GROUP_                  0          0                 0

Additional Notes and Readings

Resource Manager

Additional resources