This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Apr 5, 2013 4:57 PM by Justin Cave RSS

query for Inactive sessions consuming memory

Pointer Newbie
Currently Being Moderated
Hi All,

Can someone share a query for inactive sessions consuming memory?
I have found many inactive sessions with the following query and would like to join this information with memory usage as well.
select username, machine, program, to_char(LOGON_TIME, 'hh24:mi:ss DD-MM-YYYY'), LAST_CALL_ET/60, status from gv$session where status='INACTIVE';
Thanks in advance.

Regards,
~Pointer
  • 1. Re: query for Inactive sessions consuming memory
    sybrand_b Guru
    Currently Being Moderated
    Again, this doesn't make sense. Sessions change their state to 'INACTIVE' as soon as the client application doesn't submit a statement.
    So all sessions change from 'ACTIVE' to 'INACTIVE' and back to 'ACTIVE' all the time.
    So filtering on state 'INACTIVE' will not provide an accurate picture.

    Also, if you would study (g)v$sesstat, you should be able to write this query instantly.
    Asking for this on a forum of volunteers must be considered rude: we are not here to do your work for you for free.
    You just need to be willing to find 'pointers' in the online documentation. It seems you are not and try to outsource to a forum of volunteers as much as possible to maximize your income and minimize your efforts.

    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: query for Inactive sessions consuming memory
    User477708-OC Journeyer
    Currently Being Moderated
    http://blog.blessonjose.com/2011/06/oracle-query-to-get-session-pga-uga-memory-usage-for-inactive-sessions
  • 3. Re: query for Inactive sessions consuming memory
    sybrand_b Guru
    Currently Being Moderated
    And those queries are worthless as each session is changing from 'ACTIVE' to 'INACTIVE' all the time.

    ------------
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: query for Inactive sessions consuming memory
    Pointer Newbie
    Currently Being Moderated
    Sysbrand,
    Again, this doesn't make sense. Sessions change their state to 'INACTIVE' as soon as the client application doesn't submit a statement.
    So all sessions change from 'ACTIVE' to 'INACTIVE' and back to 'ACTIVE' all the time.
    So filtering on state 'INACTIVE' will not provide an accurate picture.
    How about if a session has been INACTIVE for last three days and the LAST_CALL_ET doesn't change and keeps on increasing?
    I guess, that makes sense, as the session is not changing it's state and is been INACTIVE for three days..Though it's not utilizing any CPU or I/O , however it might be memory intensive and would need to be analyzed before being killed.

    So if you have a query, then please provide it and i'm sure you have got it :).

    Regards,
    ~Pointer.
  • 5. Re: query for Inactive sessions consuming memory
    sybrand_b Guru
    Currently Being Moderated
    I'm also sure I don't spoon feed the lazy.
    And no, I'm not suffering from Compulsive Inactive Sessions Disorder as you are, so I would need to write the query adhoc.
    I don't care about inactive sessions.
    There are methods to kill them automatically, so there is no need to hunt for them. Just a waste of time.

    -------------
    Sybrand Bakker
    Senior Oracle DBA
  • 6. Re: query for Inactive sessions consuming memory
    Pointer Newbie
    Currently Being Moderated
    Sybrand,

    Take a break !!
    I guess it's of no use having a useless and waste of time debate with you.
    If you don't have an answer to my queries, so sit back and relax ...

    Regards,
    ~Pointer
  • 7. Re: query for Inactive sessions consuming memory
    User477708-OC Journeyer
    Currently Being Moderated
    sybrand_b wrote:
    And those queries are worthless as each session is changing from 'ACTIVE' to 'INACTIVE' all the time.
    Any query against any table is at point of time of running.
  • 8. Re: query for Inactive sessions consuming memory
    Acooper Explorer
    Currently Being Moderated
    Why not avoid the issue by setting an idle timeout?

    This might be of interest:

    How To Automate Cleanup Of Dead Connections And INACTIVE Sessions [ID 206007.1]

    Edited by: Acooper on Apr 5, 2013 5:42 AM
  • 9. Re: query for Inactive sessions consuming memory
    Acooper Explorer
    Currently Being Moderated
    I believe you are missing the point. Just because a session happens to be inactive for a moment does Not mean it needs to be killed or cleaned up. In fact this is a very bad idea.

    This is not debate worthly, maybe review:

    A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes [ID 601605.1]

    Edited by: Acooper on Apr 5, 2013 5:47 AM
  • 10. Re: query for Inactive sessions consuming memory
    Pointer Newbie
    Currently Being Moderated
    Accooper,

    Thanks for posting the note.

    I have already reviewed the provided metalink note and several other documents as well "A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes [ID 601605.1]"
    The objective is to identify the INACTIVE sessions which are present on database for more then 3 days and consuming memory too.

    Regards,
    ~Pointer
  • 11. Re: query for Inactive sessions consuming memory
    Acooper Explorer
    Currently Being Moderated
    OK.

    The nagging question for me is why make it so hard? If you set an IDLE_TIME in a profile and set resource_limit to TRUE most of the work is done for you.
  • 12. Re: query for Inactive sessions consuming memory
    sybrand_b Guru
    Currently Being Moderated
    I am answering your queries all the time.
    The problem is you don't want to accept your solution doesn't solve anything.
    I'm sorry to say so, but I can't help it, you don't want to accept the advice of someone with several decades experience in Oracle.
    I'm not here to be a code monkey and to provide any stupid hack newbies request.

    ---------
    Sybrand Bakker
    Senior Oracle DBA
  • 13. Re: query for Inactive sessions consuming memory
    Justin Cave Oracle ACE
    Currently Being Moderated
    What memory, exactly, are you concerned about?

    Every session uses a minimal amount of memory just to establish itself (a dedicated server session uses more memory than a shared server connection but we're still only talking about a couple MB). Beyond that, memory gets used when the session is doing something (sorting, doing a hash join, etc.). An inactive session is pretty much, by definition, not using a meaningful amount of memory barring a truly exceptionally poorly designed application (say, a package in PL/SQL that has a package global BLOB variable that gets loaded with a bunch of data).

    If the connection is not dead and the client is still around and expecting the session to be there, the fact that it is inactive isn't a big deal. It's not uncommon, for example, for a middle tier application server to open a bunch of connections for a connection pool that is sized for the high end of the application's usage patterns. Connections in those pools can easily be inactive for a few day. Killing those sessions, though, will often create errors for the application (many applications don't handle a connection being killed gracefully) or will create far more performance problems by forcing the application to open a physical connection while a human is waiting for the results and by creating a connection storm on the database when the application realizes that dozens of its connections are bad.

    If you are really concerned about the amount of memory inactive sessions are using, you'd generally look at using shared server rather than dedicated server sessions. That adds a bit of overhead to your calls but it uses less memory for each session.

    Justin
  • 14. Re: query for Inactive sessions consuming memory
    Pointer Newbie
    Currently Being Moderated
    I believe you are missing the point. Just because a session happens to be inactive for a moment does Not mean it needs to be killed or cleaned up. In fact this is a very bad idea.
    Yes, i agree it's not a good idea killing or cleaning up inactive sessions without knowing it's reaosn.
    I have a different scnerio, i have following programs running, sessions have logged in since 1/4/2013 and few from yesterday as well with status as INACTIVE

    Output:

    USERNAME     MACHINE     PROGRAM     LOGON_TIME     LAST_CALL_ET/60     STATUS
    BE     silver1          1/4/2013 6:39     6443.883333     INACTIVE
    BE     silver1          1/4/2013 6:39     6443.85     INACTIVE
    BE     silver1          1/4/2013 6:39     6443.833333     INACTIVE
    BE     silver1          1/4/2013 6:39     6443.8     INACTIVE
    BE_APP     silver2     ADPT@silver2 (TNS V1-V3)     4/4/2013 18:12     1430.116667     INACTIVE
    BE_APP     silver2     ADPT@silver2 (TNS V1-V3)     4/4/2013 18:15     1427.616667     INACTIVE
    BE_APP     silver2     ADPT@silver2 (TNS V1-V3)     4/4/2013 18:46     1236.316667     INACTIVE
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points