rem ----------------------------------------------------------------------- rem Filename: rsrc.sql rem Purpose: Demonstrate resource manager capabilities (limit CPU, rem degree and sessions, available from Oracle 8i) rem Date: 28-Aug-1998 rem Author: Frank Naude, Oracle FAQ rem ----------------------------------------------------------------------- --------------------------------------------------------------------------- -- Create plan with consumer groups --------------------------------------------------------------------------- exec dbms_resource_manager.create_pending_area; exec dbms_resource_manager.delete_plan_cascade('night_plan'); exec dbms_resource_manager.create_plan('night_plan', 'Plan to use after 6PM'); exec dbms_resource_manager.create_consumer_group('batch', 'Group for batch reports'); exec dbms_resource_manager.create_plan_directive('night_plan', 'batch', 'Rules for overnight batch jobs', - cpu_p1 => 75, parallel_degree_limit_p1 => 20); exec dbms_resource_manager.create_plan_directive('night_plan', 'OTHER_GROUPS', 'Rules for overnight batch jobs', - cpu_p1 => 25, parallel_degree_limit_p1 => 0, - max_active_sess_target_p1 => 1); exec dbms_resource_manager.validate_pending_area; exec dbms_resource_manager.submit_pending_area; --------------------------------------------------------------------------- -- List plans and consumer groups --------------------------------------------------------------------------- set pages 50000 col plan format a12 col status format a7 col cpu_p1 format 999 col cpu_p2 format 999 col cpu_p3 format 999 col group_or_subplan format a17 col parallel_degree_limit_p1 format 999 select plan, num_plan_directives, status, mandatory from sys.dba_rsrc_plans; select plan, group_or_subplan, cpu_p1, cpu_p2, cpu_p3, parallel_degree_limit_p1 as parallel, status from sys.dba_rsrc_plan_directives order by plan; --------------------------------------------------------------------------- -- Switch a user to a new consumer group --------------------------------------------------------------------------- exec dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT', 'batch', FALSE); exec dbms_resource_manager.set_initial_consumer_group('SCOTT', 'batch'); -- exec dbms_resource_manager.switch_consumer_group_for_user('SCOTT', 'batch'); -- Switch on-line users select username, initial_rsrc_consumer_group from sys.dba_users where username = 'SCOTT'; --------------------------------------------------------------------------- -- Enable resource management for this instance --------------------------------------------------------------------------- alter system set resource_manager_plan = 'NIGHT_PLAN'; --------------------------------------------------------------------------- -- Monitor the resource manager --------------------------------------------------------------------------- col program format a40 select program, resource_consumer_group from sys.v_$session where username = 'SCOTT'; -- select * from sys.v_$rsrc_plan; select * from sys.v_$rsrc_consumer_group;