Oracle Resource Manager 概述

 

官网的链接说明

About Oracle Database Resource Manager

http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm001.htm#i1007556

 

 

一. Solutions Does the Resource ManagerProvide for Workload Management?

When databaseresource allocation decisions are left to the operating system, you mayencounter the following problems with workload management:

--如果数据库的资源分配由操作系统来决定，那么就会遇到如下问题：

 

（1）Excessive overhead

Excessiveoverhead results from operating system context switching between OracleDatabase server processes when the number of server processes is high.

 

（2）Inefficient scheduling

The operatingsystem deschedules database servers while they hold latches, which isinefficient.

 

（3）Inappropriate allocation ofresources

The operatingsystem distributes resources equally among all active processes and cannotprioritize one task over another.

 

（4）Inability to managedatabase-specific resources, such as parallel execution servers and activesessions

 

The ResourceManager helps to overcome these problems by allowing the database more controlover how hardware resources are allocated. In an environment with multipleconcurrent user sessions that run jobs with differing priorities, all sessionsshould not be treated equally. The Resource Manager enables you to classifysessions into groups based on session attributes, and to then allocateresources to those groups in a way that optimizes hardware utilization for yourapplication environment.

--Resource Manager 允许数据库控制硬件资源的分配，在多用户并发执行不同优先级的Job时，不同的session 可以按不同的优先级对待，ResourceManager 允许我们根据session 的属性将session 分类到不同的groups，然后对不同的groups分配不同的资源。

 

With the Resource Manager, you can:

--使用ResourceManager 可以做到如下：

（1）Guarantee certain sessions aminimum amount of CPU regardless of the load on the system and the number ofusers.

--保护某个session分配到最小的CPU 资源，而不管系统的压力和用户的数量。

（2）Distribute available CPU byallocating percentages of CPU time to different users and applications. In adata warehouse, a higher percentage can be given to ROLAP (relational onlineanalytical processing) applications than to batch jobs.

--针对不同的用户和应用分配不同比例的CPU 时间。

（3）Limit the degree of parallelism ofany operation performed by members of a group of users.

--限制用户组成员任何操作的并行深度。

（4）Manage the order of parallelstatements in the parallel statement queue. Parallel statements from a criticalapplication can be enqueued ahead of parallel statements from a low prioritygroup of users.

--管理并行队列中并行的执行数序，紧急应用的并行可以优先于其他低优先级的并行。

（5）Limit the number of parallelservers that a group of users can use. This ensures that all the availableparallel servers are not allocated to only one group of users.

--限制group中用户可以使用并行的数量，这样可以确保所有可用的并行资源不会被分配到某一个group 用户。

（6）Create an active session pool. An active session pool consists of aspecified maximum number of user sessions allowed to be concurrently activewithin a group of users. Additional sessions beyond the maximum are queued forexecution, but you can specify a timeout period, after which queued jobs willterminate. The active session pool limits the total number of sessions activelycompeting for resources, thereby enabling active sessions to make fasterprogress.

--创建activesession pool。Active session pool 由同一个group中用户session被允许并发active的最大数量组成。 附加的session可以超过这个限制执行，当job 队列中断后，可以指定超时时间。

 

（7）Manage runaway sessions or calls inthe following ways:

--通过如下方法管理和掉用runaway session:

1）By placing an absolute limit on thepercentage of CPU that a group can consume

--修改group 可以消耗CPU 资源的比率值。

2）By detecting when a session or callconsumes more than a specified amount of CPU or I/O, and then automaticallyeither terminating the session or call, or switching it to a consumer groupthat is allocated a small amount of CPU, which would in effect mitigate theimpact of the runaway session or call

       --检测session 或者call是否超过了指定CPU或者I/O 的限制。 然后自动的中断session 或 call，或切换其小于consumergroup 中指定的CPU 资源数.

 

（8）Prevent the execution of operationsthat the optimizer estimates will run for a longer time than a specified limit.

       --阻止优化器估算其运行时间超过指定限制的操作。

 

（9）Limit the amount of time that asession can be idle. This can be further defined to mean only sessions that areblocking other sessions.

       --限制session 可以被空闲的时间。

 

（10）Allow a database to use differentresource plans, based on changing workload requirements. You can dynamicallychange the resource plan, for example, from a daytime resource plan to anighttime resource plan, without having to shut down and restart the instance.You can also schedule a resource plan change with Oracle Scheduler.

       --根据工作量的改变允许数据库使用不同的resource plan。可以动态的修改这些resource plan。也可以在Scheduler中使用resource plan.

 

 

二. Elements of the Resource Manager

 

The elements of the Resource Manager aredescribed in the following table.

--Resource Manager的元素如下表：

Element	Description
Resource consumer group	A group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions.
Resource plan	A container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.
Resource plan directive	Associates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.

 

You usethe DBMS_RESOURCE_MANAGER PL/SQLpackage to create and maintain these elements. The elements are stored intables in the data dictionary. You can view information about them with datadictionary views.

--可以使用DBMS_RESOURCE_MANAGER 包来创建和维护这些元素。这些元素存储在数据字典里。关于这些数据字典具体参考：

"ResourceManager Data Dictionary Views"

 

2.1 About Resource Consumer Groups

