/*
|| 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;
/