/*
|| Database Resource Management Listing 3
||
|| Contains examples of:
|| - Updating an existing resource plan (PEAKTIME) to make it more complex
|| - Dropping an existing plan (OFF-PEAK) and recreating it to make it more
||   complex with subplans
|| - Setting up automatic switchover of a plan
|| - Analyzing a resource plan's effectiveness in managing resources
|| for demonstration of Oracle Database Resource Management (DRM) features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of
|| Oracle Database Resource Management (DRM) and should be carefully proofread
|| before executing it against any existing Oracle database to insure
|| that no potential damage can occur.
||
*/

CONNECT SYS / AS SYSDBA;

 
-----
-- Listing 3.1: Update an existing plan to make it more complex
-----
BEGIN
    -- Prepare the pending area
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

    DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
        plan => 'PEAKTIME'
       ,group_or_subplan => 'OLTP'
       ,new_comment => 'OLTP Use (75% of available CPU)'
       ,new_cpu_p1 => 75
    );
    DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
        plan => 'PEAKTIME'
       ,group_or_subplan => 'DSS'
       ,new_comment => 'DSS Use (20% of available CPU)'
       ,new_cpu_p1 => 20
       ,new_switch_group => 'ADHOC'
       ,new_switch_time => 1800
    );
    DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
        plan => 'PEAKTIME'
       ,group_or_subplan => 'ADHOC'
       ,new_comment => 'Ad Hoc Use (5% of available CPU)'
       ,new_cpu_p1 => 5
       ,new_max_est_exec_time => 2400
    );

    -- Submit the changes
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

END;
/
 
-----
-- Listing 3.2: Re-creating a plan to make it more complex
-----
BEGIN
    -- Prepare the pending area
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

    -----
    -- Recreate resource plan directives for OFF-PEAK plan
    -----
    -- Drop old OFF-PEAK plan and all its attached directives
    DBMS_RESOURCE_MANAGER.DELETE_PLAN(
        plan => 'OFF-PEAK'
    );

    -- Create new BATCH plan
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
        plan => 'BATCH'
       ,comment => 'Off-Peak Batch'
    );

    -- Re-create OFF-PEAK plan
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
        plan => 'OFF-PEAK'
       ,comment => 'Off-Peak Master'
    );

    -- Allocate default OTHER_GROUPS for BATCH sub-plan
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
         plan => 'BATCH'
        ,group_or_subplan => 'OTHER_GROUPS'
        ,comment => 'Regular Batch'
        ,cpu_p1 => 0
        ,cpu_p2 => 75
    );
    -- Create new DSS plan directive that can use up to
    -- 25% of all BATCH subplan CPU resources
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
         plan => 'BATCH'
        ,group_or_subplan => 'DSS'
        ,comment => 'DSS Batch'
        ,cpu_p1 => 0
        ,cpu_p2 => 25
    );

    -- Allocate BATCH as a subplan of OFF-PEAK plan
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
         plan => 'OFF-PEAK'
        ,group_or_subplan => 'BATCH'
        ,comment => 'Batch'
        ,cpu_p1 => 80
        ,cpu_p2 => 0
    );

    -- Allocate default OTHER_GROUPS for OFF-PEAK plan
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
         plan => 'OFF-PEAK'
        ,group_or_subplan => 'OTHER_GROUPS'
        ,comment => 'Others'
        ,cpu_p1 => 0
        ,cpu_p2 => 0
        ,cpu_p3 => 100
    );

    -- Reallocate OLTP CPU level 1 percentage
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
         plan => 'OFF-PEAK'
        ,group_or_subplan => 'OLTP'
        ,comment => 'Off-Peak - OLTP'
        ,cpu_p1 => 15
   );
    -- Reallocate ADHOC to same CPU percentage as OLTP,
    -- but insure that UNDO use is restricted to prevent
    -- runaway queries
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
         plan => 'OFF-PEAK'
       ,group_or_subplan => 'ADHOC'
       ,comment => 'Off-Peak - Ad Hoc'
       ,cpu_p1 => 5
       ,undo_pool => 4096
    );

    -- Submit the changes
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

END;
/

 
-----
-- Listing 3.3: Scheduling resource plan management tasks
-----
VARIABLE jobno NUMBER;
BEGIN
    -- Switch to PEAKTIME at 06:00 daily
    DBMS_JOB.SUBMIT(
         job => :jobno
        ,what => 'BEGIN EXECUTE IMMEDIATE ''ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=PEAKTIME''; END;'
        ,next_date => ROUND(SYSDATE) + 6/24
        ,interval => 'TRUNC(SYSDATE) + 6/24 + 1'
        );
    
    -- Switch to OFF-PEAK at 18:00 daily
    DBMS_JOB.SUBMIT(
         job => :jobno
        ,what => 'BEGIN EXECUTE IMMEDIATE ''ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=OFF-PEAK''; END;'
        ,next_date => TRUNC(SYSDATE) + 18/24
        ,interval => 'TRUNC(SYSDATE) + 18/24 + 1'
        );

    COMMIT;
