The content of this lecture mainly includes:
1. Configure the Database Resource Manager
2. Access and create a resource plan
3. Create a resource consumer group
4. Assign commands to the resource consumer group
5. Map Resource Consumer combo resource plan
6. Activate the resource plan
7. Monitor Resource Manager
Resource Manager Components
Resource consumer group: a user or session with the same resource request
Resource plan: Describes the resource allocation of a resource consumer group. The plan needs to be activated first, similar to the nature of the legal outline.
Resource plan directive: Specifies the specific resource allocation method, including the scope and allocation method of the function, similar to the nature of legal rules
You can use the resource manager to manage the following resources of databases and operating systems, and allow users to switch between multiple resource consumer groups.
-CPU usage
-Degree of parallelism
-Number of active sessions
-Undo generation
-Operation execution time
-Idle time
1. Create a resource plan, a resource consumer group, and add user HR to the resource consumer group.
"Automatic plan switching enable": indicates that the subplan Automatic switching function is enabled.
"Round robin" allocates resources by time slice in the group
You can add users in the resource consumer group and user attributes. On the user attributes tab, you can select the default resource consumer group that the user belongs to. The concept of a resource consumer group is similar to that of a club, that is, the user has the permission to enter the reorganization, but not necessarily belongs to this group.
Activate a resource plan
SQL> alter system set resource_manager_plan = plan1; System altered SQL> select sid, serial #, resource_consumer_group from v $ session where username = 'hr '; sid serial # RESOURCE_CONSUMER_GR ---------- -------------------- 134 138 GROUP2 138 172 OTHER_GROUPS
Ii. Set CPU usage restrictions
In the resource plan, we can set a total of eight CPU usage priorities. Level 1 is the highest, and level 8 is the lowest. Note that the total number of CPUs at different levels cannot exceed 100%. At the same time, only when the CPU usage reaches 100%, the CPU usage of the specified level will take effect. That is to say, even if the system is completely idle, users of the other_group can log on to the system using a CPU of 100%. If the sys_group user logs on to the database during the operation, the system CPU is fully loaded. Oracle will give priority to the sys_group group CPU allocation. Only after the sys_group execution is complete will the other_group group be allocated CPU
3. Set the concurrency limit
Create an index with a parallelism of 10. by viewing the process information of the operating system, it is found that there are no 10 DOP During the creation process. When using the index, cancel the parallelism, otherwise, unnecessary resources will be wasted.
SQL> create index i04311_big on hr. t04311_big (line) parallel 10; Index created. [oracle @ rhel6 ~] $ Watch-n 1 "ps-ef | grep ora_p | grep-v 'pts/6'" Every 1.0 s: ps-ef | grep ora_p | grep-v 'pts/6' Fri Aug 5 10:04:44 2011 oracle 12828 1 0 Aug04? 00:00:05 ora_pmon_ora10g oracle 12830 1 0 Aug04? 00:00:02 ora_psp0_ora10g oracle 22800 1 0? 00:00:00 ora_p000_ora10g oracle 22802 1 0? 00:00:00 ora_p001_ora10g oracle 22804 1 0? 00:00:00 ora_p002_ora10g oracle 22806 1 0? 00:00:00 ora_p003_ora10g SQL> select degree from dba_indexes I where I. owner = 'hr' and I. table_name = 't04311 _ BIG '; DEGREE ------- 10 SQL> alter index i04311_big noparallel; Index altered. SQL> select degree from dba_indexes I where I. owner = 'hr' and I. table_name = 't04311 _ BIG '; DEGREE -------- 1
Iv. Set the total number of active sessions
SQL> show user; USER is "SYS" SQL> update hr. employees set salary = 1000 where employee_id = 100; 1 row updated. SQL> conn hr/hr Connected. SQL> update hr. employees set salary = 2000 where employee_id = 100; SQL> conn hr/hr Connected. SQL> update hr. employees set salary = 3000 where employee_id = 100; SQL> select sid, serial #, resource_consumer_group, status from v $ session where username = 'hr '; sid serial # RESOURCE_CONSUMER_GROUP STATUS ---------- -------------------------------- -------- 134 138 GROUP2 ACTIVE 138 GROUP2 active SQL> update hr. employees set salary = 4000 where employee_id = 100; update hr. employees set salary = 4000 where employee_id = 100 * ERROR at line 1: ORA-07454: queue timeout, 1 second (s), exceeded
This article is from the "yueda tianchong" blog