RE: Users reading from rollback segments

John,

I was thinking along the same lines, but then its not the sure shot way to do it. I was also wondering if one could instead do it with x$bh and dba_extents.

Just a thought.

Thanks
Raj

                                                                                                                     
                    John Kanagaraj                                                                                   
                    <john.kanagara        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>        
                    j_at_hds.com>            cc:                                                                        
                    Sent by:              Subject:     RE: Users reading from rollback segments                      
                    root_at_fatcity.c                                                                                   
                    om                                                                                               
                                                                                                                     
                                                                                                                     
                    February 26,                                                                                     
                    2002 06:09 PM                                                                                    
                    Please respond                                                                                   
                    to ORACLE-L                                                                                      
                                                                                                                     
                                                                                                                     

Glenn,

An approximation of what you require *may* be worked out this way:

1. Snoop v$session_wait for all events that start with 'db file%' and see if P1 is in a list of data files that belongs to the RBS tablespace(s). If you do see sessions that have P1s indicating RBS files, then you *may* be reading Rollback. It is not necessarily on the older entries, but see below:
2. Track the start time of that Query from the SID - using V$SESSION and V$PROCESS. If the start time is reasonably old, then you *may* have an issue, as ORA-01555 errors can be expected for those queries that started *before* the entry you are going to zap via shrink was made.

I have successfully used P1 and P2 to indicate the progress of a long running query by working out the segments being accessed and matching that with an EXPLAIN PLAN. This is especially useful if multiple table joins are involved.

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Grace - Getting something we don't deserve Mercy - NOT getting something we deserve

Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Jeremiah Wilton [mailto:jwilton_at_speakeasy.net]
> Sent: Tuesday, February 26, 2002 2:36 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Users reading from rollback segments
>
>
> 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: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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: 
  INET: Rajesh.Rao_at_jpmchase.com

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