11G OCM Set up and configure Resource Manager

Contents

  1. 1 References
  2. 2 Overview
  3. 3 The MIXED_WORKLOAD_PLAN Resource Plan
    1. 3.1 Use a Sample Plan
    2. 3.2 Activate Resource Plans
    3. 3.3 Ensure that MIXED_WORKLOAD_PLAN Plan is Active
  4. 4 Experiment in Switching
    1. 4.1 Change the Resource Group Mapping
    2. 4.2 Granting Group Switching Rights
  5. 5 Running the Experiment in Group Switching
    1. 5.1 SQL for Experiment
    2. 5.2 Lower Thresholds for Group Switching
    3. 5.3 Result of Experiment
    4. 5.4 Statistics from the Experiment
  6. 6 Cancel the SQL If It Runs Too Long
    1. 6.1 Update Plan Directive
    2. 6.2 Run the Experiment Again
    3. 6.3 Statistics from the Experiment
  7. 7 Cancel the Session If the SQL Runs Too Long
    1. 7.1 Update Plan Directive
    2. 7.2 Performance Problem
    3. 7.3 Run the Experiment Again
    4. 7.4 Statistics from the Experiment
  8. 8 Restore Resource Plan to Original State
  9. 9 Conclusion


References

Oracle® Database Administrator's Guide 11g Release 1 (11.1)

Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)

Oracle® Database Reference 11g Release 1 (11.1)

Overview

The OCM 11G Upgrade Objective is:

Set up and configure Resource Manager to control active sessions, number of I/Os, execution time..etc

My reading of this and the experience of the 11G exam leads me to say that there are several possible sub-tasks:

  1. Set up Resource Manager
  2. Configure Resource Plans to allocate CPU resources
  3. Control Active Sessions through
    1. Assigning them to Resource Groups
    2. Switching between Resource Groups
    3. Killing the session once resource limits have been exceeded
    4. Killing the SQL once resource limits have been exceeded

The MIXED_WORKLOAD_PLAN Resource Plan

Use a Sample Plan

Rather than create a resource plan from scratch, I used a sample predefined plan called MIXED_WORKLOAD_PLAN from An Oracle-Supplied Mixed Workload Plan .

Although it would be nice to go through the agony of creating a new resource plan via PL/SQL, I think that the OEM interface is sufficiently robust to create a resource plan painlessly.

Activate Resource Plans

According to Enabling Oracle Database Resource Manager and Switching Plans , the system parameter, RESOURCE_MANAGER_PLAN , needs to be set to a valid plan in order for Resource Plans to work: 

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = MIXED_WORKLOAD_PLAN SCOPE = BOTH;

Ensure that MIXED_WORKLOAD_PLAN Plan is Active

The current active plan can be determined in one of two (2) ways. The first is to query the dynamic view, V$RSRC_PLAN , to see what the top plan is: 

SQL> SELECT * FROM v$rsrc_plan;
ID NAME IS_TOP_PLAN CPU_MANAGED
11187 DEFAULT_MAINTENANCE_PLAN TRUE ON
11185 ORA$AUTOTASK_SUB_PLAN FALSE ON
11186 ORA$AUTOTASK_HIGH_SUB_PLAN FALSE ON

Or, by the system parameter, RESOURCE_MANAGER_PLAN , 

SQL> show parameter resource_manager_plan
NAME TYPE VALUE
resource_manager_plan string MIXED_WORKLOAD_PLAN

Or, if the maintenance window is currently active, the output is:

NAME TYPE VALUE
resource_manager_plan string SCHEDULER[0x2C0B]:DEFAULT_MAINTENANCE_PLAN

Experiment in Switching

I want to see the switching between resource groups in action. The chosen resource plan, MIXED_WORKLOAD_PLAN , switches between the INTERACTIVE_GROUP and the BATCH_GROUP when limits are exceeded.

The following SQL is a resource hog: 

select sum( dbms_random.value() ) from dual connect by level < 10000000;

The obvious user is SH who will need access to the following groups:

  1. INTERACTIVE_GROUP and;
  2. BATCH_GROUP

Change the Resource Group Mapping

Initially, the user, SH , is mapped to the DEFAULT_CONSUMER_GROUP : 

