During APEX Instant Tips #46 Hayden and I recommended, nay, insisted that everyone use a Database Resource Manager plan. During the session we reference Joel Kallman’s seminal blog post on the topic:

https://joelkallman.blogspot.com/2009/08/oracle-database-resource-manager-and.html

While I stand by my recommendation that simply running Joel’s code is a great place to start, I also note that Joel's blog post is from August 2009—fully 13 years ago as of this writing. I recently had the need to address a particular use case, which led to what I believe is an overall improvement to Joel’s resource plan.

Acknowledgments

This blog post would not be possible without Joel’s original post and the many conversations I have had with Joel and others about Resource Manager. This post borrows—and outright copies—a large portion of Joel’s post. Rich Soule also contributed to both the Resource Manager plan and the content of this post. Rich has a GitHub project with much of the same content. I have not compared Rich's plan line by line with the plan below, but they are certainly very very close as we collaborated on this.

Disclaimer

I have extensively reviewed the Oracle Resource Manager documentation and done a significant amount of testing. I wrote this nearly a year ago but was waiting to publish it until after comprehensively monitoring the actions of the database and confirming all of my assertions below. In an effort to get this out the door, though, I am publishing it now, without full confirmation. If anyone wishes to provide evidence that I am mistaken, I am happy to receive the criticism and will update accordingly.

TLDR

If you want to protect your database from individual APEX users grabbing more than a reasonable share of computing resources, jump down to the "Creating a Ratio Plan" section below and run the code to create a Resource Manager plan.

In addition to a Resource Manager plan, you should (nay, must) set your workspace isolation parameters. To do this, log into the INTERNAL workspaces (hopefully you have created your own user and are not using the ADMIN user). Navigate to Manage Instance > Security. Review the help for each Workspace Isolation parameter and set appropriately. Perhaps this is a topic for another post...

If you want additional context, watch APEX Instant Tips episode #46. That's from about a year ago...sorry this has taken so long.

Overview

The resource plan below provides two valuable updates to the plan provided in Joel’s post:

1. Switching from percentage-based resource limits to ratio-based limits

2. The ability to add additional plan directives for specific long-running activities

“Simple” Oracle Resource Manager plans can be percentage-based or ratio-based. A simple plan is a plan without subplans. The resource plan defined in Joel’s post and the plan I propose are both simple plans: Joel’s is a percentage plan while mine is a ratio plan. In both percentage and ratio plans, a resource restriction only becomes enforced when the database utilization is high. Once the utilization is high, the database restricts usage based upon the plan. Percentage-based plans restrict each resource consumer group (RCG) to its percentage of total capacity. Ratio-based plans restrict each RCG to its ratio of the groups that have active membership.

Example Plans

This section explains the reasons why I believe my suggested plan is better. You can skip this section if you are not interested in the background of why to use one type plan over another.

Note: A percentage plan must add to 100, whereas a ratio plan has no such restriction.

Let us assume the database is busy and has 100 active sessions: 90 sessions are in the APEX_HIGH RCG and 10 are in the APEX_LOW RCG. The chart below depicts the database utilization for both plans.

* These RCGs have no membership but nevertheless receive a CPU resource allocation when using a percentage-based plan. They do not receive a resource allocation when using a ratio-based plan and they have no membership.

As the chart indicates, the % plan allocates 28% of the CPU to RCGs that have no membership, essentially wasting available CPU. The ratio plan allocates nearly all of the CPU available, providing more CPU to both the APEX_HIGH and APEX_LOW database sessions.

Creating a ratio plan

The code below is nearly identical to Joel’s code. The differences follow:

1. The plan name

2. The plan is created with mgmt_mth => 'RATIO'

3. The values of mgmt_p1 represent ratios, not percentages, and hence do not need to sum to 100. The ratios were chosen to approximate Joel’s percentages.

-- apex_ratio_plan resource plan
-- Section 1

