RE: Long running roll back
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 15 Oct 2008 09:59:33 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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:
//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:
