• The Resource Manager provides a mechanism whereby the operating system’s time-slicing algorithm can be adjusted,
to ensure that some users receive more processing capacity than others—and to ensure that the one query does not destroy performance for everyone else.
■■The Resource Manager Architecture
1.Consumer Groups
A Resource Manager consumer group is a set of users with similar resource requirements。
One group may contain many users, and one user may be a member of many groups,but at any given moment, each session will have one group as its effective group.
・Five default groups:
SYS_GROUP:Group of system sessions. By default, the SYS and SYSTEM users only are in this group
DEFAULT_CONSUMER_GROUP:consumer group for users not assigned to any group.except of SYS and SYSTEM.
OTHER_GROUPS:consumer group for users not included in any group in the active top-plan
LOW_GROUP:Group of low priority sessions.
AUTO_TASK_CONSUMER_GROUP:System maintenance task consumer group
・View the groups in your database
SQL> select CONSUMER_GROUP,CPU_METHOD,COMMENTS from DBA_RSRC_CONSUMER_GROUPS;
SQL> select USERNAME,INITIAL_RSRC_CONSUMER_GROUP from DBA_USERS;
2.Resource Manager Plans
The most basic (and most common)type of plan is one that allocates CPU resources, but there are other resource allocation methods.
Many plans can exist within the database, but only one plan is active at any one time.
・RESOURCE_MANAGER_PLAN：By default, this parameter is NULL, which means that the INTERNAL_PLAN.
Three plans are configured at database creation time：
①INTERNAL_PLAN：enabled by default, has only one directive,will result in all users having equal priority。
②SYSTEM_PLAN：
At priority level 1, the highest priority, the SYS_GROUP consumer group can take 100 percent of CPU resources.
At level 2, OTHER_GROUPS can have 100 percent.
At level 3 the LOW_GROUP can take 100 percent.
③INTERNAL_QUIESCE:it will freeze all sessions except those of the SYS_GROUP members.
*The instance parameter RESOURCE_LIMITS has nothing to do with the Resource Manager. It pertains to the older method of controlling resources, through database profiles.
SQL> alter system set resource_manager_plan=system_plan;
■■Resource Manager Configuration Tools
①DBMS_RESOURCE_MANAGER_PRIVS:is used to put users into consumer groups and also to grant the system privilege necessary to administer the Resource Manager
②DBMS_RESOURCE_MANAGER：is used to create consumer groups, plans, and directives.It is also used to create the “pending area.” Before any work can be done with Resource Manager objects, you must create a pending area.
“pending area.”is an area ofmemory(内存块) in the SGA, used for storing the objects while they are being configured.
A pending area is needed to create consumer groups, but not to put users into groups.
If you use Database Control, the pending area will be managed for you; if you use the API directly, you must explicitly create it.
**
The DBMS_RESOURCE_MANAGER_PRIVS package includes the procedure to put someone in a group,
but it is procedures in DBMS_SESSION and DBMS_RESOURCE_MANAGER that can change a user’s active consumer group.
■■Resource Manager Plans
A plan consists of a set of directives that divide resources between consumer groups。
1.CPU Method
***
The total CPU allocated at each level cannot exceed 100 percent.If it does, the pending area will fail to validate and the plan will not be saved to the data dictionary.
It is possible to have a plan that allocates less than 100 percent at a level, but there is little purpose in doing this.
***
If the CPU is not running at 100 percent usage, then these plans will have no effect. They have an impact only if the CPU capacity cannot satisfy the demands upon it.
***
Every plan must include a directive for the group OTHER_GROUPS; otherwise, the validation will fail and you cannot save the plan from the pending area to the data dictionary.
SQL> exec dbms_resource_manager.create_plan(plan=>'DAYTIME',comment=>'plan for normal working hours');
③Create the directives within the plan:
SQL> exec dbms_resource_manager.create_plan_directive(plan=>'DAYTIME',group_or_subplan=>'SYS_GROUP',cpu_p1=>100,comment=>'give sys_group users top priority');
SQL> exec dbms_resource_manager.create_plan_directive(plan=>'DAYTIME',group_or_subplan=>'OLTP',cpu_p2=>100,comment=>'give oltp users next priority');
SQL> exec dbms_resource_manager.create_plan_directive(plan=>'DAYTIME',group_or_subplan=>'DSS',cpu_p3=>50,comment=>'dss users have half at level 3');
SQL> exec dbms_resource_manager.create_plan_directive(plan=>'DAYTIME',group_or_subplan=>'BATCH',cpu_p3=>50,comment=>'batch users have half at level 3');
SQL> exec dbms_resource_manager.create_plan_directive(plan=>'DAYTIME',group_or_subplan=>'OTHER_GROUPS',cpu_p4=>100,comment=>'if there is anything left, the others can have it');
④Validate the pending area and save the plan to the data dictionary:
SQL> alter system set resource_manager_plan=daytime;
2.The Active Session Pool Method
An active session is defined as a session that is running a query, or a session that is in an uncommitted transaction.
If parallel processing has been enabled, the individual parallel processors do not count against the session pool; rather, the entire parallel operation counts as one active session.
***
A session that is not actually doing anything will still count against the active session pool for the group if it has made a change and not committed it.
・show for every queued session the number of seconds it has been waiting:
SQL> select USERNAME,SID,SERIAL#,CURRENT_QUEUE_DURATION from v$session;
・show how many sessions for each group are queued at any given moment:
SQL> select * from V$RSRC_CONSUMER_GROUP;
3.Limiting the Degree of Parallelism
Parallel processing, both for SELECT statements and for DML, can greatly enhance the performance of individual statements, but the price you pay may be an impact on other users.
***
To enable parallel processing, you must, as a minimum:
• Create a pool of parallel execution servers, with the PARALLEL_MAX_SERVERS instance parameter.
• Enable parallelism for each table, with the ALTER TABLE
PARALLEL command.
• Enable parallel DML for your session with ALTER SESSION ENABLE PARALLEL DML (parallel query will be enabled automatically for the session,if parallelism is set for the table).
• Either set the instance parameter PARALLEL_AUTOMATIC_TUNING=TRUE or specify a degree of parallelism with hints in each statement.
4.Controlling Jobs by Execution Time
large jobs will be cancelled if they would not complete in Execution Time
5.Terminating Sessions by Idle Time
disconnect sessions that are not working, according to two criteria.
①idle time: how long is it since the session executed a statement
②idle time + whether the session is holding any record or table locks that are blocking other sessions, which is a much more serious problem
***
It is also possible to disconnect sessions by using profiles, which you must enable with the instance parameter RESOURCE_LIMITS. However, the Resource Manager is a better tool for this
6.Restricting Generation of Undo Data
***
The undo pool per group has nothing to do with tablespace quotas, which are assigned per user. You cannot even grant quotas on undo tablespaces.
7.Automatic Consumer Group Switching
SQL> select resource_manager_group from v$session whereusername='CLERK';
SQL> alter system set resource_manager_plan='';
■■Additional Features
The Resource Manager is an extremely powerful tool. This final section picks up a few peripheral features, most of which are not available through the Database Control interface.
1.Quiescing the Database
•
SQL> ALTER SYSTEM QUIESCE RESTRICTED.
This command activates the Resource Manager plan INTERNAL_QUIESCE, which sets the active session pool for all groups other than the SYS_GROUP to zero.
The effect is that statements in progress will continue until they finish, but that no one(other than members of the SYS_GROUP) can issue any more statements.
• cancel the quiesce
SQL> ALTER SYSTEM UNQUIESCE
2.Consumer Group Switching for One Call
Q:Automatic consumer group switching switches a session permanently—unless it is switched back manually.
• DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
When configuring automatic group switching, Database Control uses the SWITCH_TIME argument, which causes the Resource Manager to switch the session permanently.
If you configure automatic switching through the API, you can use the SWITCH_TIME_IN_CALL argument instead.
This will downgrade the session to the SWTICH_GROUP only for the duration of the statement, returning it to its original group when the slow statement finishes.
3.Use of the Ratio CPU Method
There is an alternative technique for allocating CPU resources.
Rather than coding CPU usage as a percentage, as in the examples earlier in this chapter, you can specify ratios—and let Oracle work out the percentages.
The CREATE_SIMPLE_PLAN procedure will do everything. You do not even need to create, validate, and submit a pending area. All you need to do afterward is put users in the groups and activate the plan.
