Maximum active sessions exceeded

Here is a new script and example output:

http://www.bobbydurrettdba.com/uploads/maxactive.zip

Use this query if you are using resource manager and have set a maximum number of active sessions.  This SQL reports the instance number and resource consumer group that have exceeded the maximum.

Here is the text of the query:

select
act.inst_id,act.resource_consumer_group,act.num_active,
pd.active_sess_pool_p1 max_active
from
(select inst_id,resource_consumer_group,count(*) num_active
from gv$session 
where status='ACTIVE' and
type='USER' and
program not like '%(P%)'
group by inst_id,resource_consumer_group) act,
DBA_RSRC_PLAN_DIRECTIVES pd,
gv$rsrc_plan pl
where
pd.plan=pl.name and
act.inst_id=pl.inst_id and
pl.is_top_plan='TRUE' and
act.resource_consumer_group=pd.group_or_subplan and
act.num_active > pd.active_sess_pool_p1;

– Bobby

P.S.  Modified query on 4/3/2012 – wasn’t excluding parallel query slaves correctly

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply