Re: About rollback consumption

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.

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.

--

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.

>> >

>>

>>

>>

>

>