How to Limit max number of concurrently active sessions with Resource Manager (Doc ID 848666.1)

Last updated on JULY 01, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.7 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 01-Jul-2016***

Goal

The Following Sample Code can be helpful for Limiting the max number of concurrently active sessions with Resource Manager feature Called active session pool .

An active session is a session that is in a call. It is considered active even if it is blocked, for example waiting for an I/O request to complete. When the active session pool is full, a session that is trying to process a call is placed into a queue. When an active session completes, the first session in the queue can then be removed from the queue and scheduled for execution. You can also specify a period after which a session in the execution queue times out, causing the call to terminate with an error.

An entire parallel execution session is counted as one active session.

This feature is useful if you want to limit the number of sessions in a consumer group that are competing for resources. For example, if a consumer group is used for processing long-running, parallel queries for reporting, you may decide to limit the number of active sessions to one to allow one report to complete as quickly as possible, without competing with other reports for CPU or for parallel query slaves.

Concurrently active sessions , means users with active jobs or calls to the database , so just logging to the database is not considered to be active sessions and the Resource Manager limits will not be applied to these sessions.

An active session pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will terminate. The active session pool limits the total number of sessions actively competing for resources, thereby enabling active sessions to make faster progress.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms