13 Replies Latest reply: Jan 12, 2013 6:05 AM by Jonathan Lewis RSS

    Application hanged when session high

    888538
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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