Oracle Active Session Pool Questions

Question

what is oracle active session pool? is it activated by default? what is the default maximum active session that there is per user?

Answer 1

The Active Session Pool allows a limit to be place on the number of sessions that can be active on your instance. It is a part of the Resource Manager. The number possible is defined according to your current resource plan. The default plan allows an unlimited number of sessions. It is activated if resource management is performed in the instance. To find out if this is so then check the resource_manager_plan parameter:

show parameter resource_manager_plan

If there is a value then it is activated. You may see something like DEFAULT_PLAN as the VALUE. You can then query DBA_RSRC_PLAN_DIRECTIVES for the number of active sessions possible:

select GROUP_OR_SUBPLAN, ACTIVE_SESS_POOL_P1
  from DBA_RSRC_PLAN_DIRECTIVES
 where PLAN = 'DEFAULT_PLAN';

You'll get a few rows back relating to the number of groups/subplans that are part of this plan. NULL will indicate unlimited. I prefer using Enterprise Manager to query and amend this. To find it in EM go to the Server tab then under the Resource Manager heading is Plans.

More information on the Resource Manager can be found here.