Hello,
I am using Oracle Database 11g Release 11.2.0.4.0.
I have one application which is calling one Procedure Query from Back End. When this query called, my application get stuck.
And Toad Session browser is showing me that this query is running.
But when I take that query from session browser and Pass same parameter as passing from Application, Query execute in miliseconds.
Can you please suggest us what can we do in this case?
Thanks in advance.
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
exec dbms_monitor.session_trace_disable;
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!
Good luck.