Release 2 (9.2)
Part Number A96521-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
Oracle provides database resource management capability through its Database Resource Manager. This chapter introduces you to its use.
The following topics are discussed:
The main goal of the Database Resource Manager is to give the Oracle database server more control over resource management decisions, thus circumventing problems resulting from inefficient operating system management.
This section contains the following topics:
When database resource allocation decisions are left to the operating system, you may encounter the following problems:
Excessive overhead results from operating system context switching between Oracle server processes when the number of server processes is high.
The operating system deschedules Oracle database servers while they hold latches, which is inefficient.
The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.
Oracle's Database Resource Manager helps to overcome these problems by allowing the database more control over how machine resources are allocated.
Specifically, using the Database Resource Manager, you can:
The elements of Oracle's database resource management, which you define through the Database Resource Manager packages, are described below.
You will learn how to create and use these elements in later sections of this chapter.
This section briefly introduces the concept of resource plans. Included are some illustrations of simple resource plans. More complex plans are included in the examples presented later ("Putting It All Together: Database Resource Manager Examples"), after it has been explained how to build and maintain the elements of the Database Resource Manager.
Resource plans specify the resource consumer groups belonging to the plan and contain directives for how resources are to be allocated among these groups. You use the DBMS_RESOURCE_MANAGER package to create and maintain these elements of the Database Resource Manager: resource plans, resource consumer groups, and resource plan directives. Plan information is stored in tables in the data dictionary. Several views are available for viewing plan data.
| See Also:
Oracle9i Database Concepts for more information about the Database Resource Manager |
The first illustration, shown in Figure 27-1, is of a single-level plan, where the plan allocates resources among resource consumer groups. The Great Bread Company has a plan called great_bread that allocates CPU resources among three resource consumer groups. Specifically, sales is allotted 60% of the CPU time, market is allotted 20%, and develop receives the remaining 20%.
Oracle provides a procedure (CREATE_SIMPLE_PLAN) that enables you to quickly create a simple resource plan. This procedure is discussed in "Creating a Simple Resource Plan".
But a plan cannot only contain resource consumer groups, it can also contain other plans, called subplans. Maybe the Great Bread Company chooses to divide their CPU resource as shown in Figure 27-2.
In this case, the great_bread plan still allocates CPU resources to the consumer group market, but now it allocates CPU resources to subplans sales_team and develop_team, who in turn allocate resources to consumer groups. Figure 27-2 illustrates a plan schema, which contains a top plan (great_bread) and all of its descendents.
It is possible for a subplan or consumer group to have more than one parent (owning plan), but there cannot be any loops in a plan schema. An example of a subplan having more that one parent would be if the Great Bread Company had a night plan and a day plan. Both the night plan and the day plan contain the sales subplan as a member, but perhaps with a different CPU resource allocation in each instance.
|
Note: As explained later, the above plans should also contain a plan directive for |
Resource consumer groups are groups of users, or sessions, that are grouped together based on their processing needs. Resource plan directives, discussed next, specify how resources are allocated among consumer groups and subplans in a plan schema.
How resources are allocated to resource consumer groups is specified in resource allocation directives. The Database Resource Manager provides several means of allocating resources.
This method enables you to specify how CPU resources are to be allocated among consumer groups or subplans. The multiple levels of CPU resource allocation (up to eight levels) provide a means of prioritizing CPU usage within a plan schema. Level 2 gets resources only after level 1 is unable to use all of its resources. Multiple levels not only provide a way of prioritizing, but they provide a way of explicitly specifying how all primary and leftover resources are to be used.
You can control the maximum number of concurrently active sessions allowed within a consumer group. This maximum designates the active session pool. When a session cannot be initiated because the pool is full, the session is placed into a queue. When an active session completes, the first session in the queue can then be scheduled for execution. You can also specify a timeout period after which a job in the execution queue (waiting for execution) will timeout, causing it to terminate with an error.
An entire parallel execution session is counted as one active session.
Specifying a parallel degree limit enables you to control the maximum degree of parallelism for any operation within a consumer group.
This method enables you to control resources by specifying criteria that, if met, causes the automatic switching of sessions to another consumer group. The criteria used to determine switching are:
The Database Resource Manager switches a running session to switch group if the session is active for more than switch time seconds. Active means that the session is running and consuming resources, not waiting idly for user input or waiting for CPU cycles. The session is allowed to continue running, even if the active session pool for the new group is full. Under these conditions a consumer group can have more sessions running than specified by its active session pool. Once the session finishes its operation and becomes idle, it is switched back to its original group.
If use estimate is set to TRUE, the Database Resource Manager uses a predicted estimate of how long the operation will take to complete. If Oracle's predicted estimate is longer than the value specified as the switch time, then Oracle switches the session before execution starts. If this parameter is not set, the operation starts normally and only switches groups when other switch criteria are met.
You can specify a maximum execution time allowed for an operation. If Oracle estimates that an operation will run longer than the specified maximum execution time, the operation is terminated with an error. This error can be trapped and the operation rescheduled.
You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds it's undo limit, the current DML statement generating the redo is terminated. No other members of the consumer group can perform further data manipulation until undo space is freed from the pool.
| See Also:
Oracle9i Database Concepts for additional conceptual information about the Database Resource Manager. |
You must have the system privilege ADMINISTER_RESOURCE_MANAGER to administer the Database Resource Manager. Typically, database administrators have this privilege with the ADMIN option as part of the DBA (or equivalent) role.
Being an administrator for the Database Resource Manager allows you to execute all of the procedures in the DBMS_RESOURCE_MANAGER package. These are listed in the following table, and their use is explained in succeeding sections of this chapter.
You may, as an administrator with the ADMIN option, choose to grant the administrative privilege to other users or roles. This is possible using the DBMS_RESOURCE_MANAGER_PRIVS package. This package contains the procedures listed in the table below.
The following example grants the administrative privilege to user scott, but does not grant scott the ADMIN option. Therefore, scott can execute all of the procedures in the DBMS_RESOURCE_MANAGER package, but scott cannot use the GRANT_SYSTEM_PRIVILEGE procedure to grant the administrative privilege to others.
EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE - (GRANTEE_NAME => 'scott', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', - ADMIN_OPTION => FALSE);
You can revoke this privilege using the REVOKE_SYSTEM_PRVILEGE procedure.
The other procedures in the DBMS_RESOURCE_MANAGER_PRIVS package are discussed in "Managing the Switch Privilege".
| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference. contains detailed information about the Database Resource Manager packages: |
You can quickly create a simple resource plan that will be adequate for many situations using the CREATE_SIMPLE_PLAN procedure. This procedure enables you to create consumer groups and allocate resources to them by executing a single statement. Using this procedure, you are not required to invoke the procedures that are described in succeeding sections for creating a pending area, creating each consumer group individually, and specifying resource plan directives.
You can specify the following parameters for the CREATE_SIMPLE_PLAN procedure:
Up to eight consumer groups can be specified using this procedure and the only plan directive that can be specified is for CPU. Each consumer group specified in the plan is allocated its CPU percentage at level 2. Also included in the plan are SYS_GROUP (an Oracle defined groups that is the initial consumer group for the users SYS and SYSTEM) and OTHER_GROUPS.
BEGIN DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1', CONSUMER_GROUP1 => 'mygroup1', GROUP1_CPU => 80, CONSUMER_GROUP2 => 'mygroup2', GROUP2_CPU => 20); END;
Executing the above statements creates the following plan:
| Consumer Group | Level 1 | Level 2 | Level 3 |
|---|---|---|---|
|
|
100% |
- |
- |
|
|
- |
80% |
- |
|
|
- |
20% |
- |
|
|
- |
- |
100% |
This section describes the actions and DBMS_RESOURCE_MANAGER procedures that you can use when your situation requires that you create more complex resource plans. It contains the following sections:
The first thing you must do to create or modify plan schemas is to create a pending area. This is a scratch area allowing you to stage your changes and to validate them before they are made active.
To create a pending area, you use the following statement:
EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
In effect, what is really happening here is that you are making the pending area active and "loading" all existing, or active, plan schemas into the pending area so that they can be updated or new plans added. Active plan schemas are those schemas already stored in the data dictionary for use by the Database Resource Manager. If you attempt to update a plan or add a new plan without first activating (creating) the pending area, you will receive an error message notifying you that the pending area is not active.
Views are available for inspecting all active resource plan schemas as well as the pending ones. These views are listed in Viewing Database Resource Manager Information.
At any time when you are making changes in the pending area you can call the validate procedure as shown here.
EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
This procedure checks whether changes that have been made are valid. The following rules must be adhered to, and are checked by the validate procedure:
OTHER_GROUPS somewhere in any active plan schema. This ensures that a session which is not part of any of the consumer groups included in the currently active plan is allocated resources (as specified by the OTHER_GROUPS directive).You will receive an error message if any of the above rules are violated. You can then make changes to fix any problems and call the validate procedure again.
It is possible to create "orphan" consumer groups that have no plan directives referring to them. This allows the creation of consumer groups that will not currently be used, but may be part of some plan to be implemented in the future.
After you have validated your changes, call the submit procedure to make your changes active.
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
The submit procedure also performs validation, so you do not necessarily need to make separate calls to the validate procedure. However, if you are making major changes to plan schemas, debugging problems is often easier if you incrementally validate your changes. No changes are submitted (made active) until validation is successful on all of the changes in the pending area.
The SUBMIT_PENDING_AREA procedure clears (deactivates) the pending area after successfully validating and committing the changes.
There is also a procedure for clearing the pending area at any time. This statement causes all of your changes to be cleared from the pending area:
EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
You must call the CREATE_PENDING_AREA procedure before you can again attempt to make changes.
When you create a resource plan, you can specify the following parameters:
Oracle provides one resource plan, SYSTEM_PLAN, that contains a simple structure that may be adequate for some environments. It is illustrated later in "An Oracle Supplied Plan".
| See Also:
Oracle9i Database Concepts contains detailed descriptions of the resource allocation methods |
You create a plan using the CREATE_PLAN procedure. The following creates a plan called great_bread. You choose to use the default resource allocation methods.
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'great_bread', - COMMENT => 'great plan');
Use the UPDATE_PLAN procedure to update plan information. If you do not specify the arguments for the UPDATE_PLAN procedure, they remain unchanged in the data dictionary. The following statement updates the COMMENT parameter.
EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN(PLAN => 'great_bread', - NEW_COMMENT => 'great plan for great bread');
The DELETE_PLAN procedure deletes the specified plan as well as all the plan directives associated with it. The following statement deletes the great_bread plan and its directives.
EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'great_bread');
The resource consumer groups themselves are not deleted, but they are no longer associated with the great_bread plan.
The DELETE_PLAN_CASCADE procedure deletes the specified plan as well as all its descendants (plan directives, subplans, resource consumer groups). If DELETE_PLAN_CASCADE encounters an error, it will roll back, leaving the plan schema unchanged.
When you create a resource consumer group, you can specify the following parameters:
There are two special consumer groups that are always present in the data dictionary, and they cannot be modified or deleted. These are:
DEFAULT_CONSUMER_GROUP
This is the initial consumer group for all users/sessions that have not been explicitly assigned an initial consumer group. DEFAULT_CONSUMER_GROUP has switch privileges granted to PUBLIC; therefore, all users are automatically granted switch privilege for this consumer group (see "Managing the Switch Privilege").
OTHER_GROUPS
This consumer group cannot can be explicitly assigned to a user. OTHER_GROUPS must have a resource directive specified in the schema of any active plan. This group applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema, including DEFAULT_CONSUMER_GROUP.
Additionally, two other groups, SYS_GROUP and LOW_GROUP, are provided as part of the Oracle supplied SYSTEM_PLAN that is described in "An Oracle Supplied Plan".
You create a consumer group using the CREATE_CONSUMER_GROUP procedure. The following creates a consumer group called sales. Remember, the pending area must be active to execute this statement successfully.
EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'sales', - COMMENT => 'retail and wholesale sales');
Use the UPDATE_CONSUMER_GROUP procedure to update consumer group information. If you do not specify the arguments for the UPDATE_CONSUMER_GROUP procedure, they remain unchanged in the data dictionary.
The DELETE_CONSUMER_GROUP procedure deletes the specified consumer group. Upon deletion of a consumer group, all users having the deleted group as their initial consumer group will have the DEFAULT_CONSUMER_GROUP set as their initial consumer group. All currently running sessions belonging to a deleted consumer group will be switched to DEFAULT_CONSUMER_GROUP.
Resource plan directives assign consumer groups to resource plans and provide the parameters for each resource allocation method. When you create a resource plan directive, you can specify the following parameters
You use the CREATE_PLAN_DIRECTIVE to create a resource plan directive. The following statement creates a resource plan directive for plan great_bread.
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', - GROUP_OR_SUBPLAN => 'sales', COMMENT => 'sales group', - CPU_P1 => 60, PARALLEL_DEGREE_LIMIT_P1 => 4);
To complete the plan, similar to that shown in Figure 27-1, execute the following statements:
BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', GROUP_OR_SUBPLAN => 'market', COMMENT => 'marketing group', CPU_P1 => 20); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', GROUP_OR_SUBPLAN => 'develop', COMMENT => 'development group', CPU_P1 => 20); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'this one is required', CPU_P1 => 0, CPU_P2 => 100); END;
In this plan, consumer group sales has a maximum degree of parallelism of 4 for any operation, while none of the other consumer groups are limited in their degree of parallelism. Also, whenever there are leftover level 1 CPU resources, they are allocated (100%) to OTHER_GROUPS.
Use the UPDATE_PLAN_DIRECTIVE procedure to update plan directives. This example changes CPU allocation for resource consumer group develop.
EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'great_bread', - GROUP_OR_SUBPLAN => 'develop', NEW_CPU_P1 => 15);
If you do not specify the arguments for the UPDATE_PLAN_DIRECTIVE procedure, they remain unchanged in the data dictionary.
To delete a resource plan directive, use the DELETE_PLAN_DIRECTIVE procedure
If there are multiple resource plan directives that refer to the same consumer group, then the following rules apply for specific cases:
Before you enable the Database Resource Manager, you must assign resource consumer groups to users. In addition to providing procedures to create, update, or delete the elements used by the Database Resource Manager, the DBMS_RESOURCE_MANAGER package contains the procedure to assign resource consumer groups to users. It also provides procedures that allow you to temporarily switch a user session to another consumer group.
The DBMS_RESOURCE_MANAGER_PRIVS package, described earlier for granting the Database Resource Manager system privilege, can also be used to grant the switch privilege to another user, who can then alter their own consumer group.
You do not use a pending area for any of the procedures discussed below.
The initial consumer group of a user is the consumer group to which any session created by that user initially belongs. The user's initial consumer group is automatically set to DEFAULT_CONSUMER_GROUP when the user is created.
A user (or PUBLIC) must be granted permission to switch to a specific consumer group before that consumer group can become the user's initial consumer group. This permission is called the switch privilege, and is explained in "Managing the Switch Privilege". The switch privilege to an initial consumer group cannot come from a role granted to that user.
The following statements illustrate setting a user's initial consumer group.
EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('scott', 'sales',- TRUE); EXEC DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('scott', 'sales');
There are two procedures, which are part of the DBMS_RESOURCE_MANAGER package, that allow administrators to change the resource consumer group of running sessions. Both of these procedures can also change the consumer group of any parallel execution server sessions associated with the coordinator's session. The changes made by these procedures pertain to current sessions only; they are not persistent. They also do not change the initial consumer groups for users.
Instead of killing a session of a user who is using excessive CPU, an administrator can instead change that user's consumer group to one that is allowed less CPU. Or, this switching can be enforced automatically, using automatic consumer group switching resource plan directives.
The SWITCH_CONSUMMER_GROUP_FOR_SESS causes the specified session to immediately be moved into the specified resource consumer group. In effect, this statement can raise or lower priority. The following statement changes the resource consumer group of a specific session to a new consumer group. The session identifier (SID) is 17, the session serial number (SERIAL#) is 12345, and the session is to be changed to the high_priority consumer group.
EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('17', '12345', - 'high_priorty');
The SID, session serial number, and current resource consumer group for a session are viewable using the V$SESSION data dictionary view.
The SWITCH_CONSUMER_GROUP_FOR_USER procedure changes the resource consumer group for all sessions with a given user name.
EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('scott', - 'low_group');
Using the DBMS_RESOURCE_MANAGER_PRIVS package, you can grant or revoke the switch privilege to a user, role, or PUBLIC. The switch privilege gives users the privilege to switch their current resource consumer group to a specified resource consumer group. The package also enables you to revoke the switch privilege.
The actual switching is done by executing a procedure in the DBMS_SESSION package. A user who has been granted the switch privilege (or a procedure owned by that user) can use the SWITCH_CURRENT_CONSUMER_GROUP procedure to switch to another resource consumer group. The new group must be one to which the user has been specifically authorized to switch.
The following example grants the privilege to switch to a consumer group. User scott is granted the privilege to switch to consumer group bug_batch_group.
EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('scott', - 'bug_batch_group', TRUE);
User scott is also granted permission to grant switch privileges for bug_batch_group to others.
If you grant a user permission to switch to a particular consumer group, then that user can switch their current consumer group to the new consumer group.
If you grant a role permission to switch to a particular resource consumer group, then any users who have been granted that role and have enabled that role can immediately switch their current consumer group to the new consumer group.
If you grant PUBLIC the permission to switch to a particular consumer group, then any user can switch to that group.
If the GRANT_OPTION argument is TRUE, then users granted switch privilege for the consumer group can also grant switch privileges for that consumer group to others.
The following example revokes user scott's privilege to switch to consumer group bug_batch_group.
EXEC DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP ('scott', - 'bug_batch_group');
If you revoke a user's switch privileges to a particular consumer group, then any subsequent attempts by that user to switch to that consumer group will fail. If you revoke the initial consumer group from a user, then that user will automatically be part of the DEFAULT_CONSUMER_GROUP when logging in.
If you revoke a role's switch privileges to a consumer group, then any users who only had switch privilege for the consumer group through that role will not be able to subsequently switch to that consumer group.
If you revoke switch privileges to a consumer group from PUBLIC, then any users other than those who are explicitly assigned switch privileges either directly or through PUBLIC, will not be able to subsequently switch to that consumer group.
If granted the switch privilege, users can switch their current consumer group using the SWITCH_CURRENT_CONSUMER_GROUP procedure in the DBMS_SESSION package.
This procedure enables users to switch to a consumer group for which they have the switch privilege. If the caller is another procedure, then this procedure enables users to switch to a consumer group for which the owner of that procedure has switch privileges.
The parameters for this procedure are:
The following example illustrates switching to a new consumer group. By printing the value of the output parameter old_group, we illustrate how the old consumer group name has been saved.
SET serveroutput on DECLARE old_group varchar2(30); BEGIN DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('sales', old_group, FALSE); DBMS_OUTPUT.PUT_LINE('OLD GROUP = ' || old_group); END;
The following line is output:
OLD GROUP = DEFAULT_CONSUMER_GROUP
The DBMS_SESSION package can be used from within a PL/SQL application, thus allowing the application to change consumer groups, or effectively priority, dynamically.
| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for additional examples and more information about the |
You enable the Database Resource Manager by setting the RESOURCE_MANAGER_PLAN initialization parameter. This parameter specifies the top plan, identifying the plan schema to be used for this instance. If no plan is specified with this parameter, the Database Resource Manager is not activated. The following example activates the Database Resource Manager and assigns the top plan as mydb_plan.
RESOURCE_MANAGER_PLAN = mydb_plan
You can also activate or deactivate the Database Resource Manager, or change the current top plan, using the ALTER SYSTEM statement. In this example, the top plan is specified as mydb_plan.
ALTER SYSTEM SET RESOURCE _MANAGER_PLAN = mydb_plan;
An error message is returned if the specified plan does not exist in the data dictionary.
To deactivate the Database Resource Manager, issue the following statement:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
This section provides some examples of resource plan schemas. The following examples are presented:
The following statements create a multilevel schema as illustrated in Figure 27-3. They use default plan and resource consumer group methods.
BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'bugdb_plan', COMMENT => 'Resource plan/method for bug users sessions'); DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'maildb_plan', COMMENT => 'Resource plan/method for mail users sessions'); DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'mydb_plan', COMMENT => 'Resource plan/method for bug and mail users sessions'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Online_group', COMMENT => 'Resource consumer group/method for online bug users sessions'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Batch_group', COMMENT => 'Resource consumer group/method for batch job bug users sessions'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maintenance_group', COMMENT => 'Resource consumer group/method for users sessions for bug db maint'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_users_group', COMMENT => 'Resource consumer group/method for mail users sessions'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Postman_group', COMMENT => 'Resource consumer group/method for mail postman'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maintenance_group', COMMENT => 'Resource consumer group/method for users sessions for mail db maint'); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 'Bug_Online_group', COMMENT => 'online bug users sessions at level 1', CPU_P1 => 80, CPU_P2=> 0, PARALLEL_DEGREE_LIMIT_P1 => 8); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 'Bug_Batch_group', COMMENT => 'batch bug users sessions at level 1', CPU_P1 => 20, CPU_P2 => 0, PARALLEL_DEGREE_LIMIT_P1 => 2); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 'Bug_Maintenance_group', COMMENT => 'bug maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 100, PARALLEL_DEGREE_LIMIT_P1 => 3); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0, CPU_P3 => 100); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 'Mail_Postman_group', COMMENT => 'mail postman at level 1', CPU_P1 => 40, CPU_P2 => 0, PARALLEL_DEGREE_LIMIT_P1 => 4); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 'Mail_users_group', COMMENT => 'mail users sessions at level 2', CPU_P1 => 0, CPU_P2 => 80, PARALLEL_DEGREE_LIMIT_P1 => 4); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 'Mail_Maintenance_group', COMMENT => 'mail maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 20, PARALLEL_DEGREE_LIMIT_P1 => 2); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0, CPU_P3 => 100); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', GROUP_OR_SUBPLAN => 'maildb_plan', COMMENT=> 'all mail users sessions at level 1', CPU_P1 => 30); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', GROUP_OR_SUBPLAN => 'bugdb_plan', COMMENT => 'all bug users sessions at level 1', CPU_P1 => 70); DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END;
The preceding call to VALIDATE_PENDING_AREA is optional because the validation is implicitly performed in SUBMIT_PENDING_AREA.
The example presented here could represent a plan for a database supporting a packaged ERP (Enterprise Resource Planning) or CRM (Customer Relationship Management). The work in such an environment can be highly varied. There may be a mix of short transactions and quick queries, in combination with longer running batch jobs that include large parallel queries. The goal is to give good response time to OLTP (Online Transaction Processing), while allowing batch jobs to run in parallel.
The plan is summarized in the following table.
The following statements create the above plan, which is named erp_plan:
BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'erp_plan', COMMENT => 'Resource plan/method for ERP Database'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp', COMMENT => 'Resource consumer group/method for OLTP jobs'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch', COMMENT => 'Resource consumer group/method for BATCH jobs'); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80, SWITCH_GROUP => 'batch', SWITCH_TIME => 3,SWITCH_ESTIMATE => TRUE, UNDO_POOL => 200); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', GROUP_OR_SUBPLAN => 'batch', COMMENT => 'BATCH sessions', CPU_P2 => 100, ACTIVE_SESS_POOL_P1 => 5, QUEUEING_P1 => 600, MAX_EST_EXEC_TIME => 3600); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory', CPU_P3 => 100); DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END;
Oracle provides one default resource manager plan, SYSTEM_PLAN, which gives priority to system sessions. SYSTEM_PLAN is defined as follows:
| CPU Resource Allocation | |||
|---|---|---|---|
| Resource Consumer Group | Level 1 | Level 2 | Level 3 |
|
|
100% |
0% |
0% |
|
|
0% |
100% |
0% |
|
|
0% |
0% |
100% |
The Oracle provided groups in this plan are:
SYS_GROUP is the initial consumer group for the users SYS and SYSTEM.OTHER_GROUPS applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema.LOW_GROUP provides a group having lower priority than SYS_GROUP and OTHER_GROUPS in this plan. It is up to you to decide which user sessions will be part of LOW_GROUP. Switch privilege is granted to PUBLIC for this group.These groups can be used, or not used, and can be modified or deleted.
You can use this simple Oracle provided plan if it is appropriate for your environment.
To effectively monitor and tune the Database Resource Manager, you must design a representative environment. The Database Resource Manager works best in large production environments in which system utilization is high. If a test places insufficient load on the system, measured CPU allocations can be very different from the allocations specified in the active resource plan.
To create a representative environment, there must be sufficient load (demand for CPU resources) to make CPU resources scarce. If the following rules are followed, the test environment should generate actual (measured) resource allocations that match those specified in the active resource plan.
BEGIN DECLARE m NUMBER; BEGIN FOR i IN 1..100000 LOOP FOR j IN 1..100000 LOOP m := sqrt(4567); END LOOP; END LOOP; END; END; /
When every group can secure as much CPU resources as it demands, the Database Resource Manager first seeks to maximize system throughput, not to enforce allocation percentages. For example, consider the following conditions:
In this case, the measured CPU allocation to each consumer group will be 25%, no matter what the allocations specified in the active resource plan.
Another factor determines the calculation in (1) above. Processor affinity scheduling at the operating system level can distort CPU allocation on underutilized systems. This is explained in the following paragraphs.
Until the number of concurrently running processes reaches a certain level, typical operating system scheduling algorithms will prevent full utilization. The Database Resource Manager controls CPU usage by restricting the number of running processes. By deciding which processes are allowed to run and for what duration, the Database Resource Manager controls CPU resource allocation. When a CPU has resources available, and other processors are fully utilized, the operating system migrates processes to the underutilized processor, but not immediately.
With processor affinity, the operating system waits (for a time) to migrate processes, "hoping" that another process will be dispatched to run instead of forcing process migration from one CPU to another. On a fully loaded system with enough processes waiting, this strategy will work. In large production environments, processor affinity increases performance significantly, because invalidating the current CPU cache and then loading the new one is quite expensive. Since processes have processor affinity on most platforms, more processes than CPUs for each consumer group must be run. Otherwise, full system utilization is not possible.
Use the V$RSRC_CONSUMER_GROUP view to monitor CPU usage. It provides the cumulative amount of CPU time consumed by all sessions in each consumer group. It also provides a number of other measures helpful for tuning.
SQL> SELECT NAME, CONSUMED_CPU_TIME FROM V$RSRC_CONSUMER_GROUP; NAME CONSUMED_CPU_TIME -------------------------------- ----------------- OTHER_GROUPS 14301 TEST_GROUP 8802 TEST_GROUP2 0 3 rows selected.
The following table lists views that are associated with Database Resource Manager:
You can use these views for viewing privileges, viewing plan schemas, or you can monitor them to gather information for tuning the Database Resource Manager. Some examples of their use follow.
| See Also:
Oracle9i Database Reference for detailed information about the contents of each of these views |
The DBA_RSRC_CONSUMER_GROUP_PRIVS view displays the consumer groups granted to users or roles. Specifically, it displays the groups to which a user or role is allowed to belong or be switched. For example, in the view shown below, user scott can belong to the consumer groups market or sales, he has the ability to assign (grant) other users to the sales group but not the market group. Neither group is his initial consumer group.
SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS; GRANTEE GRANTED_GROUP GRA INI ------------------------------ ------------------------------ --- --- PUBLIC DEFAULT_CONSUMER_GROUP YES YES PUBLIC LOW_GROUP NO NO SCOTT MARKET NO NO SCOTT SALES YES NO SYSTEM SYS_GROUP NO YES
Scott was granted the ability to switch to these groups using the DBMS_RESOURCE_MANAGER_PRIVS package.
This example shows using the DBA_RSRC_PLANS view to display all of the resource plans defined in the database. All of the plans displayed are active, meaning they are not staged in the pending area
SQL> SELECT PLAN,COMMENTS,STATUS FROM DBA_RSRC_PLANS; PLAN COMMENTS STATUS ----------- ------------------------------------------------------- ------ SYSTEM_PLAN Plan to give system sessions priority ACTIVE BUGDB_PLAN Resource plan/method for bug users sessions ACTIVE MAILDB_PLAN Resource plan/method for mail users sessions ACTIVE MYDB_PLAN Resource plan/method for bug and mail users sessions ACTIVE GREAT_BREAD Great plan for great bread ACTIVE ERP_PLAN Resource plan/method for ERP Database ACTIVE 6 rows selected.
You can use the V$SESSION view to display the consumer groups that are currently assigned to sessions.
SQL> SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION; SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP ----- ------- ------------------------ -------------------------------- . . . 11 136 SYS SYS_GROUP 13 16570 SCOTT SALES 10 rows selected.
This example sets mydb_plan, as created by the statements shown earlier in "Multilevel Schema Example", as the top level plan. The V$RSRC_PLAN view is queried to display the currently active plans.
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan; System altered. SQL> SELECT * FROM V$RSRC_PLAN; NAME -------------------------------- MYDB_PLAN MAILDB_PLAN BUGDB_PLAN
|
 Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|