1 / 37

Managing Resources

Managing Resources. 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. Overview. Manage mixed workload Control system performance.

gur
Download Presentation

Managing Resources

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. 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

  6. 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

  7. 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.

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

  9. Setting the Active Session Pool

  10. 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

  11. 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

  12. 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 = ...;

  13. Configuring Consumer Group Switching

  14. Adaptive Consumer Group Mapping

  15. 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');

  16. 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

  17. The Initial Plan: SYSTEM_PLAN

  18. 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.

  19. Oracle Enterprise Manager:Resource Manager

  20. 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;

  21. Creating a Complex Plan

  22. Resource Allocation Methods for Resource Plans

  23. Comparison of EMPHASIS and RATIO

  24. Creating Resource Consumer Groups

  25. Assigning Users to Consumer Groups

  26. Resource Allocation Methods for Consumer Groups

  27. 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

  28. Assigning Resource Manager Privileges

  29. Setting the Resource Plan for an Instance

  30. 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; /

  31. 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');

  32. Database Resource Manager Information

  33. 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;

  34. Monitoring the Resource Manager

  35. 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

  36. 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

  37. Practice 15 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

More Related