Hung Process

[ocp2b]

I think I have hung process. A developer in interactive mode has issued a delete command.

Delete from chartsetup
where chart_id = 123;

This delete has been running for over an hour. How can I tell what's going on? The process is getting CPU time. That much I can tell. But something else has got to be going on and I am not sure where to start.

[sambavan]

Check the session_wait and lock views to have more idea on what is going on.

Sam

[reydp]

try this inquiry to the developer:
how many records where expected to be deleted?
how many records all in all in this table to be deleted?
Is there an index in chart_id of this table chartsetup?
where there a dependent table of this chartsetup and was configured as
ON DELETE CASCADE?
Was there a pre-process that change/update one or more records of the table chartsetup
and still have to be commited before the DELETE process was made?

In your part:
If this is a huge number of records to be deleted,
Have you query the v$session_wait or v$session_event already?
To establish that endeed there is a waiting of this SID process.
Does your rollback segment can accommodate fast with this scenario?
Have you queried the v$waitstat aside from v$rollstat to assess you in
your rollback segment configuration?
To be aware that the rollback segment storage defination should be well-defined.
Is there a lock issues that causes this process to hung? have you query v$lock?
To identify that the SID process has lock problems.

Keep it cool.

regards

[ocp2b]

The developer does not know how many rows will be deleted. There is an index on Chartsetup and the index does include the chart_id column. There are also dependent tables on Chartsetup and it was configured with ON DELETE CASCADE.

I can't figure out which process is running because the developer ran his job/script from server manager and the only thing that shows up in v$session are the six "system" sessions and my interactive session.

When I query v$session_wait, four sessions are waiting on
"rdbms ipc message", one is waiting on "smon timer" and one on "pmon timer". I'm not sure what I need to be looking at in v$session_event. V$waitstat shows all zeros for count and time. V$rollstat shows zero waits and everything is online.

The only thing I find interesting although I'm not sure how to interpret it is the v$lock view. Here is the results:

SID Ty ID1 ID2
3 RT 1 0
2 MR 1-5 0 (this sid has 5 rows with id1 = 1,2,3,4,5)
5 TS 33573916 0

I found some documentation to explain what RT, MR and TS are. But I have not found anything that explains what is going on with each one. Although MR is pretty self explanitory since it is Media Recovery. I don't know how any processes could be in media recovery. All the disk drives are online and have plenty of free disk space. Aside from this hung process, everything else is humming along just fine. (knock on wood).