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:

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.
Follow-Ups:

RE: Long running roll back
From: Allen, Brandon

RE: Long running roll back
From: Powell, Mark D
Long running roll back

Other related posts:
