Home » SQL & PL/SQL » SQL & PL/SQL » getting the number of current connections
getting the number of current connections [message #219503] Wed, 14 February 2007 12:01 Go to next message
beeky
Messages: 6
Registered: February 2007
Junior Member
I'm searching for a memory leak in a large web-based system that uses JDBC to connect to oracle 10g. I think the leak is caused by unclosed connections so I want to look at the number of open connections while stressing the application. One big complication is that many other applications are connected to this DB via JDBC using the same username so I need to correlate connections with either of these V$SESSION columns: osuser, machine or terminal. These three columns seem to have values that can be used to distinguish the application under test from the rest of the users.

I would appreciate any help in the form of SQL or suggestions for a different approach if what I'm trying to do does not make sense.

Thanks in advance,
beeky
Re: getting the number of current connections [message #219504 is a reply to message #219503] Wed, 14 February 2007 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I'm searching for a memory leak in a large web-based system
Based upon what specific value(s) of which specific metric(s) do you reach this conclusion?

>I think the leak is caused by unclosed connections
SELECT COUNT(*) FROM V$SESSION;
If connections NEVER get closed, they should always increase & eventually hit the limit & fail to open any new sessions.

IMO, you are using the READY, FIRE, AIM troubleshooting methodology

You know you have a problem, but not really sure what it is so you keeping guessing at possible "solutions".
Re: getting the number of current connections [message #219506 is a reply to message #219504] Wed, 14 February 2007 12:23 Go to previous messageGo to next message
beeky
Messages: 6
Registered: February 2007
Junior Member
I'll assume you were trying to be helpful in spite of the scornful tone of your reply.

I think unclosed connections are a prime candidate for the memory leak because the app does not have correct try-catch-finally code for the majority of the connections it tries to close.

I would not classify my approach as 'ready,aim,fire' although you are entitled to your opinion. At this point I have no idea of what is causing the leak so I'm starting with the easiest causes to find and correct. If connections turn out not to be the cause I have to move to profiling the application which is a major pain with a Java 1.4 web-based application.

Thanks for attempting to answer my query.

-=beeky
Re: getting the number of current connections [message #219508 is a reply to message #219503] Wed, 14 February 2007 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I'm searching for a memory leak in a large web-based system

Yes, Java applications are notorious for having memory leaks, but I suspect that the Java code is actually running on the middle tier application server.

If the underlying "assumption" that a memory leak exists within Oracle is incorrect, then you are wasting your time looking for one.

Based upon what specific value(s) of which specific metric(s) do you reach this conclusion that a memory leak exists on the Oracle database server?

I am NOT saying whether or not a memory leak exists.
I am asking for independently verifiable evidence that you looking for the right problem.
Re: getting the number of current connections [message #219514 is a reply to message #219503] Wed, 14 February 2007 13:07 Go to previous messageGo to next message
beeky
Messages: 6
Registered: February 2007
Junior Member
anacedent,

I realize you are trying to be helpful but you seem to be missing the point.

First of all, Java is not 'notorious' for memory leaks. In fact, Java is pretty much leak free by comparison to most other languages. However, is is possible to leak memory by doing things like not closing connections. Not closing a connection when it is no longer in scope does two things: 1. requires that more connections be created and 2. memory usage creeps steadily upward as more memory is allocated for new connections.

Perhaps my usage of the term 'memory leak' was confusing. I as not implying that Oracle is leaking. I would not have the slightest idea of how to determine much less correct a leak in Oracle. With an application though it is very easy to see the characteristic heap growth that indicates a leak.

When the leak is caused by unclosed connections the quickest diagnostic is to watch connection counts as the application receives a lot of requests in a short period of time. In an non-leaking app the connection count quickly levels out or never goes beyond a maximum. In an app that is leaking connections the connection count just keeps going up.

The app and db in question are shut down fairly frequently so the maximum number of allowed connections is probably never reached. So running out of connections is not a useful diagnostic.

So this gets me back to my original question, which I think I have gotten an answer for from another source. Perhaps you can verify that this does what I need.

What is the SQL to get the number of connections from a single application running on a remote machine using the osuser, machine or terminal cols of v$session?

For a terminal value of FISH_LT.
select count(SID) from v$session where terminal='FISH_LT'

I believe terminal will be a value that distinguishes the application under test from all others that are connected. And, most importantly, each row of v$session with that terminal value represents a connection from an app running from that terminal.

Thanks again for trying to help,
-=beeky




Re: getting the number of current connections [message #219517 is a reply to message #219503] Wed, 14 February 2007 13:17 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>In an app that is leaking connections the connection count just keeps going up.
I agree. Assuming no DB restarts count today should be higher than yesterday & count tomorrow should be higher than today.

  1* select machine, terminal, count(*) from v$session group by machine, terminal order by 3
SQL> /

MACHINE                                                          TERMINAL                         COUNT(*)
---------------------------------------------------------------- ------------------------------ ----------
hbds41.example.com                                                                                        1
comdb02.example.com                                               pts/1                                   1
cfx02.example.com                                                                                         1
vrx12-in.example.com                                                                                      1
cfx01.example.com                                                                                         1
comdb01.example.com                                                                                       1
comdb03.example.com                                               unknown                                 2
rapi41.example.com                                                                                        2
rapi42.example.com                                                                                        2
vrx13-in.example.com                                                                                      3
cfx03.example.com                                                                                         4
comdb02.example.com                                                                                       8
w21-in.example.com                                                                                        8
hbds42.example.com                                                                                       11
eg10.example.com                                                                                         16
eg11.example.com                                                                                         16
eg46.example.com                                                                                         16
eg45.example.com                                                                                         17
comdb02.example.com                                               UNKNOWN                                17
comdb02.example.com                                               pts/3                                  33

20 rows selected.
Previous Topic: HELP! Insert Script
Next Topic: Group By - Top records
Goto Forum:
  


Current Time: Fri Dec 09 15:51:33 CST 2016

Total time taken to generate the page: 0.27910 seconds