9 Replies Latest reply on Sep 14, 2016 7:28 AM by Beauty_and_dBest

    Lots of inactive sessions/processes

    Beauty_and_dBest

      Hi ALL,

       

      11gR1

      OL5

       

      We have lots of inactive process which have been  in the system for more that 4 days.

      Could these be zombie or runaway processes already? Is it okay to kill them?

      Why is that there are duplicates SPID? For example the SPID 2460

       

      It has two occurences in v$session :

      Capture.PNG

      And has 16 occurrences in v$locks:

      Capture2.PNG

       

      Are the above SPID in two tables related? Can we kill these processes which are long overdue?

      We usually encounter error > can not forked too many processes.

      Because we are afraid to kill these long running programs,which I supposed are runaways.

       

      Please help...

       

       

      Kind regards,

      jc

        • 1. Re: Lots of inactive sessions/processes
          Pavan Kumar

          Hi,

           

          1. Is it possible to find the end user name, who logged in ?

          2. Check from fnd_users and ping or check with them since from lock view perspective session is active.

           

          - Pavan Kumar N

          • 2. Re: Lots of inactive sessions/processes
            AndrewSayer

            You've not shown us anything to suggest these sessions aren't being used. Have a look at the last_call_et column of v$session. But even then you have no reason to kill a session unless its actually causing problems.

             

            Are other sessions waiting for them to release locks? Are users phoning you because they can't do their processes?

            Yes one OS spid can support more than one session.

            • 3. Re: Lots of inactive sessions/processes
              AJ

              Seems you're running E-business suite (at least from looking at the schema names).

              I would recommend you ask this question in that forum instead - maybe some "peers" have the same issues as you? E-Business Suite

               

               

              AJ

              • 4. Re: Lots of inactive sessions/processes
                JohnWatson2

                What queries are you running? For example, you claim that SPID is a column in v$session. It isn't.

                 

                Most of your sessions are just the concurrent managers, you can kill them if you want but they will restart when needed. What's the problem?

                • 5. Re: Lots of inactive sessions/processes
                  Srini Chavali-Oracle

                  Unknown EBS version, with unknown timeout settings for forms and/or sql*net.

                   

                  It could possibly be an issue - it could possibly be normal. Are any of these sessions using up 100% of a core or processor ?

                   

                  If so these may be relevant

                   

                  MOS Doc 1382442.1

                  MOS Doc 1330392.1

                  • 6. Re: Lots of inactive sessions/processes
                    Beauty_and_dBest

                    Thanks Srini and ALL,

                    See you & Hussein in the Oracle Openworld next week

                     

                    EBS R12.1.3

                    OL5

                    11gR1

                    Timeout setting is default ( i think it is 30 minutes or 1 hour)

                     

                    So those programs above which are logged-in more that 4 days are not an issue? I thought they are already running for a long time and does not finish? I thought they are users login and left hanging.

                    We have lots of CPU so we do not felt the cpu stress, but we do encounter issues like users can not login anymore do to limits in OS "can not fork too many process" at the database server.

                    All our users are reporting 9am to 6pm. They usually click "X" their login forms(abnormal exit) once ready to go home.

                    So what accounted these more than 4 days processes? and some even 11 days? Maybe the non-graceful exit of users?

                    Or is it normal for the above programs to be running more than 11 days?

                     

                     

                    Kind regards,

                    • 7. Re: Lots of inactive sessions/processes
                      Beauty_and_dBest

                      Hi John,

                       

                      Re: Most of your sessions are just the concurrent managers, you can kill them if you want but they will restart when needed. What's the problem?

                       

                      The problem is (based on the lock query) we can see lots of similar SPID, if we kill them will other programs which are good, get killed as well?

                       

                      I am not aware also that they are concurrent programs

                      So why is the the concurrent programs staying too long? when the reports is supposedly runs only 10 minutes?

                      Or it is not the program that is running 11 days but only the managers? and Is is not ok to kill it? I am confused

                       

                       

                      Kind regards,

                      • 8. Re: Lots of inactive sessions/processes
                        JohnWatson2

                        You need to read up (yes, I know you have an aversion to reading) on concurrent managers. They run all the time.

                         

                        To repeat: what queries did you run to get tat output? Why do you think there is a problem?

                        • 9. Re: Lots of inactive sessions/processes
                          Beauty_and_dBest

                          Thanks John,

                           

                          The problem is there is increasing number of  locking, and it will eventually lead to can not fork too many process at OS level.

                          Then eventually leads to new users can not login to the EBS sytem

                           

                          The query run which I got searching in google is: (Note, I did not display some of the columns which include "alter system kill session" which is part of the output columns)

                           

                          SELECT

                          DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400),

                          'SSSSS'), 'HH24:MI:SS') LOGON,

                          v$session.SID,

                          v$session.SERIAL#,

                          v$process.SPID spid,

                          v$session.process unix_appl,

                          v$session.USERNAME,

                          STATUS,

                          OSUSER,

                          MACHINE,

                          v$session.PROGRAM,

                          MODULE,

                          action,

                          SQL_HASH_VALUE,

                          'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql

                          FROM v$session, v$process

                          WHERE v$session.paddr = v$process.addr AND

                                v$session.status = 'INACTIVE' AND

                                v$session.username = 'APPS' AND v$session.last_call_et/3600 > 96

                                AND v$session.action LIKE 'FRM%'    -- << inadvertently ommitted

                          ORDER BY logon_time ASC;

                           

                           

                           

                          Kind regards,