8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

Oracle Resource Manager Enhancements In Oracle 9i

Oracle Resource Manager is an Enterprise Edition feature that was introduced in Oracle 8i, allowing users with differing processing needs to be allocated to different resource consumer groups, each with it's own limited allocation of system resources. The functionality of Resource Manager has been enhanced in Oracle 9i to include the following features:

Related articles.

Active Session Pool

As new transactions start in a specific consumer group they take a share of the available resources. If too many transactions are active at once performance can suffer.The new Active Session Pool feature allows a maximum number of active sessions to be set for each resource consumer group. Once this figure is reached, all subsequent requests are queued until an active session completes or becomes inactive.

The active session pool currently works on the First-In-First-Out (FIFO) basis, with a timeout period. If the request times out an error is issued that can be trapped by an application. Parallel operations are counted as single sessions by resource manager.

If there are multiple resource plan directives that refer to the same consumer group, the active session pool is the sum of all the incoming values. In this case the queue timeout is the minimum of all incoming timeout values.

The active session pool is defined using the following parameters of the CREATE_PLAN_DIRECTIVE and UPDATE_PLAN_DIRECTIVE procedures in the DBMS_RESOURCE_MANAGER package.

Maximum Estimated Execution Time

Sometimes you need to prevent long running operations from using up system resources. This can be done by specifying the MAX_ESTIMATED_EXEC_TIME parameter in the plan directive. When a transaction is intiated resource manager makes an estimate of the processing time needed to complete the transaction. If that time in seconds exceeds the value of this parameter the transaction is aborted.

If a resource consumer group has more than one plan directive assigned to it, the most restrictive MAX_ESTIMATED_EXEC_TIME value is used.

Automatic Consumer Group Switching

Oracle8i allowed resource intensive transactions to be manually switched between consumer groups to give them less priority. Oracle 9i improves on this to allow the session to be automatically switched when it exceeds a specified threshold.

The threshold only applies to active sessions. Once the session completes it is switched back to it's original consumer group.

If multiple plan directives are applied to a single consumer group the most restrictive SWITCH_TIME is used. If the plan directives have different SWITCH_GROUP values resource manager decides which to use.

When a transaction is switched to a new consumer group the active session pool of that group is not taken into account. This means the group may exceed it's active session pool limit until the session completes.


The UNDO_POOL parameter of a plan directive can be used to specify the maximum amount of UNDO a resource consumer group can use. Once this threshold is met any transactions requiring more UNDO will fail with the following error.

ORA-30027: "Undo quota violation - failed to get %s (bytes)"

This situation will persist until some UNDO is released from another transaction or the DBA increases the threshold. Once more UNDO is available transactions will proceed normally. This mechanism works regardless of whether UNDO is managed automatically or manually.

If processes die or are killed their undo is credited to the pool once recovery starts, regardless of how long recovery takes. That way users are not penalized during the recovery phase.

UNDO associated with recursive transactions is included with the top-level transaction and is not credited to the pool until the top-level transaction completes. Autonomous transactions are considered separately with their UNDO credited immediately on their completion.

Resource Plan Directive Interactions

If multiple resource plan directives are associated with a single resource consumer group the resulting actions will follow the following rules.

Modified Views

The CURRENT_QUEUE_DURATION column has been added to the V$SESSION and V$MYSESSION views displaying the number of seconds the session has been queued or 0.

The QUEUE_LENGTH and CURRENT_UNDO_CONSUMPTION columns have been added to the V$RSRC_CONSUMER_GROUP view. These dispay the current queue length and the UNDO consumption for the consumer group respectively.

The QUEUEING_MTH column on the DBA_RSRC_PLANS views indicates the queuing resource allocation method for the plan.


For more information see:

Hope this helps. Regards Tim...

Back to the Top.