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

DA Morgan <damorgan_at_psoug.org> 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