Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

DB: Oracle 10g

O/S: Windows Server 2003 64 bits

I query list of Oracle sessions generated by web applications (filter by program=w3wp.exe)

select * from V$session
where UPPER(program) LIKE '%W3%'
order by logon_time

enter image description here

According to Logon_Time, why are still alive sessions from 31/07/2012 or 01/08/2012 or any session before today (21/08/2012)?

I have configured on sqlnet.ora: SQLNET.EXPIRE_TIME= 20 So it means that every 20 min Oracle is checking if connections are still active.

All user-schemas have default profile. It would means that no session would never expired or die?

enter image description here

Added in response of Phil's comment:

enter image description here

Added in response of Phil's answer:

enter image description here

share|improve this question
    
What is v$session.PREV_EXEC_START for these sessions? I suspect they're part of a connection pool & therefore haven't idled out because they're being used frequently. –  Phil Aug 21 '12 at 19:21
    
Mine is Oracle 10g, so there is no PREV_EXEC_START column on v$session. But I have query again including Last_Call_ET column (I added that picture on my question). –  user316687 Aug 21 '12 at 19:53
    
I talked with our developers, and they told me that they are using ODP.NET in their web apps. So it's for sure that those sessions were generated in a connection pool. I don't trust completely when Oracle shows status INACTIVE, but in this case... Do you think that I could kill sessions from two weeks ago? –  user316687 Aug 21 '12 at 19:56
7  
No. INACTIVE merely means there isn't a SQL statement being executed at the exact moment you check v$session. If they're part of a connection pool they're doing their job properly - the whole point of connection pooling is to remove the need for lots of logons/logoffs & keep persistent sessions for fast startup (much larger overhead logging in again & again just to execute one query). I don't understand why you're worried about this. –  Phil Aug 21 '12 at 20:05
    
@Phil - I'd happily upvote that as an answer! –  Justin Cave Aug 21 '12 at 21:05
show 1 more comment

1 Answer 1

up vote 13 down vote accepted

I suspect they're part of a connection pool & therefore haven't idled out because they're being used frequently.

INACTIVE in v$session merely means there isn't a SQL statement being executed at the exact moment you check v$session.

If they're part of a connection pool they're doing their job properly by being logged in for long periods of time. The whole point of connection pooling is to remove the need for lots of logons/logoffs & keep persistent sessions for fast query startup - there's a much larger overhead logging in execute one query, then disconnecting every time.

To get the last activity time for each session:

select username, UPPER(program), logon_time, 
       floor(last_call_et / 60) "Minutes since active", status
from v$session
where UPPER(program) LIKE '%W3%'
order by last_call_et;

I'd advise against killing sessions unless you know that doing so will not cause problems on the application side (trying to use a session that's been killed, for example).

It may be the case that you're looking at an incorrectly configured connection pool which creates hundreds of connections once the app starts up - the connection pool may be an order of magnitude bigger than it needs to be. I suggest reaching out to the developers/application support staff & taking a look at how the connection pool is configured.

Having done a bit of research, w3wp.exe is the IIS Application Pool Process - you almost certainly want to talk to your IIS webserver admins to help get to the bottom of the connection pooling configuration.

share|improve this answer
    
Thanks for your explanation, but what if those odp.net sessions values are growing and growing in numbers? I query v$resource_limit (added at my original question) and shows that processes and sessions values are reaching 80% of value limit. Those odp.net sessions could be consuming my sessions values, reach 701 limit value and then drop my database connection? (I know that I could extend those limit values to 1000 or 2000, but it's not part of the question) –  user316687 Aug 21 '12 at 21:42
    
Then you have an application problem. –  Phil Aug 21 '12 at 21:44
    
Why? Web applications are opening, closing and disposing odp.net connection... As I'm understanding, those odp.net sessions are just still alive waiting for future connections, even if web application disposes them. –  user316687 Aug 21 '12 at 21:50
    
It looks like it'll be a connection pool configuration problem. Have you graphed a count(*) of v$session over time? Do you have a diagnostics pack license for the DB? –  Phil Aug 21 '12 at 21:51
2  
I assume you meant IIS (Microsoft's HTTP/ app server), not ISS (the International Space Station) :-) –  Justin Cave Aug 21 '12 at 23:12
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.