SQL> SELECT username, initial_rsrc_consumer_group FROM dba_users WHERE username = 'SH';
USERNAME INITIAL_RSRC_CONSUMER_GROUP
SH DEFAULT_CONSUMER_GROUP

Since the resource plan, MIXED_WORKLOAD_PLAN , does not mention the group, DEFAULT_CONSUMER_GROUP , explictly, users in this group are mapped to the rule for OTHER_GROUPS in the plan.

Instead of running in the OTHER_GROUPS , the user, SH , will be mapped to the INTERACTIVE_GROUP and thereby be affected by possible group switching. This is done through the SET_CONSUMER_GROUP_MAPPING procedure: 

BEGIN
    dbms_resource_manager.clear_pending_area();
    dbms_resource_manager.create_pending_area();
    dbms_resource_manager.set_consumer_group_mapping(
        dbms_resource_manager.oracle_user,
        'SH',
        'INTERACTIVE_GROUP'
    );
    dbms_resource_manager.submit_pending_area();
END;

Checking the initial resource consumer group is now correct via the DBA_USERS view: 

SQL> SELECT username, initial_rsrc_consumer_group FROM dba_users WHERE username = 'SH';
USERNAME INITIAL_RSRC_CONSUMER_GROUP
SH INTERACTIVE_GROUP

The user, SH , has to be granted access to the following groups:

  1. INTERACTIVE_GROUP ; and
  2. BATCH_GROUP .

Without giving the permission to switch into the BATCH_GROUP , we get an error as follows when I try using the SWITCH_CURRENT_CONSUMER_GROUP procedure: 

SQL> connect sh/sh
Connected.
SQL> VARIABLE old_consumer_group VARCHAR2(32)
SQL> EXEC DBMS_SESSION.switch_current_consumer_group( 'BATCH_GROUP', :old_consumer_group, TRUE )
Attempting to switch to BATCH_GROUP from current group
BEGIN DBMS_SESSION.switch_current_consumer_group( 'BATCH_GROUP', :old_consumer_group, TRUE ); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 130
ORA-06512: at line 1

Granting Group Switching Rights

According to Granting and Revoking the Switch Privilege ,:

Using the DBMS_RESOURCE_MANAGER_PRIVS PL/SQL package, you can grant or revoke the switch privilege to a user, role, or PUBLIC. The switch privilege enables a user or application to switch a session to a specified resource consumer group. It also enables the database to automatically switch a session to a consumer group specified in a session-to–consumer group mapping rule or specified in the SWITCH_GROUP parameter of a resource plan directive.

Emphasis Mine

Thus, I need to grant the privilege to switch into the target groups to the SH user through the GRANT_SWITCH_CONSUMER_GROUP Procedure procedure as follows: 

BEGIN
  dbms_resource_manager_privs.grant_switch_consumer_group( 'SH', 'INTERACTIVE_GROUP', FALSE );
  dbms_resource_manager_privs.grant_switch_consumer_group( 'SH', 'BATCH_GROUP', FALSE );
END;
/

Now, I test the ability of the SH user to switch between groups by again using the SWITCH_CURRENT_CONSUMER_GROUP procedure: 

SQL> connect sh/sh
Connected.
SQL> select initial_rsrc_consumer_group from user_users;

INITIAL_RSRC_CONSUMER_GROUP
------------------------------
INTERACTIVE_GROUP

SQL> VARIABLE old_consumer_group VARCHAR2(32)
SQL> exec DBMS_SESSION.switch_current_consumer_group( 'BATCH_GROUP', :old_consumer_group, TRUE )

PL/SQL procedure successfully completed.

SQL> print old_consumer_group

OLD_CONSUMER_GROUP
--------------------------------------------------------------------------------


SQL> exec DBMS_SESSION.switch_current_consumer_group( 'BATCH_GROUP', :old_consumer_group, TRUE )

PL/SQL procedure successfully completed.

SQL> print old_consumer_group

OLD_CONSUMER_GROUP
--------------------------------------------------------------------------------


SQL> exec DBMS_SESSION.switch_current_consumer_group( 'INTERACTIVE_GROUP', :old_consumer_group, TRUE )

PL/SQL procedure successfully completed.

SQL> print old_consumer_group

OLD_CONSUMER_GROUP
--------------------------------------------------------------------------------
BATCH_GROUP

