1 / 40

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.

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

More Related