Re: looping cursor on a query hangs.
Date: Sun, 3 May 2009 11:06:48 +0000 (UTC)
On Sat, 02 May 2009 16:06:13 -0700, William Robertson wrote:
> Assuming you are using Oracle 10g, the simpler "FOR recordname IN
> cursorname LOOP" syntax (or equivalent "FOR recordname IN (query) LOOP")
> will be converted by the compiler to a more efficient bulk- collect
> construction. Whether this addresses your main problem here I can't say.
Nobody can say that, without using the famous "method R", nobody can
accurately pinpoint the source of the problem. The proper procedure
would require tracing the session by setting the event 10046 on the
level 8 or 12 and then analyzing the trace file. One could do that by
using DBMS_MONITOR or DBMS_SUPPORT packages, too. Trace files can be
analyzed by using Hotsos profiler, orasrp (my personal favorite), trcanlzr
If the trace information is not present, guessing is all that is left. If needed, one can guess rather accurately, based on V$SESSION_EVENTS and V$SESSION_WAIT, but it is still a guess. To guess accurately, one would also need to know the structure of the underlying schema: table sizes, available indexes, CPU usage and some other things. Without that information being present, I decline making un-educated guesses, it makes me feel like madamme Enygma with her deck of Tarot cards and the inevitable crystal ball. Given my 6'4", 260 LBS frame, I don't see myself in that role.