| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Row Chaining/Migration & UNDO
Where were you picking up the volume of undo generated ?
v$sysstat
v$undostat
v$transaction
v$rollstat
How many extra blocks were allocated to the table as you did the update.
Did you really do the update as a single statement (I know this is what you said in the post, but sometimes people don't quite say what they meant) or was it a pl/sql loop with frequent commits.
What is your block size, as this might be significant.
20GB of undo seems a little over the top, but could be in the right ball-park.
18M rows at 23GB is about 1.3K per row, which would be about 6 rows per block at 8K.
If you update most of the rows in the table, then the first row in each block will migrate to a new block, leaving enough space for the other five to be updated in place.
A migration is roughly equal to an insert (of 1.3K in your case) in the new location followed by a delete from the old location. On a delete, the old row will be copied into the undo, so you will have 3M * 1.3K of undo generated: which is 4GB. (As I said, the right ball-park).
On top of this, every undo record has an overhead of about 100 bytes - and you get 2 records for each migration, plus one for the non-migratory updates:
15M + 2 x 3M * 100 = 2.1GB.
Still too small, but now we're up to 6GB.
If you were actually thinking REDO when you said UNDO, then you can double this, as the 6GB of UNDO is protected by REDO, and the 4GB of new inserts are also protected by redo, which also carries an overhead per record.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February ____UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "MAK" <maks70_at_comcast.net> wrote in message news:b7178504.0402021334.18a29c39_at_posting.google.com...Received on Fri Feb 06 2004 - 06:57:19 CST
> Hello Experts!!
>
> I am using 9.2.0.3 database on AIX and using AUM(Automatic UNDO... )
>
> I have table with 18M rows and its size is 23GB. Most blocks allocated
> to this table are probably tightly packed. i.e. no room for UPDATEs to
> column values. ( longer than what currently exist ).
>
> I ran a single column ( larger % of values were NULL ) update
> statement. This update statement ran for long long time and generated
> 20GB of undo. This was the only statement running on the database.
>
> I looked at the statspack report. Most waits were 'db file %' reads.
> There was high count for "table fetch continued row". Which lead me
> to believe that there was row chaining going on..
>
> Now question is, Should oracle generate this much UNDO with just
> single column value being updated?
>
> As I understand row chaining, If oracle does not find a space for the
> updated column in the existing block, it removes the column value from
> that block and copies value in the new block and put the pointer in
> the old block. So in this case , Oracle should have generated undo for
> two transactions 1) Update column value from NULL to 50 character
> update & 2) Update of pointer in old block & copy of old column value
> to new block ) and may be some other system related update. In any
> case this should not have generated 20GB of undo space.
>
>
> Can some body explain why its generating this huge amount of UNDO?
>
> Thanks
> Max
 |
 |