Re: what's a "reasonable" length of time to keep a cursor open?

On Aug 28, 8:42 pm, m...@xxxxxxxxx wrote:
DA Morgan <damor...@xxxxxxxxx> wrote:
And your Oracle version is?

Load the records into a global temporary table if they are
available in your version.

10g, So that's definitely something I'll look at.

But I've got a problem with any process that takes several days to
complete. It might be worth investing your efforts into tuning the
offending process.

I don't think I explained my program very well... It is basically
an image processing work dispatcher that runs continuously.

Work is enqueued by
adding rows to a table (setting the "state" column to "enqueued"),
and dequeued by "select * ... where state='enqueued'". Worker
processes set the state to "in progress", "failed", "completed",
etc.

So the overall algorithm for the work dispatcher is:

loop forever:
select * ... where state = 'queued'
if there is no data:
sleep for a while
for each row in the cursor:
process the item corresponding to that row
if more than X minutes has passed:
close cursor
continue loop

So if a huge client job dumps a lot of work into the queue
then the "for each row" could keep the cursor open for quite
a while.

Currently I'm using a 2 minute max time for the cursor, but
I don't know if that is being too conservative or not. Before
I put the time check, Oracle would return the previously mentioned
error after 8-10 hours. :-)

Of course, I'm open to any suggestions as to improving my queuing!

Thanks,
Mark

--
Mark Harrison
Pixar Animation Studios

Hi Mark,

A simple solution is to select a limited number of records (100 for
example) with a 'queued' state. If there are more records in the
queue, they will be processed in the following iterations since you
have an unlimited loop. Processing a limited number of records in a
row ensures that your cursor will stay open for a deterministic period
of time. This could be done by adding WHERE ROWNUM < 100 predicate to
your original select statement. Please consult your oracle
documentation for more reference.

BR Deyan

.

Relevant Pages

  • Re: More on closing Oracle ref cursors
    ... I would be very grateful if you could find out for me if the same is true in your Oracle 8i database. ... loop in the _do_proc method. ... cursor handle, but I can't see what would be doing that. ... IF bind_param_inout creates a reference from the pl/sql statement ...
    (perl.dbi.users)
  • Re: whats a "reasonable" length of time to keep a cursor open?
    ... Load the records into a global temporary table if they are ... an image processing work dispatcher that runs continuously. ... for each row in the cursor: ...
    (comp.databases.oracle.misc)
  • Re: Learning Cursors
    ... A cursor may be necessary when you need to process each row in a result ... be complex and not easily solved within a single query. ... Oracle 10g ... Many times all I do with a cursor is to loop the ...
    (comp.databases.oracle.server)
  • Re: cursor for loop
    ... karthika Jegatheesan via SQLMonster.com wrote: ... > for emp_rec IN c loop ... > Similar to cursor for loop in oracle is there any corresponding for ... Look up Declare Cursor in the docs. ...
    (microsoft.public.sqlserver.programming)
  • Using BULK BIND.....
    ... Have a program which is currently processing a cursor and doing various ... bits and bobs with the data. ... each iteration of the loop. ... Using Oracle 9.2 and will be several thousand rows. ...
    (comp.databases.oracle.server)