Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the packets involved in database user resource management". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

User Resource Management DBMS_RESOURCE_MANAGER

There are two main packets involved in user resource management: DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER _ PRIVS.

The package DBMS_RESOURCE_MANAGER is mainly used to establish resource planning, resource user groups, resource allocation methods and other user resource-related management, while the main purpose of DBMS_RESOURCE_MANAGER _ PRIVS is to control the authority of user resource management.

I. Preface

Resource Manager consists of three parts: resource user Group (Resource consumer group), Resource Planning (Resource plan), Resource allocation method (Resource allocation method), and Resource Plan Directory (Resource plan directives). Their functions are as follows:

Description of the assembly:

Resource user groups: divide user sessions into groups according to database resource processing requirements

Resource planning: a command that specifies which resources are assigned to resource users

Resource allocation method: the method used by Database Explorer to allocate special resources, used by resource user groups and resource planning.

Resource planning commands: administrators use these commands to connect resource user groups to special plans and to allocate resources among resource user groups.

Database Explorer can complete:

1. Make sure that some users handle a small amount of resources, regardless of the load on the system and the number of users.

2. Allocate CPU time proportionally to different users and programs, and allocate effective processing resources.

3. Limit the degree of parallelism that a group of users can use.

4. Configure the instance to use a special resource allocation method. For example, DBA can dynamically change these configuration methods without shutting down the database instance.

II. Overview

There are two main packets involved in user resource management: DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER _ PRIVS.

The package DBMS_RESOURCE_MANAGER is mainly used to establish resource planning, resource user groups, resource allocation methods and other user resource-related management, while the main purpose of DBMS_RESOURCE_MANAGER _ PRIVS is to control the authority of user resource management.

For a simple user resource management plan, using only the DBMS_RESOURCE_MANAGER package is sufficient, so the use of DBMS_RESOURCE_MANAGER is only described in detail below.

Third, give examples

The following is through the establishment of a simple user resource management plan to make you familiar with the use of the DBMS_RESOURCE_MANAGER package.

(1) schematic diagram of user resource management

DW-PLAN DB_DEV OTHER_GROUPS TMP_DATA CPU 80% LEVEL 1 CPU 100% LEVEL 2 CPU 20% LEVEL 1

The above is a simple user resource management plan diagram of a data warehouse. The name of the resource user management plan is DW_PLAN, under which there are three resource user groups, DB_DEV,TMP_DATA,OTHER_GROUPS.

This resource management plan only includes the control of CPU, in which the resources available to user group DB_DEV are CPU 80% LEVEL 1, user group TMP_DATA is CPU 20% LEVEL 1, and user group OTHER_GROUPS is CPU 100% LEVEL 2.

The percentage of CPU is understandable, for example, DB_DEV has access to 80% of CPU resources, and his level is LEVEL 1. It is very confusing about this LEVEL. In fact, LEVEL is the priority of resource acquisition. Take the above example, assuming that DB_DEV and TMP_DATA get 80% and 20% of the CPU resources of the system respectively, then OTHER_GROUPS, as the next level of LEVEL 2, will not get any CPU resources. When DB_DEV and TMP_DATA get 40% and 10% of the CPU resources of the system, then OTHER_GROUPS, as the next level of LEVEL 2, will get 50% of the CPU resources.

In other words, all the resources available to LEVEL 2 are those that LEVEL 1 cannot use. Priority: LEVEL1 > LEVEL 2 > LEVEL 3... LEVEL Nmur1 > LEVEL N

One point to emphasize is the resource user group OTHER_GROUPS. Any resource plan must include this OTHER_GROUPS user group. If your resource plan does not include this user group, you will get an ORA-07453 error that requires you to add this user group. The role of this user group is to act as a post-option, when a SESSION that does not match any resource user group connects to the database, it automatically matches under OTHER_GROUPS and executes SQL according to the resource limits of OTHER_GROUPS.

Another thing that needs to be emphasized is the effective condition of the user resource limit. Take the above example, when the CPU utilization of the system does not reach 100%, the DW_PLAN resource plan will not take effect, and each resource user will not allocate the use of CPU according to the limit. Resource planning can be effective only when CPU usage is 100%, limiting CPU allocation for individual resource user groups. This point is often overlooked, so we must pay special attention to it.

