Resource Manager Enhancements in Oracle Database 11g Release 1

This article provides an overview of all the resource manager enhancements in the Enterprise Edition of Oracle Database 11g Release 1, including the following.

I/O Calibration
Per Session I/O Limits
Resource Manager Statistics in AWR
Built-In Resource Plans

I/O Calibration

The DBMS_RESOURCE_MANAGER package now includes a CALIBRATE_IO procedure to assess the I/O performance of the database servers storage system by performing an I/O intensive read-only workload. This should only be run during off-peak times to make sure the calibration doesn't affect production workloads, as well as the production workloads affecting the results of the calibration.

The procedure only works if asynchronous I/O is enabled. If asynchronous I/O is not enabled, the procedure returns the following error.

DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 453
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153
ORA-06512: at line 6

SQL>

Asynchronous I/O is enabled by setting the FILESYSTEMIO_OPTIONS parameter to ASYNC or SETALL.

SHOW PARAMETER FILESYSTEMIO_OPTIONS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
SQL>

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

The CALIBRATE_IO procedure accepts two parameters to specify the number of physical disks (default 1) and the maximum tolerable latency (default 20ms). On completion it returns the maximum I/O requests per second, the maximum Mb per second and the actual latency.

SET SERVEROUTPUT ON
DECLARE
  l_max_iops        PLS_INTEGER;
  l_max_mbps        PLS_INTEGER;
  l_actual_latency  PLS_INTEGER;
BEGIN
  DBMS_RESOURCE_MANAGER.calibrate_io (
    num_physical_disks => 1,
    max_latency        => 20,
    max_iops           => l_max_iops,
    max_mbps           => l_max_mbps,
    actual_latency     => l_actual_latency);
 
  DBMS_OUTPUT.put_line ('l_max_iops       = ' || l_max_iops);
  DBMS_OUTPUT.put_line ('l_max_mbps       = ' || l_max_mbps);
  DBMS_OUTPUT.put_line ('l_actual_latency = ' || l_actual_latency);
END;
/
l_max_iops       = 556
l_max_mbps       = 48
l_actual_latency = 17

PL/SQL procedure successfully completed.

SQL>

Only one calibration can run at a time, with the workload initiated for all nodes in a RAC environment.

The [G]V$IO_CALIBRATION_STATUS views show the current status of the calibration runs. During a run the status of 'IN PROGRESS' is displayed. Once a run is complete the status switches to 'READY' and the calibration time is displayed.

SQL> SELECT * FROM v$io_calibration_status;

STATUS        CALIBRATION_TIME
------------- -------------------------------
IN PROGRESS

SQL> SELECT * FROM v$io_calibration_status;

STATUS        CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY         28-JUL-2008 14:37:38.410

1 row selected.

SQL>

Information about calibration runs is displayed using the DBA_RSRC_IO_CALIBRATE view.

SET LINESIZE 150
COLUMN start_time FORMAT A30
COLUMN end_time FORMAT A30

SELECT * FROM dba_rsrc_io_calibrate;

START_TIME                     END_TIME                         MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ------------------
28-JUL-2008 14:32:33.689059    28-JUL-2008 14:37:38.409870           556         48         48         17                  1

1 row selected.

SQL>

Per Session I/O Limits

In previous Oracle versions, automatic consumer group switching was based entirely on the amount of time a session or a single call ran for. Oracle 11g also allows automatic consumer group switching based on I/O thresholds (maximum requests or megabytes). These new thresholds are implemented in the CREATE_PLAN_DIRECTIVE procedure, which includes a number of other changes.

The CPU_P1 - CPU_P8 parameters have been deprecated in favor of the MGMT_P1 - MGMT_P8 parameters
The SWITCH_TIME_IN_CALL parameter has been deprecated in favor of the SWITCH_FOR_CALL parameter.
The SWITCH_IO_MEGABYTES and SWITCH_IO_REQS parameters are now available in addition to the SWITCH_TIME parameter. All three parameters default to NULL, meaning unlimited.