begin
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();

  dbms_resource_manager.create_consumer_group(
      consumer_group => 'APEX_HIGH',
      comment        => 'All sessions start in this group.');
  dbms_resource_manager.create_consumer_group(
      consumer_group => 'APEX_MEDIUM',
      comment        => 'Sessions are switched to this group after 10 seconds.');     
  dbms_resource_manager.create_consumer_group(
      consumer_group => 'APEX_LOW',
      comment        => 'Any sessions in this group have been executing for more than 120 seconds');

  dbms_resource_manager.submit_pending_area();
end;
/

-- Section 2
begin
    dbms_resource_manager_privs.grant_switch_consumer_group (
        grantee_name => 'APEX_PUBLIC_USER',
        consumer_group => 'APEX_HIGH',
        grant_option => FALSE );
    dbms_resource_manager_privs.grant_switch_consumer_group (
        grantee_name => 'APEX_PUBLIC_USER',
        consumer_group => 'APEX_MEDIUM',
        grant_option => FALSE );
    dbms_resource_manager_privs.grant_switch_consumer_group (
        grantee_name => 'APEX_PUBLIC_USER',
        consumer_group => 'APEX_LOW',
        grant_option => FALSE );
end;
/

begin
   -- Section 3
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.create_plan( plan => 'APEX_RATIO_PLAN',
        comment => 'APEX Plan using ratios', mgmt_mth => 'RATIO');

  -- Plan Directives Section     
  dbms_resource_manager.create_plan_directive(
      plan => 'APEX_RATIO_PLAN',
      group_or_subplan => 'APEX_HIGH',
      comment => 'All sessions start in this group.',
      mgmt_p1 => 40, -- ALL sessions start with the highest ratio (40x of APEX_LOW)
      switch_group => 'APEX_MEDIUM',
      switch_time => 10,
      switch_for_call => TRUE,
      switch_estimate => FALSE );
  dbms_resource_manager.create_plan_directive(
      plan => 'APEX_RATIO_PLAN',
      group_or_subplan => 'APEX_MEDIUM',
      comment => 'Sessions are switched to this group after 10 seconds.',
      mgmt_p1 => 5, -- Sessions switch to a medium ratio (5x of APEX_LOW)
      switch_group => 'APEX_LOW',
      switch_time => 120,
      switch_for_call => TRUE,
      switch_estimate => FALSE );

  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_RATIO_PLAN',
      group_or_subplan => 'APEX_LOW',
      comment => 'Any sessions in this group have been executing for more than 120 seconds',
      mgmt_p1 => 1, -- Sessions switch to the lowest ratio
      switch_group => 'CANCEL_SQL',
      switch_time => 1800,  -- sessions are canceled after 1800 seconds
      switch_for_call => TRUE,
      switch_estimate => FALSE );

  -- Section 4
  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_RATIO_PLAN',
      group_or_subplan => 'OTHER_GROUPS',
      comment => 'The mandatory group',
      mgmt_p1 => 5);

  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_RATIO_PLAN',
      group_or_subplan => 'ORA$AUTOTASK',
      comment => 'Sub plan for maintenance activity',
      mgmt_p1 => 5 );

  -- Section 5
  dbms_resource_manager.set_initial_consumer_group(
      user => 'APEX_PUBLIC_USER',
      consumer_group => 'APEX_HIGH');

  dbms_resource_manager.submit_pending_area();
end;
/

-- Section 6
begin
  dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_RATIO_PLAN');
  dbms_scheduler.set_attribute( name => 'TUESDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_RATIO_PLAN');
  dbms_scheduler.set_attribute( name => 'WEDNESDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_RATIO_PLAN');
  dbms_scheduler.set_attribute( name => 'THURSDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_RATIO_PLAN');
  dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_RATIO_PLAN');
  dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_RATIO_PLAN');
  dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_RATIO_PLAN');
end;
/

-- Section 7
alter system set resource_manager_plan = 'APEX_RATIO_PLAN' scope=both;

