Oracle FAQ Your Portal to the Oracle Knowledge Grid

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: DSB <>
Date: Tue, 28 Aug 2007 12:16:01 -0700
Message-ID: <>

On Aug 28, 8:42 pm, wrote:
> DA Morgan <> 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

Original text of this message