Re: Users reading from rollback segments

Jeremiah :

    What I meant was that the delayed_logging_block_cleanouts parameter (=FALSE) will make the next reader of the block to cleanout that block.

    Now, if this parameter's value is TRUE (default in Oracle 7 and 8.0) the next reader will NOT clean out the block (it will read the rollback segment and generate the appropiate block image but it will not clean the block out).

    The delayed "logging" block clean out feature delays the redo for the cleanout blocks until it could be logged in combination with another redo for another change to the block.

    So if you have this parameter set in TRUE the block clean out will be made only when you'll make another change to these blocks.

    According to what Glenn was saying:
> > "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)."

I think that there's a way you can be sure that noone will need to read some RBS blocks for a cleanout operation.

Greetings
Diego Cutrone

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, February 27, 2002 4:38 PM

> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Diego Cutrone
  INET: dcutrone_at_afip.gov.ar

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).