In a word, trace it.
Use Toads Session Browser to find the session that you know will get stuck, start tracing and then leave it to runt until stuck, then a wee bit longer, then stop the trace and see what it shows.
If you don’t have the DBA Module which provides the session browser, then add the following code before and after your sticking query:
alter session set events ‘10046 trace name context forever, level 12’;
your query here
alter session set events ‘10046 trace name context off’;
This obviously requires your user to have ALTER SESSION privileges.
There are other ways such as calling dbms_system or dbms_monitor but the depend on certain versions of Oracle. For 11g you should/could use the following instead:
exec dbms_monitor.session_trace_enable(waits=>true, binds=>false);
Your SQL here
This requires that your user has EXECUTE privilege granted on DBMS_MONITOR.
Then you just need to check out the contents of the trace file. If you have the DBA Module in Toad, then you can use the trace file browser. Otherwise, it’s the “joy of hex” and you’ll have to trawl through the data looking for long waits etc. Or you could use tkprof I suppose!