Long running roll back

From: Ram Raman <veeeraman_at_gmail.com>
Date: Tue, 14 Oct 2008 16:59:10 -0500
Message-ID: <effc058d0810141459j641703bcy6feb3fb27d2f656e@mail.gmail.com>

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 Tue Oct 14 2008 - 16:59:10 CDT