Oracle Database provides database resource management capability through its Database Resource Manager. This chapter introduces you to its use and contains the following topics:
Viewing Database Resource Manager Information
Note:
This chapter discusses the use of the Oracle-suppliedDBMS_RESOURCE_MANAGERandDBMS_RESOURCE_MANAGER_PRIVSpackages to administer the Database Resource Manager. You can more easily administer the Database Resource Manager through the Oracle Enterprise Manager (EM). It provides an easy to use graphical interface for administering the Database Resource Manager. See the Oracle Enterprise Manager documentation set for more information.
What Is the Database Resource Manager?
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:
What Problems Does the Database Resource Manager Address?
When database resource allocation decisions are left to the operating system, you may encounter the following problems:
Excessive overhead
Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.
Inefficient scheduling
The operating system deschedules database servers while they hold latches, which is inefficient.
Inappropriate allocation of resources
The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.
Inability to manage database-specific resources, such as parallel execution servers and active sessions
How Does the Database Resource Manager Address These Problems?
The Oracle 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:
Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users
Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP (relational on-line analytical processing) applications than to batch jobs.
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. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will terminate.
Allow automatic switching of users from one group to another group based on administrator defined criteria. If a member of a particular group of users creates a session that executes for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.
Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit
Create an undo pool. This pool consists of the amount of undo space that can be consumed in by a group of users.
Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.
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.
Allow the cancellation of long-running SQL statements and the termination of long-running sessions.
What Are the Elements of the Database Resource Manager?
The elements of database resource management, which you define through the Database Resource Manager packages, are described below.
| Element | Description |
|---|---|
| Resource consumer group | User sessions grouped together based on resource processing requirements. |
| Resource plan | Contains directives that specify how resources are allocated to resource consumer groups. |
| Resource allocation method | The method/policy used by the Database Resource Manager when allocating for a particular resource; used by resource consumer groups and resource plans. The database provides the resource allocation methods that are available, but you determine which method to use. |
| Resource plan directive | Used by administrators to associate resource consumer groups with particular plans and allocate resources among resource consumer groups. |
You will learn how to create and use these elements in later sections of this chapter.
Understanding Resource Plans
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.
A Single-Level Resource Plan
The first illustration, shown in Figure 24-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 Database 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 ".
A Multilevel Resource Plan
In addition to containing resource consumer groups, a plan can contain subplans. Perhaps the Great Bread Company chooses to divide their CPU resource as shown in Figure 24-2. The figure illustrates a plan schema, which contains a top plan (GREAT_BREAD) and all of its descendents.
In this case, the GREAT_BREAD plan still allocates 20% of CPU resources to the consumer group MARKET. However, now it allocates CPU resources to subplans SALES_TEAM (60%), which in turn divides its share equally between the WHOLESALE and RETAIL groups, and DEVELOP_TEAM (20%), which in turn divides its resources equally between the BREAD and MUFFIN groups.
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 in subsequent sections, the plans described should also contain a plan directive forOTHER_GROUPS. To present a simplified view, however, this plan directive is not shown. |
Resource Consumer Groups
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.
Resource Plan Directives
How resources are allocated to resource consumer groups is specified in resource allocation directives. The Database Resource Manager provides several means of allocating resources.
CPU Method
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.
Active Session Pool with Queuing
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.
Degree of Parallelism Limit
Specifying a parallel degree limit enables you to control the maximum degree of parallelism for any operation within a consumer group.
Automatic Consumer Group Switching
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:
Switch group: specifies the consumer group to which this session is switched if the other (following) criteria are met
Switch time: specifies the length of time that a session can execute before it is switched to another consumer group
Switch time in call: specifies the length of time that a session can execute before it is switched to another consumer group. Once the top call finishes, the session is restored to its original consumer group.
Use estimate: specifies whether the database is to use its own estimate of how long an operation will execute
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 the database estimate is longer than the value specified as the switch time, then the database 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.
Switch time in call is useful for three-tier applications where the middle tier server is using session pooling. At the end of every top call, a session is switched back to its original consumer group--that is, the group it would be in had it just logged in. A top call in PL/SQL is an entire PL/SQL block being treated as one call. A top call in SQL is an individual SQL statement issued separately by the client being treated as a one call.
You cannot specify both switch time in call and switch time.
Canceling SQL and Terminating Sessions
You can also specify directives to cancel long-running SQL queries or to terminate long-running sessions. You specify this by setting CANCEL_SQL or KILL_SESSION as the switch group.
Execution Time Limit
You can specify a maximum execution time allowed for an operation. If the database 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.
Undo Pool
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 its 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.
Idle Time Limit
You can specify an amount of time that a session can be idle, after which it will be terminated. You can further restrict such termination to only sessions that are blocking other sessions.
Administering 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 lets you 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.
| Procedure | Description |
|---|---|
CREATE_SIMPLE_PLAN | Creates a simple resource plan, containing up to eight consumer groups, in one step. This is the quickest way to get started when you use this package. |
CREATE_PLAN | Creates a resource plan and specifies its allocation methods. |
UPDATE_PLAN | Updates a resource plan. |
DELETE_PLAN | Deletes a resource plan and its directives. |
DELETE_PLAN_CASCADE | Deletes a resource plan and all of its descendents. |
CREATE_CONSUMER_GROUP | Creates a resource consumer group. |
UPDATE_CONSUMER_GROUP | Updates a consumer group. |
DELETE_CONSUMER_GROUP | Deletes a consumer group. |
CREATE_PLAN_DIRECTIVE | Specifies the resource plan directives that allocate resources to resource consumer groups within a plan or among subplans in a multilevel plan schema. |
UPDATE_PLAN_DIRECTIVE | Updates plan directives |
DELETE_PLAN_DIRECTIVE | Deletes plan directives |
CREATE_PENDING_AREA | Creates a pending area (scratch area) within which changes can be made to a plan schema |
VALIDATE_PENDING_AREA | Validates the pending changes to a plan schema |
CLEAR_PENDING_AREA | Clears all pending changes from the pending area |
SUBMIT_PENDING_AREA | Submits all changes for a plan schema |
SET_INITIAL_CONSUMER_GROUP | Sets the initial consumer group for a user. This procedure has been deprecated. The database recommends that you use the SET_CONSUMER_GROUP_MAPPING procedure to specify the initial consumer group. |
SWITCH_CONSUMER_GROUP_FOR_SESS | Switches the consumer group of a specific session |
SWITCH_CONSUMER_GROUP_FOR_USER | Switches the consumer group of all sessions belonging to a specific user |
SET_CONSUMER_GROUP_MAPPING | Maps sessions to consumer groups |
SET_CONSUMER_MAPPING_PRI | Establishes session attribute mapping priorities |
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.
| Procedure | Description |
|---|---|
GRANT_SYSTEM_PRIVILEGE | Grants ADMINISTER_RESOURCE_MANAGER system privilege to a user or role. |
REVOKE_SYSTEM_PRIVILEGE | Revokes ADMINISTER_RESOURCE_MANAGER system privilege from a user or role. |
GRANT_SWITCH_CONSUMER_GROUP | Grants permission to a user, role, or PUBLIC to switch to a specified resource consumer group. |
REVOKE_SWITCH_CONSUMER_GROUP | Revokes permission for a user, role, or PUBLIC to switch to a specified resource consumer group. |
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.
Note: TheADMINISTER_RESOURCE_MANAGER system privilege can only be granted or revoked by using the DBMS_RESOURCE_MANAGER_PRIVS package. It cannot be granted or revoked through the SQL GRANT or REVOKE statements. |
The other procedures in the DBMS_RESOURCE_MANAGER_PRIVS package are discussed in "Managing the Switch Privilege".
See Also: PL/SQL Packages and Types Reference. contains detailed information about the Database Resource Manager packages:
|
Creating a Simple Resource Plan
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:
| Parameter | Description |
|---|---|
SIMPLE_PLAN | Name of the plan |
CONSUMER_GROUP1 | Consumer group name for first group |
GROUP1_CPU | CPU resource allocated to this group |
CONSUMER_GROUP2 | Consumer group name for second group |
GROUP2_CPU | CPU resource allocated to this group |
CONSUMER_GROUP3 | Consumer group name for third group |
GROUP3_CPU | CPU resource allocated to this group |
CONSUMER_GROUP4 | Consumer group name for fourth group |
GROUP4_CPU | CPU resource allocated to this group |
CONSUMER_GROUP5 | Consumer group name for fifth group |
GROUP5_CPU | CPU resource allocated to this group |
CONSUMER_GROUP6 | Consumer group name for sixth group |
GROUP6_CPU | CPU resource allocated to this group |
CONSUMER_GROUP7 | Consumer group name for seventh group |
GROUP7_CPU | CPU resource allocated to this group |
CONSUMER_GROUP8 | Consumer group name for eighth group |
GROUP8_CPU | CPU resource allocated to this group |
Up to eight consumer groups can be specified using this procedure and the only plan directive that can be specified is for CPU. The plan uses the EMPHASIS CPU allocation policy and each consumer group uses the ROUND_ROBIN scheduling policy.Each consumer group specified in the plan is allocated its CPU percentage at level 2. Also included in the plan are SYS_GROUP (a system-defined group that is the initial consumer group for the users SYS and SYSTEM) and OTHER_GROUPS.
Example: Using the CREATE_SIMPLE_PLAN Procedure
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 preceding statements creates the following plan:
| Consumer Group | Level 1 | Level 2 | Level 3 |
|---|---|---|---|
SYS_GROUP | 100% | - | - |
mygroup1 | - | 80% | - |
mygroup2 | - | 20% | - |
OTHER_GROUPS | - | - | 100% |
Creating Complex Resource Plans
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:
Using the Pending Area for Creating Plan Schemas
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.
Creating a Pending Area
To create a pending area, you use the following statement:
EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
In effect, 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.
Validating Changes
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:
No plan schema can contain any loops.
All plans and resource consumer groups referred to by plan directives must exist.
All plans must have plan directives that point to either plans or resource consumer groups.
All percentages in any given level must not add up to greater than 100.
A plan that is currently being used as a top plan by an active instance cannot be deleted.
The following plan directive parameters can appear only in plan directives that refer to resource consumer groups (not other resource plans):
PARALLEL_DEGREE_LIMIT_P1ACTIVE_SESS_POOL_P1QUEUEING_P1SWITCH_GROUPSWITCH_TIMESWITCH_ESTIMATEMAX_EST_EXEC_TIMEUNDO_POOLMAX_IDLE_TIMEMAX_IDLE_BLOCKER_TIMESWITCH_TIME_IN_CALL
There can be no more than 32 resource consumer groups in any active plan schema. Also, at most, a plan can have 32 children.
Plans and resource consumer groups cannot have the same name.
There must be a plan directive for
OTHER_GROUPSsomewhere 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 theOTHER_GROUPSdirective).
You will receive an error message if any of the preceding 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.
Submitting Changes
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.
Note: A call toSUBMIT_PENDING_AREA may fail even if VALIDATE_PENDING_AREA succeeds. This can happen if, for example, a plan being deleted is loaded by an instance after a call to VALIDATE_PENDING_AREA, but before a call to SUBMIT_PENDING_AREA. |
Clearing the Pending Area
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.
Creating Resource Plans
When you create a resource plan, you can specify the parameters shown in the following table. The first parameter is required; the remainder are optional.
| Parameter | Description |
|---|---|
PLAN | Name of the plan. |
COMMENT | Any descriptive comment. |
CPU_MTH | Resource allocation method for specifying how much CPU each consumer group or subplan gets. EMPHASIS, the default method, is for multilevel plans that use percentages to specify how CPU is distributed among consumer groups. RATIO is for single-level plans that use ratios to specify how CPU is distributed. |
ACTIVE_SESS_POOL_MTH | Active session pool resource allocation method. Limits the number of active sessions. All other sessions are inactive and wait in a queue to be activated. ACTIVE_SESS_POOL_ABSOLUTE is the default and only method available. |
PARALLEL_DEGREE_LIMIT_MTH | Resource allocation method for specifying a limit on the degree of parallelism of any operation. PARALLEL_DEGREE_LIMIT_ABSOLUTE is the default and only method available. |
QUEUEING_MTH | Queuing resource allocation method. Controls order in which queued inactive sessions will execute. FIFO_TIMEOUT is the default and only method available. |
Oracle Database 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".
Creating a Plan
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');
Updating a 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');
Deleting a Plan
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.
Using the Ratio Policy
the RATIO policy is a single-level CPU allocation method. Instead of percentages, you specify numbers corresponding to the ratio of CPU you want to give to the consumer group. For example, given three consumer groups GOLD_CG, SILVER_CG, and BRONZE_CG, assume that we specify the following plan directives:
DBMS_RESOURCE_MANAGER.CREATE_PLAN
(PLAN => 'service_level_plan',
CPU_MTH -> 'RATIO',
COMMENT => 'service level plan');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'GOLD_CG',
COMMENT => 'Gold service level customers',
CPU_P1 => 10);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'SILVER_CG',
COMMENT => 'Silver service level customers',
CPU_P1 => 5);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'BRONZE_CG',
COMMENT => 'Bonze service level customers',
CPU_P1 => 2);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'service_level_plan',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'Lowest priority sessions',
CPU_P1 => 1);
The ratio of CPU allocation would be 10:5:2:1 for the GOLD_CG, SILVER_CG, BRONZE_CG, and OTHER_GROUPS consumer groups, respectively.
If sessions exists only in the GOLD_CG and SILVER_CG consumer groups, then the ratio of CPU allocation would be 10:5 between the two groups.
Creating Resource Consumer Groups
When you create a resource consumer group, you can specify the following parameters:
| Parameter | Description |
|---|---|
CONSUMER_GROUP | Name of the consumer group. |
COMMENT | Any comment. |
CPU_MTH | The resource allocation method for distributing CPU among sessions in the consumer group. The default is ROUND_ROBIN, which uses a round-robin scheduler to ensure that sessions are fairly executed. RUN_TO_COMPLETION specifies that sessions with the largest active time are scheduled ahead of other sessions. |
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_GROUPThis is the initial consumer group for all users/sessions that have not been explicitly assigned an initial consumer group.
DEFAULT_CONSUMER_GROUPhas switch privileges granted toPUBLIC; therefore, all users are automatically granted switch privilege for this consumer group (see "Managing the Switch Privilege").OTHER_GROUPSThis consumer group cannot be explicitly assigned to a user.
OTHER_GROUPSmust 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, includingDEFAULT_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".
Creating a Consumer Group
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');
Updating a Consumer Group
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.
Deleting a Consumer Group
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.
Specifying Resource Plan Directives
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
| Parameter | Description |
|---|---|
PLAN | Name of the resource plan. |
GROUP_OR_SUBPLAN | Name of the consumer group or subplan. |
COMMENT | Any comment. |
CPU_P1 | For EMPHASIS, specifies the CPU percentage at the first level. For RATIO, specifies the weight of CPU usage. Default is NULL for all CPU parameters. |
CPU_P2 | For EMPHASIS, specifies CPU percentage at the second level. Not applicable for RATIO. |
CPU_P3 | For EMPHASIS, specifies CPU percentage at the third level. Not applicable for RATIO. |
CPU_P4 | For EMPHASIS, specifies CPU percentage at the fourth level. Not applicable for RATIO. |
CPU_P5 | For EMPHASIS, specifies CPU percentage at the fifth level. Not applicable for RATIO. |
CPU_P6 | For EMPHASIS, specifies CPU percentage at the sixth level. Not applicable for RATIO. |
CPU_P7 | For EMPHASIS, specifies CPU percentage at the seventh level. Not applicable for RATIO. |
CPU_P8 | For EMPHASIS, specifies CPU percentage at the eighth level. Not applicable for RATIO. |
ACTIVE_SESS_POOL_P1 | 来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/330796/viewspace-885059/，如需转载，请注明出处，否则将追究法律责任。 
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%> <%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%}%>
<%=items[i].items.items[j].createtime%>
回复
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备09055130号-4 北京市公安局海淀分局网监中心备案编号：11010802021510 广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员 |
转载于:http://blog.itpub.net/330796/viewspace-885059/
扫一扫
到【灌水乐园】发言
