resource manager tesing and explain

for the testing purpose , I assgin oracle user DD to batch group and QDL to oltp group , oltp can switch to batch group when switch condition are meet.

two consume groups are batch and oltp .

see comment details inline:

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'erp_plan',  COMMENT => 'Resource plan/method for ERP Database');

/* two group created */

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp',  COMMENT => 'Resource consumer group/method for OLTP jobs');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch',  COMMENT => 'Resource consumer group/method for BATCH jobs');

/* this statement assign cpu 80% to oltp :
 when exectuion time more than 3 seconds or undo space more than 200k ,
 the user belong to this group will switch to oltp group  */

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80, SWITCH_GROUP => 'batch', SWITCH_TIME => 3, UNDO_POOL => 200);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', GROUP_OR_SUBPLAN => 'batch', COMMENT => 'BATCH sessions', CPU_P2 => 100,   ACTIVE_SESS_POOL_P1 => 1, QUEUEING_P1 => 10, MAX_EST_EXEC_TIME => 20);

the upper statement is critical for the my testing :

/* for batch group cpu_p2 is 100% ,only one active session can connect , QUEUEING_P1 define the wait time  , for any batch group user , when logon you could recevie the erro below 
ERROR:
ORA-07454: queue timeout, 10 second(s), exceeded

to generate the error, I run a long run sql as QDL , and try to logon as dd user , then the error raised.
*/

/* while one DD is locking it self by exec dbms_lock.sleep(600); another dd connect , then error raised as following this prove ACTIVE_SESS_POOL_P1 => 1 works。

[oracle@ocm ~]$ sqlplus dd/oracle

SQL*Plus: Release 10.1.0.4.2 - Production on Tue Apr 23 17:13:06 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-07454: queue timeout, 10 second(s), exceeded
ERROR:
ORA-07454: queue timeout, 10 second(s), exceeded

*/

/* the state can be qurey as the statement :
SQL> SELECT username,RESOURCE_CONSUMER_GROUP,status FROM V$SESSION where username in ('DD','QDL');
USERNAME                       RESOURCE_CONSUMER_GROUP          STATUS
------------------------------ -------------------------------- --------
DD                             BATCH                            ACTIVE
QDL                            OLTP                             INACTIVE
DD                             BATCH                            ACTIVE

*/

overall the active session count limited the group can only have 1 active session , and QUEUEING_P1 will print the message when the time reached. 

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory', CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;


BEGIN
    dbms_resource_manager_privs.grant_switch_consumer_group( 
        grantee_name => 'QDL',
        consumer_group => 'OLTP',
        grant_option => FALSE
    );
     dbms_resource_manager_privs.grant_switch_consumer_group( 
        grantee_name => 'QDL',
        consumer_group => 'BATCH',
        grant_option => FALSE
    );
END;
BEGIN
    dbms_resource_manager_privs.grant_switch_consumer_group( 
        grantee_name => 'DD',
        consumer_group => 'BATCH',
        grant_option => FALSE
    );
END;
BEGIN 
    dbms_resource_manager.set_initial_consumer_group( 
        user => 'DD',
        consumer_group => 'BATCH'
    );
END;

there are still many other setting in grid control or through the package itself , the most hard part to understand is the queueing_p1 for me