Re: looping cursor on a query hangs.
Date: Sun, 3 May 2009 11:06:48 +0000 (UTC)
Message-ID: <gtjts8$t69$3_at_solani.org>
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.
-- http://mgogala.freehostia.comReceived on Sun May 03 2009 - 06:06:48 CDT