Chirag, you can use the following query to see if any transaction is actually rolling back:
select s.username, s.sid, t.addr, t.status, t.used_ublk, decode(bitand(t.flag ,128),0,'NO','YES') rolling_back from v$session s, v$transaction t where s.taddr =t.addr;
(Thanks to Mark Powell for telling me about this flag a few weeks ago)
And you can query v$transaction.used_ublk at an interval to calculate the remaining time to complete the rollback - here is a query that will do it for you:
http://www.ixora.com.au/scripts/rbs.htm
(Thanks to Jared Still for pointing me to this script last month, and thanks to Steve Adams for writing and sharing it)
-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]On Behalf Of Chirag DBA Sent: Thursday, September 15, 2005 8:24 AM To: Chen, Sarah Cc: yaoyongping@(protected); Thomas.Mercadante@(protected); Oracle-L Freelists Subject: Re: Session Killed...!!
well, that is understandable. But the question is why it is taking too long to roll back.
It is more than 10 hrs now.
What is it rolling back for this long?
regards - chirag
On 9/15/05, Chen, Sarah < Sarah_Chen@(protected)> wrote:
I did some research according to this issue before. Here is from my notes(I don't know where I got it, but must from one of the Oracle experts):
when a session is killed, oracle needs to rollback whatever work it had been doing - that's why it waits.
going to the o/s and killing the processes won't speed this up. However, when you use "alter system kill session", the session doesn't know it's killed until it makes another request to the database - if it was waiting for a response (particularly to a remote operation), it may not make a request for a long time, in which case killing the process from the o/s may help to stop the session. at that time, the database still needs to rollback whatever had happened already.
Sarah
-- --Original Message-- -- From: Yongping Yao [mailto: <mailto:yaoyongping@(protected)> yaoyongping@(protected) .com] Sent: Thursday, September 15, 2005 11:06 AM To: Chen, Sarah Cc: Thomas.Mercadante@(protected); chiragdba@(protected); Oracle-L Freelists Subject: Re: Session Killed...!!
I agree that it's very strange to kill a session in Oracle. As I experienced, SELECTs are more easier to kill than DMLs and SELECTs running after a long time are more difficult to kill. UPDATE is the most difficult to kill. Oracle always reponds as "we have marked the session killed" but the session is still there and the lock remained hold. Anyone with good recommendations?
2005/9/15, Chen, Sarah < Sarah_Chen@(protected)>: > > I always check process id as well as sid and serial# while killing a session > from Oracle. > > I will always query v$session before and after, and if the status of > v$session marked "killed", and then I will go ahead kill OS process to clean > this session. It always works on Solaris. > > Sarah > > > -- --Original Message-- -- > From: oracle-l-bounce@(protected) [mailto: <mailto:oracle-l-bounce@(protected)> oracle-l-bounce@(protected) ]On > Behalf Of Mercadante, Thomas F (LABOR) > Sent: Thursday, September 15, 2005 10:47 AM > To: chiragdba@(protected); Oracle-L Freelists > Subject: RE: Session Killed...!! > > > > > If you kill the unix process associated with this session first, and then > the session it will go away. > > > > Killing sessions in Oracle has always been weird. Sometimes they go away > and sometimes not, depending on OS. In Windows, use orakill to kill the > session �C this always seems to work in windows. And killing the unix > session then the Oracle session seems to work just fine in AIX. > > > > __ ____ ____ ____ ____ ____ ____ > > > From: oracle-l-bounce@(protected) [mailto: <mailto:oracle-l-bounce @(protected)> oracle-l-bounce@(protected)] > On Behalf Of Chirag DBA > Sent: Thursday, September 15, 2005 10:36 AM > To: askdba@(protected); Oracle-L Freelists > Subject: Session Killed...!! > > > > > Hi , > > > > > > I saw many users on my database connected for more than 6 days without any > activity. > > > > > > I killed 1 user and still the status in v$session is showing as killed. > > > > > > but it it not getting removed from the v$session. > > > > > > I am running 9.0.1.3 on solaris. > > > > > > any idea? We already hit the bug ora-07442 (See ora-07442.ora-code.com). > > > > > > regards - chirag
-- Yao Yongping Learning Oracle, UNIX/Linux... Love Reading, Classical Music, Philosophy, Economics etc. Blog: http://blog.csdn.net/ern <http://spaces.msn.com/members/yaoyp/> http://spaces.msn.com/members /yaoyp/
Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1515" name=GENERATOR></HEAD> <BODY> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2>Chirag, you can use the following query to see if any transaction is actually rolling back:</FONT></SPAN></DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2 >select s.username, s.sid, t.addr, t.status, t.used_ublk, decode(bitand(t.flag,128),0,'NO','YES') rolling_back from v$session s, v$transaction t where s.taddr=t.addr;</FONT></SPAN></DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2>(Thanks to Mark Powell for telling me about this flag a few weeks ago)</FONT></SPAN></DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2>And you can query v$transaction.used_ublk at an interval to calculate the remaining time to complete the rollback - here is a query that will do it for you:</FONT></SPAN></DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=212020616-15092005><A href="http://www.ixora.com.au/scripts/rbs.htm">http://www.ixora.com.au/scripts /rbs.htm</A><BR></SPAN></DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2>(Thanks to Jared Still for pointing me to this script last month, and thanks to Steve Adams for writing and sharing it)</FONT></SPAN></DIV> <DIV><SPAN class=212020616-15092005><FONT face=Arial color=#0000ff size=2></FONT> </DIV></SPAN> <BLOCKQUOTE> <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma size=2>-- --Original Message-- --<BR><B>From:</B> oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]<B>On Behalf Of </B>Chirag DBA<BR><B>Sent:</B> Thursday, September 15, 2005 8:24 AM<BR><B>To:</B> Chen, Sarah<BR><B>Cc:</B> yaoyongping@(protected); Thomas.Mercadante@(protected); Oracle-L Freelists<BR><B>Subject:</B> Re : Session Killed...!!<BR><BR></FONT></DIV> <DIV>well, that is understandable. But the question is why it is taking too long to roll back.</DIV> <DIV> </DIV> <DIV>It is more than 10 hrs now.</DIV> <DIV> </DIV> <DIV>What is it rolling back for this long? </DIV> <DIV> </DIV> <DIV>regards - chirag<BR><BR> </DIV> <DIV><SPAN class=gmail_quote>On 9/15/05, <B class=gmail_sendername>Chen, Sarah</B> <<A href="mailto:Sarah_Chen@(protected)">Sarah_Chen@(protected)</A>> wrote:</SPAN> <BLOCKQUOTE class=gmail_quote style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">I did some research according to this issue before. Here is from my notes(I<BR>don't know where I got it, but must from one of the Oracle experts): <BR><BR>when a session is killed, oracle needs to rollback whatever work it had been<BR>doing - that's why it waits.<BR><BR>going to the o/s and killing the processes won't speed this up. However,<BR>when you use "alter system kill session", the session doesn't know it's <BR>killed until it makes another request to the database - if it was waiting<BR>for a response (particularly to a remote operation), it may not make a<BR>request for a long time, in which case killing the process from the o/s may <BR >help to stop the session. at that time, the database still needs to rollback<BR>whatever had happened already.<BR><BR><BR>Sarah<BR><BR><BR>-- --Original Message-- --<BR>From: Yongping Yao [mailto:<A href="mailto:yaoyongping@(protected)"> yaoyongping@(protected)</A>]<BR>Sent: Thursday, September 15, 2005 11:06 AM<BR>To: Chen, Sarah<BR>Cc: <A href="mailto:Thomas.Mercadante@(protected)">Thomas.Mercadante@(protected) .state.ny.us</A>; <A href="mailto:chiragdba@(protected)">chiragdba@(protected)</A>; Oracle-L<BR>Freelists<BR>Subject: Re: Session Killed...!!<BR><BR><BR>I agree that it's very strange to kill a session in Oracle. As I<BR>experienced, SELECTs are more easier to kill than DMLs and SELECTs <BR>running after a long time are more difficult to kill. UPDATE is the<BR>most difficult to kill. Oracle always reponds as "we have marked the<BR>session killed" but the session is still there and the lock remained <BR>hold. Anyone with good recommendations?<BR><BR>2005/9/15, Chen, Sarah <<A href="mailto:Sarah_Chen@(protected)">Sarah_Chen@(protected)</A>>:<BR>><BR >> I always check process id as well as sid and serial# while killing a <BR>session<BR>> from Oracle.<BR>><BR>> I will always query v$session before and after, and if the status of<BR>> v$session marked "killed", and then I will go ahead kill OS process to<BR>clean<BR>> this session. It always works on Solaris. <BR>><BR>> Sarah<BR>><BR>><BR>> -- --Original Message-- --<BR>> From: <A href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce@(protected)< /A><BR>[mailto:<A href="mailto:oracle-l-bounce@(protected)"> oracle-l-bounce@(protected)</A>]On<BR>> Behalf Of Mercadante, Thomas F (LABOR)<BR>> Sent: Thursday, September 15, 2005 10:47 AM<BR>> To: <A href="mailto:chiragdba@(protected)">chiragdba@(protected)</A>; Oracle-L Freelists <BR>> Subject: RE: Session Killed...!!<BR>><BR>><BR>><BR>><BR>> If you kill the unix process associated with this session first, and then<BR>> the session it will go away.<BR>><BR>><BR>><BR>> Killing sessions in Oracle has always been weird. Sometimes they go away <BR>> and sometimes not, depending on OS. In Windows, use orakill to kill the<BR>> ; session �C this always seems to work in windows. And killing the unix<BR>> session then the Oracle session seems to work just fine in AIX . <BR>><BR>><BR>><BR>> __ ____ ____ ____ ____ ____ ___ _<BR>><BR>><BR>> From: <A href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce@(protected)< /A> [mailto:<A href="mailto:oracle-l-bounce@(protected)"> oracle-l-bounce@(protected)</A>]<BR>> On Behalf Of Chirag DBA<BR>> Sent: Thursday, September 15, 2005 10:36 AM<BR>> To: <A href="mailto:askdba@(protected)">askdba@(protected)</A>; Oracle-L Freelists<BR>> Subject: Session Killed...!! <BR>><BR>><BR>><BR>><BR>> Hi ,<BR>><BR>><BR>><BR>><BR>><BR>> I saw many users on my database connected for more than 6 days without any<BR>> activity.<BR>><BR>><BR>><BR>><BR>><BR>> I killed 1 user and still the status in v$session is showing as killed.<BR>><BR>><BR>><BR>><BR>><BR>> but it it not getting removed from the v$session.<BR>><BR>><BR>><BR>><BR>> <BR>> I am running <A href="http://9.0.1.3">9.0.1.3</A> on solaris.<BR>><BR>><BR>><BR>><BR>><BR>> any idea? We already hit the bug ora-07442 (See ora-07442.ora-code.com).<BR>><BR>><BR>><BR>><BR>><BR>> regards - chirag <BR><BR><BR>--<BR>Yao Yongping<BR>Learning Oracle, UNIX/Linux...<BR>Love Reading, Classical Music, Philosophy, Economics etc.<BR>Blog: <A href="http://blog.csdn.net/ern">http://blog.csdn.net/ern</A><BR>   ; <A href="http://spaces.msn.com/members/yaoyp/"> http://spaces.msn.com/members/yaoyp/</A><BR></BLOCKQUOTE></DIV><BR>< /BLOCKQUOTE></BODY><!--[object_id=#oneneck.com#]--><FONT face=Tahoma size=2> <FONT color=#0000ff> <P>Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.</P>< /FONT></FONT></HTML>
