Re: Rollback question

From: Alan <not.me_at_rcn.com>
Date: Mon, 24 Jan 2005 19:01:37 -0500
Message-ID: <35lgjkF4i0bgnU1@individual.net>

"Syltrem" <syltremzulu_at_videotron.ca> wrote in message news:keaJd.2431$df.102804_at_tor-nn1.netcom.ca...
> "Alan" <not.me_at_rcn.com> a écrit dans le message de
> news:35kp0pF4mq1e6U1_at_individual.net...
> >
> > "Syltrem" <syltremzulu_at_videotron.ca> wrote in message
> > news:IF9Jd.2429$df.102832_at_tor-nn1.netcom.ca...
> > > Hi
> > >
> > > I had to kill a user that had about 5.5 GB used up in the rollback
> segment
> > > (as per v$transaction).
> > > I was expecting to follow the progress of the rollback with
> v$transaction,
> > > but I cannot see the transaction anymore.
> > >
> > > I can't see how such a transaction could have rolled back in less than
5
> > > seconds.
> > >
> > > Any hints appreciated.
> > >
> > > --
> > > Syltrem
> > >
> > > OpenVMS 7.3-1 + Oracle 8.1.7.4
> > > http://pages.infinit.net/syltrem (OpenVMS related web site, en
français)
> > > ---zulu is not in my email address---
> > >
> > >
> >
> > Perhaps it committed before you could kill it.
> >

>

> I don't think so.
> Queries against the data updated by this transaction, are resolved by
> reading the rollback segments, not the data files where the tables reside.
> And with so many users accessing those same segments the system is
terribly
> slow.

>

> Waits with "db file sequential read" on the rollback segments' datafiles.

>

> >

>
>

Run this several times a few seconds apart next time:

select used_ublk from v$transaction where addr=(select TADDR from v$session where sid='#'); /* replace # with the appropriate SID. */

If the number is decreasing, then rollback is occurring. If the number is increasing, data is being added to the rollback blocks. You can also time the results and estimate how long it will take to complete the rollback. Very handy. Received on Mon Jan 24 2005 - 18:01:37 CST