Procedure hanging

You could have a lock.  Check out this link for how to find out if there are any locks:

https://www.experts-exchange.com/questions/21787127/ORA-00054-resource-busy-and-acquire-with-NOWAIT.html

let me see...

After I killed the session, the locks are all gone, os process is also gone. So I don't know if performance might be one issue. It is however pretty quick last week.

The other thing is if I schedule this procedure as a job in toad, the TOTAL_TIME parameter(which means the number of times the job has been executed), is growing like wild fire. In a minute it grows to over 100 times, and the FAILURES parameter is always empty (which should be 0 if last time is successful; or a number indicating times of failure)

From my monitoring total_time increment by 1 after each run; any suggestion on what DBMS_OUTPUT statement to put there?

Maybe you are right total_time is the seconds.

I however suspect, it must be a bulk update query on the table which inturn has a trigger (on update) such that causing  a recursive lock.

--There is something related to trigger, but I don't know if it is causing the problems. I am trying to load one table which has triggers to insert into a table on Monday morning, say T1. It is taking so long that I eventually killed it. This procedure is a scheduled job at that time(every 2 hours). It uses records in T1 to put into another table (insert into T2 select ...from T1...). So trigger and procedure could possible happened at the same time. Then I noticed the failures for the job is 3. So I dropped the job, then things are like what is now.

I also rescheduled the job, it got completed but with failures. Again the same thing is fine on the development server.

Now it looks like hanging again.

Thanks for all your comments. I find out that it is a performance issue.