Long running roll back
- From: "Ram Raman" <veeeraman@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Oct 2008 16:59:10 -0500
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:
//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.
Other related posts:
