Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: About rollback consumption

Re: About rollback consumption

From: Howard J. Rogers <howardjr_at_www.com>
Date: Fri, 29 Sep 2000 22:21:25 +1000
Message-ID: <39d47aaf@news.iprimus.com.au>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:970219615.20478.0.nnrp-08.9e984b29_at_news.demon.co.uk...
>
> Howard,
>
> I was pretty confident you knew what you meant,
> and the point you were trying to make. However
> if you read what you said in the post, it doesn't
> actually point out that the major space wastage
> is due to the combination of short, concurrent,
> transactions, not to the single update/insert...
> per se.
>
> In fact, having done a little research, I now
> find that we are both out of date anyway as
> far as 8.1.5 is concerned. A rollback block can
> be re-acquired after a commit, and Oracle will
> even 'step backwards' through the rollback
> block list to do so, irrespective of the order
> in which multiple session commit etc.
>

That's new, and something I genuinely wasn't aware of -so thanks *a lot* for that. Time I hunkered down with a portable and an expendable database to do some research of my own, I think..!!

> This means that an insert will NOT use a
> whole rollback block even when there are
> multiple concurrent transactions going
> on - it will simply use the necessary
> rollback records from that block, and it,
> or another process, can use further rollback
> records from the same block after the first
> transaction commits.
>

When rollback segments shrink to optimal as they cross the extent boundary, are you saying that they can now drop extents 'behind' the direction of travel?

Regards
HJR
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Howard J. Rogers wrote in message <39d3a88c_at_news.iprimus.com.au>...
> >Hi Jonathan,
> >
> >I'm not sure we disagree (or that I was wrong!).
> >
> >I posted earlier that two *concurrent* or pending transactions couldn't
> >share the same block (at least, that's what I was *trying* to post!)
> >
> >Your listing here shows you doing a transaction, committing, and then
 doing
> >a *second* transaction.
> >
> >I'm not surprised you've re-used the one block, since your first
 transaction
> >isn't reserving it -because you've just finished it with that commit.
> >
> >Let me know if I'm missing something.
> >
> >Regards
> >HJR
> >--
>
>--------------------------------------------------------------------------
> >Opinions expressed are my own, and not those of Oracle Corporation
> >Oracle DBA Resources: http://www.geocities.com/howardjr2000
>
>--------------------------------------------------------------------------
> >
> >
> >
> >"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> >news:970145689.20485.0.nnrp-07.9e984b29_at_news.demon.co.uk...
> >>
> >> Howard,
> >>
> >> Your comments about rollback block usage aren't
> >> correct (at least in 8.1.5), although it is possible
> >> to produce experiments that make them look correct.
> >>
> >> Consider the following screen dump - which I contrived
> >> by reducing a database to having a single non-system
> >> rollback segment. The camera did not cut away whilst
> >> this screen dump was going on ...
> >>
> >>
> >> SQL> insert into junk values (1);
> >>
> >> 1 row created.
> >>
> >> SQL> select start_ubafil, start_ubablk, start_ubarec from
 v$transaction;
> >>
> >> START_UBAFIL START_UBABLK START_UBAREC
> >> ------------ ------------ ------------
> >> 3 116 16
> >>
> >> SQL> commit;
> >>
> >> Commit complete.
> >>
> >> SQL> insert into junk values (1);
> >>
> >> 1 row created.
> >>
> >> SQL> select start_ubafil, start_ubablk, start_ubarec from
 v$transaction;
> >>
> >> START_UBAFIL START_UBABLK START_UBAREC
> >> ------------ ------------ ------------
> >> 3 116 17
> >>
> >> SQL> commit;
> >>
> >> Commit complete.
> >>
> >>
> >> As you can see, there are likely to be multiple
> >> rollback records available within a rollback block,
> >> and it is possible for one transaction to end leaving
> >> space in a rollback block that another transaction
> >> can acquire. I have two consecutive transactions
> >> which have used the same rollback block.
> >>
> >> However, you may find that two processes cannot use
> >> the same rollback block simultaneously (I haven't
> >> actually checked that in 8.1 - it used to be true),
> >> and block usage in rollback segments always moves
> >> on, so if you tried to repeat the test on a system where
> >> there were always twice as many active transactions
> >> as rollback segments, then you would get results
> >> that suggested that any transaction had to use a
> >> whole rollback block.
> >>
> >>
> >> --
> >>
> >> Jonathan Lewis
> >> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >>
> >> Howard J. Rogers wrote in message <39d324af$1_at_news.iprimus.com.au>...
> >>
> >> >> INSERT (?)
> >> >> DELETE (?)
> >> >> UPDATE (?)
> >> >>
> >> >> I write them in a supposed growing consumption order. Can anyone
 confirm
> >> >> that ?
> >> >>
> >> >
> >> >Also not true. A rollback block stores the prior image of the data.
 The
> >> >before image of an insert is the rowid where the insert is to take
 place.
> >> >Which is trivial in size -but it would still require an entire
 rollback
> >> >block.
> >> >
> >>
> >>
> >>
> >
> >
>
>
>
>
Received on Fri Sep 29 2000 - 07:21:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US