Database Resource Manager New Features

Page 24

The Database Resource Manager in Oracle Database 10g offers a few new features that enable you to do the following:

Revert to the original consumer group at the end of an operation that caused a change of consumer groups

Set idle timeout values for consumer groups

Create mappings for the automatic assignment of sessions to specific consumer groups

Each of these topics is discussed, in turn, in more detail in the following sections.

Reverting Back to the Original Consumer Group

Prior to Oracle Database 10g, if a SQL call caused a session to be put into a different consumer group (for example, because a long-running query exceeded a SWITCH_TIME directive value in the consumer group), then that session would remain assigned to the new resource group until it was ended. The new SWITCH_ TIME_IN_CALL directive is much like the SWITCH_TIME directive in that it specifices the number of seconds that a given session should execute before the consumer group is changed. However, when using the SWITCH_TIME_IN_CALL directive, the session will be reverted back to the original consumer group once the top call has completed.

This is very useful for n-tier applications that create a pool of sessions in the database for clients to share. Previously, after the consumer group had been changed, all subsequent connections would be penalized based on the settings of the consumer group resource plan. The new SWITCH_TIME_IN_CALL directive allows the session to be reset, thus eliminating the impact to future sessions. Here is an example of the use of this new feature:

 EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'main_plan', GROUP_OR_SUBPLAN => 'goonline', COMMENT => 'Online sessions', CPU_P1 => 80, SWITCH_GROUP => 'ad-hoc', SWITCH_ESTIMATE => TRUE, SWITCH_TIME_IN_CALL=>30);

In this case, I have created a plan directive that is a part of an overall plan called MAIN_PLAN. This particular plan directive is designed to limit the impact of online ad-hoc users (or perhaps applications that are throwing out a great deal of dynamic