Re: fetch across commit

These are all excellent posts. I believe we are going to sit down with the developers again and try to see what we can do to break this pretty big db down into smaller datamarts we can spin off for our marketing folks.

The 2 original tables the cursors fetch from are actually into the 200's of millions of rows. His committed table has over 20+M rows. The developer wrote the code
pretty much as I summarized in the asktom post. He runs the package in parallel in about a dozen sqlplus sessions and wondered why he hit the 1555. He decided to promptly blame the rollback segments, which showed ample room for growth and transactional capacity.

As soon as I saw the code, the commit inside the double cursor looked a tad suspicious. Most of the fetch across commit data from Oracle (even google) show
a same table cursor and commit, which doesn't really underline the core issue here.

Anyway, thanks to everyone for the input. I've added the ideas to the original suggestions I had put on the table to reevaluate how the transactions were being
done in the code.

On Fri, 3 Dec 2004 16:43:43 -0800 (PST), Steve Rospo <srospo_at_watchmark.com> wrote:
>
> [Appolgies if this gets double posted, I got an "overquote" bounce. ]
>
> I haven't tried a "where current of" or the "order by" workarounds, but I
> usually just push the data into some sort of PL/SQL collection. It's got
> the same transactional semantics with no (real) risk of 1555s with only a
> minor code tweak. Even if there wasn't a commit in the loop, this might
> be a good technique to prevent the RBSs from getting enormous if the inner
> part of the loop took an *extremely* long time.
>
> If you do a bulk collect, you get the added benefit of doing a single
> round trip, rather than a row by row fetch. (I think 9i or 10g does a
> transparent optimization here, turning your row by row fetch into a bulk
> under the covers)
>
> S-
>
>
>
>
> >
> > On Tue, 30 Nov 2004, Jared Still wrote:
> >
> > > Hi all,
> > >
> > > Have any of you considered using a 'where current of' update
> > > cursor to work around this?
> > >
> > > I've used it in the past to avoid both ora-1555 and 'fetch across commit'.
> > >
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l