Re: looping cursor on a query hangs.
From: joel garry <joel-garry_at_home.com>
Date: Fri, 1 May 2009 15:42:01 -0700 (PDT)
Message-ID: <cc5e2985-5316-4836-8d5c-b1e387f0ed51_at_m19g2000yqk.googlegroups.com>
On May 1, 1:03 pm, Joyce <joyce.sz.c..._at_gmail.com> wrote:
> Is it reasonable for Oracle to hang on a query like this? Basically,
> it's a cursor on a query which splits csv entries, and using the
> splitted up values to insert into another table. Does it hang because
> my table is too large? what is a better way that I can accomplish
> what I'm trying to do here? thank you, Joyce
>
> DECLARE
> CURSOR photo_cursor IS
> select occurrence_num, graphic from (
> select occurrence_num, trim(substr(str, instr(str, ',', 1, level) +
> 1,
> instr(str, ',', 1, level + 1) - instr(str, ',', 1, level) - 1)
> ) graphic, level lv
> , lag(level, 1, 0) over (partition by occurrence_num order by level)
> lg
> from (select occurrence_num, ','||graphics||',' str from supp)
> connect by instr(str, ',', 1, level) > 0)
> where graphic is not null and lv != lg and rownum <=10;
> photo_record photo_cursor%ROWTYPE;
> BEGIN
> OPEN photo_cursor;
> LOOP
> FETCH photo_cursor INTO photo_record;
> EXIT WHEN photo_cursor%NOTFOUND;
> -- insert into image (photo_id,report_id, filename)
> -- values (PHOTO_key_seq.nextval,r.occurrence_num,
> r.graphic);
> END LOOP;
> CLOSE photo_cursor;
> END;
To answer reasonableness, you need to know what it is holding it up. There are various ways to figure this out, method-r is one popular one where you can go out and buy a book, the Oracle docs have another, people like http://blog.tanelpoder.com/ have others. There are also plenty of tools at your disposal, but it's too hard to tell from the information given. Of course, it may be as simple as the instr forcing a full table scan, which would likely make it table size dependent - depending on when the rownum is evaluated. Some things that normally are good, like your use of analytic functions,can be bad in some cases. Some things that people think are always bad, like full table scans, can be good in some cases. This morning I had a report that gets run a couple of times a day with no problems for years suddenly go bad, so it wasn't from statistics changing (I still need to verify exactly when they've run it, and the statistics gathering..."Don't trust, and verify"), indices corrupting, or anything obvious - looks like I'll be doing a full workup on it, but at least I was able to narrow it down to a between clause, and tell people what to do in their form to make it an in list (which would be gibberish to them in those terms). But at least the problem replicates.
Date: Fri, 1 May 2009 15:42:01 -0700 (PDT)
Message-ID: <cc5e2985-5316-4836-8d5c-b1e387f0ed51_at_m19g2000yqk.googlegroups.com>
On May 1, 1:03 pm, Joyce <joyce.sz.c..._at_gmail.com> wrote:
> Is it reasonable for Oracle to hang on a query like this? Basically,
> it's a cursor on a query which splits csv entries, and using the
> splitted up values to insert into another table. Does it hang because
> my table is too large? what is a better way that I can accomplish
> what I'm trying to do here? thank you, Joyce
>
> DECLARE
> CURSOR photo_cursor IS
> select occurrence_num, graphic from (
> select occurrence_num, trim(substr(str, instr(str, ',', 1, level) +
> 1,
> instr(str, ',', 1, level + 1) - instr(str, ',', 1, level) - 1)
> ) graphic, level lv
> , lag(level, 1, 0) over (partition by occurrence_num order by level)
> lg
> from (select occurrence_num, ','||graphics||',' str from supp)
> connect by instr(str, ',', 1, level) > 0)
> where graphic is not null and lv != lg and rownum <=10;
> photo_record photo_cursor%ROWTYPE;
> BEGIN
> OPEN photo_cursor;
> LOOP
> FETCH photo_cursor INTO photo_record;
> EXIT WHEN photo_cursor%NOTFOUND;
> -- insert into image (photo_id,report_id, filename)
> -- values (PHOTO_key_seq.nextval,r.occurrence_num,
> r.graphic);
> END LOOP;
> CLOSE photo_cursor;
> END;
To answer reasonableness, you need to know what it is holding it up. There are various ways to figure this out, method-r is one popular one where you can go out and buy a book, the Oracle docs have another, people like http://blog.tanelpoder.com/ have others. There are also plenty of tools at your disposal, but it's too hard to tell from the information given. Of course, it may be as simple as the instr forcing a full table scan, which would likely make it table size dependent - depending on when the rownum is evaluated. Some things that normally are good, like your use of analytic functions,can be bad in some cases. Some things that people think are always bad, like full table scans, can be good in some cases. This morning I had a report that gets run a couple of times a day with no problems for years suddenly go bad, so it wasn't from statistics changing (I still need to verify exactly when they've run it, and the statistics gathering..."Don't trust, and verify"), indices corrupting, or anything obvious - looks like I'll be doing a full workup on it, but at least I was able to narrow it down to a between clause, and tell people what to do in their form to make it an in list (which would be gibberish to them in those terms). But at least the problem replicates.
So the very first thing to do is to post the relevant plan, and other relevant information like in http://dbaoracle.net/readme-cdos.htm#subj12
jg
-- _at_home.com is bogus. Dear Friends, when Chrysler emerges from streamlined bankruptcy, the United Auto Workers will own 55%. In what must be one of the larger ironies of modern times, the deregulatory polices of Reagan, Bush and the Objectivist Alan Greenspan have led directly to the ownership and control of production by the collectivist workers.Received on Fri May 01 2009 - 17:42:01 CDT