A resourceconsumer group (consumer group) is a collection of user sessions that aregrouped together based on their processing needs. When a session is created, itis automatically mapped to a consumer group based on mapping rules that you setup.

--Resource consumer group 是一个用户session 的集合，其根据session需要的进程进行归类，当一个session 创建之后，它就自动根据用户指定的规则映射到对应的consumergroup。

 

As a databaseadministrator (DBA), you can manually switch a session to a different consumergroup. Similarly, an application can run a PL/SQL package procedure thatswitches its session to a particular consumer group.

--DBA 也可以手工的切换session 到不同的consumer group。 简言之，就是使用PL/SQL包来切换session 到指定的consumer group.

 

Because theResource Manager allocates resources (such as CPU) only to consumer groups,when a session becomes a member of a consumer group, its resource allocation isdetermined by the allocation for the consumer group.

       --因为Resource Manager 仅分配资源到一个consumer group，当一个session变成consumer group中的一员时，它的资源分配就由该consumer group 决定。

 

There are threespecial consumer groups that are always present in the data dictionary. Theycannot be modified or deleted. They are:

--在数据字典里一直存在3个特定的consumer groups，它们不能修改和删除：

 

（1）SYS_GROUP

This is theinitial consumer group for all sessions created by useraccounts SYS or SYSTEM. This initial consumer group can beoverridden by session-to–consumer group mapping rules.

 

（2）OTHER_GROUPS

This consumergroup contains all sessions that have not been assigned to a consumer group.Every resource plan must contain a directive to OTHER_GROUPS.

 

更多内容参考:

Table27-5, "Predefined Resource Consumer Groups"

"SpecifyingSession-to–Consumer Group Mapping Rules"

 

2.2 About Resource Plan Directives

The ResourceManager allocates resources to consumer groups according to the set of resourceplan directives (directives) that belong to the currently active resource plan.There is a parent-child relationship between a resource plan and its resourceplan directives. Each directive references one consumer group, and no twodirectives for the currently active plan can reference the same consumer group.

--Resource Manager 根据当前active resource plan的ResourcePlan directive的设置分配资源到consumer groups，resource plan和resource plan directives 是父子关系，每个resource plan directives 只对应一个consumer group。

 

A directive hasseveral ways in which it can limit resource allocation for a consumer group.For example, it can control how much CPU the consumer group gets as apercentage of total CPU, and it can limit the total number of sessions that canbe active in the consumer group.

--每个指令都有一些方法，其用来限制资源分配到某个consumer group。

 

2.3 About Resource Plans

In addition tothe resource plans that are predefined for each Oracle database, you can createany number of resource plans. However, only one resource plan is active at atime. When a resource plan is active, each of its child resource plandirectives controls resource allocation for a different consumer group. Eachplan must include a directive that allocates resources to the consumer groupnamed OTHER_GROUPS. OTHER_GROUPS applies to all sessions thatbelong to a consumer group that is not part of the currently active plan.

--resource plan 是预先定义好的，可以创建任意个resourceplan，但是在同一时刻只有一个resource plan 是激活的。当resource plan 是激活时，它的每个子resource plan 指令控制每个资源分配到不同的consumer group。 每个plan 必须包含一个指令，其分配资源到OTHER_GROUPS 的consumer group。 OTHER_GROUPS 适用于所有的session，即使不是当前激活的plan。

 

Note:

Although theterm "resource plan" (or just "plan") denotes one elementof the Resource Manager, in this chapter it is also used to refer to acomplete resource plan schema, which includes the resource plan elementitself, its resource plan directives, and the consumer groups that thedirectives reference. For example, when this chapter refers to the DAYTIME resourceplan, it could mean either the resource plan element named DAYTIME, or theparticular resource allocation schema that the DAYTIME resource planand its directives define. Thus, for brevity, it is acceptable to say,"the DAYTIME plan favors interactive applications over batchapplications."

 

Example:A Simple Resource Plan

Figure27-1 shows a simple resource plan for an organization that runs onlinetransaction processing (OLTP) applications and reporting applicationssimultaneously during the daytime. The currently active plan, DAYTIME,allocates CPU resources among three resource consumer groups.Specifically, OLTP is allotted 75% of the CPU time,REPORTS is allotted15%, and OTHER_GROUPS receives the remaining 10%.

--DAYTIME Resource Plan 用来分配3个resourceconsumer groups的CPU 资源，OLTP 分配75%CPU time，REPOTS 分配15%，OTHER_GROUPS使用剩下的10%。

 

 

 

Oracle Databaseprovides a procedure (CREATE_SIMPLE_PLAN) that enables you to quickly create asimple resource plan. This procedure is discussed in "Creatinga Simple Resource Plan".

       --Oracle 数据库提供CREATE_SIMPLE_PLAN过程来快速的创建resourceplan。

 

Note:

The currentlyactive resource plan does not enforce allocations until CPU usage is at 100%.If the CPU usage is below 100%, the database is not CPU-bound and hence thereis no need to enforce allocations to ensure that all sessions get their designatedresource allocation.

       --当前active resource plan 不会强制分配直到CPU使用率达到100%，如果CPU 使用率在100%以下，那么数据库不会强制分配资源。

 

