11G OCM Set up and configure Resource Manager

References

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

• Chapter 25, "Managing Resource Allocation with Oracle Database Resource Manager"
  • Creating a Simple Resource Plan
  • Assigning Sessions to Resource Consumer Groups
    • Assigning an Initial Resource Consumer Group
    • Granting and Revoking the Switch Privilege
  • Enabling Oracle Database Resource Manager and Switching Plans
  • Putting It All Together: Oracle Database Resource Manager Examples
    • An Oracle-Supplied Mixed Workload Plan

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

• DBMS_RESOURCE_MANAGER
  • CREATE_PENDING_AREA
  • SET_CONSUMER_GROUP_MAPPING
  • SUBMIT_PENDING_AREA
  • UPDATE_PLAN_DIRECTIVE
  • VALIDATE_PENDING_AREA
• DBMS_RESOURCE_MANAGER_PRIVS
  • GRANT_SWITCH_CONSUMER_GROUP
• DBMS_SESSION
  • SWITCH_CURRENT_CONSUMER_GROUP

Oracle® Database Reference 11g Release 1 (11.1)

• Part I Initialization Parameters
  • RESOURCE_MANAGER_PLAN
• Part II Static Data Dictionary Views
  • DBA_USERS
• Part III Dynamic Performance Views
  • V$RSRC_CONS_GROUP_HISTORY
  • V$RSRC_PLAN

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;

Or, by the system parameter, RESOURCE_MANAGER_PLAN,

SQL> show parameter resource_manager_plan

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

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;
1. 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';

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';

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';

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;

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';

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;

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:

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;

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.