This discussion is archived
13 Replies Latest reply: Jan 12, 2013 4:05 AM by Jonathan Lewis RSS

Application hanged when session high

888538 Newbie
Currently Being Moderated
Hi Expert..

Im having a problem with application hanged and my application use php/jsp with connect oracle database 10g.Currently my application always hanged and when im checking v$session the count reach almost 2k.

My question is isit because session to high or database configuration issue make all my application hanged..im stuck now,any idea or solutions..
  • 1. Re: Application hanged when session high
    Niket Kumar Pro
    Currently Being Moderated
    check you database limits of session and processes....increase the processes or tune the application for less session creation.

    select
    resource_name,
    current_utilization,
    max_utilization,
    limit_value
    from
    v$resource_limit
    where
    resource_name in ( 'sessions', 'processes');
  • 2. Re: Application hanged when session high
    888538 Newbie
    Currently Being Moderated
    im get this:

    resource_name current_utilization max_utilization limit_value
    processes     1452     1500     1500
    sessions     1455     1507     1655

    and before this,im already try to increase the session but most of session still high with status 'INACTIVE'..
  • 3. Re: Application hanged when session high
    Niket Kumar Pro
    Currently Being Moderated
    you need to kill inactive session to release resource but do check if any critical work is going on if not you can go ahead with killing inactive session.

    you can kill inactive sesssion using this script
    set pages 3000
    spool kill_inactive.sql
    select 'alter system kill session '||'''||s.sid||','||s.serial#||''' from v$session where status='INACTIVE';
    @kill_inactive.sql

    Edited by: Niket Kumar on Jan 11, 2013 1:12 PM
  • 4. Re: Application hanged when session high
    888538 Newbie
    Currently Being Moderated
    Im also try investigate and im get this:

    select name, value from v$sysstat where name like '%logon%';

    logons cumulative     70340
    logons current     634

    select sessions_current, sessions_highwater from v$license;
    sessions_current sessions_highwater
    607     1481

    anyone can help or give suggestion....
  • 5. Re: Application hanged when session high
    888538 Newbie
    Currently Being Moderated
    Nilet Kumar,

    Thanks for the quick response...isit have any query to kill all inactive session...im found out the query at internet,but when im try that query still not kill inactive session...


    example query im find:

    SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||';'
    FROM v$session WHERE STATUS='INACTIVE';

    or


    select s.username||' '||s.osuser userinfo,s.machine, s.terminal, s.sid, s.serial#,
    p.spid,
    s.process , w.seq#
    from v$session s, v$process p
    ,v$session_wait w
    where p.addr = s.paddr
    and s.sid = w.sid
    and w.event = 'SQL*Net message from client'
    and s.status = 'INACTIVE'
    order by s.osuser, s.machine;
  • 6. Re: Application hanged when session high
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Newbie wrote:

    Im having a problem with application hanged and my application use php/jsp with connect oracle database 10g.Currently my application always hanged and when im checking v$session the count reach almost 2k.

    My question is isit because session to high or database configuration issue make all my application hanged..im stuck now,any idea or solutions..
    Not possible to make a totally firm statement, but it is very likely that it is because you're allowing the number of processes (rather than sessions specifically) to get too high.

    A common error with 3-tier applications is to allow far too many connections to be made to the database; and often the mid-tier configuration allows the number of connections to grow just at the moment that the database starts to suffer from a little resource starvation or from concurrency problems. I write up an example of how I demonstrated this (by accident) at a customer site many years ago, but for the last couple of years Graham Wood, Tom Kyte and Andrew Holdsworth have been touring the world demonstrating this type of problem. There are some videos about it on the Oracle Learning Channel - for example: http://www.youtube.com/watch?v=xNDnVOCdvQ0

    Basic message - there is a good chance you need to check the maximum number of connections you allow your mid-tier to make, and set the default to the maximum so that you're not creating and dropping them when the system start to get overloaded.

    Regards
    Jonathan Lewis
  • 7. Re: Application hanged when session high
    Niket Kumar Pro
    Currently Being Moderated
    SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||';'
    FROM v$session WHERE STATUS='INACTIVE';

    this query will create dynamic queries to kill inactive session.you need to run these queries to kill inactive session.
  • 8. Re: Application hanged when session high
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Niket Kumar wrote:
    SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||';'
    FROM v$session WHERE STATUS='INACTIVE';

    this query will create dynamic queries to kill inactive session.you need to run these queries to kill inactive session.
    Two problems with that -

    a) killing lots of sessions will trigger a massive clean-up of the shared pool, which could cause the system to respond very slowly for a few minutes, which will cause the mid-tier to create a load of new connections, which will cause the shared pool to get massively disrupted, which could slow down the system for a few minutes which ...

    b) some of the sessions you are busy killing may have gone inactive for a couple of milliseconds between fetches.

    c) (not checked) are there any newer add-on features (cdc, streams, etc.) that might get caught up in your general sweep for inactive sessions ? (Or maybe the OP has some processes that aren't coming in through the mid-tier that will be very unhappy if the session is killed.)


    Far better to spend a little time understanding what the problem is, before trying to address the visible symptoms.


    Regards
    Jonathan Lewis
  • 9. Re: Application hanged when session high
    Niket Kumar Pro
    Currently Being Moderated
    im get this:
    
    resource_name current_utilization max_utilization limit_value
    processes      1452      1500      1500
    sessions      1455      1507      1655
    
    and before this,im already try to increase the session but most of session still high with status 'INACTIVE'..
    Op is already saying application is hanged again and again and number of inactive session are also there... so that's also make application already slow... he will try to release some session may be this is help.....
  • 10. Re: Application hanged when session high
    Mihael Pro
    Currently Being Moderated
    Jonathan Lewis wrote:
    Niket Kumar wrote:
    SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||';'
    FROM v$session WHERE STATUS='INACTIVE';

    this query will create dynamic queries to kill inactive session.you need to run these queries to kill inactive session.
    Two problems with that -

    a) killing lots of sessions will trigger a massive clean-up of the shared pool, which could cause the system to respond very slowly for a few minutes, which will cause the mid-tier to create a load of new connections, which will cause the shared pool to get massively disrupted, which could slow down the system for a few minutes which ...

    b) some of the sessions you are busy killing may have gone inactive for a couple of milliseconds between fetches.

    c) (not checked) are there any newer add-on features (cdc, streams, etc.) that might get caught up in your general sweep for inactive sessions ? (Or maybe the OP has some processes that aren't coming in through the mid-tier that will be very unhappy if the session is killed.)
    For safe shutdown of inactive sessions you can use Resource Manager directive max_idle_time and put all app sessions to special consumer group.
  • 11. Re: Application hanged when session high
    marksmithusa Journeyer
    Currently Being Moderated
    Have you thought about reducing the number of sessions connecting by considering connection pooling?

    Sounds to me - without knowing your system - like you have a) a lot of inactive sessions (do you know how long they're INACTIVE for? why not pool them?), b) concurrency. Therefore, I would tend to point towards fixing how the application connects to the database - but it depends on the exact nature of the concurrency.

    Mark
  • 12. Re: Application hanged when session high
    sb92075 Guru
    Currently Being Moderated
    Newbie wrote:
    Hi Expert..

    Im having a problem with application hanged and my application use php/jsp with connect oracle database 10g.Currently my application always hanged and when im checking v$session the count reach almost 2k.
    What is Operating System name & version?

    It will take a DB Server with sufficient CPU & RAM to handle 2000 SESSIONS.

    How many CPU/cores are in DB Server?
    How much RAM in DB Server?
    My question is isit because session to high or database configuration issue make all my application hanged..im stuck now,any idea or solutions..
    Is application 3-tier?
    Does application utilize Connection Pooling?
  • 13. Re: Application hanged when session high
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Mihael wrote:

    For safe shutdown of inactive sessions you can use Resource Manager directive max_idle_time and put all app sessions to special consumer group.
    For the typical 3-tier configuration this isn't likely to add value. A couple of comments following mine have explicitly mentioned connection pooling - and that's the configuration that my comments are aimed at. Default connection pool setup tends to have an "idle time" (of a few minutes) built in, which means that connections in the connection pool are terminated cleanly after the pool has not used them for a few minutes. Unless you set your RM idle time to something less than the connection pool idle time, it won't make any difference - if you do set it to something less that just means you get an earlier logoff storm when the (currently hypothetical) catastrophe strikes.

    Regards
    Jonathan Lewis

Legend

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