In addition,when allocations are enforced, unused allocation by any consumer group can beused by other consumer groups. In the previous example, ifthe OLTP group does not use all of its allocation, the ResourceManager permits the REPORTS group orOTHER_GROUPS group to usethe unused allocation.

--当强制分配时，其他consumer 不用的allocation都可以被其他的consumergroups使用。

 

2.4 AboutSubplans

Instead ofreferencing a consumer group, a resource plan directive (directive) canreference another resource plan. In this case, the plan is referred to as asubplan. The subplan itself has directives that allocate resources to consumergroups and other subplans. The resource allocation scheme then works like this:The top resource plan (the currently active plan) divides resourcesamong consumer groups and subplans.

--resource plan directive 可以参考另一个resource plan，这种情况下，这plan就叫作subplan。Subplan自己包含指令用来分配资源到consumer groups和其他的subplans。

 

Each subplanallocates its portion of the total resource allocation among its consumergroups and subplans. You can create hierarchical plans with any number ofsubplans.

       --每个subplan 分配的资源都是其consumer groups 和subplans的一部分，可以创建任意多个subplans。

 

You create aresource subplan in the same way that you create a resource plan. To create aplan that is to be used only as a subplan, you usethe SUB_PLAN argument in the packageprocedure DBMS_RESOURCE_MANAGER.CREATE_PLAN.

 

In any top levelplan, you can reference a subplan only once. A subplan is not required to havea directive to OTHER_GROUPS and cannot be set as a resource plan.

 

Example:A Resource Plan with Subplans

In this example,the Great Bread Company allocates the CPU resource as shown in Figure27-2. The figure illustrates a top plan (GREAT_BREAD) and all of its descendents. Forsimplicity, the requirement to include the OTHER_GROUPS consumergroup is ignored, and resource plan directives are not shown, even though theyare part of the plan. Rather, the CPU percentages that the directives allocateare shown along the connecting lines between plans, subplans, and consumergroups.

 

The GREAT_BREAD plan allocates resources as follows:

（1）20% of CPU resources to the consumer group MARKET

（2）60% of CPU resources to subplan SALES_TEAM, which in turndivides its share equally betweenthe WHOLESALE and RETAIL consumer groups

（3）20% of CPU resources to subplan DEVELOP_TEAM, which in turndivides its resources equally betweenthe BREAD and MUFFIN consumer groups

It is possiblefor a subplan or consumer group to have multiple parents. An example would beif the MARKET group were included inthe SALES_TEAM subplan. However, a plan cannot contain any loops. Forexample, the SALES_TEAM subplan cannot have a directive thatreferences the GREAT_BREAD plan.

 

三. About Resource Manager Administration Privileges

You must havethe system privilege ADMINISTER_RESOURCE_MANAGER toadminister the Resource Manager. This privilege (withthe ADMIN option) is granted to database administrators throughthe DBA role.

       --管理Resource Manager必须需要ADMINISTER_RESOURCE_MANAGER权限，在DBA的role里已经包含了这个权限。

 

Being anadministrator for the Resource Manager enables you to execute all of theprocedures in the DBMS_RESOURCE_MANAGER PL/SQLpackage.

 

You may, as anadministrator with the ADMIN option, choose to grant theadministrative privilege to other users or roles. To do so, use the DBMS_RESOURCE_MANAGER_PRIVS PL/SQLpackage. The relevant package procedures are listed in the following table.

 

Procedure	Description
GRANT_SYSTEM_PRIVILEGE	Grants the ADMINISTER_RESOURCE_MANAGER system privilege to a user or role.
REVOKE_SYSTEM_PRIVILEGE	Revokes the ADMINISTER_RESOURCE_MANAGER system privilege from a user or role.

 

The followingPL/SQL block grants the administrative privilege to user HR, but does notgrant HR the ADMIN option. Therefore, HR canexecute all of the procedures in the DBMS_RESOURCE_MANAGER package, but HR cannot usethe GRANT_SYSTEM_PRIVILEGE procedure to grant the administrativeprivilege to others.

-以上代码块赋予HR 用户管理的权限，赋值成功之后，HR 用户可以执行所有DBMS_RESOURCE_MANAGER 包下的过程。

 

BEGIN

 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(

  GRANTEE_NAME   => 'HR',

  PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',

  ADMIN_OPTION   => FALSE);

END;

/

 

You can revokethis privilege using the REVOKE_SYSTEM_PRVILEGE procedure.

--可以使用REVOKE_SYSTEM_PRVILEGE过程来移除权限。

 

Note:

The ADMINISTER_RESOURCE_MANAGER systemprivilege can only be granted or revoked usingthe DBMS_RESOURCE_MANAGER_PRIVSpackage. It cannot be granted or revoked through theSQL GRANT or REVOKE statements.

       --注意，ADMINISTER_RESOURCE_MANAGER的权限只能通过DBMS_RESOURCE_MANAGER_PRIVS包来进行grant和 revoke。

 

四. The Types of Resources Managed by theResource Manager

4.1 CPU

To manage CPUresources, Resource Manager allocates resources among consumer groups andredistributes CPU resources that were allocated but were not used. You can alsoset a limit on the amount of CPU resources that can be allocated to aparticular consumer group.

       --为了管理CPU 资源，Resource Manager 在consumer groups和未分配的受限的CPU资源中进行资源分配，也可以设定特定的CPU 资源来分配给某个特定的consumer groups。

 