Note: The first switch says that the old consumer group is NULL even though the initial resource consumer group is not.

Running the Experiment in Group Switching

SQL for Experiment

The SQL to be tested is a guaranteed CPU hog: 

select sum( dbms_random.value() ) from dual connect by level < 10000000;

Lower Thresholds for Group Switching

The supplied resource plan, MIXED_WORKLOAD_PLAN , switches to BATCH_GROUP after 60 seconds of CPU. I want to lower the switching threshold to five (5) CPU seconds by using UPDATE_PLAN_DIRECTIVE procedure: 

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (
     plan                          => 'MIXED_WORKLOAD_PLAN', 
     group_or_subplan              => 'INTERACTIVE_GROUP', 
     new_switch_time               => 5,
     new_switch_for_call           => TRUE); 
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

Result of Experiment

Confimed that the SH user is in the correct initial resource group: 

select resource_consumer_group from v$session where username = 'SH';
RESOURCE_CONSUMER_GROUP
INTERACTIVE_GROUP

Run the SQL: 

SQL> select sum( dbms_random.value() ) from dual connect by level < 10000000;

SUM(DBMS_RANDOM.VALUE())
------------------------
              4998828.87

Elapsed: 00:01:58.72

Statistics from the Experiment

The data from the dynamic view, V$RSRC_CONS_GROUP_HISTORY , shows the group switching having occurred because the SQL exceeded the CPU quantum: 

