Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: November 06, 2012 - 6:19 pm UTC

Answered by: Tom Kyte - Last updated: November 19, 2012 - 10:02 am UTC

Category: Database - Version: 10.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have inheirited a V10 database,
we are getting alot of reports of the database is running slow.

It's a web based app and I know the application server connection pools are having problems however looking at the database I can see the number of
inactive sessions get to about 600, active sessions 90 and of those approx 45 are blocked.

We don't have 600 users so I expect that many of the inactive sessions are orphans so I am looking at setting up a profile to expire session in active after a while.

Would you have any suggestions how I can diagnose the blocked sessions further please

I am currently running this


select s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2 ;

and we said...

We don't have 600 users so I expect that many of the inactive sessions are orphans so I am looking at setting up a profile to expire session in active after a while.

do not do that, you'll kill the connection pool in the app server.

what you do want to do is reduce the size of the connection pool. Watch this video:

http://www.youtube.com/watch?v=xNDnVOCdvQ0


Now, to your problem at hand. Your developers are leaking connections - probably connections and cursors. They have some code like:


try
{
  grab a connection
  prepare statement
  execute statement  <<<==== this locks some resource
  close statement
  prepare statement2
  execute statement2 <===== this fails for whatever reason
  close statement2
  commit
  release connection back to pool
} 
catch (e exception)
{
 print out a silly message somewhere
}



I am at least 100% certain they have code similar to this - the error happens every now and then - just once is enough - and they fly over the release of the connection back to the pool. They have an outstanding transaction with locked resources associated with a connection handle they can never get access to again - it has gone out of scope.


In order to prove this - have them set their connection pool to a minimum size of ONE and a maximum size of ONE in test/qa and run a full regression. If that test cannot run forever without getting stuck (without running out of connections) - this bug exists in their code.


So, what I suggest is:

a) get a handle on your connection pool settings, always set MIN=MAX=some reasonable number (do not ever let these things grow dynamically, just have them create their connections when they start).

b) run that test in dev/qa - with connection pool = 1 until it works forever.

c) for now, use enterprise manager to see the blocking/blocker graphs and kill sessions that hold locks and block others that have been idle for some period of time. that really stinks but until the developers fix their very serious bug - it is "state of the art"


d) do not just kill idle sessions - they could be associated with a connection in a connection pool that just hasn't been used in a little while.

and you rated our response

  (7 ratings)

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

Reviews

Very interesting video

November 07, 2012 - 5:39 pm UTC

Reviewer: A reader

Thanks Tom,

I will follow your suggestions.

the video was EXCELLENT

I would like to look into this further, do you know if Oracle releases test labs like the example in the video?


Tom Kyte

Followup  

November 08, 2012 - 7:32 am UTC

what is a test lab?

Very interesting indeed

November 08, 2012 - 3:45 am UTC

Reviewer: Michel Cadot from France


I often had this on my database from clients exceeding sessions parameter.
As I'm not a Java guy, I'd just be able to warn us to check their close and close their connections in each and every path.
Now I know, I'll can explain us.

Regards
Michel

November 08, 2012 - 4:23 pm UTC

Reviewer: A reader

sorry, by "test lab"
I should have said does oracle release things like the "dummy" application.

The web application we have is a nightname to get running as a standalone system. I was hoping I could get a simple or test application to prove the concept of what the video shows
Tom Kyte

Followup  

November 09, 2012 - 6:27 am UTC

no, that is just an internal little application we wrote up.

All you need is a load test tool, an "application" (some set of transaction(s) that represent what you do), and a database. You can do this yourself.


What harm is there in dropping the size of your connection pool - besides possibly finding out your life becomes boring as the database and application server stop going to 100% spuriously (unless of course you have a connection pool leak bug in the developed code, in which case - you are hosed until they fix that)

Yep am dropping the pool size

November 09, 2012 - 10:16 pm UTC

Reviewer: A reader

Tom

I an certain there are leaking connections/cursors

I will drop the connection pool size as you suggested.
Thankyou

Limiting the application server connection

November 12, 2012 - 5:57 am UTC

Reviewer: Adhika from Singapore

Hi Tom,

I just read the 10.1.2 application server documentation.
As far as I have read, to set the maximum connection on OC4J, we can do this by changing the parameters in the server.xml file.

If the application I'm supporting is a packaged software, is the server.xml file the only file which I can use to limit the connection pool?

Thanks in advance for replying,
Adhika
Tom Kyte

Followup  

November 12, 2012 - 9:21 am UTC

I'm sure the application itself could override that, this would be a question to ask of the 3rd party application maker.

What is that utility?

November 15, 2012 - 9:55 pm UTC

Reviewer: Al Ricafort from Hong Kong

Do you know what is that utility the speaker is using that shows the connection pool, CPU, wait events etc in one screen?


Tom Kyte

Followup  

November 19, 2012 - 10:02 am UTC

a custom script, handwritten, just for the specific demonstrations involved. part of the demonstration itself.

Inactive (Orphan) session holding locks

February 06, 2015 - 3:44 am UTC

Reviewer: Jithin Sarath from Blue Bell, PA, USA

I have observed something similar in one of the environments that I support. In this case it's a bit more complex. The problem manifests as sessions waiting for locks (Enq TM locks) in the database (11.2.0.4, on Windows 2008R2 x64).

The holder is an INACTIVE session from the database. We've let it run as it please for hours, and the INACTIVE session never releases the lock.The webapp has a connection pool with min 0 and max 200. The issue is seen only when we run the load tests on WebApp, so it appears that the loads hit some nerve in the code.

Your idea of setting connection pool to 1 and run the system is perfect, but i doubt if it'll hold up under a load test. I'm afraid that the system will crawl and /or timeout for most of the virtual users.