Resource Managerprovides the following resource plan directive attributes to control CPUresource allocation:

--Resource Manager 提供了如下两种resourceplan directive 属性来控制CPU 资源的分配：

（1）ManagementAttributes

（2）MaximumUtilization Limit

 

4.1.1 Management Attributes

Managementattributes enable you to specify how CPU resources are to be allocated among consumergroups and subplans. Multiple levels of CPU resource allocation (up to eightlevels) provide a means of prioritizing CPU usage within a plan.

--管理属性可以用来指定多少的CPU 资源可以分配给consumer groups 和 subplans. 最多提供8个级别的CPU 资源分配策略。

Consumer groupsand subplans at level 2 get resources that were not allocated at level 1 orthat were allocated at level 1 but were not completely consumed by the consumergroups or subplans at level 1. Similarly, resource consumers at level 3 areallocated resources only when some allocation remains from levels 1 and 2. Thesame rules apply to levels 4 through 8. Multiple levels not only provide a wayof prioritizing, but they provide a way of explicitly specifying how allprimary and leftover resources are to be used.

       --consumer groups 和subplans 可以在level2级别获取level 1 未分配或者在level 1级别未完全使用的资源。类似的，resourceconsumer 在level 3 可以获取level 1和level 2 级别未分配的资源。

 

Use themanagement attributes MGMT_Pn, where n is an integer between 1and 8, to specify multiple levels of CPU resource allocation. For example, usethe MGMT_P1directive attribute to specify CPU resource allocation at level1 and MGMT_P2 directive attribute to specify resource allocation atlevel 2.

       --管理的属性是MGMT_Pn，这里的n是1到8的整数，用来指定CPU资源分配的级别。

 

Use managementattributes with parallel statement directive attributes, such as Degreeof Parallelism Limit and ParallelTarget Percentage, to control parallel statement queuing. When parallelstatement queuing is used, management attributes are used to determine whichconsumer group is allowed to issue the next parallel statement. For example, ifyou set the MGMT_P1 directive attribute for a consumer group to 80,that group has an 80% chance of issuing the next parallel statement.

 

Managementattributes also enable you to specify CPU resource allocation for Exadata I/O.

 

Table27-1 illustrates a simple resource plan with three levels.

 

Table 27-1 A Simple Three-Level ResourcePlan

Consumer Group	Level 1 CPU Allocation	Level 2 CPU Allocation	Level 3 CPU Allocation
HIGH_GROUP	80%
LOW_GROUP		50%
MAINT_SUBPLAN		50%
OTHER_GROUPS			100%

 

High priorityapplications run within HIGH_GROUP, which is allocated 80% of CPU.Because HIGH_GROUP is at level one, it gets priority for CPUutilization, but only up to 80% of CPU. This leaves a remaining 20% of CPU tobe shared 50-50 by LOW_GROUP and the MAINT_SUPLAN at level2. Any unused allocation from levels 1 and 2 are then availableto OTHER_GROUPS at level 3. Because OTHER_GROUPS has nosibling consumer groups or subplans at its level, 100% is specified.

通过上图可以看到，HIGH_GROUP在level 1分配了80%的CPU 资源，这个级别保留了20%的CPU 资源到level 2，其被平均分配到LOW_GROUP 和 MAINT_SUPLAN，任何在level1和level 2未使用的资源都会分配到level 3的OTHER_GROUPS。

 

Within aparticular level, CPU allocations are not fixed. If there is not sufficientload in a particular consumer group or subplan, residual CPU can be allocatedto remaining consumer groups or subplans. Thus, when there is only one level,unused allocation by any consumer group or subplan can be redistributed toother "sibling" consumer groups or subplans. If there are multiplelevels, then the unused allocation is distributed to the consumer groups orsubplans at the next level. If the last level has unused allocations, theseallocations can be redistributed to all other levels in proportion to theirdesignated allocations.

       --在某个特定的级别上，CPU 分配不是固定的，如果没有足够的负载，那么剩余的CPU 将被分配到剩下的consumer groups和subplans.

 

As an example ofredistribution of unused allocations from one level to another, if during aparticular period, HIGH_GROUP consumes only 25% of CPU, then 75% isavailable to be shared by LOW_GROUP and MAINT_SUBPLAN. Anyunused portion of the 75% at level 2 is then made availableto OTHER_GROUPS at level 3. However, if OTHER_GROUPS has nosession activity at level 3, then the 75% at level 2 can be redistributed toall other consumer groups and subplans in the plan proportionally.

 

4.1.2 Maximum Utilization Limit

In the previousscenario, suppose that due to inactivityelsewhere, LOW_GROUP acquires 90% of CPU. Suppose that you do notwant to allow LOW_GROUP to use 90% of the server because you do notwant non-critical sessions to inundate the CPUs.The MAX_UTILIZATION_LIMIT attribute of resource plan directives canprevent this situation.

-在4.1.1的示例中，如果因为未激活的session，LOW_GROUP获取了90%的CPU，假设我们不希望LOW_GROUP 使用90%的资源，那么可以使用resource plan directives 的MAX_UTILIZATION_LIMIT 属性来限制。

Usethe MAX_UTILIZATION_LIMIT attribute to impose an absolute upper limiton CPU utilization for a resource consumer group. This absolute limit overridesany redistribution of CPU within a plan.

-- MAX_UTILIZATION_LIMIT可以限制resource group上CPU 资源的最大值.

 

Settingthe MAX_UTILIZATION_LIMIT attribute is optional. If you omit thisattribute for a consumer group, there is no limit on the amount of CPU that theconsumer group can use. Therefore, if all the other applications are idle, a consumergroup that does not have MAX_UTILIZATION_LIMIT set can be allocated100% of the CPU resources.

-- MAX_UTILIZATION_LIMIT 属性是可选的选项，如果没有设置这个属性，那么默认没有限制，即可以使用100%的的CPU 资源。

 

You can also usethe MAX_UTILIZATION_LIMIT attribute as the sole means of limiting CPUutilization for consumer groups, without specifying level limits.

--也可以单独使用MAX_UTILIZATION_LIMIT，不指定level。

 

Table27-2 shows a variation of the previous plan. In this plan,using MAX_UTILIZATION_LIMIT, CPU utilization is capped at 75%for LOW_GROUP, 50% forMAINT_SUBPLAN, and 75% for OTHER_GROUPS. (Notethat the sum of all maximum utilization limits can exceed 100%. Each limit isapplied independently.)

 

Table 27-2 A Three-Level Resource Plan withMaximum Utilization Limits

Consumer Group	Level 1 CPU Allocation	Level 2 CPU Allocation	Level 3 CPU Allocation	Maximum Utilization Limit
HIGH_GROUP	80%
LOW_GROUP		50%		75%
MAINT_SUBPLAN		50%		50%
OTHER_GROUPS			100%	75%

 

In the exampledescribed in Table27-2, if HIGH_GROUP is using only 10% of the CPU at a given time,then the remaining 90% is available to LOW_GROUP and the consumergroups in MAINT_SUBPLAN at level 2. If LOW_GROUP uses only20% of the CPU, then 70% can be allocated to MAINT_SUBPLAN. However, MAINT_SUBPLAN hasa MAX_UTILIZATION_LIMIT of 50%. Therefore, even though more CPUresources are available, the server cannot allocate more than 50% of the CPU tothe consumer groups that belong to the subplan MAINT_SUBPLAN.

--根据上表中的情况，假设HIGH_GROUP使用了10%，那么在level2 上LOW_GROUP和MAINT_SUBPLAN 可以使用90%，如果LOW_GROUP 使用了20%，那么MAINT_SUBPLAN就有70%可用，但是因为MAX_UTILIZATION_LIMIT设置为50%，所以即使有更多的可用资源，也只能使用50%。

 

You can set MAX_UTILIZATION_LIMIT for both a subplan and the consumer groupsthat the subplan contains. In such cases, the limit for a consumer group iscomputed using the limits specified for the subplan and that consumer group.

 

For example,the MAINT_SUBPLAN contains the consumergroups MAINT_GROUP1 and MAINT_GROUP2.MAINT_GROUP1 has MAX_UTILIZATION_LIMIT setto 40%. However, the limit for MAINT_SUBPLAN is set to 50%.Therefore, the limit for consumer group MAINT_GROUP1is computed as 40% of50%, or 20%.

For an exampleof how to compute MAX_UTILIZATION_LIMIT for a consumer group whenlimits are specified for both the consumer group and the subplan to which thegroup belongs, see "Example4 - Specifying a Maximum Utilization Limit for Consumer Groups andSubplans".

 

 

4.2 Degreeof Parallelism Limit

You can limitthe maximum degree of parallelism for any operation within a consumer group.The degree of parallelism is the number of parallel execution servers that areassociated with a single operation. Use the PARALLEL_DEGREE_LIMIT_P1 directiveattribute to specify the degree of parallelism for a consumer group.

       --可以限制同一个consumer group中最大的并行数，degree ofparallelism是同一个操作的并行执行的数量，使用 PARALLEL_DEGREE_LIMIT_P1  指令来指定consumergroup的并行限制。

 

See Also:

OracleDatabase VLDB and Partitioning Guide for more information about degreeof parallelism in producer/consumer operations

 

The degree ofparallelism limit applies to one operation within a consumer group; it does notlimit the total degree of parallelism across all operations within the consumergroup. However, you can combine both the PARALLEL_DEGREE_LIMIT_P1 andthe PARALLEL_TARGET_PERCENTAGE directive attributes to achieve thedesired control.

--degree of parallelism limit 仅对consumer group中的一个操作限制，其不限制同一个consumer group中所有操作的并行度。但可以通过PARALLEL_DEGREE_LIMIT_P1和PARALLEL_TARGET_PERCENTAGE指令属性来实现该功能。

 

For moreinformation about the PARALLEL_TARGET_PERCENTAGE attribute, see "ParallelTarget Percentage".

 

 

4.3 ParallelTarget Percentage

It is possiblefor a single consumer group to launch enough parallel statements to use all theavailable parallel servers. If this happens, when a high-priority parallelstatement from a different consumer group is run, then no parallel servers areavailable to allocate to this group. You can avoid such a scenario by limitingthe number of parallel servers that can be used by a particular consumer group.

