1 2 Previous Next 15 Replies Latest reply: Mar 24, 2013 8:57 PM by 548518 RSS

    A problem session

    jmft2012
      11gR2
      I had very a troublesome oracle session of a web app. we did not know what caused the session to block a number of other oracle sessions. we understood that because of holding the resource causing the row level blocking. that was not problem for the understanding.

      The problem was that the session with SID 563 and its serial# was increasing in every 0.5 or 1 second !
      we could not kill it from the database nor from the server because of the serial# rapidly increasing.
      we could not use ALTER SESSION to kill it.
      because of that the v$process.spid was also dynamically changing, we could not identify spid on the server.
      I did notice that serial# was incrementally increasing like 34778, 34779, 34780 ...

      because i have never seen something like this before, i was not prepared with a program to kill the session in this kind of cases. Tried manually killing with projected serial# and did not work. I ended up restarted the app to resolve the issue.

      Has anyone seen something like this before?
        • 1. Re: A problem session
          TSharma-Oracle
          Locking is always an application issue. You need to check which what is running on those blocking sessions . Find the offending query and fix that and Locks will not be the problem for you.
          • 2. Re: A problem session
            jmft2012
            TSharma wrote:
            Locking is always an application issue. You need to check which what is running on those blocking sessions . Find the offending query and fix that and Locks will not be the problem for you.
            thanks.
            we fully understood these.
            it is a 3rd party app. we had found the queries. we will notice the 3rd party.

            However my post was inquiring whether any one has even seen the exact or similar to what was happening in my case.
            Please review my post Carefully and
            Comment on my specific raised if you could.
            • 3. Re: A problem session
              JohnWatson
              jmft2012 wrote:
              11gR2
              I had very a troublesome oracle session of a web app. we did not know what caused the session to block a number of other oracle sessions. we understood that because of holding the resource causing the row level blocking. that was not problem for the understanding.

              The problem was that the session with SID 563 and its serial# was increasing in every 0.5 or 1 second !
              we could not kill it from the database nor from the server because of the serial# rapidly increasing.
              we could not use ALTER SESSION to kill it.
              because of that the v$process.spid was also dynamically changing, we could not identify spid on the server.
              I did notice that serial# was incrementally increasing like 34778, 34779, 34780 ...

              because i have never seen something like this before, i was not prepared with a program to kill the session in this kind of cases. Tried manually killing with projected serial# and did not work. I ended up restarted the app to resolve the issue.

              Has anyone seen something like this before?
              Your problem was not with one session. It was that your client software was continuously disconnecting from its session (or being disconnected) and reconnecting to a new session. That will often result in re-using the same SID, but a different serial#. See this:
              orcl> conn scott/tiger
              Connected.
              orcl> select sid,serial# from v$session where username='SCOTT';
              
                     SID    SERIAL#
              ---------- ----------
                      10        271
              
              orcl> conn scott/tiger
              Connected.
              orcl> select sid,serial# from v$session where username='SCOTT';
              
                     SID    SERIAL#
              ---------- ----------
                      10        273
              
              orcl> conn scott/tiger
              Connected.
              orcl> select sid,serial# from v$session where username='SCOTT';
              
                     SID    SERIAL#
              ---------- ----------
                      10        275
              
              orcl>
              that is three different sessions. You need to investigate why your web app is repeatedly logging on and off.
              • 4. Re: A problem session
                jmft2012
                I have been awere of that, and sent the notification of the possible reason and malfunctioning of the web app to the 3rd party this morning.

                I tried to kill the session with

                ALTER SYSTEM KILL SESSION 'sid, serial#';

                got the current serial#,

                then ran block statement repeatly:

                *****
                ALTER SYSTEM KILL SESSION 'sid, serial#+1',

                ALTER SYSTEM KILL SESSION 'sid, serial#+2',

                ...
                ..

                ALTER SYSTEM KILL SESSION 'sid, s..erial#+10',


                ****

                But could not capture , and kill it.

                I may write the procedure for .
                • 5. Re: A problem session
                  JohnWatson
                  No, you are missing the point. You do not need to kill anything: the sessions are killing themselves (or being killed). That is the problem. Why are they terminating? That the applciation server should attempt to logon again is fair enough.
                  • 6. Re: A problem session
                    jmft2012
                    No, i was not.
                    Did you read the first in this thread? it blocked other sessions.

                    v$lock (v$session) showing
                    USERNAME          SID   SERIAL# TYPE     CTIME HELD       REQ             ID1      ID2
                    --------------- ----- --------- ---- --------- ---------- ---------- -------- --------
                    *jsacc         563       21626 TX       82199 Exclusive  None        1245214   584534*
                    jsacc          289     33809 TX       81298 None       Exclusive   1245214   584534
                    jsacc          731     45725 TX       80394 None       Exclusive   1245214   584534
                    jsacc          882     12487 TX       80094 None       Exclusive   1245214   584534
                    jsacc         1735      2399 TX       80999 None       Exclusive   1245214   584534
                    jsacc         1883     62333 TX       79564 None       Exclusive   1245214   584534
                    jsacc          572     17821 TX       81065 None       Exclusive   1245214   584534
                    jsacc         2357     58979 TX       81598 None       Exclusive   1245214   584534
                    jsacc         2395     35771 TX       81899 None       Exclusive   1245214   584534
                    jsacc         1296     44953 TX       80163 None       Exclusive   1245214   584534
                    jsacc         1050     41597 TX       80398 None       Exclusive   1245214   584534
                    jsacc          728     21025 TX       80465 None       Exclusive   1245214   584534
                    jsacc         1013     47029 TX       80764 None       Exclusive   1245214   584534
                    jsacc           44     12083 TX       79864 None       Exclusive   1245214   584534
                    
                    ---
                    
                    
                    ....
                    blocked hundreds!
                    That was the reason tried to terminated it on the fly.
                    • 7. Re: A problem session
                      JohnWatson
                      For the third time: you can't kill something that is already committing suicide.
                      It would seem unlikely that such a short-lived session can be blocking anything.
                      But clearly my time is of no value to you or you would have said "thank you for trying to help" so I shall sign off.
                      Perhaps someone else will try to assist.
                      • 8. Re: A problem session
                        jmft2012
                        Did you not notice as shown blocker had been blocking hundreds of the others even as rapidly login/off?
                        i tried to kill the session as monument just logged in , hopefully that could release the lock.

                        That was the story.

                        Edited by: jmft2012 on Mar 23, 2013 8:44 AM
                        • 9. Re: A problem session
                          jmft2012
                          Question,
                          We knew that session toggled in/out , why was the resource held by the SID not released as off?
                          • 10. Re: A problem session
                            sb92075
                            how to kill a session?
                            First you must identify it.

                            Problem to pass Oracle variable to Unix
                            • 11. Re: A problem session
                              Justin_Mungal
                              I think you're probably wasting a little too much brain power on this one. It looks like the app is starting some work, then spawning a new session from the same connection before the previous session's work is done, and repeating that exact same work (based on the TX locking you've shown). Then it does this again for the new session, etc.

                              So this is obviously a broken app... talk to your vendor and have them fix it. It's not really something we can help you with.
                              • 12. Re: A problem session
                                jmft2012
                                Justin Mungal wrote:
                                I think you're probably wasting a little too much brain power on this one. It looks like the app is starting some work, then spawning a new session from the same connection before the previous session's work is done, and repeating that exact same work (based on the TX locking you've shown). Then it does this again for the new session, etc.

                                So this is obviously a broken app... talk to your vendor and have them fix it. It's not really something we can help you with.
                                I did inform the vendor.
                                before they can address app's issue, i need to resolve the lock issue once it occurs again, through we do not want it back.
                                the resource was held up by the session with the sid and the serial# as explained and shown in this thread , it was blocking hundreds of other sessions.
                                As explained , the problem was the session toggled in/out of db so rapidly, i could not capture the serial# manually , thus we were not able to remove the blocker. and ended up restarted the app.

                                I may be in a better shape next time since i have the programs to kill the problem session with just the sid. the program uses the sid to get the serial# and kills it. this is much faster than manually issue ALTER SYSTEM KILL.
                                Hopefully it would work as expect.

                                i marked this as "solved". if next time it did not do, i will create a new thread to discuss.
                                • 13. Re: A problem session
                                  jmft2012
                                  .
                                  • 14. Re: A problem session
                                    Justin_Mungal
                                    jmft2012 wrote:
                                    Justin Mungal wrote:
                                    I think you're probably wasting a little too much brain power on this one. It looks like the app is starting some work, then spawning a new session from the same connection before the previous session's work is done, and repeating that exact same work (based on the TX locking you've shown). Then it does this again for the new session, etc.

                                    So this is obviously a broken app... talk to your vendor and have them fix it. It's not really something we can help you with.
                                    I did inform the vendor.
                                    before they can address app's issue, i need to resolve the lock issue once it occurs again, through we do not want it back.
                                    the resource was held up by the session with the sid and the serial# as explained and shown in this thread , it was blocking hundreds of other sessions.
                                    As explained , the problem was the session toggled in/out of db so rapidly, i could not capture the serial# manually , thus we were not able to remove the blocker. and ended up restarted the app.

                                    I may be in a better shape next time since i have the programs to kill the problem session with just the sid. the program uses the sid to get the serial# and kills it. this is much faster than manually issue ALTER SYSTEM KILL.
                                    Hopefully it would work as expect.

                                    i marked this as "solved". if next time it did not do, i will create a new thread to discuss.
                                    Just for the record: it's trivial to write a script that generates the ALTER SYSTEM KILL commands for you, to kill by whatever predicate you want. I'm not saying it's a good idea, but I've done it a few times for various reasons.
                                    select 'ALTER SYSTEM KILL SESSION '''|| s.sid || ',' || s.serial#|| ''' IMMEDIATE;' as KILLCOMMAND
                                     from v$session s
                                     where s.sql_id='&sql_id';
                                    1 2 Previous Next