The resource plan code is as follows:

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA ()

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN= > 'DW_PLAN',COMMENT= >' Resource plan/method for DW')

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP= > 'DB_DEV',COMMENT = >' Resource plan user group for DB_DEV')

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP= > 'TMP_DATA',COMMENT = >' Resource plan user group for TMP_DATA')

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP= > 'OTHER_GROUPS',COMMENT = >' Resource plan user group for OTHER_GROUPS')

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN= > 'DW_PLAN',GROUP_OR_SUBPLAN = >' DB_DEV', COMMENT = > 'DB_DEV', CPU_P1 = > 80)

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN= > 'DW_PLAN',GROUP_OR_SUBPLAN = >' TMP_DATA', COMMENT = > 'TMP_DATA', CPU_P1 = > 20)

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN= > 'DW_PLAN',GROUP_OR_SUBPLAN = >' OTHER_GROUPS', COMMENT = > 'OTHER_GROUPS', CPU_P1 = > 0, CPU_P2 = > 100)

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA ()

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA ()

Description of the execution process:

1.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA ()

Create a pending area, and you must perform a suspend operation to request a piece of memory before each operation on the resource plan.

2,DBMS_RESOURCE_MANAGER.CREATE_PLAN

Create a resource plan. The parameter PLAN indicates the name of the resource plan, and COMMENT is the comment information of the resource plan.

3,DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP

Create a resource user group. The parameter CONSUMER_GROUP is the resource user group name, and COMMENT is the comment information of the resource user group.

4,DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE

Create a resource allocation method. The parameter PLAN is the name of the resource plan, GROUP_OR_SUBPLAN is the name of the lower-level resource user, COMMENT is the comment information of the resource allocation method, and CPU_P1 represents the CPU allocation scheme of the resource user group on LEVEL.

5DBMSRESOURCEMANAGER.VALIDATEPRNDINGRENDINGRESOURCEAREA ()

Verify the validity of the user resource plan

6. DBMSRESOURCERESOURCEMANAGER.SUBMITY PENDINGAREA ()

Submit user resource plan

(2) when a user resource plan is established, it is necessary to associate a specific user with a specific resource plan.

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.ORACLE_USER,'CNODS', 'DB_DEV')

With this command, the user CNODS can be assigned to the resource group DB_DEV.

(3) after the association is completed, the newly established user resource management can be set to be effective.

Dbms_resource_manager.switch_plan (plan_name = > 'DW_PLAN', sid = >' ORCL')

Use this command to set the current user resource management plan to DW_PLAN

SQL > show parameter resource

NAME TYPE VALUE

-

Resource_limit boolean FALSE

Resource_manager_cpu_allocation integer 1

Resource_manager_plan string DW_PLAN

SQL >

(4) next, check the initialization parameter resource_limit and set it to TRUE

Sys@ORCL > show parameter resource_limit

NAMETYPEVALUE

-

Resource_limitbooleanTRUE

Sys@ORCL > alter system set resource_limit=true scope=both

The system has changed.

After the above steps, you can use the newly generated user resource plan.

The above only introduces the resource management of CPU, in fact, the user resource management package can also manage the number of active SESSION, SESSION idle time and UNDO space of the same user. The following is a detailed description of the use of each management and points for attention.

IV. ACTIVE_SESS_POOL_P1

This parameter controls the maximum number of active SESSION that users in the resource user group can run at the same time. It is worth emphasizing that ACTIVE_SESS_POOL_P1 does not limit those inactive SESSION, only those active SESSION, because generally speaking, only those active SESSION will consume the resources of the system.

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN = > 'dw_plan',GROUP_OR_SUBPLAN = >' TEST_GROUP',NEW_ACTIVE_SESS_POOL_P1 = > 1)

The above statement indicates that users within the resource user group TEST_GROUP can have only one active transaction at a time.

For example:

Assume that the resource group of TEST users is TEST_GROUP

SESSION1:

Test@ORCL > CONN TEST/TEST

Test@ORCL > SELECT COUNT (*) FROM DBA_OBJECTS,DBA_OBJECTS

SESSION2:

