This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Mar 24, 2013 6:57 PM by 548518 RSS

A problem session

jmft2012 Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    how to kill a session?
    First you must identify it.

    Problem to pass Oracle variable to Unix
  • 11. Re: A problem session
    Justin_Mungal Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    .
  • 14. Re: A problem session
    Justin_Mungal Journeyer
    Currently Being Moderated
    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

Legend

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