Oracle Management Security and Resources (iii) Introduction to Oracle Resource Manager

Oracle Resource Manager (Oracle Database Resource Manager, hereinafter referred to as DBRM) manages database resources and assigns different database resources to different sessions. The resources managed by DBRM mainly include CPU time. The following chapters are described below:

Oracle DBRM to help you with what issues

How Oracle DBRM to deal with these problems

What the Oracle DBRM contains

Oracle DBRM Resource allocation method

Rights Management for Oracle DBRM

1, Oracle DBRM to help you deal with what problems

When a database server resource is allocated by the operating system, you may experience the following problems:

Excessive resource overhead

When the number of server processes is large, the switching back and forth between the operating system process and the database service process can lead to high CPU usage or memory usage.

Low-efficiency scheduling

This is inefficient when the operating system schedules the database service to occupy registers.

Unreasonable allocation of resources

The operating system allocates system resources evenly to the active processes (for the database process) and does not determine their priority level.

You cannot manage resources specific to a database, such as the number of services executed in parallel and the number of sessions that are active.

2, Oracle Rmdb is how to solve these problems

Oracle Rmdb the allocation of resources such as hardware to the database server itself to solve the above problems. In a database environment, where multiple user requests for the database service may exist, and the tasks they want to accomplish are different, then we should treat these requests differently. Oracle Rmdb allows you to group them according to the application properties of each session, and then assign different database resources to each group, maximizing your database application performance.

3, the specific functions of Oracle Rmdb:

Rmdb can limit certain session requests to only the fewest process resources and user caps when the system starts.

Assign different CPU times to different users or applications. In a data warehousing application, ROLAP (relational online analytical Processing) applications should be assigned a higher proportion than the batch task.

Limits the degree of parallelism that users in the same group have to database operations.

Establish an active session pool. A session pool consists of a set of user active sessions, and for a group of users, the number of sessions that are active at the same time has a specific upper limit. If the session pool is full, the new session request is placed in the wait queue, and you can set a time limit that exceeds this limit and waits for the queue to be stopped. The session pool limits the number of session requests that are active at the same time, ensuring that the active session request completes the task more quickly.

Manage long, unresponsive sessions or requests that often consume a lot of CPU or I/O resources. These sessions can be automatically terminated or switched to other low-level groups.

The optimizer estimates the elapsed time of the request, and if a display is exceeded, RMDB will block its request.

Limit the idle wait time for a session.

Configure different patterns according to the different resource allocation requirements. You can change these patterns dynamically, for example, from daytime running mode to night run mode without restarting the database service. You can also manage schema changes through the Oracle scheduler. For more information, see: Chapter, "Oracle Scheduler Concepts".

4. Component elements of Oracle RDMB

The following table describes the constituent elements of Oracle RDMB.

Resource Usage groups: divide them into groups based on resource requests from the session. RDMB the allocation of resources by group management sessions, rather than by a single session.

Resource planning: A resource plan contains a series of directives that determine the allocation of resources to each group. To perform the allocation of resources, you only need to implement the appropriate resource plan.

Resource Plan directive: The resource Plan directive specifies the mapping relationship between the resource plan and the group.

Oracle implements these functions through stored procedures, and you can find these stored procedures in the following packages: Dbms_resource_manager.

(1) Resource Usage groups: Resource Usage groups consist of many user sessions that have the same resource usage request. When you create a new session, RDMB automatically assigns it to a group based on your settings. The database administrator can also manually adjust the groups to which a session belongs.

The following three special groups are system groups that cannot be modified or deleted.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Sys_group

Default_consumer_group

Other_group

(2) Resource planning directives

RDMB allocates resources for resource use groups based on a series of resource planning directives in the current active resource plan. There is a one-to-many relationship between resource planning and instruction, and resource planning cannot contain two identical directives.

There are many different instructions for allocating resources, such as assigning a certain percentage of the total CPU time to a group, or limiting the maximum number of active sessions in a group.

(3) Resource planning

Only one resource plan works at the same time in a database. A resource plan can also contain child resource plans. Each resource plan must contain instructions to assign support to the Other_group.

(4) Example: a simple resource plan

The following figure shows a daytime resource allocation plan that contains an OLTP (online transaction processing) application and a report system database system.

It allocates 75% CPU time for OLTP applications, allocates 15% CPU time for the report system, and the remainder is allocated for other_groups.

(5) Sub-resource plan

Resource directives, in addition to assigning resources to groups, can also allocate resources to other resource plans, and the assigned resource's schedule becomes a child plan. The following figure is an example of a resource plan that contains a child plan.

5, the content of DBRM management

A resource-allocation method specifies the percentage of a resource assigned to a group or a child plan. The resources managed by DBRM include the following:

CPU time

Active session pool with Queuing: You can set the maximum number of active sessions for a group, and when the session pool is full, the new session is placed in the waiting queue.

Degree of Parallelism Limit: Limits the number of sessions running concurrently.

Automatic Consumer Group Switching: This feature is similar to the trigger mechanism, which is automatically assigned to other groups when a session satisfies a set condition.

Cancelling SQL and terminating SESSIONS:DBRM can cancel those long-running SQL queries or session activities.

Excution time LIMIT:DMRM can estimate the runtime of an operation, and if it exceeds the set value, the database does not execute it.

Undo Pool

Idle Time Limit: Sets the maximum response times for a session.