--如果一个consumer group 使用了所有的并行，那么当其他consumergroup的高优先级的并行就没有parallel server 来分配，可以通过限制特定consumer group的并行数来避免这个问题。

 

Note:

This functionality is available starting with Oracle Database 11g Release 2(11.2.0.2).

--注意，这个功能仅从oracle 11gR2 开始使用。

 

Usethe PARALLEL_TARGET_PERCENTAGE directive attribute to specify themaximum percentage of the parallel server pool that a particular consumer groupcan use. The number of parallel servers used by a particular consumer group iscounted as the sum of the parallel servers used by all sessions in thatconsumer group.

--使用PARALLEL_TARGET_PERCENTAGE 指令属性可以指定consumergroup 可以使用的最大parallel server pool的比率。

 

See Also:

OracleDatabase VLDB and Partitioning Guide for information about parallelstatement queuing

 

For example,assume that the total number of parallel servers is 32, as set by the PARALLEL_SERVERS_TARGET initializationparameter, and the PARALLEL_TARGET_PERCENTAGE directive attribute for theconsumer group MY_GROUP is set to 50%. This consumer group can use amaximum of 50% of 32, or 16 parallel servers.

       --假设总共的parallel server 是32，在初始化参数里设置MY_GROUP  consumer group的PARALLEL_SERVERS_TARGET为50%，那么该组group最大就只能使用16个parallel servers.

 

If your resourceplan has management attributes (MGMT_P1, MGMT_P2, and so on), then aseparate parallel statement queue is managed as a First In First Out (FIFO)queue for each management attribute.

       --如果resource plan 有如下管理属性：MGMT_P1,MGMT_P2等，那么每个独立的并行的语句队列按照先进先出队列来进行管理。

 

If your resourceplan does not have any management attributes, then a single parallel statementqueue is managed as a FIFO queue.

--如果resource plan 没有管理属性，那么每个独立并行队列也是按照先进先出队列来管理的。

 

In the case ofan Oracle Real Application Clusters (Oracle RAC) environment, the target numberof parallel servers is the sum of(PARALLEL_TARGET_PERCENTAGE *PARALLEL_SERVERS_TARGET / 100) acrossall Oracle RAC instances. If a consumer group is using the number of parallelservers computed above or more, then it has exceeded its limit, and itsparallel statements will be queued.

       --在RAC 环境西安，target number 的并行数是所有节点(PARALLEL_TARGET_PERCENTAGE *PARALLEL_SERVERS_TARGET /100)的总和。

 

If a consumergroup does not have any parallel statements running within an Oracle RACdatabase, then the first parallel statement is allowed to exceed the limitspecified by PARALLEL_TARGET_PERCENTAGE.

       --在RAC下，如果consumer group没有并行语句运行，那么第一个并行语句运行超过 PARALLEL_TARGET_PERCENTAGE指定的参数。

 

Note:

In an OracleReal Application Clusters (Oracle RAC) environment,the PARALLEL_TARGET_PERCENTAGE attribute applies to the entirecluster and not to a single instance.

--在RAC 环境下，PARALLEL_TARGET_PERCENTAGE  应用于整个集群，而不是一个单实例。

 

4.3.1 ManagingParallel Statement Queuing Using Parallel Target Percentage

The PARALLEL_TARGET_PERCENTAGE attributeenables you to specify when parallel statements from a consumer group can bequeued. Oracle Database maintains a separate parallel statement queue for eachconsumer group.

       --Oracle为每个consumer group 维护一个独立的并行队列。

 

A parallelstatement from a consumer group is not run and instead added to the parallelstatement queue of that consumer group if the following conditions are met:

--在如下条件下，consumer group的非运行的parallel statement 将添加到consumergroup的parallel队列中。

 

（1）PARALLEL_DEGREE_POLICY is setto AUTO.

-- PARALLEL_DEGREE_POLICY参数设置为AUTO.

Setting thisparameter to AUTO enables automatic degree of parallelism (Auto DOP),parallel statement queuing, and in-memory parallel execution.

Note thatparallel statements which have PARALLEL_DEGREE_POLICY set to MANUAL or LIMITED areexecuted immediately and not added to the parallel statement queue.

 

（2）The number of active parallelservers across all consumer groups exceeds the PARALLEL_SERVERS_TARGET initializationparameter setting. This condition applies regardless of whether you specify PARALLEL_TARGET_PERCENTAGE.If PARALLEL_TARGET_PERCENTAGE is not specified, then it defaults to100%.

       --active的parallel server超过PARALLEL_SERVERS_TARGET的设置。

 

（3）The sum of the number of activeparallel servers for the consumer group and the degree of parallelism of theparallel statement exceeds the target number of active parallel servers.

The targetnumber of active parallel servers is computed as follows:

PARALLEL_TARGET_PERCENTAGE/100 * PARALLEL_SERVERS_TARGET

 

4.4 Parallel Queue Timeout

When you useparallel statement queuing, if the database does not have sufficient resourcesto execute a parallel statement, the statement is queued until the requiredresources become available. However, there is a chance that a parallelstatement may be waiting in the parallel statement queue for longer than isdesired. You can prevent such scenarios by specifying the maximum time aparallel statement can wait in the parallel statement queue.

       --当使用并行队列时，如果数据库没有足够的资源来执行并行，那么并行就会进入队列，直到有资源时才变成可用。 但是存在一种情况，就是并行等待了很长的时间才执行，可以设置并行在队列中的最长等待时间来避免这种问题。

 

