Introduction to Oracle Resource Manager (i)

The database resource Manager controls the distribution of resources across sessions by controlling execution schedules within the database. By controlling which sessions to run and the length of time that the session runs, Database Explorer ensures that the resource distribution matches the plan directives and therefore meets the business objectives.    Note that Oracle Resource Manager has a CPU-bound granularity of consumption group (consumer group) and cannot fine-tune the proportion of CPU consumed by individual processes in the consumer group, as long as the CPU limit for that consumer group is not exceeded. The CPU usage of single or multiple processes within a consumer group is unrestricted. 　

The types of resources that Resource Manager Explorer can control in 10g:

1. CPU Utilization for Oracle processes
2. Degree of parallelism (Parallel)
3. Undo Quantity
4. SQL statement operation execution time (execute times)
5. Session Idle Time------The connection user is in the unactive state more than Idle, the session is automatically killed
6. Number of active sessions (session) ---Note: A session that is active in the active State

CPU Resource Usage Instructions:

One, the emphasis CPU allocation method determines how much attention is paid to the sessions in the different consumer groups in the resource plan. Cpu

The occupancy level is assigned from 1 to 8, and level 1 has the highest priority. Percent specifies how CPU resources are allocated

To each user group in each level.

The following rules apply to the emphasis resource allocation method:

The CPU resources are allocated at a given level by a specified percentage. The percentage of CPU that is specified for the resource consumer group is the

The maximum value that a consumer group can use at a given level.

Consumer resources that are not used at a given level can be used by the next level of consumer groups. For example, if level 1

Consumer group uses only 60% of the available resources, the remaining 40% can be used by the consumer group of Level 2.

The sum of percentages for any given level must be less than or equal to 100.

For all levels that do not explicitly specify a schedule directive, the default resource for all of its child plans or consumer groups is 0%.

The emphasis resource allocation approach avoids resource shortages, which leads to lower priority users

Have the opportunity to run.

Second, the RATIO policy is a single-level CPU allocation method. The number that corresponds to the CPU ratio that you want to assign to the consumer group is specified, not the percentage. For example, suppose you have three consumer groups oltp_users, Dss_users, and batch_users, you can specify the following ratios:

Oltp_users:4

Dss_users:3

Batch_users:2

Other:1

This is similar to letting OLTP users get 40% of the resources, DSS users get 30% of the resources, batch users get 20% of the resources, and all other consumer groups get 10% of the available resources. If no consumer is currently using CPU resources in the other or dss_users consumer groups, the Oltp_users consumer group will get two-thirds of the available resources, and the Batch_users consumer group will get One-third.

Maximum Estimated execution time:

1. The Database Explorer can pre-estimate the execution time of the operation.

2. You can specify the maximum estimated execution time for an operation at the resource consumer group level.

3. If the estimated time exceeds max_est_exec_time, the operation will not start. (ORA-07455)

4. The benefit of this feature is the elimination of unusually large jobs that use excessive system resources.

5. The default value is unlimited.

By setting the Max_est_exec_time parameter of the resource Plan directive, you can define what happens at any given time

The maximum estimated execution time for any operation. When this parameter is set, the Database Explorer estimates that the specific job

Time. If the estimated time for the operation exceeds Max_est_exec_time, the operation is not initiated and emitted

ORA-07455 error. This eliminates any abnormally large jobs that consume too much system resources.

If more than one plan directive references a resource consumer group, the group may have more than one max_est_exec_time.

The Database Explorer selects the most restrictive value in all incoming values.

Using statistics from cost-based optimizer, you can calculate the estimated execution time for a given statement.

Degree of parallelism

PARALLEL_DEGREE_LIMIT_MTH limits the maximum degree of parallelism for any operation. Can only be a resource consumer group,

You cannot specify this method for a child plan. The ABSOLUTE method is a possible value that specifies that an operation can be assigned

Number of processes. If more than one plan directive references the same child plan or consumer group, all possible values are used

The minimum value for the sub-plan or consumer group is limited by the degree of parallelism.

Active Session Pool

Active_sess_pool_mth limit the number of active sessions. All other sessions are inactive, in the queue

pending activation . Active_sess_pool_absolute is the default and only available method.

Using the active session Pooling feature, you can control the maximum number of concurrent active sessions per resource consumer group. With this feature,

Because resource consumption is proportional to the number of active sessions, DBAs can indirectly control the use of any resource consumer group

Amount of resources. Using an active session pool can help reduce the number of servers that get resources from the system, thus avoiding

Inefficient paging, switching, and other resource losses (such as memory) resulting from attempts to run too many jobs at the same time.

When an active session pool is populated with an active session, the Resource Manager makes any subsequent sessions that attempt to become active sessions

Queued until the other active session completes or becomes inactive. An active session is a transaction, query, or parallel operation

The session currently involved in the. A separate parallel dependent process is not considered a session, and the entire parallel operation is treated as an activity

Session.

There is only one queue per resource consumer group, and the queuing method is first in, out (FIFO) with a timeout. Queue with

Memory structure, cannot be queried directly.

Reference: Liu Xianbing's Blog

Introduction to Oracle Resource Manager (i)