END;
/
 
-----
-- Listing 3.4: Make those CPUs spin!
-----
DECLARE
    sqrtval NUMBER := 0;
BEGIN
    FOR idx1 IN 1..1000
        LOOP
            FOR idx2 IN 1..1000
                LOOP
                    SELECT (SQRT(idx2/idx1)) INTO sqrtval FROM DUAL;
                END LOOP;
        END LOOP;
END;
/
 
-----
-- Listing 3.5: Measuring a resource plan's effectiveness
-----

-- What CPU resource allocation methods are currently defined
-- for resource plans?
SELECT *
  FROM v$rsrc_plan_cpu_mth
;  

-- What CPU resource allocation methods are currently defined
-- for resource consumer groups?
SELECT *
  FROM v$rsrc_consumer_group_cpu_mth
;  

-- Which resource plan is currently active?
SELECT * 
  FROM v$rsrc_plan
;

-- Which current user sessions are utilizing which resource plans?
COL username                 FORMAT A12  HEADING 'User Name'
COL resource_consumer_group  FORMAT A24  HEADING 'Resource|Consumer|Group'

SELECT 
     resource_consumer_group 
    ,username
  FROM v$session
 WHERE resource_consumer_group IS NOT NULL
 ORDER BY resource_consumer_group, username
;

-- Which resources are being consumed by which resource consumer group?
COL name                      FORMAT A12     HEADING 'Resource|Consumer|Group'
COL active_sessions           FORMAT 9999    HEADING 'Act|Sess'
COL execution_waiters         FORMAT 9999    HEADING 'Exec|Wtrs'
COL requests                  FORMAT 9999    HEADING 'Reqs'
COL cpu_wait_time             FORMAT 9999999 HEADING 'CPU|Wait|Time'
COL cpu_waits                 FORMAT 9999999 HEADING 'CPU|Waits'
COL consumed_cpu_time         FORMAT 9999999 HEADING 'CPU|Time|Used'
COL yields                    FORMAT 9999    HEADING 'Ylds'
COL queue_length              FORMAT 99999   HEADING 'Queue|Len'
COL current_undo_consumption  FORMAT 99999   HEADING 'Curr|UNDO|Used'

SELECT
     name
    ,active_sessions
    ,execution_waiters
    ,requests
    ,cpu_wait_time
    ,cpu_waits
    ,consumed_cpu_time
    ,yields
    ,queue_length
    ,current_undo_consumption
  FROM v$rsrc_consumer_group
;
 
-----
-- Listing 3.6: General maintenance using DBMS_RESOURCE_MANAGER:
--  (a) Update comments for a resource plan
--  (b) Update comments for a resource consumer group
--  (c) Delete a resource consumer group
-----
BEGIN
    -- Prepare the pending area
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

    -- Update a resource plan's comments
    DBMS_RESOURCE_MANAGER.UPDATE_PLAN(
         plan => 'PEAKTIME'
        ,new_comment => 'Updated Peak Time Plan'
    );

    -- Update comments and for OLTP resource consumer group
    DBMS_RESOURCE_MANAGER.UPDATE_CONSUMER_GROUP(
         consumer_group => 'OLTP'
        ,new_comment => 'OLTP now contains new directives'
    );

    -- Remove the ADHOC consumer group
    DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(
       consumer_group => 'ADHOC'
    );

    -- Submit the changes
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

END;
/
 
-----
-- Listing 3.7: General maintenance:
--  (a) Grant a system privilege to a user
--  (b) Revoke a system privilege from a user
--  (c) Revoke a user's ability to switch consumer groups
-----
BEGIN
    -- Prepare the pending area
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

    -- Grant a DRM system privilege to a user, and extend
    -- user's ability to GRANT privilege to other users
    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
         grantee_name => 'SLSMGR'
        ,privilege_name => 'ADMINISTER RESOURCE MANAGER'
        ,admin_option => TRUE
    );

    -- Revoke an existing DRM system privilege from a user
    DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SYSTEM_PRIVILEGE(
         revokee_name => 'SLSMGR'
        ,privilege_name => 'ADMINISTER RESOURCE MANAGER'
    );

    -- Change the ability of a user to switch resource consumer groups
	DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP(
        revokee_name => 'SLSMGR'
       ,consumer_group => 'OLTP'
    );

    -- Submit the changes
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

END;
/