The Oracle Database Resource Manager is a feature that provides granular control of database resources allocated to users, applications, and services. The Oracle Database Resource Manager enables you to manage multiple workloads that are contending for system and database resources.

The DBMS_RESOURCE_MANAGER package is to be used to setup and configure resource manager. The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives. It also provides semantics so that you may group together changes to the plan schema.

Enabling the Database Resource Manager

You can enable the Database Resource Manager by setting the RESOURCE_MANAGER_PLAN initialization parameter. This parameter specifies the top plan, identifying the plan schema to be used for this instance. If no plan is specified with this parameter, the Database Resource Manager is not activated.

You can also activate or deactivate the Database Resource Manage using the ALTER SYSTEM statement.

Getting Started with Resource Manager

The following example is suitable when only CPU resource allocation is needed.

In this scenario, the only resource allocation method is CPU. The plan uses the EMPHASIS_CPU allocation policy (default) and the ROUND_ROBIN scheduling policy (also the default).

After enabling this plan, the total CPU allocation for this database instance will be only 50% of total available. (CPU CAPPING)

Create a consumer group called “Z_TEST_APPL”:

QL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    dbms_resource_manager.clear_pending_area();
  3    dbms_resource_manager.create_pending_area();
  4    dbms_resource_manager.create_consumer_group(
  5       consumer_group => 'Z_TEST_APPL',
  6       comment => 'Z Sample Consumer Group');
  7    dbms_resource_manager.submit_pending_area();
  8  END;
  9  /

PL/SQL procedure successfully completed.

Create a plan called “Z_DEFAULT_PLAN”:

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    dbms_resource_manager.clear_pending_area();
  3    dbms_resource_manager.create_pending_area();
  4    dbms_resource_manager.create_plan(
  5       plan => 'Z_DEFAULT_PLAN',
  6       comment => 'Z Sample Plan');
  7  END;
  8  /

PL/SQL procedure successfully completed.

Create plan directives

For “Z_TEST_APPL” group CPU allocation will be only 30% of total available.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    dbms_resource_manager.create_plan_directive(
  3       plan =>'Z_DEFAULT_PLAN',
  4       comment => 'Limit CPU resource',
  5       group_or_subplan =>'Z_TEST_APPL',
  6       cpu_p1 => 30 );
  7  END;
  8  /

PL/SQL procedure successfully completed.

For “SYS_GROUP” group CPU allocation will be only 15% of total available

SQL> BEGIN
  2    dbms_resource_manager.create_plan_directive(
  3       plan =>'Z_DEFAULT_PLAN',
  4       comment => 'Limit CPU resource',
  5       group_or_subplan =>'SYS_GROUP',
  6       cpu_p1 => 15 );
  7  END;
  8  /

PL/SQL procedure successfully completed.

For OTHER_GROUPS group CPU allocation will be only 5% of total available

SQL> BEGIN
  2    dbms_resource_manager.create_plan_directive(
  3       plan =>'Z_DEFAULT_PLAN',
  4       comment => 'Limit CPU resource',
  5       group_or_subplan =>'OTHER_GROUPS',
  6       cpu_p1 => 5 );
  7  END;
  8  /

PL/SQL procedure successfully completed.

Validate the plan

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    dbms_resource_manager.validate_pending_area();
  3  END;
  4  /

PL/SQL procedure successfully completed.

Submit the plan

SQL> BEGIN
  2    dbms_resource_manager.submit_pending_area();
  3  END;
  4  /

PL/SQL procedure successfully completed.

Now you can perform some useful selects. In this case to validate that Z_TEST_APPL consumer group is present as expected:

SQL> SET LINESIZE 100
SQL> SET PAGESIZE 50
SQL> COL consumer_group FORMAT a30
SQL> COL cpu_method FORMAT a15
SQL> COL status FORMAT a10
SQL> SELECT consumer_group, cpu_method, status
  2    FROM dba_rsrc_consumer_groups
  3   ORDER BY 1;

CONSUMER_GROUP                 CPU_METHOD      STATUS                                               
------------------------------ --------------- ----------                                           
BATCH_GROUP                    ROUND-ROBIN                                                          
DEFAULT_CONSUMER_GROUP         ROUND-ROBIN                                                          
DSS_CRITICAL_GROUP             ROUND-ROBIN                                                          
DSS_GROUP                      ROUND-ROBIN                                                          
ETL_GROUP                      ROUND-ROBIN                                                          
INTERACTIVE_GROUP              ROUND-ROBIN                                                          
LOW_GROUP                      ROUND-ROBIN                                                       
OTHER_GROUPS                   ROUND-ROBIN                                                          
SYS_GROUP                      ROUND-ROBIN                                                          
Z_TEST_APPL                    ROUND-ROBIN                                                          

10 rows selected.

And this one to validate that plan directives are set as expected:

SQL> SET LINESIZE 100
SQL> COL plan FORMAT a15
SQL> COL group_or_subplan FORMAT a15
SQL> SELECT plan,
  2        group_or_subplan,
  3        type,
  4        cpu_p1,
  5        status
  6    FROM dba_rsrc_plan_directives
  7   WHERE plan='Z_DEFAULT_PLAN'
  8   ORDER BY 1,2,3,4;

PLAN            GROUP_OR_SUBPLA TYPE               CPU_P1 STATUS
--------------- --------------- -------------- ---------- ----------
Z_DEFAULT_PLAN  OTHER_GROUPS    CONSUMER_GROUP          5
Z_DEFAULT_PLAN  SYS_GROUP       CONSUMER_GROUP         15
Z_DEFAULT_PLAN  Z_TEST_APPL     CONSUMER_GROUP         30

A future post will address some practical examples to illustrate the Resource Manager functionality.

Link to script that contains the examples in this post.

 

Comment