The new I/O parameters mean it is now possible to switch consumer groups, cancel SQL statements or kill sessions based on any combination of elapsed time, I/O requests and I/O in megabytes. The following example switches the resource consumer group from oltp_group to batch_group for the current call within the session if the elapsed time exceeds 120 seconds, the number of I/O requests exceeds 5000 or the amount of I/O requested exceeds 1024M.

BEGIN
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                => 'my_plan',
    group_or_subplan    => 'oltp_group',
    comment             => 'OLTP Group',
    mgmt_p1             => 70,
    switch_group        => 'batch_group',
    switch_time         => 120,
    switch_io_reqs      => 5000,
    switch_io_megabytes => 1024,
    switch_for_call     => TRUE);
END;
/

The following example kills the session if it consumes more than 4096M of I/O.

BEGIN
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                => 'my_plan',
    group_or_subplan    => 'oltp_group',
    comment             => 'OLTP Group',
    mgmt_p1             => 70,
    switch_group        => 'KILL_SESSION',
    switch_io_megabytes => 4096);
END;
/

Resource Manager Statistics in AWR

The new DBA_HIST_RSRC_PLAN and DBA_HIST_RSRC_CONSUMER_GROUP views have been added to provide a historical view of the data in the V$RSRC_PLAN_HISTORY and V$RSRC_CONS_GROUP_HISTORY views.

The V$RSRCMGRMETRIC_HISTORY view displays one hours worth of metrics taken from the V$RSRCMGRMETRIC view.

For a complete list of resource manager views look here.

Built-In Resource Plans

Oracle 11g includes a built-in MIXED_WORKLOAD_PLAN, designed to give OLTP operations priority over batch operations. The plan contains two consumer groups (INTERACTIVE_GROUP and BATCH_GROUP). Calls assigned to the INTERACTIVE_GROUP are automatically switched to the BATCH_GROUP if they exceed 60 seconds.

The RESOURCE_MANAGER_PLAN parameter is used to tell the instance which resource plan to use.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'MIXED_WORKLOAD_PLAN';

You must then grant access on the relevant consumer groups to individual users. The following example grants access on both consumer groups to the TEST user and makes the INTERACTIVE_GROUP the default consumer group. Querying the DBA_USERS view shows the default setting has been recorded.

BEGIN
  -- Assign users to consumer groups
  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'test',
    consumer_group => 'interactive_group',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'test',
    consumer_group => 'batch_group',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('test', 'interactive_group');
END;
/

SELECT initial_rsrc_consumer_group FROM dba_users WHERE username = 'TEST';

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

1 row selected.

SQL>

Connecting to the database as the TEST user and querying the V$SESSION view shows the correct consumer group assignment is working.

CONN test/test
SELECT resource_consumer_group
FROM   v$session
WHERE  audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

RESOURCE_CONSUMER_GROUP
--------------------------------
INTERACTIVE_GROUP

1 row selected.

SQL>

We can see the consumer group switch take place if we create an artificially long call.

CONN test/test
SET SERVEROUTPUT ON
DECLARE
  l_cg  v$session.resource_consumer_group%TYPE;
BEGIN
  SELECT resource_consumer_group
  INTO   l_cg
  FROM   v$session
  WHERE  audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

  DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);

  -- Sleep to cause switch based on time.
  DBMS_LOCK.sleep(65);

  SELECT resource_consumer_group
  INTO   l_cg
  FROM   v$session
  WHERE  audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

  DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg);
END;
/
Current Consumer Group: INTERACTIVE_GROUP
Current Consumer Group: BATCH_GROUP

PL/SQL procedure successfully completed.

SQL>

As we expected, the call was initially assigned to the INTERACTIVE_GROUP, but switched to the BATCH_GROUP as the call took longer than 60 seconds.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.