SELECT
    name,
    requests,
    consumed_cpu_time,
    switches_in_cpu_time,
    switches_out_cpu_time,
    sql_canceled,
    active_sess_killed
  FROM
    v$rsrc_cons_group_history
  WHERE
      sequence# =
        (SELECT MAX(sequence#) FROM V$RSRC_CONS_GROUP_HISTORY)
    AND name NOT LIKE 'ORA%'
    AND name <> '_ORACLE_BACKGROUND_GROUP_'
  ORDER BY name;
NAME REQUESTS CONSUMED_CPU_TIME SWITCHES_IN_CPU_TIME SWITCHES_OUT_CPU_TIME SQL_CANCELED ACTIVE_SESS_KILLED
BATCH_GROUP 0 6168608 2 1 0 0
INTERACTIVE_GROUP 2 9890216 1 2 0 0
OTHER_GROUPS 26 16677997 0 0 0 0
SYS_GROUP 5 6277055 0 0 0 0

Here we see two (2) switches out of the INTERACTIVE_GROUP and one (1) switch back in due to CPU threshold. And the reverse is true for the BATCH_GROUP . It would appear that the switch from BATCH_GROUP to INTERACTIVE_GROUP is due to the SWITCH_AFTER_CALL flag in the resource plan directive.

We confirm the resource consumer group after the call is back to the original: 

SQL> select resource_consumer_group from v$session where username = 'SH';
RESOURCE_CONSUMER_GROUP
INTERACTIVE_GROUP

Cancel the SQL If It Runs Too Long

Update Plan Directive

To cancel the SQL statement after running for more than five (5) CPU seconds, the MIXED_WORKLOAD_PLAN plan is updated to set the NEW_SWITCH_GROUP to CANCEL_SQL via the UPDATE_PLAN_DIRECTIVE procedure: 

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (
     plan                          => 'MIXED_WORKLOAD_PLAN', 
     group_or_subplan              => 'INTERACTIVE_GROUP', 
     new_switch_time               => 5,
     new_switch_group              => 'CANCEL_SQL'); 
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

Run the Experiment Again

The same SQL is used again with the following results: 

SQL> select sum( dbms_random.value() ) from dual connect by level < 10000000;
select sum( dbms_random.value() ) from dual connect by level < 10000000
            *
ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted


Elapsed: 00:00:05.30

Statistics from the Experiment

The data from the dynamic view, V$RSRC_CONS_GROUP_HISTORY , shows the group switching having occurred because the SQL exceeded the CPU quantum: 

SELECT
    name,
    requests,
    consumed_cpu_time,
    switches_in_cpu_time,
    switches_out_cpu_time,
    sql_canceled,
    active_sess_killed
  FROM
    v$rsrc_cons_group_history
  WHERE
      sequence# =
        (SELECT MAX(sequence#) FROM V$RSRC_CONS_GROUP_HISTORY)
    AND name NOT LIKE 'ORA%'
    AND name <> '_ORACLE_BACKGROUND_GROUP_'
  ORDER BY name;
NAME REQUESTS CONSUMED_CPU_TIME SWITCHES_IN_CPU_TIME SWITCHES_OUT_CPU_TIME SQL_CANCELED ACTIVE_SESS_KILLED
BATCH_GROUP 0 0 0 0 0 0
INTERACTIVE_GROUP 1 1765545 0 0 1 0
OTHER_GROUPS 4 92 0 0 0 0
SYS_GROUP 3 0 0 0 0 0

This shows that the statistics are reset (actually given a new SEQUENCE# ) whenever the resource plan is updated.

Here we see the SQL being cancelled is being recorded for the INTERACTIVE_GROUP .

I cannot make sense of the CONSUMED_CPU_TIME value.

Cancel the Session If the SQL Runs Too Long

Update Plan Directive

To cancel the session whenever the SQL statement after running for more than five (5) CPU seconds, the MIXED_WORKLOAD_PLAN plan is updated to set the NEW_SWITCH_GROUP to KILL_SESSION via the UPDATE_PLAN_DIRECTIVE procedure: 

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (
     plan                          => 'MIXED_WORKLOAD_PLAN', 
     group_or_subplan              => 'INTERACTIVE_GROUP', 
     new_switch_time               => 5,
     new_switch_group              => 'KILL_SESSION'); 
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

Performance Problem

This change in Resource Plan Directive took a very long time (about 40 minutes to complete). The OEM top activity showed the following graph:

The ASH report for the period, 03:10 to 03:50, is attached as ASH_report_1327656054538.html . The Top User Events were:

Event Event Class % Event Avg Active Sessions
resmgr:cpu quantum Scheduler 99.05 3.48

Run the Experiment Again

The same SQL is used again with the following results: 

SQL> select sum( dbms_random.value() ) from dual connect by level < 10000000;
select sum( dbms_random.value() ) from dual connect by level < 10000000
*
ERROR at line 1:
ORA-00041: active time limit exceeded - session terminated


Elapsed: 00:00:05.80

Statistics from the Experiment

The data from the dynamic view, V$RSRC_CONS_GROUP_HISTORY , shows the group switching having occurred because the SQL exceeded the CPU quantum: 

SELECT
    name,
    requests,
    consumed_cpu_time,
    switches_in_cpu_time,
    switches_out_cpu_time,
    sql_canceled,
    active_sess_killed
  FROM
    v$rsrc_cons_group_history
  WHERE
      sequence# =
        (SELECT MAX(sequence#) FROM V$RSRC_CONS_GROUP_HISTORY)
    AND name NOT LIKE 'ORA%'
    AND name <> '_ORACLE_BACKGROUND_GROUP_'
  ORDER BY name;
NAME REQUESTS CONSUMED_CPU_TIME SWITCHES_IN_CPU_TIME SWITCHES_OUT_CPU_TIME SQL_CANCELED ACTIVE_SESS_KILLED
BATCH_GROUP 0 0 0 0 0 0
INTERACTIVE_GROUP 1 3974203 0 0 0 1
OTHER_GROUPS 9 6086496 0 0 0 0
SYS_GROUP 6 5355213 0 0 0 0

And we see the statistic, ACTIVE_SESS_KILLED , has a value of one (1) for when the session was killed.

Restore Resource Plan to Original State

Used the following SQL to restore the MIXED_WORKLOAD_PLAN plan back to its original state: 

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (
     plan                          => 'MIXED_WORKLOAD_PLAN', 
     group_or_subplan              => 'INTERACTIVE_GROUP', 
     new_switch_time               => 60,
     new_switch_group              => 'BATCH_GROUP',
     new_switch_for_call           => TRUE); 
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

Conclusion

This demonstration of the Resource Manager is far in excess of what is expected on the OCM 11G upgrade exam, but I wanted to explore as much as possible. I have demonstrated:

  • Switching between groups
  • Killing the SQL
  • Killing the Session

This was only done for CPU.

The performance of the plan updating is problematic. Probably a bug in Oracle RDBMS 11.1.0.6.

The values displayed in CONSUMED_CPU_TIME of the V$RSRC_CONS_GROUP_HISTORY dynamic view do not make any sense.