QL> SET SERVEROUTPUT ON
SQL> BEGIN
2 dbms_resource_manager.clear_pending_area();
3 dbms_resource_manager.create_pending_area();
4 dbms_resource_manager.create_consumer_group(
5 consumer_group => 'Z_TEST_APPL',
6 comment => 'Z Sample Consumer Group');
7 dbms_resource_manager.submit_pending_area();
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 dbms_resource_manager.clear_pending_area();
3 dbms_resource_manager.create_pending_area();
4 dbms_resource_manager.create_plan(
5 plan => 'Z_DEFAULT_PLAN',
6 comment => 'Z Sample Plan');
For “Z_TEST_APPL” group CPU allocation will be only 30% of total available.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 dbms_resource_manager.create_plan_directive(
3 plan =>'Z_DEFAULT_PLAN',
4 comment => 'Limit CPU resource',
5 group_or_subplan =>'Z_TEST_APPL',
6 cpu_p1 => 30 );
7 END;
8 /
PL/SQL procedure successfully completed.
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_resource_manager.create_plan_directive(
3 plan =>'Z_DEFAULT_PLAN',
4 comment => 'Limit CPU resource',
5 group_or_subplan =>'SYS_GROUP',
6 cpu_p1 => 15 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_resource_manager.create_plan_directive(
3 plan =>'Z_DEFAULT_PLAN',
4 comment => 'Limit CPU resource',
5 group_or_subplan =>'OTHER_GROUPS',
6 cpu_p1 => 5 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 dbms_resource_manager.validate_pending_area();
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_resource_manager.submit_pending_area();
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SET LINESIZE 100
SQL> SET PAGESIZE 50
SQL> COL consumer_group FORMAT a30
SQL> COL cpu_method FORMAT a15
SQL> COL status FORMAT a10
SQL> SELECT consumer_group, cpu_method, status
2 FROM dba_rsrc_consumer_groups
3 ORDER BY 1;
CONSUMER_GROUP CPU_METHOD STATUS
------------------------------ --------------- ----------
BATCH_GROUP ROUND-ROBIN
DEFAULT_CONSUMER_GROUP ROUND-ROBIN
DSS_CRITICAL_GROUP ROUND-ROBIN
DSS_GROUP ROUND-ROBIN
ETL_GROUP ROUND-ROBIN
INTERACTIVE_GROUP ROUND-ROBIN
LOW_GROUP ROUND-ROBIN
OTHER_GROUPS ROUND-ROBIN
SYS_GROUP ROUND-ROBIN
Z_TEST_APPL ROUND-ROBIN
10 rows selected.
SQL> SET LINESIZE 100
SQL> COL plan FORMAT a15
SQL> COL group_or_subplan FORMAT a15
SQL> SELECT plan,
2 group_or_subplan,
3 type,
4 cpu_p1,
5 status
6 FROM dba_rsrc_plan_directives
7 WHERE plan='Z_DEFAULT_PLAN'
8 ORDER BY 1,2,3,4;
PLAN GROUP_OR_SUBPLA TYPE CPU_P1 STATUS
--------------- --------------- -------------- ---------- ----------
Z_DEFAULT_PLAN OTHER_GROUPS CONSUMER_GROUP 5
Z_DEFAULT_PLAN SYS_GROUP CONSUMER_GROUP 15
Z_DEFAULT_PLAN Z_TEST_APPL CONSUMER_GROUP 30