Note:

Thisfunctionality is available starting with Oracle Database 11g Release 2(11.2.0.2).

--这个功能是Oracle 11gR2 以后才有的。

 

The PARALLEL_QUEUE_TIMEOUT directiveattribute enables you to specify the maximum time, in seconds, that a parallelstatement can wait in the parallel statement queue before it is timed out.

 

The PARALLEL_QUEUE_TIMEOUT attributecan be set for each consumer group. This attribute is applicable even if you donot specify other management attributes (MGMT_P1, MGMT_P2, and so on) inyour resource plan.

 

See Also:

OracleDatabase VLDB and Partitioning Guide for more information aboutparallel statement queuing

 

Note:

Because theparallel statement queue is clusterwide, all directives related to the parallelstatement queue are also clusterwide.

When a parallelstatement is timed out, the statement execution ends with the following errormessage:

ORA-07454: queuetimeout, n second(s), exceeded

 

If you want moreper-workload management,then you must use the following directive attributes:

（1）MT_Pn

Managementattributes control how a parallel statement is selected from the parallelstatement queue for execution. You can prioritize the parallel statements ofone consumer group over another by setting a higher value for the managementattributes of that group.

（2）PARALLEL_TARGET_PERCENTAGE

（3）PARALLEL_QUEUE_TIMEOUT

（4）PARALLEL_DEGREE_LIMIT_P1

 

See Also:

"Exampleof Managing Parallel Statements Using Directive Attributes" formore information about the combined use of all the parallel server directiveattributes

 

Althoughparallel server usage is monitored for all sessions, the parallel serverdirective attributes you set affect only sessions for which parallel statementqueuing is enabled (PARALLEL_DEGREE_POLICY is set to AUTO). If asession has the PARALLEL_DEGREE_POLICY set to MANUAL, parallelstatements from this session are not queued. However, any parallel servers usedby such sessions are included in the count that is used to determine the limitfor PARALLEL_TARGET_PERCENTAGE. Even if this limit is exceeded, parallelstatements from this session are not queued.

 

4.5 Active Session Pool with Queuing

You can controlthe maximum number of concurrently active sessions allowed within a consumergroup. This maximum defines the active session pool.

--可以控制单个consumer group中最大的并发的active sessions 的数量。这个最大的数就是active session pool.

 

An activesession is a session that is actively processing a transaction or SQLstatement. Specifically, an active session is either in a transaction, holdinga user enqueue, or has an open cursor and has not been idle for over 5 seconds.

--active session 是活动的事务或者SQL 语句进程，特别的，一个active session 是一个事务或一个打开的游标，且游标没有空闲超过5秒。

An activesession is considered active even if it is blocked, for example waiting for anI/O request to complete. When the active session pool is full, a session thatis trying to process a call is placed into a queue. When an active sessioncompletes, the first session in the queue can then be removed from the queueand scheduled for execution. You can also specify a period after which asession in the execution queue times out, causing the call to terminate with anerror.

       --即使session 是block的，也可以认为是activesession。如等待I/O。 当active session pool 满了之后，session 就会尝试存放到队列里，当有一个active session 完成，队列中的第一个session 就会移除队列并执行，可以指定队列中session的超时时间，如果超过时间就会抛出错误。

 

Active sessionlimits should not be used for OLTP workloads. In addition, active sessionlimits should not be used to implement connection pooling or parallel statementqueuing.

--在OLTP环境下，不设置active sessin 的限制。

 

To manageparallel statements, you must use parallel statement queuing with the PARALLEL_TARGET_PERCENTAGE attributeand management attributes (MGMT_P1,MGMT_P2, and so on).

 

4.6 Automatic Consumer Group Switching

This methodenables you to control resource allocation by specifying criteria that, if met,causes the automatic switching of a session to a specified consumer group.Typically, this method is used to switch a session from a high-priorityconsumer group—one that receives a high proportion of system resources—to alower priority consumer group because that session exceeded the expectedresource consumption for a typical session in the group.

 

See "SpecifyingAutomatic Switching by Setting Resource Limits" for moreinformation.

 

4.7 CancelingSQL and Terminating Sessions

You can alsospecify directives to cancel long-running SQL queries or to terminatelong-running sessions based on the amount of system resources consumed. See"SpecifyingAutomatic Switching by Setting Resource Limits" for moreinformation.

--可以使用命令取消或中止long-running的session。

 

4.8 Execution Time Limit

You can specifya maximum execution time allowed for an operation. If the database estimatesthat an operation will run longer than the specified maximum execution time,then the operation is terminated with an error. This error can be trapped andthe operation rescheduled.

--可以指定操作最大的执行时间，如果超过这个时间，操作将终止。

 

4.9 Undo Pool

You can specifyan undo pool for each consumer group. An undo poolcontrols the total amount of undo for uncommitted transactions that can begenerated by a consumer group. When the total undo generated by a consumergroup exceeds its undo limit, the current DML statement generating the undo isterminated. No other members of the consumer group can perform further datamanipulation until undo space is freed from the pool.

