8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 8i » Here

Oracle Resource Manager

Oracle Resource Manager is an Enterprise Edition feature that provides a set of PL/SQL APIs that allow the DBA to assign a priority to sessions, making sure that the most important transactions get the major share of system resources. In this article I'll present a simple example for a hydrid system that comprises of high priority web based OLTP transactions and low priority batch processing.

Related articles.

First we create a web and a batch user for the test.

CREATE USER web_user identified by web_user
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

GRANT CONNECT TO web_user;

CREATE USER batch_user identified by batch_user
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

GRANT CONNECT TO batch_user;

In order to set up a resource plan a pending area has to be created. This is simply a working area where the plan can be defined and validated before it is applied to the server. The following examples show a breakdown of setting up the resource plan, ending with the complete plan definition. Remember, only a complete and valid plan can be applied to the server so don't try and run these commands individually.

First we create a pending area.

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;
END;
/

Next we create a plan.

BEGIN
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'hybrid_plan',
    comment => 'Plan for a combination of high and low priority tasks.');
END;
/

Then we create a web and a batch consumer group.

BEGIN
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'WEB_CG',
    comment        => 'Web based OTLP processing - high priority');

  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'BATCH_CG',
    comment        => 'Batch processing - low priority');
END;
/

Then we assign the consumer groups to the plan and indicate their relative priority, remembering to add the OTHER_GROUPS plan directive.

BEGIN
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                     => 'hybrid_plan',
    group_or_subplan         => 'web_cg',
    comment                  => 'High Priority',
    cpu_p1                   => 80,
    cpu_p2                   => 0,
    parallel_degree_limit_p1 => 4);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                     => 'hybrid_plan',
    group_or_subplan         => 'batch_cg',
    comment                  => 'Low Priority',
    cpu_p1                   => 0,
    cpu_p2                   => 80,
    parallel_degree_limit_p1 => 4);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'hybrid_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'all other users - level 3',
    cpu_p1           => 0,
    cpu_p2           => 0,
    cpu_p3           => 100);
END;
/

Finally we validate and apply the resource plan.

BEGIN
  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

To define the complete plan we do something like this.

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  -- Create plan
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'hybrid_plan',
    comment => 'Plan for a combination of high and low priority tasks.');

  -- Create consumer groups
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'web_cg',
    comment        => 'Web based OTLP processing - high priority');

  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'batch_cg',
    comment        => 'Batch processing - low priority');

  -- Assign consumer groups to plan and define priorities
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'hybrid_plan',
    group_or_subplan => 'web_cg',
    comment          => 'High Priority - level 1',
    cpu_p1           => 100,
    cpu_p2           => 0,
    cpu_p3           => 0);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'hybrid_plan',
    group_or_subplan => 'batch_cg',
    comment          => 'Low Priority - level 2',
    cpu_p1           => 0,
    cpu_p2           => 100,
    cpu_p3           => 0);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'hybrid_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'all other users - level 3',
    cpu_p1           => 0,
    cpu_p2           => 0,
    cpu_p3           => 100);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

Now that the plan has been defined and applied to the server we can assign our users to individual consumer groups. A session can be manually switched between consumer groups it has been assigned to, but in the example below we set the default consumer group and assume the session will remain with this for it's lifetime.

BEGIN
  -- Assign users to consumer groups
  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'web_user',
    consumer_group => 'web_cg',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'batch_user',
    consumer_group => 'batch_cg',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('web_user', 'web_cg');

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('batch_user', 'batch_cg');
END;
/

The RESOURCE_MANAGER_PLAN parameter is used to tell the instance which resource plan to use. This can be set in the init.ora file or by using the ALTER SYSTEM command.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'hybrid_plan';

We can now see that sessions connecting via these users are assigned to the correct consumer group.

SQL> SELECT username, resource_consumer_group 
  2  FROM   v$session
  3  WHERE  username IN ('WEB_USER','BATCH_USER');

USERNAME                       RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
BATCH_USER                     BATCH_CG
WEB_USER                       WEB_CG

2 rows selected.

SQL>

Alternatively you could use a single user and switch the consumer group for the current session depending on the type of processing being done. Assuming the user has been assigned the switch privilege for the consumer group this switch is done as follows.

DECLARE
  v_old_cg  VARCHAR2(30);
BEGIN
  DBMS_SESSION.switch_current_consumer_group (
    new_consumer_group     => 'BATCH_CG',
    old_consumer_group     => v_old_cg,
    initial_group_on_error => FALSE);
END;
/

The following views are available to query information about the existing resource plans.

We clean up the example by disabling the plan, then deleting it.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.delete_plan_cascade(
    plan    => 'hybrid_plan');

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.