It’s the gift that keeps on giving – no matter how many problems you find there are always more waiting to be found. It’s been some time since I last wrote about tables with more than 255 columns, and I said then that there was more to come. In the last article I described how adding a few columns to a table, or updating a trailing column in a way that made the table’s used column count exceed 255, could result in some strange row-splitting behaviour – in this article I’m going to look at a critical side effect of that behaviour.
We’ll start with a simple model and a question – I’ll create a table with a thousand rows of data, then I’ll add a few columns to that table and update the last column in every row and check the undo and redo generated by the update. Eventually I’m going to need a table with more than 255 columns so the script to create the table is rather long and I’ve posted it at the end of the article in case you want to experiment – in the following text I’ve omitted a couple of hundred lines each of column declarations and values.
rem
rem Script: wide_table_6.sql
rem Author: Jonathan Lewis
rem Dated: Feb 2018
rem
create table t1(
col000,
col001,
col002,
col003,
...
col199,
/*
col200,
col201,
col202,
col203,
col204,
col205,
col206,
col207,
col208,
col209,
*/
col210,
col220,
...
col247,
col248,
col249
)
as
with generator as (
select
rownum id
from dual
connect by
level <= 1e3 -- > comment to avoid WordPress format issue
)
select
lpad(000,10,'0'),
lpad(001,10,'0'),
lpad(002,10,'0'),
lpad(003,10,'0'),
...
lpad(247,10,'0'),
lpad(248,10,'0'),
lpad(249,10,'0')
from
generator v2
where
rownum <= 1e4 -- > comment to avoid WordPress format issue
;
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T1',
method_opt => 'for all columns size 1'
);
end;
/
alter table t1 add(
col250 varchar2(10),
col251 varchar2(10),
col252 varchar2(10),
col253 varchar2(10),
col254 varchar2(10),
col255 varchar2(10),
col256 varchar2(10),
col257 varchar2(10),
col258 varchar2(10),
col259 varchar2(10)
)
;
update t1 set col259 = lpad('259',10,'0');
commit;
The table I’ve created has columns named from col000 to col249 populated with a text matching the column’s numeric id – a total of 250 columns, except that for the first part of the test I’ve commented out the creation and population of 10 of those columns, giving me a total of 240 columns. Then I’ve added 10 more columns and updated the last of those 10. So, for the first part of the test, I’ve grown my table from 240 used columns to 250 used columns. Here are a few critical stats for that update from the session statistics view (v$sesstat joined to v$statname):
Name Value ---- ----- db block gets 1,023 consistent gets 555 db block changes 2,012 redo entries 907 redo size 262,692 undo change vector size 76,052 table scan rows gotten 1,002 table scan blocks gotten 501 HSC Heap Segment Block Changes 1,000
That’s pretty much the sort of thing we might expect. For a small update to a row it’s reasonable to see 250 – 300 bytes of redo of which about half is the redo for the undo. We can see that we’ve scanned 1,000 rows and made 2,000 block changes (one to the table block and one to an undo block for each row in the table). The table was 500 blocks of data (the avg_row_len is about 2640 bytes from 240 columns at 10 bytes + a length byte, which makes two rows per block with lots of spare space at the default 10 pctfree). You might been expecting the number of redo entries to be a close match to the number of rows but it’s a little short because the redo records for the first few updates would have been stored in private redo and turned into a single large redo entry.
So what do the stats look like when we start with 250 columns and grow to 260 columns, breaking through the dreaded 255 barrier ?
Name Value ---- ----- db block gets 9,503 consistent gets 1,894 db block changes 9,384 redo size 8,110,584 redo entries 5,778 undo change vector size 3,780,260 table scan rows gotten 1,002 table scan blocks gotten 501 HSC Heap Segment Block Changes 3,000
Such a simple change – with such a large impact.
The average undo is now nearly 3.5KB per row (and the rows were only about 2,860 bytes each anyway), the number of redo entries is up to 6 times the original, we’re averaging 3 “HSC Heap Segment Block Changes” per row instead of 1 and in total we’ve managed to do an extra 7,000 db block changes overall.
To get an idea of what was going on behind the scenes I dumped the redo log file. For the first part of the test most of the redo entries consisted of a pair of redo change vectors with OP codes 5.1 (modify undo block) and 11.5 (update row piece). The 5.1 corresponded to the undo needed to reverse out the effect of its paired 11.5 and both change vectors were pretty small.
For the second part of the test I found a frequently repeated sequence of three consecutive redo records of paired redo vectors: (5.1, 11.4), (5.1, 11.2) and (5.1, 11.6). Again each 5.1 corresponds to the undo needed to reverse out the effect of its associated 11.x, and in this case the three “table” (level 11) OP codes are, in order: “lock row piece”, “insert row piece”, “overwrite row piece”. These 3 pairs occured 1,000 times each, accounting for roughly 3,000 of the redo entries reported.
On top of this there were 2,500 redo records holding redo change vectors with op code 13.22 (“State change on level 1 bitmap block”), and a few hundred , with op code 13.21 (“Format page table block”) with just a few others bringing the total up to the 5,800 redo entries reported. (You might note that the last OP code is a clue that we added quite a lot of new blocks to the table as we executed the update – in fact the number of used table blocks grew by about 50%.)
We also had 500 redo change vectors of type 5.2 (“get undo header”). This number was significantly more than in the first part of the test because we had a lot more undo block changes (with bigger undo change vectors) which means we used a lot more undo blocks, and each time we move to a new undo block we update our transaction slot in the undo header with the current undo block address. I mentioned the pairing of 5.1 and 11.6 above – in fact 50% of those records recorded just two changes (5.1, 11.6) the other 50% recorded three changes (5.2, 5.1, 11.6) – in effect every other row update resulted in Oracle demanding a new undo block.
I’ll explain in a moment why we have a very large number of “State change on level 1 bitmap block”; first let’s examine the key steps of how Oracle is processing a single row update – the sequence of 11.4, 11.2, 11.6:
- 11.4: lock the head row piece – this seems to happen when the row is in (or going to turn into) multiple pieces; presumably because the piece that is going to change might not be the head piece. This is a tiny operation that generates undo and redo in the order of tens of bytes.
- 11.2: our code extends the row to 260 columns, which means Oracle has to split it into two pieces of 5 columns and 255 columns respectively – so one of those row-pieces has to cause an insert to take place. Inserting a row may require a lot of redo, of course, but the undo for a (table-only) insert is, again, tiny.
- 11.6: When Oracle has to split a wide row (>255 columns) it counts columns from the end, so the first row piece of our row is 5 columns and the second row piece (which is the one inserted by the 11.2 operation) is 255 columns. This means we have to overwrite the original row piece with a much shorter row piece. So we’re replacing 2,750 bytes (250 columns) with 55 bytes (5 columns), which means we have to write the contents of the “deleted” 250 columns to the undo tablespace – and that’s where most of the huge volume of undo comes from.
There are two extra points to note about the way Oracle handles the insert/overwrite steps. The length of our row exaggerates the effect, of course, but when we insert the ending 255 columns the block they go to is probably going to change from nearly empty to half full, or half full to full – which means its bitmap entry has to be updated; similarly when the initial 250 columns is overwritten with just 5 columns a huge amount of free space will appear in the block which, again, means that the block’s bitmap entry has to be updated. This gives us a good idea of why we see so many 13.22 (“L1 bitmap state change”)redo change vectors.
The second point is that the numbers still don’t add up. Allowing a couple of hundred bytes of undo per row for the lock row and insert row, then 2,750 plus a few more for the overwrite, I’ve accounted for about 3,000 bytes per row updated – which leaves me short by about 800 bytes per row. If I dump undo blocks I can see that the undo change vector for the overwrite is actually 3,628 bytes long rather than being “slightly over” the 2,750 for the before image of the overwritten row. Strangely I can see a couple of hundred bytes of what looks like damage in the row image, and there’s also a couple of hundred bytes of what looks like garbage (but probably isn’t) after the row image, but I’ve got no idea why there’s so much excess data in the record.
One day I (or someone else) may get around to looking very closely at why that particular undo record is a lot bigger than an observer might expect – but at least we can see that the space is there, and even if some of that volume could be made to disappear the problem of the massive undo relating to the overwrite and Oracle’s odd strategy of counting columns from the end is still going to be present, and there are probably some occasions when you need to know about this.
tl;dr
Once the number of columns in a table goes over 255 then a simple update to a “trailing” null column (i.e. one past the current end of the row) is likely to generate a much larger volume of undo and redo than you might expect. In particular the size of the undo record is likely to be equivalent to the volume of the last 255 columns of the table – and then a large fraction more.
The reason why this particular anomaly came to my attention is because a client had a problem upgrading an application that required them to add a few columns to a table and then update a couple of them. The size of the undo tablespace was 300 GB, the update (the only thing running at the time) should have added about 30 bytes to the length of each row, the update should have affected 250 million rows. The process crashed after a few hours “ORA-30036: unable to extend segment by %s in undo tablespace ‘%s'”. Even allowing for a “reasonable” overhead it seemed rather surprising that Oracle needed more than 1,200 bytes of undo space per row – but then I found the table was defined with 350 columns.
Solutions for this user: it’s an upgrade that’s allowed to take quite a long time, and the nature of the update is such that it would be possible to update in batches, committing after each batch. It would also be nice to review how the table was used to see if it could be rebuilt with a different column order to move all the unused columns to the end of the row – with a little luck the result table might find almost all the rows fitting into a single row piece, even after the upgrade.
Footnote
If you want to experiment, here’s the whole script to create the table, insert some rows, then add a few more columns and update one of them. It’s very long, and not in the least bit exciting, but it may save you a little typing time if you want to use it.
create table t1(
col000,
col001,
col002,
col003,
col004,
col005,
col006,
col007,
col008,
col009,
col010,
col011,
col012,
col013,
col014,
col015,
col016,
col017,
col018,
col019,
col020,
col021,
col022,
col023,
col024,
col025,
col026,
col027,
col028,
col029,
col030,
col031,
col032,
col033,
col034,
col035,
col036,
col037,
col038,
col039,
col040,
col041,
col042,
col043,
col044,
col045,
col046,
col047,
col048,
col049,
col050,
col051,
col052,
col053,
col054,
col055,
col056,
col057,
col058,
col059,
col060,
col061,
col062,
col063,
col064,
col065,
col066,
col067,
col068,
col069,
col070,
col071,
col072,
col073,
col074,
col075,
col076,
col077,
col078,
col079,
col080,
col081,
col082,
col083,
col084,
col085,
col086,
col087,
col088,
col089,
col090,
col091,
col092,
col093,
col094,
col095,
col096,
col097,
col098,
col099,
col100,
col101,
col102,
col103,
col104,
col105,
col106,
col107,
col108,
col109,
col110,
col111,
col112,
col113,
col114,
col115,
col116,
col117,
col118,
col119,
col120,
col121,
col122,
col123,
col124,
col125,
col126,
col127,
col128,
col129,
col130,
col131,
col132,
col133,
col134,
col135,
col136,
col137,
col138,
col139,
col140,
col141,
col142,
col143,
col144,
col145,
col146,
col147,
col148,
col149,
col150,
col151,
col152,
col153,
col154,
col155,
col156,
col157,
col158,
col159,
col160,
col161,
col162,
col163,
col164,
col165,
col166,
col167,
col168,
col169,
col170,
col171,
col172,
col173,
col174,
col175,
col176,
col177,
col178,
col179,
col180,
col181,
col182,
col183,
col184,
col185,
col186,
col187,
col188,
col189,
col190,
col191,
col192,
col193,
col194,
col195,
col196,
col197,
col198,
col199,
col200,
col201,
col202,
col203,
col204,
col205,
col206,
col207,
col208,
col209,
col210,
col211,
col212,
col213,
col214,
col215,
col216,
col217,
col218,
col219,
col220,
col221,
col222,
col223,
col224,
col225,
col226,
col227,
col228,
col229,
col230,
col231,
col232,
col233,
col234,
col235,
col236,
col237,
col238,
col239,
col240,
col241,
col242,
col243,
col244,
col245,
col246,
col247,
col248,
col249
)
as
with generator as (
select
rownum id
from dual
connect by
level <= 1e3 -- > comment to avoid WordPress format issue
)
select
lpad(000,10,'0'),
lpad(001,10,'0'),
lpad(002,10,'0'),
lpad(003,10,'0'),
lpad(004,10,'0'),
lpad(005,10,'0'),
lpad(006,10,'0'),
lpad(007,10,'0'),
lpad(008,10,'0'),
lpad(009,10,'0'),
lpad(010,10,'0'),
lpad(011,10,'0'),
lpad(012,10,'0'),
lpad(013,10,'0'),
lpad(014,10,'0'),
lpad(015,10,'0'),
lpad(016,10,'0'),
lpad(017,10,'0'),
lpad(018,10,'0'),
lpad(019,10,'0'),
lpad(020,10,'0'),
lpad(021,10,'0'),
lpad(022,10,'0'),
lpad(023,10,'0'),
lpad(024,10,'0'),
lpad(025,10,'0'),
lpad(026,10,'0'),
lpad(027,10,'0'),
lpad(028,10,'0'),
lpad(029,10,'0'),
lpad(030,10,'0'),
lpad(031,10,'0'),
lpad(032,10,'0'),
lpad(033,10,'0'),
lpad(034,10,'0'),
lpad(035,10,'0'),
lpad(036,10,'0'),
lpad(037,10,'0'),
lpad(038,10,'0'),
lpad(039,10,'0'),
lpad(040,10,'0'),
lpad(041,10,'0'),
lpad(042,10,'0'),
lpad(043,10,'0'),
lpad(044,10,'0'),
lpad(045,10,'0'),
lpad(046,10,'0'),
lpad(047,10,'0'),
lpad(048,10,'0'),
lpad(049,10,'0'),
lpad(050,10,'0'),
lpad(051,10,'0'),
lpad(052,10,'0'),
lpad(053,10,'0'),
lpad(054,10,'0'),
lpad(055,10,'0'),
lpad(056,10,'0'),
lpad(057,10,'0'),
lpad(058,10,'0'),
lpad(059,10,'0'),
lpad(060,10,'0'),
lpad(061,10,'0'),
lpad(062,10,'0'),
lpad(063,10,'0'),
lpad(064,10,'0'),
lpad(065,10,'0'),
lpad(066,10,'0'),
lpad(067,10,'0'),
lpad(068,10,'0'),
lpad(069,10,'0'),
lpad(070,10,'0'),
lpad(071,10,'0'),
lpad(072,10,'0'),
lpad(073,10,'0'),
lpad(074,10,'0'),
lpad(075,10,'0'),
lpad(076,10,'0'),
lpad(077,10,'0'),
lpad(078,10,'0'),
lpad(079,10,'0'),
lpad(080,10,'0'),
lpad(081,10,'0'),
lpad(082,10,'0'),
lpad(083,10,'0'),
lpad(084,10,'0'),
lpad(085,10,'0'),
lpad(086,10,'0'),
lpad(087,10,'0'),
lpad(088,10,'0'),
lpad(089,10,'0'),
lpad(090,10,'0'),
lpad(091,10,'0'),
lpad(092,10,'0'),
lpad(093,10,'0'),
lpad(094,10,'0'),
lpad(095,10,'0'),
lpad(096,10,'0'),
lpad(097,10,'0'),
lpad(098,10,'0'),
lpad(099,10,'0'),
lpad(100,10,'0'),
lpad(101,10,'0'),
lpad(102,10,'0'),
lpad(103,10,'0'),
lpad(104,10,'0'),
lpad(105,10,'0'),
lpad(106,10,'0'),
lpad(107,10,'0'),
lpad(108,10,'0'),
lpad(109,10,'0'),
lpad(110,10,'0'),
lpad(111,10,'0'),
lpad(112,10,'0'),
lpad(113,10,'0'),
lpad(114,10,'0'),
lpad(115,10,'0'),
lpad(116,10,'0'),
lpad(117,10,'0'),
lpad(118,10,'0'),
lpad(119,10,'0'),
lpad(120,10,'0'),
lpad(121,10,'0'),
lpad(122,10,'0'),
lpad(123,10,'0'),
lpad(124,10,'0'),
lpad(125,10,'0'),
lpad(126,10,'0'),
lpad(127,10,'0'),
lpad(128,10,'0'),
lpad(129,10,'0'),
lpad(130,10,'0'),
lpad(131,10,'0'),
lpad(132,10,'0'),
lpad(133,10,'0'),
lpad(134,10,'0'),
lpad(135,10,'0'),
lpad(136,10,'0'),
lpad(137,10,'0'),
lpad(138,10,'0'),
lpad(139,10,'0'),
lpad(140,10,'0'),
lpad(141,10,'0'),
lpad(142,10,'0'),
lpad(143,10,'0'),
lpad(144,10,'0'),
lpad(145,10,'0'),
lpad(146,10,'0'),
lpad(147,10,'0'),
lpad(148,10,'0'),
lpad(149,10,'0'),
lpad(150,10,'0'),
lpad(151,10,'0'),
lpad(152,10,'0'),
lpad(153,10,'0'),
lpad(154,10,'0'),
lpad(155,10,'0'),
lpad(156,10,'0'),
lpad(157,10,'0'),
lpad(158,10,'0'),
lpad(159,10,'0'),
lpad(160,10,'0'),
lpad(161,10,'0'),
lpad(162,10,'0'),
lpad(163,10,'0'),
lpad(164,10,'0'),
lpad(165,10,'0'),
lpad(166,10,'0'),
lpad(167,10,'0'),
lpad(168,10,'0'),
lpad(169,10,'0'),
lpad(170,10,'0'),
lpad(171,10,'0'),
lpad(172,10,'0'),
lpad(173,10,'0'),
lpad(174,10,'0'),
lpad(175,10,'0'),
lpad(176,10,'0'),
lpad(177,10,'0'),
lpad(178,10,'0'),
lpad(179,10,'0'),
lpad(180,10,'0'),
lpad(181,10,'0'),
lpad(182,10,'0'),
lpad(183,10,'0'),
lpad(184,10,'0'),
lpad(185,10,'0'),
lpad(186,10,'0'),
lpad(187,10,'0'),
lpad(188,10,'0'),
lpad(189,10,'0'),
lpad(190,10,'0'),
lpad(191,10,'0'),
lpad(192,10,'0'),
lpad(193,10,'0'),
lpad(194,10,'0'),
lpad(195,10,'0'),
lpad(196,10,'0'),
lpad(197,10,'0'),
lpad(198,10,'0'),
lpad(199,10,'0'),
lpad(200,10,'0'),
lpad(201,10,'0'),
lpad(202,10,'0'),
lpad(203,10,'0'),
lpad(204,10,'0'),
lpad(205,10,'0'),
lpad(206,10,'0'),
lpad(207,10,'0'),
lpad(208,10,'0'),
lpad(209,10,'0'),
lpad(210,10,'0'),
lpad(211,10,'0'),
lpad(212,10,'0'),
lpad(213,10,'0'),
lpad(214,10,'0'),
lpad(215,10,'0'),
lpad(216,10,'0'),
lpad(217,10,'0'),
lpad(218,10,'0'),
lpad(219,10,'0'),
lpad(220,10,'0'),
lpad(221,10,'0'),
lpad(222,10,'0'),
lpad(223,10,'0'),
lpad(224,10,'0'),
lpad(225,10,'0'),
lpad(226,10,'0'),
lpad(227,10,'0'),
lpad(228,10,'0'),
lpad(229,10,'0'),
lpad(230,10,'0'),
lpad(231,10,'0'),
lpad(232,10,'0'),
lpad(233,10,'0'),
lpad(234,10,'0'),
lpad(235,10,'0'),
lpad(236,10,'0'),
lpad(237,10,'0'),
lpad(238,10,'0'),
lpad(239,10,'0'),
lpad(240,10,'0'),
lpad(241,10,'0'),
lpad(242,10,'0'),
lpad(243,10,'0'),
lpad(244,10,'0'),
lpad(245,10,'0'),
lpad(246,10,'0'),
lpad(247,10,'0'),
lpad(248,10,'0'),
lpad(249,10,'0')
from
generator v2
where
rownum <= 1e4 -- > comment to avoid WordPress format issue
;
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T1',
method_opt => 'for all columns size 1'
);
end;
/
select
avg_row_len, num_rows, blocks,
num_rows / trunc(8000/avg_row_len) estimated_blocks
from
user_tables
where
table_name = 'T1'
;
prompt =================
prompt Add a few columns
prompt =================
alter table t1 add(
col250 varchar2(10),
col251 varchar2(10),
col252 varchar2(10),
col253 varchar2(10),
col254 varchar2(10),
col255 varchar2(10),
col256 varchar2(10),
col257 varchar2(10),
col258 varchar2(10),
col259 varchar2(10)
)
;
-- alter system switch logfile;
update t1 set col259 = lpad('259',10,'0');
commit;
-- execute dump_log
P.S. if you do investigate and solve the question of the excess space in the undo record, and the odd content in the row “before image” then do let me know. (Hint: part of the excess may be a “null columns” map – but that still leaves plenty to account for.)
Oracle Scratchpad 
Did you enable dumping of supplemental log info ?
alter session set events ‘1354 trace name context forever, level 32768’;
alter session set events ‘1348 trace name context forever, level 1032’;
There might be extra supplemental log info in your undo vectors …
Comment by Kurt Van Meerbeeck — March 6, 2018 @ 1:35 pm GMT Mar 6,2018 |
Kurt,
Thanks for the suggestion, but I didn’t get anything in the trace file by setting these events.
Comment by Jonathan Lewis — March 6, 2018 @ 4:19 pm GMT Mar 6,2018 |
[…] Good question! The whole undo/redo infrastructure in Oracle is probably the most astounding technological achievement in the entire code base – so would you test it to see that it was working properly and if you could break it ? Probably not – although if you were about to recreate your undo tablespace with a 32KB block size you might test to see if the change would produce any surprise side-effects); or you might wonder if anything funny could happen to the redo generation if you created all your varchar2() columns as 4000 bytes “just in case”. or possibly you’d check for undo or redo anomalies if you were told to create a table with more than 255 columns. […]
Pingback by Assumptions | Oracle Scratchpad — July 9, 2019 @ 7:55 pm BST Jul 9,2019 |
Great article!
I have reproduced this scenario in my lab and dumped the contents of the Redo Log using OpenLogReplicator on a 11g database. My 5.1 UNDO vector is about 3.7k long. The vector has 255 fields elements.
1. element lengths: 255 x 2 + 2 = 512 bytes
2. various headers – more or less 200 bytes
3. 250 rows x 12 bytes (10 chars aligned to 4-bytes boundary) = 3000 bytes
Sums to 3,7k.
Comment by Adam Leszczyński — January 29, 2020 @ 7:18 pm GMT Jan 29,2020 |
Adams,
Thank you very much for that analysis.
Looking at the original numbers, my missing (roughly) 800 bytes and your description I’d guess that what I hadn’t catered for was probably the 200 bytes of “various headers” bit and the 512 bytes of element lengths.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — January 30, 2020 @ 1:00 pm GMT Jan 30,2020 |
What those around 200 bytes of headers are you can display using logdump mode of OpenLogReplicator. The header can probably depend on the Oracle version (for example for 11g redo header is 24 bytes, for 12+ is 32 bytes, etc.).
Comment by Adam Leszczyński — February 11, 2020 @ 10:43 pm GMT Feb 11,2020 |
[…] there’s still more to come – but it will have to wait a little […]
Pingback by 255 Again! | Oracle Scratchpad — December 1, 2021 @ 4:26 pm GMT Dec 1,2021 |
[…] 255 columns (Feb 2018) – huge increase in undo and redo with wide tables […]
Pingback by 255 column catalogue | Oracle Scratchpad — January 25, 2022 @ 12:17 pm GMT Jan 25,2022 |