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

On Aug 28, 8:42 pm, m..._at_pixar.com wrote:
> DA Morgan <damor..._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

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 Received on Tue Aug 28 2007 - 14:16:01 CDT