The delayed_logging_block_cleanouts parameter does not force or
suppress cleanouts. It just makes any cleanouts that do occur get
logged as redo entries.
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
On Wed, 27 Feb 2002, Diego Cutrone wrote:
> Hi Glenn and list:
>
> "As you mentioned, even if noone is reading from rollback at the time
> all transactions commit, a query may be executing which will access rollback
> later in its current run (which started prior to the commit)."
>
> If you're under Oracle 7 or 8.0, I think that you could set
> "delayed_logging_block_cleanouts=FALSE" (to make sure that the next reader
> will do the cleanout), and execute a FTS on the table after the commit, this
> would make all the block cleanouts for you. This way you can be sure that
> noone will need to read this RBS blocks for a cleanout operation.
>
> ----- Original Message -----
> > Thank you all. I agree now that there is no way to tell if someone will
> need the rollback segment data, EVEN if no queries are running when all
> transactions are committed (due to delayed block cleanout - I had forgotten
> about this!). As you mentioned, even if noone is reading from rollback at
> the time all transactions commit, a query may be executing which will access
> rollback later in its current run (which started prior to the commit).
> >
> > This was very helpful information and an eductional discussion. I'll post
> my rollback queries later today...
> >
> > > -----Original Message-----
> > > From: Paul Baumgartel [mailto:treegarden_at_yahoo.com]
> > >
> > > Jeremiah is correct. In addition, rollback segments are read as part
> > > of the delayed block cleanout process, and it's not possible
> > > to predict
> > > that, either.
> > >
> > >
> > > --- Jeremiah Wilton <jwilton_at_speakeasy.net> wrote:
> > > > People also obtain read consistency data from the rollback segments
> > > > after transactions have committed. If a query began before someone
> > > > else's transaction committed, but continues reading, then needs the
> > > > reconstruct the data from before the commit, in needs rollback data
> > > > that is both committed and impossible to predict.
> > > >
> > > > I suppose if you could determine that the age of all undo entries in
> > > > the portion of RBS that you will obliterate through shrinking are
> > > > older than any query currently running in the database, then you
> > > > could
> > > > be sure that the shrink will not cause an ORA-01555.
> > > >
> > > > But the flaw in your logic is believing that once
> > > committed, rollback
> > > > entries will not be needed for read consistency. They very
> > > well may.
> > > >
> > > > Because a query doesn't know what rollback entries it may need
> > > > further
> > > > down the road, you can't predict if your shrink will obliterate undo
> > > > entries that a long-running query might need in the future.
> > > You keep
> > > > asking if we can tell who is reading the rollback segments. The
> > > > answer is that it doesn't matter. What you really need to ask is if
> > > > we can tell who will need to read the rollback segments sometime
> > > > soon.
> > > > And you can't.
> > > >
> > > > --
> > > > Jeremiah Wilton
> > > > http://www.speakeasy.net/~jwilton
> > > >
> > > > On Tue, 26 Feb 2002, Glenn Travis wrote:
> > > >
> > > > > Hmmm. I think I CAN predict FUTURE needs of the rollback segments.
> > > > > If there are transactions using the rollback space, users MAY need
> > > > > it. If there are no transactions, then they won't. If I
> > > were able
> > > > > to know who is reading from rollback, I would know if shrinking
> > > > > might cause ORA-01555.
> > > > >
> > > > > Tell me if I'm off on this...
> > > > >
> > > > > Users will not read from the rollback segment unless they need
> > > > > read-consistent data due to an open transaction against the data
> > > > > they are looking for (thus reading the redo or undo info from
> > > > > rollback). Otherwise they read from the data segments (committed
> > > > > data).
> > > > >
> > > > > Oracle will not shrink the rollback segment if it contains open
> > > > > transactions.
> > > > >
> > > > > So, if there are no users reading from rollback and I issue a
> > > > > 'shrink' command, and it works, then the transactions are complete
> > > > > and any user coming in after that will read from the data
> > > segments.
> > > > >
> > > > > If there are no users reading from rollback and I issue a 'shrink'
> > > > > command, and it DOES NOT work, then the transactions are NOT
> > > > > complete and any user coming in after that will read from the
> > > > > rollback segments (the data is still there).
> > > > >
> > > > > If there ARE users reading from rollback and I issue a 'shrink'
> > > > > command, and it works, then users run the risk of getting
> > > ORA-01555
> > > > > (the data MAY be gone). Which is exactly why I asked my original
> > > > > question ("How do I identify READERS of the rollback
> > > segments?") :)
> > > >
> > > >
> > > > > > -----Original Message-----
> > > > > > From: Jeremiah Wilton [mailto:jwilton_at_speakeasy.net]
> > > > > >
> > > > > > Since you cannot predict who might need to generate consistent
> > > > reads
> > > > > > from the RBS in the FUTURE, you cannot predict if you will cause
> > > > > > ORA-01555 or not by shrinking.
> > > > > >
> > > > > > Your best bet is to get rid of people bloating up RBSs by
> > > > limiting
> > > > > > their growth, and enforcing the use of smaller transactions.
> > > > That way
> > > > > > you won't have to shrink so much.
> > > > > >
> > > > > > --
> > > > > > Jeremiah Wilton
> > > > > > http://www.speakeasy.net/~jwilton
> > > > > >
> > > > > > On Tue, 26 Feb 2002, Glenn Travis wrote:
> > > > > >
> > > > > > > Is there a way to tell if anyone is reading from the rollback
> > > > > > > segments?
> > > > > > >
> > > > > > > I would like to manually issue 'alter rollback segment XXX
> > > > shrink;',
> > > > > > > but do not want to do so if there are users reading read
> > > > consistent
> > > > > > > data from the rollback space (thus giving them the ORA-01555
> > > > error).
> > > > > > >
> > > > > > > Is there a way to check if the rollback segment is in use
> > > > first?
> > > > > > >
> > > > > > > Can I try to take it offline? Will it fail if there
> > > is someone
> > > > > > > reading from it?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeremiah Wilton
INET: jwilton_at_speakeasy.net
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Feb 27 2002 - 13:38:24 CST
