Oracle9i Database Resource Manager Manager Can You Afford Not to Use It

1. Oracle9i Database Resource Manager Manager � Can You Afford Not to Use It? AOTC Spring 2003 Paul Druker - Metaway Corporation Lisa Spory - Northrop Grumman Mission Systems

3. Overview The ability to easily and accurately perform system and resource management is crucial to maintaining application and database performance, scalability, and availability The Oracle9i DRM ensures system resources are applied to the most important tasks of the enterprise at the levels required to meet enterprise goals This tool seems to be underutilized by the Oracle community. Steep learning curve Viewed as a pure DBA tool This presentation will show how both DBAs and developers will benefit from using the tool

4. History Introduced in Oracle8i (8.1.5), significant enhancements in Oracle 9i (9.0.1). Shipped with Enterprise Edition.

5. What is the DRM? The DRM controls the distribution of resources between user sessions by controlling the execution schedule inside the database. Determines which sessions are allowed to run and for how long The basic objective of the DRM is to maximize system throughput in a way that conforms to business objectives. Does not try to enforce CPU allocation percentage limits as long as sessions are getting the resources they need Does not affect the Oracle background processes

6. What is the DRM? May help to solve some of the resource allocation problems that an operating system does not manage well, such as:

7. What is the DRM? Using the DRM, you can: Guarantee certain users a minimum amount of processing resources Distribute available processing resources by allocating percentages of CPU time to different users and applications Limit the degree of parallelism of any operation performed by members of a group of users Create an active session pool. This pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users.

8. What is the DRM?

9. What is the DRM? Using the DRM, you can: Create an undo pool Amount of undo space that can be consumed by a group of users Configure an instance to use a particular method of allocating resources You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.

10. Administering the DRM Elements of the DRM:

11. Administering the DRM Administering the DRM with PL/SQL Packages DBMS_RESOURCE_MANAGER package Allows to maintain plans, consumer groups and plan directives DBMS_RESOURCE_MANAGER_PRIVS package Allows to grant the administrative privilege to other users or roles DBMS_SESSION package Allows user to change current session�s resource consumer group

12. Administering the DRM Administering the DRM with PL/SQL Packages Must have the system privilege ADMINISTER_RESOURCE_MANAGER to administer the DRM Usually DBAs get this privilege with ADMIN option as part of the DBA role DRM Administrator can execute all procedures in the DBMS_RESOURCE_MANAGER package ADMIN option allows to execute procedures in the DBMS_RESOURCE_MANAGER_PRIVS package

13. Administering the DRM Example: Grant DRM Administration privileges to user DRMADMIN without the ADMIN option

14. Administering the DRM Now, the DRMADMIN can execute procedures in the DBMS_RESOURCE_MANAGER package, but not the DBMS_RESOURCE_MANAGER_PRIVS package

15. Administering the DRM How to Activate or Deactivate the DRM Activated by setting the RESOURCE_MANAGER_PLAN initialization parameter

16. Administering the DRM How to Activate or Deactivate the DRM An error message is returned if the specified plan name is incorrect:

17. DRM Contents in Oracle 9.2 The $ORACLE_HOME/rdbms/admin/catrm.sql Catalog script for the DRM Executed from a catproc.sql script Default Resource Consumer Groups LOW_GROUP DEFAULT_CONSUMER_GROUP SYS_GROUP Several Default Resource Plans, such as: INTERNAL_PLAN SYSTEM_PLAN DRM Packages

18. DRM Contents in Oracle 9.2 Static Data Dictionary Views for DRM

19. DRM Contents in Oracle 9.2 Allocation Methods Available for Resource Consumer Groups and Plans

20. DRM Relationships

21. Example 1: Simple Plan, Single-Level Allocation of CPU Time Three Users, Separate Resource Consumer Group for each User: OLTP_USER ? OLTP_GROUP ADHOC_USER ? ADHOC_GROUP BATCH_USER ? BATCH_GROUP

22. Example 1 Con�t

23. Example 1 Con�t

24. Example 1 Con�t

25. Example 1 Con�t

26. Example 1 Con�t

27. Example 1 Analysis

28. Example 2: Complex Plan, Multi-Level Allocation of Resources with Two Subplans

29. Example 2 Con�t

30. Example 2 Con�t

31. Example 2 Con�t

32. Example 2 Con�t

33. Example 2 Con�t

34. Example 2 Con�t

35. Example 2 Con�t

36. Example 2 Con�t

37. Example 2 Con�t

38. Example 3 - Limiting Estimated Execution Time

39. Example 3 � Con�t

40. Example 3 � Con�t

41. Example 3 � Con�t

42. Example 3 � Con�t

43. Example 3 � Con�t

44. Example 3 � Con�t

45. Example 3 � Con�t

46. Example 3 � Con�t

47. Example 3 � Con�t

48. Example 3 � Con�t

49. Example 3 � Con�t

50. Example 3 � Con�t

51. Example 3 � Con�t

52. Example 4 - Switching Resource Consumer Groups

53. Example 4 � Con�t

54. Example 4 � Con�t

55. Administering the DRM through OEM

56. Conclusion When the DRM is Not Appropriate The DRM does not compensate for a poorly tuned system When the DRM Will Not Work If system resources are under-utilized Can You Afford Not to Use it? Administering an overloaded system Different database user types with different resource needs and priorities e.g. OLTP users vs. ADHOC users

57. References Oracle9i Database Concepts. Release 9.2 Oracle9i Database Administrator�s Guide. Release 9.2 Oracle9i Database Reference. Release 9.2 Oracle9i Supplied PL/SQL Packages and Types Reference. Release 9.2 http://metalink.oracle.com OracleMetaLink website http://otn.oracle.com Oracle Technology Network http://asktom.oracle.com Ask Tom website Sushil Kumar. Oracle9i Database Resource Manager Technical Whitepaper, June 2001