--可以为每个consumer group 指定undo pool，undopool 控制总的undo 数量。当总的undo 生成量超过了这个限制，那么当前的事务操作生成的undo会被中断，只有undo pool 空间足够时，才可以执行。

 

4.10 IdleTime Limit

You can specifyan amount of time that a session can be idle, after which it is terminated. Youcan also specify a more stringent idle time limit that applies to sessions thatare idle and blocking other sessions.

       --指定session 空闲时间，当超过这个时间时，session 被中断。

 

五. 其他说明

Toad上有关Resource Manager的内容：

 

 

 

也可以通过SQL 来查看，相关的数据字典如下表：

View	Description
DBA_RSRC_CONSUMER_GROUP_PRIVS USER_RSRC_CONSUMER_GROUP_PRIVS	DBA view lists all resource consumer groups and the users and roles to which they have been granted. USER view lists all resource consumer groups granted to the user.
DBA_RSRC_CONSUMER_GROUPS	Lists all resource consumer groups that exist in the database.
DBA_RSRC_MANAGER_SYSTEM_PRIVS USER_RSRC_MANAGER_SYSTEM_PRIVS	DBA view lists all users and roles that have been granted Resource Manager system privileges.USER view lists all the users that are granted system privileges for theDBMS_RESOURCE_MANAGER package.
DBA_RSRC_PLAN_DIRECTIVES	Lists all resource plan directives that exist in the database.
DBA_RSRC_PLANS	Lists all resource plans that exist in the database.
DBA_RSRC_GROUP_MAPPINGS	Lists all of the various mapping pairs for all of the session attributes.
DBA_RSRC_MAPPING_PRIORITY	Lists the current mapping priority of each attribute.
DBA_HIST_RSRC_PLAN	Displays historical information about resource plan activation. This view contains AWR snapshots of V$RSRC_PLAN_HISTORY.
DBA_HIST_RSRC_CONSUMER_GROUP	Displays historical statistical information about consumer groups. This view contains AWR snapshots of V$RSRC_CONS_GROUP_HISTORY.
DBA_USERS USERS_USERS	DBA view contains information about all users of the database. It contains the initial resource consumer group for each user. USER view contains information about the current user. It contains the current user's initial resource consumer group.
V$RSRC_CONS_GROUP_HISTORY	For each entry in the view V$RSRC_PLAN_HISTORY, contains an entry for each consumer group in the plan showing the cumulative statistics for the consumer group.
V$RSRC_CONSUMER_GROUP	Displays information about active resource consumer groups. This view can be used for tuning.
V$RSRCMGRMETRIC	Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past minute.
V$RSRCMGRMETRIC_HISTORY	Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past hour on a minute-by-minute basis. If a new resource plan is enabled, the history is cleared.
V$RSRC_PLAN	Displays the names of all currently active resource plans.
V$RSRC_PLAN_HISTORY	Shows when Resource Manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time.
V$RSRC_SESSION_INFO	Displays Resource Manager statistics for each session. Shows how the session has been affected by the Resource Manager. Can be used for tuning.
V$SESSION	Lists session information for each current session. Specifically, lists the name of the resource consumer group of each current session.

 

--查看Resource group：

SQL> select CONSUMER_GROUP from DBA_RSRC_CONSUMER_GROUPS;

 

CONSUMER_GROUP

------------------------------

DSS_GROUP

DSS_CRITICAL_GROUP

AUTO_TASK_CONSUMER_GROUP

ORA$AUTOTASK_URGENT_GROUP

BATCH_GROUP

ORA$DIAGNOSTICS

ORA$AUTOTASK_HEALTH_GROUP

ORA$AUTOTASK_SQL_GROUP

ORA$AUTOTASK_SPACE_GROUP

ORA$AUTOTASK_STATS_GROUP

ORA$AUTOTASK_MEDIUM_GROUP

 

CONSUMER_GROUP

------------------------------

INTERACTIVE_GROUP

OTHER_GROUPS

DEFAULT_CONSUMER_GROUP

SYS_GROUP

LOW_GROUP

ORA$APPQOS_0

ORA$APPQOS_1

ORA$APPQOS_2

ORA$APPQOS_3

ORA$APPQOS_4

ORA$APPQOS_5

 

CONSUMER_GROUP

------------------------------

ORA$APPQOS_6

ORA$APPQOS_7

ETL_GROUP

 

25 rows selected.

 

SQL>

 

 

--查看ResourcePlan：

SQL> select plan from DBA_RSRC_PLANS;

PLAN

------------------------------

DSS_PLAN

ETL_CRITICAL_PLAN

MIXED_WORKLOAD_PLAN

ORA$AUTOTASK_SUB_PLAN

APPQOS_PLAN

DEFAULT_MAINTENANCE_PLAN

DEFAULT_PLAN

INTERNAL_QUIESCE

INTERNAL_PLAN

ORA$AUTOTASK_HIGH_SUB_PLAN

 

10 rows selected.

 

 

 

 

-------------------------------------------------------------------------------------------------------

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Email:   tianlesoftware@gmail.com

Skype: tianlesoftware

Blog:     http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook:http://www.facebook.com/tianlesoftware

 

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满);   DBA2 群：62697977(满)  DBA3 群：62697850(满)  

DBA 超级群：63306533(满);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474