RE: Long running roll back

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Wed, 15 Oct 2008 09:59:33 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A902FFC92D@usahm208.amer.corp.eds.com>

If there is no entry in v$transaction but the session background process is still getting time kill it from the OS. If the session held any row level locks there would be an entry in v$transaction. If the session background process does not exist then Oracle has just not reused the v$session entry yet.  

• Mark D Powell -- Phone (313) 592-5148

  ---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman

	Sent: Tuesday, October 14, 2008 5:59 PM
	To: ORACLE-L
	Subject: Long running roll back
	
	
	Hi all, 
	 
	V: 10.203

	We had a long running process that was running for hours (20 hrs
for one qry) and the CPU usage spiked to 100%. We had to kill it. 
	The status is being shown as Killed in V$session. It has been

that way for the past several hours. Users are wondering when it will finish. Usually in this scenario I look at v$transaction to see the progress of it, but I don't see any.

        v$session:         

	       SID    SERIAL# CLIENT_INFO
	---------- ----------


----------------------------------------------------------------

	PROGRAM                                          OSUSER
PROCESS
	------------------------------------------------
------------------------------ ------------
	USERNAME                       MACHINE
STATUS
	------------------------------


----------------------------------------------------------------

--------
	TO_CHAR(S.LOGON_T PADDR            SADDR            SPID
PID SQL_ADDRESS
	----------------- ---------------- ---------------- ------------
---------- ----------------
	       882       5587
	                                                 SYSTEM
1234
	SUPRKRON                       abc-dsonos4
KILLED
	03-SEP-08 11:03am C00000009EA9C7F8 C00000009D1A8D00 19385

18 C0000000928A0DE0         

        SQL> select * from v$transaction;

        no rows selected

	SQL> 
	SQL> select * from v$sqlarea where address = 'C0000000928A0DE0';

	no rows selected

	  1  select * from v$session_wait
	  2* where sid = 882
	SQL> /

	       SID       SEQ# EVENT
	---------- ----------


----------------------------------------------------------------

	P1TEXT
P1 P1RAW
	----------------------------------------------------------------
---------- ----------------
	P2TEXT
P2 P2RAW
	----------------------------------------------------------------
---------- ----------------
	P3TEXT
P3 P3RAW            WAIT_CLASS_ID WAIT_CLASS#
	----------------------------------------------------------------
---------- ---------------- ------------- -----------
	WAIT_CLASS
WAIT_TIME SECONDS_IN_WAIT STATE
	----------------------------------------------------------------
---------- --------------- -------------------
	       882      20028 kksfbc child completion
	

0 00         

0 00         

0 00                  1893977003           0
	Other
5         2409758 WAITED KNOWN TIME

	 

	I googled on kkssfbc and I got this: 

	

http://www.freelists.org/archives/oracle-l/02-2007/msg00465.html.

        16:23:18 SQL> show parameter cursor

	NAME                                 TYPE        VALUE
	------------------------------------ -----------
------------------------------
	cursor_sharing                       string      EXACT
	cursor_space_for_time                boolean     FALSE
	open_cursors                         integer     1000
	session_cached_cursors               integer     20
	

	Can anyone shed light on what is going on? THe OS process

corresponding to the killed session (882) is using one CPU fully out of 2 CPUs.

        Thanks.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 15 2008 - 08:59:33 CDT