Re: Confused about Queries and Rollback Segments!

In article <s4boif9pcv66_at_corp.supernews.com>,   "Jonathan E Miller" <jmiller_at_fnol.net> wrote:
> When does a simple query acquire a rollback segment? Does it always
need one
> or only when there are other transactions ocurring that may be
updating the
> same rows that the query is returning? We have frequentlly run into
the
> snapshot too old problem seemingly when the only thing running is a
large
> query.
>
> Thanks
> Jonathan
>

A query does not acquire a rollback segment (ignore select for update) but it will access rbs blocks to read rows of data that have been changed since the time that the select statement started. That is all rows your query returns will appear as they existed at the time your query started. All changes that were made to any block your query access will be undone (rolled back) as far as your query is concerned. Oracle obtains the original time specific rows from the rbs segments. It the copy of the data you need to query is no longer in the rbs segments you get the 'snapshot too old message'. Oracle does not know what data someone will query at a future point. When someone does an update the original row data is copied into an available rbs segment block. When the updater issues a commit Oracle no longer needs this data and once the rbs segment is full it loops around to the beginning and starts to reuse the rbs segment (if there are no current transactions in the segment). I have a copy of an Oracle white paper on snapshot tool old errors that I would be glad to email or post. I did not post it because it is several screens long and this is a common topic on the board.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 02 1999 - 12:51:33 CST