Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, VV.

Asked: February 05, 2019 - 9:22 pm UTC

Last updated: February 06, 2019 - 5:08 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

hi - we recently had a code change in our production database. Since then we see that every 2 days we have hundereds of inactive sessions in the database and maximum number of processes is reached. We increased this number a couple of times and every time it reached the new max value. Dev team is also unsure of the root cause of this issue. Trying to find out if there is a better way to understand more about all the inactive sessions and where they are originating from. Is there a better way to handle this issue ?

and Connor said...

You need to query v$session to get some characteristics about those sessions, ie, what app server(s) they are coming from, what programs they are running etc.

My first hypothesis would be you have either a program crash, or not correctly exiting and it leaves its connection to the database open. Hence over time, they just add up and you hit max processes.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

dead connection detection

lh, February 06, 2019 - 11:41 am UTC

Hi

This doesn't help in finding the root cause, but if the reason is that some client programs die and do not close connections properly, the dead connection detection might help.

sqlnet.ora in database server:
EXPIRE_TIME=1

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database