Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: what's a "reasonable" length of time to keep a cursor open?

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

From: <mh_at_pixar.com>
Date: Tue, 28 Aug 2007 17:42:44 GMT
Message-ID: <o8ZAi.501$Sd4.115@nlpi061.nbdc.sbc.com>


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
Received on Tue Aug 28 2007 - 12:42:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US