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: Rollback extents

Re: Rollback extents

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Thu, 27 Jun 2002 18:45:06 +1000
Message-ID: <afejc7$u9d$1@lust.ihug.co.nz>

"nilanjan" <nilanjan_sarkar_at_hotmail.com> wrote in message news:6c8b1f5.0206261454.760b80a7_at_posting.google.com...
> Per the math involved here, the # extents multipled by extent size in
> yr case is 500*320K=160Megabytes ...which is wayyy smaller than your
> single load of 3-8 Gigabytes !!!
>
> Would be interesting to find if NOLOGGING resolves yr problem.
>
> BTW: thinking about the rollback stuff, basic question, any comments
> on why would Oracle need rollback ( not REDO) for an INSERT or SELECT
> ??

Selects don't *generate* rollback, but they need to use it because you can't ever see data that was modified after the time your report started. Therefore the blocks you are selecting from (or, rather more accurately, copies of those blocks) may need to be rolled back to a point consistent with the time your select started.

For example: you start a large report at 10.00am. It chugs through huge tables, joining them, aggregating things, and so on. At 10.02am, I update one of the rows in one of the tables that your report will eventually get to, but hasn't got to yet. At 10.03am, I commit that update. No matter -because it was committed after your report started, your report is not allowed to see the updated data. You must see the data as it was, consistent at 10.00am.

So at 10.15am, your report finally arrives at the block of data that I'd previously updated. Your server process notices that the block contains data from a time after 10.00am, so it can't read it directly. It therefore copies the block into a free buffer, and then uses rollback stored in any number of rollback segments to get the copy back to how it looked at 10.00am. Once it's done that, your report uses that version of the block (what is known as the 'read consistent' block), not the real block (otherwise known as the current block).

You've just experienced a 'consistent get'. And without rollback, it wouldn't have happened. Therefore, selects need rollback.

As for inserts: it goes without saying that you might want to reverse a mistaken insert. We therefore also need to generate rollback for inserts. But it's also the case that exactly the same scenario as above applies to me doing inserts just as much as to me doing updates. Your select will need the rollback info for an insert as much as it needs any other piece of rollback if it is to successfully generate a read consistent image of the data block as it was at 10.00am.

Regards
HJR Received on Thu Jun 27 2002 - 03:45:06 CDT

Original text of this message

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