This article provides an overview of all the resource manager enhancements in the Enterprise Edition of Oracle Database 11g Release 1, including the following.
Related articles.
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>
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.
CPU_P1
- CPU_P8
parameters have been deprecated in favor of the MGMT_P1
- MGMT_P8
parametersSWITCH_TIME_IN_CALL
parameter has been deprecated in favor of the SWITCH_FOR_CALL
parameter.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 (or CPU time in 11gR2 onward), 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; /
In Oracle 11gR2, the SWITCH_TIME
is now the limit on CPU time usage, not elapsed time!
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.
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; l_start DATE; l_number NUMBER; l_last_action gv$rsrc_session_info.last_action%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); -- In 11.1 sleep would cause switch based on elapsed time. -- DBMS_LOCK.sleep(65); -- From 11.2 onward it is based on CPU time, not elapsed time. -- Burn some CPU for 65 seconds. l_start := SYSDATE; WHILE SYSDATE <= l_start + 1/24/60/60*65 LOOP l_number := 1234 * 1234; END LOOP; SELECT resource_consumer_group INTO l_cg FROM v$session WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); SELECT last_action INTO l_last_action FROM gv$rsrc_session_info WHERE sid = SYS_CONTEXT('USERENV', 'SID'); DBMS_OUTPUT.put_line('Current Consumer Group : ' || l_cg); DBMS_OUTPUT.put_line('Resource Manager Last Action: ' || NVL(l_last_action, '- no action -')); END; / Current Consumer Group : INTERACTIVE_GROUP Current Consumer Group : BATCH_GROUP Resource Manager Last Action: SWITCH TO 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 normal view: https://oracle-base.com/articles/11g/resource-manager-enhancements-11gr1