Inactive Sessions
A reader, December 05, 2005 - 7:19 am UTC
Tom,
This means inactive sessions can become active when user executes some sql.
Yes/ No
December 06, 2005 - 4:15 am UTC
pretty much, unless a session is executing some sql, it'll be "inactive" - not active.
Inactive sessions
A reader, December 05, 2005 - 7:31 am UTC
Tom,
This means inactive sessions can become active when user executes some sql.
Yes/ No ?
Session Cleanup
Dave Martin, February 09, 2006 - 6:28 am UTC
Tom,
assuming you have no Dead Connection Detection configured and no idle time set, will your inactive/dead sessions stay in the system for ever, assuming you dont kill them or bounce the db?
I have a db that was bounced on 10/1/2006 and the earlier (non-system) logon time in v$session is 27/1/2006.
I would expect to see older dead or inactive sessions than that, knowing my users, I expect to see more sessions lying around as a result of broken web connections, PCs rebooted etc..?
February 10, 2006 - 10:58 am UTC
for inactive - sure, inactive sessions are just that - inactive. not evil, not bad, not good, not anything. Just INACTIVE. Nothing wrong or bad about inactive (unless you personally consider it otherwise, then you set timeouts).
good thing you gave me two dates :) Else, I would have thought 10/1/2006 was in the future.....
Guess your users are better than you thought they were.
thanks..
Dave Martin, February 13, 2006 - 4:19 am UTC
guess they must be.
Thanks,
Dave
13/2(FEB:))/2005
Alexander, May 24, 2010 - 2:46 pm UTC
So is it false to say a session can be running sql but also show up as inactive? I swear you explained to me once that this can be true. If for example you have a query running for hours, it would show inactive, and the last_call_et column would represent the number of seconds since the query started and not how long it's (the session) been idle.
May 24, 2010 - 3:12 pm UTC
I can
a) open sql - that'll be active
b) let the client do something, like ask the end user "would you like to see the results?" - that'll be inactive, but I'm still running my sql
c) fetch 25 rows - that'll be active
d) wait for the user to hit page down - that'll be inactive
e) fetch 25 more rows - that'll be active
f) wait for the user....
and so on. it might takes "days" to complete the query and you'll be going active/inactive over and over.
The last call et column would be reset every time the status flipped from active/inactive - it would not represent the number of seconds since the query started - it would be the number of seconds the session was in the current state (active or not).
Paul, April 14, 2011 - 11:50 pm UTC
I was discussing with topic with one of my senior DBA and his response was "We need to clean-up the inactive sessions especially in RAC database for better performance" Is this true?
April 15, 2011 - 9:57 am UTC
If you have lots of inactive session - so many that they are consuming gobs of RAM - and you feel the need to clean them up
You have a problem that should be solved once and for all. Get to the root cause, what is CAUSING these inactive sessions that you can just kill? It sounds like you have a bug in your middle tier application and it is losing connections in a connection pool - that is some developer has:
a) grabbed a connection
b) hit and exception
c) propagated over and out of the scope of the connection handle from (a)
d) so they can never give it back - they lost it
that would be the only type of session it would be safe to kill.
And you shouldn't kill it, you should fix it - the application has a pretty bad bug.
Awesome as usual
Paul, April 16, 2011 - 10:16 pm UTC
Thanks for your quick response. Your explanations are awesome!!
usha, April 09, 2014 - 1:41 pm UTC
Hi Tom,
I have a similar question,Can anybody help me out?
when a session can becomes inactive permanently,
I got an issue like number of processes exceeded interms says that number of sessions exceeded.
Will the database cleans the sessions those have been inactive for long time ?
Regards
usha
April 16, 2014 - 4:49 pm UTC
no, why would it?
you can set a profile to disconnect idle sessions - but YOU DO NOT WANT TO, it'll break your connection pool.
If these session exist, but your application is not able to use them - then you have a bug in your code, you are using a connection pool. your code grabs a connection from the pool. You hit an exception somewhere and due to bad error handling, you forget to return the connection to the pool. You are leaking connections (probability of this paragraph being true so close to 100% that we can say "it is 100%)
just wrote that yesterday:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8178218400346699475
Bit confusing
A, April 28, 2014 - 12:42 pm UTC
Hello Tom,
Bit confused and I have question on what you said earlier
a) grabbed a connection
b) hit and exception
c) propagated over and out of the scope of the connection handle from (a)
d) so they can never give it back - they lost it
Will be session be inactive state for ever if application user closes his session from the front-end or there was an exception (from db side) and the user closed the application?
Thanks
A
Find root cause for inactive session
Mansi Raval, March 16, 2017 - 1:36 pm UTC
I have similer issue. Inactive sessions are increasing from application server. I am not able to find root cause for this.
It keeps increasing and does not allow user to use application.
this lead us to bounce application server.
I can see one query returning around 20 million rows and more than 6 sessions are there for such query.
I am seeing this in gv$SQL
Is such queries increased inactive session. because when I search for inactive session,small queries returning less no. of rows or small IO also gone into inactive session.
March 19, 2017 - 5:22 am UTC
As Tom said a couple of reviews up:
"You can set a profile to disconnect idle sessions - but YOU DO NOT WANT TO, it'll break your connection pool.
If these session exist, but your application is not able to use them - then you have a bug in your code, you are using a connection pool. your code grabs a connection from the pool. You hit an exception somewhere and due to bad error handling, you forget to return the connection to the pool. You are leaking connections (probability of this paragraph being true so close to 100% that we can say "it is 100%) "
If you *cant* find the cause of the leak, then you *might* have to use a profile to clean them up, but the reality is - it is the application layer that needs to manage connections.
Application session and database status
Pol, March 20, 2017 - 9:30 am UTC
When I try to Insert 10 Rows in Oracle database.
So when I insert 3 rows using vb.net and i check session status from back-end side using select * from v$session.
My Question is here i get session status is "INACTIVE".
but still i am connect since 10 seconds.
so why i get status is "INACTIVE"?
March 22, 2017 - 1:40 am UTC
The moment you have finished inserting a row into the database, your session will be inactive (because its "done").
If you have 10 inserts (1 at a time), then the session will be active for a tiny fraction whilst each insert occurs, and then be inactive.
ACTIVE means I am *currently* running something. So unless it is a long running operation (eg more than a couple of seconds), the session will generally appear an inactive.
Inactive Sessions
sairaj, April 02, 2019 - 1:13 pm UTC
Hi Tom ,
Any JVM is there to clear the inactive session after running the batch processors ?that could create a solution for us .after running the batch processors we are seeing many inactive sessions that leading to the server down .
April 03, 2019 - 10:08 am UTC
That will depend on the specifics of the application server you're using. This is outside our arena - you'll need to find someone who knows about your environment.
we are seeing many inactive sessions that leading to the server down
Inactive sessions are just that - inactive. They're not doing anything. I'm not sure how this leads to "server down"... unless you're hitting the process limit?
INACTIVE session
Dinesh kanna, May 11, 2020 - 10:34 am UTC
Thanks for this thread.
I have few doubts. Recently I have joined an org, In which
We do have alert monitoring system where if there are more than 250 connections(Inactive) send an alert.
1. Do we really need to monitor this? Since Inactive aren't going to create any harm( as per this thread info. Correct me if I am wrong)
2. What is the idle thread time out parameter plays a role with respect to INACTIVE/ACTIVE connections?
3. Assume the same oracle instance has been shared with multiple users for testing. In this case is that more than 250 INCATIVE connections will create an impact?
Please clarify.
May 12, 2020 - 8:21 am UTC
1) Maybe.
If those inactive session are holding locks, it might be an issue. If you are *also* monitoring for sessions that get block on locks, then it is less of an issue. Also depends on how close you are to the instance 'sessions' limit.
2) When you pass the idle timeout, the sessions are become "sniped". They'll be cleaned up in due course by a background process.
3) See (1)
A reader, August 10, 2020 - 11:41 am UTC
session related info
A reader, April 21, 2021 - 9:46 pm UTC
if any connection is idle for long time(1 hr) in SQL Paas instance, will connection closed by instance or will it still in in active mode?
April 27, 2021 - 5:55 am UTC
Depends on the connection and the configuration of the database.
Typically connection pools will have an idle timeout at which a session will be terminated.
Similarly, a database admin can set idle timeouts via profiles.
A reader, May 11, 2022 - 5:50 pm UTC
17.5 session inactive pl active immediately
May 12, 2022 - 1:30 pm UTC
17.5 - as in 17 and one half? How do you get half an active session?!