We have a Stored Procedure that is hanging for a long time now. But It used to run 1 minute only.
I monitored locks but there is none.
If I run manually one by one the sql contents of the procedure in sqlplus, it will run okay.
I already stopped and start it again, but to no avail.
I also recompile it and gather schema stats, but to no avail.
How do I troubleshoot this issue please..
Post the facts that support your conclusion that the procedure is 'hanging'. It may just be taking longer than usual to execute.
To troubleshoot you need to identify the procedure statement that is 'hanging'. When it seems to be hung look at V$SESSION_LONGOPS and see what statement is being executed.
You could also instrument the procedure by adding logging statements before each of the key blocks of code. Then you could tell from the log what statement the procedure is executing when it hangs.
That log would also let you monitor the typical execution times of the procedure and its statements.
does SP do DML?
does SP do COMMIT?
It is a REAL challenge to debug code that can not be seen.
my car won't go
tell me how to make my car go.
How do I ask a question on the forums?
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
But no rows selected.
The weird thing is.
I only have these sql statement (analogy only)..
insert into TABLE_A (select * from TABLE_B)
If I run manually select * from TABLE_B, it runs ok.
But if I run
insert into TABLE_A (select * from TABLE_B) ; it hangs with no info in the v$session_longops.
If I tried inserting insert into TABLE_A (values 1) it is okay ...
I AM GETTING CRAZY NOW....what is happening to this program....it has been running before.
Or what is happening to Oracle??? Shall I reboot the prod server?
I have tried to help. The only help I can offer is what I gave in my reply above. If you don't want to accept that help and try that then just wait for others to respond.
All you posted was a query with predicates that are preventing you from seeing what the session is actually doing and the code you post (analogy only) isn't what you are actually using.
No one can debug or understand code that they can't see.
Good luck with your problem.
Maybe my stored procedure is corrupted?
I tried dropping it but it is hanging also
What is going on? How do I select the process that blocks my dropping of this procedure?
I tried this:
SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
|| ' ( SID= '
|| ' ) with the statement: '
||' is blocking the SQL statement on '
|| ' ( SID='
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM Gv$lock l1,
Gv$session s1 ,
Gv$lock l2 ,
Gv$session s2 ,
Gv$sql sqlt1 ,
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid
AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id
AND l1.BLOCK =1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
But no one is blocking....
May I will request a restart of the database. Something is weird going on here.
Thanks rp, sb,
Bouncing the database resolved the issue. So to make your job easy and not go get crazy I suggest operators to restart the database, if the same issue occurs.
As there are lots of things that can not be explained.
Rebooting the DB is like tearing down a wall because you lost the door key.
as POGO once said, "We have met the enemy & they is us!"
Oracle does not allow dropping or recompile of any SP after DML has occurred & unless or until COMMIT or ROLLBACK has been issued by the same session.
You & you alone are the root cause for problems in this database!
You are right that the cause of not able to drop the stored_proc is that it is being used by the run-away program. The one that were executed hours ago but was aborted.
The problem is I can not find the session to rollback or kill as it does not display on the monitor transaction locks.
The problem is it took a long time to rollback aborted transactions. But is it very fast to roll it back using bouncing the database. So which one do I choose? rollback fast or
rollback waiting forever.