Re: looping cursor on a query hangs.

From: Mladen Gogala <>
Date: Sun, 3 May 2009 11:06:48 +0000 (UTC)
Message-ID: <gtjts8$t69$>

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 or tkprof.
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.

Received on Sun May 03 2009 - 06:06:48 CDT

Original text of this message