Test@ORCL > CONNN TEST/TEST

You can see that the SESSION2 is blocked at this time, and the SESSION2 can connect to the database only when the SQL of the SESSION1 has finished executing and becomes the INACTIVE state. Then there are two SESSION connected to the database at this time, but when one executes SQL, the other SESSION will be suspended immediately.

This parameter is of great significance to our data warehouse. Because we want to control the resources of the system, and most of the resources of the system are occupied by the SESSION of those ACTIVE, then as long as the number of SESSION of concurrent ACTIVE is limited, the resources of the system will be effectively controlled.

5. QUEUEING_P1

This parameter controls the waiting time of SESSION, when a SESSION is placed in the waiting queue, so under normal circumstances, it will wait for the required resources all the time. When QUEUEING_P1 is set, when the time specified by QUEUEING_P1 is exceeded, the system will report an error ORA-07454, reminding you that the wait has timed out.

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN = > 'dw_plan',GROUP_OR_SUBPLAN = >' TEST_GROUP', NEW_ACTIVE_SESS_POOL_P1 = > 1 recorder QUEUEINGING P1 = > 10)

SESSION1

CONN TEST/TEST

SESSION2:

CONNN TEST/TEST

SESSION1

Test@ORCL > SELECT COUNT (*) FROM DBA_OBJECTS,DBA_OBJECTS

SESSION2:

Test@ORCL > select sysdate from dual

Select sysdate from dual

*

An error occurred on line 1:

ORA-07454: queue timed out for more than 10 seconds

This parameter is used much like SELECT * FROM XXX FOR UPDATE to prevent users from waiting all the time.

VI. PARALLEL_DEGREE_LIMIT_P1

There is nothing to say about this parameter. It limits the degree of parallelism when a user executes SQL. It is not illustrated by example. The calling method is as follows:

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN = > 'dw_plan',GROUP_OR_SUBPLAN = >' TEST_GROUP', NEW_ACTIVE_SESS_POOL_P1 = > 1 newer QUEUEINGINGLING P1 = > 10 LIMT = > 2)

7. SWITCH_GROUP,NEW_SWITCH_TIME,NEW_SWITCH_ESTIMATE

These three parameters are introduced together because they work together to complete a very important function.

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN = > 'dw_plan',GROUP_OR_SUBPLAN = >' DW_TEST', NEW_SWITCH_GROUP = > dw_sys', NEW_SWITCH_TIME = > 300, NEW_SWITCH_ESTIMATE = > 'true')

The function of the above statement is that if a SESSION's resource user group belongs to DW_TEST, if he executes a very complex SQL, and the system estimates that the execution time of the SQL will exceed 300 seconds, automatically switch the SESSION to the user group dw_sys, so that the SESSION will get more resources and execute the SQL faster. However, after the execution is complete, the SESSION remains in the switched group at a later time.

Another parameter opposed to NEW_SWITCH_TIME is NEW_SWITCH_TIME_IN_CALL, which, if used, switches back to the original resource user group after execution is complete.

VIII. MAX_EST_EXEC_TIME

This parameter controls the maximum execution time of a transaction. If a transaction is complex and takes a long time to execute, it will not be executed by the system.

IX. UNDO_POOL

This parameter is very confusing. At first, I thought it controlled the size of the largest UNDO segment in the rollback tablespace for a user. Because the blocks of the UNDO segment are recycled, there should be no problem as long as the rollback information generated by a single transaction does not exceed this maximum value.

But in fact, after testing, this parameter controls a total amount, that is, the total number of UNDO generated by the user cannot exceed this value, and an error will be reported if it exceeds this value. Notice that it is the total UNDO, which is a cumulative value.

10. MAX_IDLE_TIME

This parameter controls the maximum idle time of a user's SESSION. If the idle time exceeds this limit, the SESSION will be terminated.

11. MAX_IDLE_BLOCKER_TIME

This parameter controls the idle SEESION that occupies resources. When the idle time of an occupied SESSION exceeds the limit of MAX_IDLE_BLOCKER_TIME, then the system will ask him to release the occupied resources, which is generally a ROLLBACK operation.

This is the end of the content of "which packets are involved in database user resource management". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.