Oracle Database Resource Manager and Oracle Application Express

At ODTUG Kaleidoscope this year, I gave a presentation on using the Oracle Database Resource Manager with Oracle Application Express. The Oracle Database Resource Manager enables an administrator to control the allocation of hardware resources within an Oracle database - something that an OS-based process scheduler cannot accomplish. The actual PowerPoint presentation is here.

I often talk about the scalability of Oracle Application Express on apex.oracle.com, which runs on fairly modest hardware. However, I fully believe that this database and server would eventually crumble if it were not for our use of the Oracle Database Resource Manager. Unlike a database instance which is running a handful of tuned applications, apex.oracle.com is a free-for-all. As long as you have a workspace, you can run whatever SQL you like from SQL Commands (I'm not recommending this), or, you could inadvertently write some pretty inefficient SQL or PL/SQL in your application. Without any controls in place, a user could easily and quickly monopolize the CPU resources on apex.oracle.com. And for a site that gets between 3.5 million and 6 million page views per week, page view requests could quickly back up and overwhelm the database server.

So how do we manage this chaos? Via the Oracle Database Resource Manager. Rather than explain the syntax of the Resource Manager, I think it's much simpler to convey the plan that is in place right now on apex.oracle.com and then comment on it.

-- apex.oracle.com 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 APEX sessions start in this group.');
  dbms_resource_manager.create_consumer_group(
      consumer_group => 'APEX_MEDIUM',
      comment        => 'APEX 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_ORACLE_COM_PLAN', comment => 'APEX Plan');


    
  -- Plan Directives Section      
  dbms_resource_manager.create_plan_directive(
      plan => 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'APEX_HIGH',
      comment => 'All APEX sessions start in this group.',
      mgmt_p1 => 70,
      switch_group => 'APEX_MEDIUM',
      switch_time => 10,
      switch_for_call => TRUE,
      switch_estimate => FALSE );

  dbms_resource_manager.create_plan_directive(
      plan => 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'APEX_MEDIUM',
      comment => 'APEX sessions are switched to this group after 10 seconds.',
      mgmt_p1 => 8,
      switch_group => 'APEX_LOW',
      switch_time => 120,
      switch_for_call => TRUE,
      switch_estimate => FALSE );


    
  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'APEX_LOW',
      comment => 'Any sessions in this group have been executing for more than 120 seconds',
      mgmt_p1 => 2,
      switch_group => 'CANCEL_SQL',
      switch_time => 1800,
      switch_for_call => TRUE,
      switch_estimate => FALSE );
    

  -- Section 4
  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'OTHER_GROUPS',
      comment => 'The mandatory group',
      mgmt_p1 =>10);
    
  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'ORA$AUTOTASK_SUB_PLAN',
      comment => 'Sub plan for maintenance activity',
      mgmt_p1 => 10 );
    
  -- 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_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'TUESDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'WEDNESDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'THURSDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
end;
/


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

• Section 1 - Create three consumer groups with the names APEX_HIGH, APEX_MEDIUM and APEX_LOW. These group names are arbitrary. A consumer group is simply a collection of users.


• Section 2 - Remember that the Oracle HTTP Server and mod_plsql connect to the database as user APEX_PUBLIC_USER (if you're using the embedded PL/SQL Gateway, this is user ANONYMOUS). From a database perspective, all it sees are a bunch of database sessions, connecting as database user APEX_PUBLIC_USER. So in Section 2, we are granting the ability to switch among these consumer groups to database user APEX_PUBLIC_USER.


• Section 3 - Create the plan directives which controls how much of the machine resources is dedicated to a particular consumer group, and how long they are allowed to remain in this group before switching to another group. So if a session is in the APEX_HIGH group, the APEX_HIGH group is entitled to 70% of the CPU resources. If the top-level SQL call is executing for more than 10 "on-CPU" seconds, then they get switched to the APEX_MEDIUM group. The APEX_MEDIUM group gets only 8% of the available CPU resources, for up to 120 seconds. After 120 seconds, the session gets switched to the APEX_LOW group. The APEX_LOW group has only 2% of the CPU resources available. After 1800 seconds (which is a *long* time), if the top-level SQL call is still executing, then it will be canceled. The session will not be killed, but the top-level SQL call will be canceled.


• Section 4 - Directives are created for two other pre-defined and built-in groups, namely, OTHER_GROUPS and ORA$AUTOTASK_SUB_PLAN. The ORA$AUTOTASK_SUB_PLAN is used for all of the automated maintenance tasks of the database (e.g., statistics collection). OTHER_GROUPS is used for any other session not already a part of a group in the active Resource Manager plan. This would be the group for anyone connecting directly to the database, background database jobs not running as APEX_PUBLIC_USER, etc.


• Section 5 - Make APEX_HIGH, our newly created consumer group, the default consumer group for user APEX_PUBLIC_USER. Then, submit the pending area for validation.


• Section 6 - The Oracle database (at least for 11gR1 and 11gR2) ships with a predefined scheduler plan for maintenance tasks. Modify the resource plan in effect during these maintenance windows. If we don't, then the DEFAULT_MAINTENANCE_PLAN would be in effect and not our newly created Resource Manager Plan APEX_ORACLE_COM_PLAN. And what that means is we'd be back to a free-for-all during this maintenance window, with anyone being able to monopolize the server.


• Section 7 - Change the settings of the database to use our newly created plan.

There are tools available in Enterprise Manager to create and monitor resource plans, so you don't have to commit the syntax of all of these PL/SQL packages to memory. Also, there are a large number of database views that can be used to monitor the Resource Manager, including:

• V$RSRC_PLAN – Currently active resource plan


• V$RSRC_CONSUMER_GROUP – Cumulative amount of CPU stats


• V$RSRC_PLAN_HISTORY – History of resource plan, when enabled, disabled or modified


• V$RSRC_CONS_GROUP_HISTORY – History of consumer group statistics


• V$RSRCMGRMETRIC – Information about resources consumed and wait times per consumer group


• V$RSRCMGRMETRIC_HISTORY – History of Resource Manager metrics

I granted privileges on these SYS-owned views to another database user that was mapped to an APEX workspace (actually, my workspace on apex.oracle.com). Then, I was able to easily build an APEX application using Interactive Reports on top of these views and monitor how well we were doing.

The Oracle Database Resource Manager is only available as a part of the Oracle Database Enterprise Edition. It is not available with XE, Standard Edition One, or Standard Edition of the Oracle Database. But for those customers that are already using Oracle Application Express on the Oracle Database Enterprise Edition, and they're attempting to consolidate a number of groups and applications onto a single instance, the Oracle Database Resource Manager can be easily used to prevent any one user or application from monopolizing the server.