Oracle Database Resource Manager 11g - Undocumented New Parameters

I've played around with Oracle Database Resource Manager in 10g and it's quite nice and might be very useful for high CPU usage systems, but I found the inability to limit I/O as a drawback since in most cases I've faced the need to limit I/O is more necessary than CPU limit. When you have, let's say, 8 CPU's on your machine, you need all the 8 to be 100% utilized by Oracle sessions for the resource manager start limit sessions. However, if your machine I/O capabilities are 50 mbps, you need only one or two sessions which perform intensive I/O (batch job/heavy report) to make the database very heavy. In Oracle Database 11g Release 1, Resource Manager has gotten some new features related to I/O. So I've installed the 11g, made some tests and found some interesting issues. I'm not going to write about Resource Manager basics or about 11g enhancements as some great articles have already been published about it. For example, you can read Tim's blog post - "Resource Manager Enhancements in Oracle Database 11g Release 1" But... I'm going to discuss one missing capability (in my opinion) that will hopefully be available with Oracle Database 11g Release 2 with 2 new parameters which have already available but inactive and undocumented. For those who are not familiar with Oracle Database Resource Manager I'll try to give a short basic introduction: Oracle Database Resource Manager helps us to prioritize sessions to optimize resource allocation within our database by: Creating groups of sessions (Consumer Groups) based on similar resource requirements Allocate resources to those groups (Resource Plan Directive) Resource Plan - is a container of Resource Plan Directives that allocate resources to Consumer Groups Only one Resource Plan is active at a time. When Oracle Database 11g was introduced, some new features for Resource Manager related to I/O have been revealed. Among them: I/O Calibration - New procedure which helps to assess the I/O capabilities of the database storage. The output of this procedure (CALIBRATE_IO) is max_iops, max_mbps and actual_latency.   Per Session I/O Limits - Unlike previous versions, you can limit I/O requests (or I/O MB) for a session before it moved to a new consumer group. (switch_io_megabytes and switch_io_reqs have been added to CREATE_PLAN_DIRECTIVE procedure)   Oracle have added the option to "capture" Oracle sessions by the I/O requests or by the megabytes of I/O they issued in order to move them to a lower priority consumer group. I have a very fundamental doubt about this enhancements as I don't get the meaning of "capturing" an intensive I/O session and move it to a low priority consumer group which can have only CPU limit ... ?!  The reason we "capture" this session is the amount of I/O it makes, and when we move it to a low priority consumer group we can just limit its CPU resources. We can't limit the amount of I/O for a Consumer Group. It could have been very useful if Oracle had added the ability to limit I/O for a Consumer Group, like we can limit CPU (with mgmt_pN) for a Consumer Group. What is missing here is the ability to limit I/O for a specific Consumer Group in terms of Maximum I/O per second or Maximum megabytes per second. Will Oracle enhance Resource Manager in 11g Release 2 to fulfill this capability? I don't have a confident answer for this question but I assume they will. While playing around I've noticed two new parameters to the CREATE_PLAN procedure - MAX_IOPS and MAX_MBPS. On first sight it looked like the answer to my question - the ability to limit I/O for session within a plan, but it's not... Those two parameter are undocumented and totally ignored in Oracle 11g Release 1 Documentation but available in 11g Release 1 database: -- create a new resource plan -- -- Input arguments: --   plan                       - name of resource plan --   comment                    - user's comment --   cpu_mth                    - allocation method for CPU resources --                                (deprecated) --   active_sess_pool_mth       - allocation method for max. active sessions --   parallel_degree_limit_mth  - allocation method for degree of parallelism --   queueing_mth               - type of queueing policy to use --   mgmt_mth                   - allocation method for CPU and I/O resources --   sub_plan                   - whether plan is sub_plan --   max_iops                   - maximum I/O requests per second --   max_mbps                   - maximum megabytes of I/O per second -- PROCEDURE create_plan(plan IN VARCHAR2,                       comment IN VARCHAR2,                       cpu_mth IN VARCHAR2 DEFAULT NULL,                       active_sess_pool_mth IN VARCHAR2                       DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',                       parallel_degree_limit_mth IN VARCHAR2 DEFAULT                       'PARALLEL_DEGREE_LIMIT_ABSOLUTE',                       queueing_mth IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT',                       mgmt_mth IN VARCHAR2 DEFAULT 'EMPHASIS',                       sub_plan IN BOOLEAN DEFAULT FALSE,                       max_iops IN NUMBER DEFAULT NULL,                       max_mbps IN NUMBER DEFAULT NULL                       ); I tried to create a new plan using one of these two parameters, but it returned an error for each value I tried. It turned out that the only valid value to MAX_IOPS and MAX_MBPS is null. begin   dbms_resource_manager.create_plan(plan => 'PLAN_EX_1'                                    ,comment => 'Plan Example 1'                                    ,max_iops => 100); end; / begin * ERROR at line 1: ORA-29355: NULL or invalid MAX_IOPS argument specified ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 38 ORA-06512: at line 2 I've confirmed it with Oracle support and their answer was: "This is currently an expected behaviour, we can not explicitly set either max_iops or max_mbps to any value other than null, that's why these parameters are currently not included in the documentation." So here is my guess: It looks like Oracle are going to enhance Resource Manager to allow I/O limit, but unlike CPU limit through Plan Directive (mgmt_pN), it's going to be a limit in Plan level, so you can create a sub plan with limited I/O resources allocated. Maybe the allocation method parameter (mgmt_mth) will get more valid options except for EMPHASIS and RATIO. I'll keep track of it as soon as Oracle Database 11g Release 2 will be announced and I'll update. You are welcome to leave a comment and/or share your opinion about this topic. Aviad