A description of the sections above can be found on Joel’s blog post:

https://joelkallman.blogspot.com/2009/08/oracle-database-resource-manager-and.html

Adding a plan directive for specific routines

A benefit of using a ratio plan is that you can define additional RCGs and directives that only have CPU allocation if there is a session that is part of the RCG. For example, you may have a process that you know will typically take 2 minutes and you don’t want it to be pushed into a lower RCG during the time that it runs. In this scenario, you can define an additional RCG, a plan directive that specifies its ratio, and a consumer group mapping that moves the session into this RCG while the process runs.

While there are many ways to move a session into a RCG, a common method is to set the module and action of a session to a defined value using dbms_application_info.set_module. As a developer, the code below can be used within an APEX page process or any pl/sql to set the module and action of a session.

begin
    dbms_application_info.set_module(
        module_name => 'INSUM_HIGH',
        action_name => 'IMPORTANT');
end;

Note: You may wish to capture the current module_name and action_name prior to setting them. Then, upon completing your task set them back to what they were.

declare
l_module    varchar2(4000);
l_action    varchar2(4000);
begin
   dbms_application_info.read_module ( 
        module_name => l_module, 
        action_name => l_action);    

    dbms_application_info.set_module(
        module_name => 'INSUM_HIGH',
        action_name => 'IMPORTANT');

    -- do your important thing here

    dbms_application_info.set_module(
        module_name => l_module,
        action_name => l_action);
end;

The code below creates a RCG, plan directive, and consumer group mapping. Resource Manager will recognize when a session has the module INSUM_HIGH and action IMPORTANT, move the session into the INSUM_30 RCG, and give a ratio of 30. If the session takes more than 600 seconds, it will cancel the SQL.

-- Example for adding resources to specific code by specifying session MODULE and ACTION
begin
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.create_consumer_group(
      consumer_group => 'INSUM_30',
      comment        => 'Group to speed up important process.');
  dbms_resource_manager.submit_pending_area();         
  dbms_resource_manager_privs.grant_switch_consumer_group (
    grantee_name => 'APEX_PUBLIC_USER',
    consumer_group => 'INSUM_30',
    grant_option => FALSE );       
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_RATIO_PLAN',
      group_or_subplan => 'INSUM_30',
      comment => 'INSUM_30 sessions get higher priority. Cancel after 600 seconds.',
      mgmt_p1 => 30,
      switch_group => 'CANCEL_SQL',
      switch_time => 600,
      switch_for_call => TRUE,
      switch_estimate => FALSE );

  dbms_resource_manager.set_consumer_group_mapping(
    attribute => dbms_resource_manager.MODULE_NAME_ACTION,
    value => 'INSUM_HIGH.IMPORTANT',
    consumer_group => 'INSUM_30');

  dbms_resource_manager.submit_pending_area();   
end;
/

With the additional resource plan directive above, sessions that have a session MODULE of INSUM_HIGH and ACTION of IMPORTANT will be assigned to the RCG IMSUM_30. These sessions will share a ratio of 30.

Let’s modify our example of 100 active sessions to include 80 APEX_HIGH, 10 APEX_LOW and 10 INSUM_30

As the chart indicates, each APEX_HIGH session continues to have significant resources (although reduced) while APEX_LOW is reduced significantly. The INSUM_30 sessions rightfully receive high priority.

Useful Resource Manager Code Snippets

Below are some code blocks that are helpful when working with Resource Manager.

-- utility and sample blocks

begin
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
end;
/

begin
  dbms_resource_manager.validate_pending_area();
end;
/

begin
  dbms_resource_manager.submit_pending_area();
end;
/

begin
  dbms_resource_manager.delete_plan_directive(
      plan=> 'APEX_RATIO_PLAN',
      group_or_subplan => 'INSUM_30');     
end;
/

Workspace Isolation

If you didn't read the TLDR, go back and read the part about Workspace Isolation :)