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 have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply