Managing Resources

1. Managing Resources

2. Objectives • After completing this lesson, you should be able to do the following: • Set up Database Resource Manager • Assign users to Resource Manager groups • Create resource plans within groups

3. Overview • Manage mixed workload • Control system performance Database resource manager OLTP More resources OLTP user DSS Less resources OracleDatabase DSS user

4. Database ResourceManager Concepts Resource consumer group • User groups or sessions with similar resource needs • A blueprint for resource allocation among resource consumer groups (one active plan) • Specifies how a resource is divided among the resource consumer groups Resource plan Resource plan directives

5. Resource Manager Configurations • You can manage database and operating system resources, such as: • CPU usage • Number of active sessions • Degree of parallelism • Undo generation • Operation execution time • Idle time • You can also specify criteria that, if met, causes the automatic switching of sessions to another consumer group.

6. The Initial Plan: SYSTEM_PLAN

7. Creating a New Resource Plan • There are three ways to create a new resource plan: • Use Enterprise Manager • Use the CREATE_SIMPLE_PLAN procedure of DBMS_RESOURCE_MANAGER • Use the procedures in the DBMS_RESOURCE_MANAGER package to create a complex plan.

8. Creating a Simple Plan BEGIN DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN( SIMPLE_PLAN => 'simple_plan1', CONSUMER_GROUP1 => 'mygroup1', GROUP1_CPU => 80, CONSUMER_GROUP2 => 'mygroup2', GROUP2_CPU => 20); END;

9. Oracle Enterprise Manager:Resource Manager

10. p001 p002 p003 Active Session Pool Mechanism OLTP Users Active_sess_pool_P1=5 DSS Users Active_sess_pool_P1=3 Sess4 Sess5 DSSActiveSessionQueue Sess10 Sess3 Sess8 Sess9 Sess1 Sess2 Sess6 Sess7

11. Setting the Active Session Pool

12. GROUP ONLINE BATCH ACTIVE SESSION POOL No limits ACTIVE_SESS_POOL_P1 = 5 QUEUEING_P1 = 600 Setting the Active Session Pool • Example: • OLTP: Set no limit on concurrent active sessions • BATCH: Set to limit concurrent active sessions to 5 • QUEUEING_P1, set to 600, aborts all operations that wait on the queue for more than ten minutes

13. Maximum Estimated Execution Time • The Database Resource Manager can estimate the execution time of an operation proactively. • A DBA can specify a maximum estimated execution time for an operation at the resource consumer group level. • Operation will not start if the estimate is longer than MAX_EST_EXEC_TIME. • The benefit of this feature is the elimination of the exceptionally large job that uses too many system resources. • The default is UNLIMITED.

14. Setting Idle Timeouts DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'DAY_PLAN', GROUP_OR_SUBPLAN => 'DSS_GROUP', COMMENT => 'Limit Idle Time Example', MAX_IDLE_TIME => 600, MAX_IDLE_BLOCKER_TIME => 300);

15. Switching Back to the Initial Consumer Group at End of Call Call 1 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'Day_Plan', GROUP_OR_SUBPLAN => 'DSS_GROUP', CPU_P1 => 100, CPU_P2 => 0, SWITCH_GROUP => 'LONGRUN_GROUP', SWITCH_TIME_IN_CALL => 600); Call 2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'Day_Plan', GROUP_OR_SUBPLAN => 'LONGRUN_GROUP', CPU_P1 => 0, CPU_P2 => 100); At call end

16. Creating Resource Consumer Groups

17. Assigning Users to Consumer Groups

18. Automatic Consumer Group Switching SWITCH_GROUP=ONLINE_GROUPSWITCH_TIME=43200SWITCH_ESTIMATE=FALSE ONLINE Group Sess1 Sess2 Sess3 BATCH Group Sess4 Sess5 SWITCH_GROUP=BATCH_GROUPSWITCH_TIME_IN_CALL=180SWITCH_ESTIMATE=TRUE

19. Automatic Consumer Group Switching ONLINE Group Sess1 Sess2 SWITCH_GROUP='CANCEL_SQL'SWITCH_TIME=300SWITCH_ESTIMATE=TRUE Sess3 SELECT c.cust_last_name, line_item_id, product_idFROM customers c, orders o, order_itemsWHERE c.customer_id = ...;

20. Configuring Consumer Group Switching

21. Adaptive Consumer Group Mapping

22. Creating a Mapping Using DBMS_RESOURCE_MANAGER DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'PDML', 'DSS_GROUP'); DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'TP1', 'OLTP_GROUP'); DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.CLIENT_OS_USER, 'BILL', 'MANAGER_GROUP');

23. Assigning Priorities Using DBMS_RESOURCE_MANAGER DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI (EXPLICIT => 1, -- highest SERVICE_MODULE => 2, SERVICE_MODULE_ACTIONS => 3, MODULE_NAME_ACTION => 4, MODULE_NAME => 5, SERVICE_NAME => 6, ORACLE_USER => 9, CLIENT_PROGRAM => 8, CLIENT_OS_USER => 7, CLIENT_MACHINE => 10); -- lowest

24. 100% @ L2 100% @ L2 MAILSORTGroup DELIVERYGroup Using Sub-Plans to Limit CPU Utilization MYDB PLAN 30% @ L1 70% @ L1 MAILDBPLAN BUGDB PLAN 100% @ L1 80% @ L1 20% @ L1 100% @ L3 OTHERGroups ONLINEGroup BUG MAINT Group BATCHGroup

25. MAILSORTGroup DELIVERYGroup Limiting CPU Utilization: Example MYDB PLAN 30% @ L1 70% @ L1 MAILDBPLAN BUGDB PLAN 100% @ L1 80% @ L1 20% @ L1 100% @ L2 100% @ L2 100% @ L3 OTHERGroups ONLINEGroup BUG MAINT Group BATCHGroup

26. Creating a Complex Plan

27. Resource Allocation Methods for Resource Plans

28. Comparison of EMPHASIS and RATIO

29. Resource Allocation Methods for Consumer Groups

30. Administering the Resource Manager • Grant privileges to administer the Resource Manager to users • Activate a plan for the database instance • Switch the current group for users or sessions with the package DBMS_RESOURCE_MANAGER or DBMS_SESSION • Monitoring Resource Manager objects: • Consumer groups • Resource plans • Resource plan directives • Resource consumer group mappings

31. Assigning Resource Manager Privileges

32. Setting the Resource Plan for an Instance

33. Changing a Consumer GroupWithin a Session • The user or the application can manually switch the current consumer group. DECLARE old_grp VARCHAR2(32); BEGIN DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP ( new_consumer_group => 'ONLINE_GROUP', old_consumer_group => old_grp, initial_group_on_error => FALSE ); END; /

34. Changing Consumer Groups for Sessions • Can be set by DBA for a session • Can be set by DBA for all sessions for a user EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ( - session_id => 7, - session_serial => 13, - consumer_group => 'ONLINE_GROUP'); EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ( - user => 'SCOTT', - consumer_group => 'BUG_MAINT_GROUP');

35. Database Resource Manager Information

36. Resource Plan Directives SQL> SELECT plan, group_or_subplan, cpu_p1, cpu_p2, 2 cpu_p3, parallel_degree_limit_p1, status 3 FROM DBA_RSRC_PLAN_DIRECTIVES;

37. Monitoring the Resource Manager

38. Monitoring the Resource Manager • V$SESSION: Contains the resource_consumer_group column that shows the current group for a session • V$RSRC_PLAN: A view that shows the active resource plan • V$RSRC_CONSUMER_GROUP: A view that contains statistics for all active groups

39. Summary • In this lesson, you should have learned how to do the following: • Set up Database Resource Manager • Assign users to Resource Manager groups • Create resource plans within groups

40. Practice 16 Overview: Using the Resource Manager • This practice covers the following topics: • Creating resource consumer groups • Specifying CPU resource allocation directives for consumer groups • Associating users with a resource consumer group using Adaptive Consumer Group Mapping