Re: looping cursor on a query hangs.

From: joel garry <joel-garry_at_home.com>
Date: Mon, 4 May 2009 14:58:05 -0700 (PDT)
Message-ID: <bc57cbfb-7295-435d-ac53-7875c1695201_at_w35g2000prg.googlegroups.com>

On May 3, 4:06 am, Mladen Gogala <mla..._at_bogus.email.com> wrote:
> 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.
>

Hey, ya gotta have big crystal balls: http://farm1.static.flickr.com/169/433575350_24b1c036d2.jpg

Regarding the statistics example I gave earlier in this thread, it turned out while I was out for a few days, a vendor came in, did a metadata change on the table, which led to stale data, which led to new statistics, which led to users complaining. Grrrrrrrrrr. Resetting the statistics to before the update fixed everything. I give big props to 10g built-ins and EM for this one, keeping a statistics history and making it easy to go check and go back and lock stats with a few clicks made all the difference in the world in a highly visible production scenario having to do with the business collecting money. Not to mention a solid example of where proper tools help, maybe I hope to the point of getting some for other predictable optimizer issues.

jg

--
_at_home.com is bogus.
That's a hard act to follow.

Received on Mon May 04 2009 - 16:58:05 CDT