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 few hundred lines of column declarations and values.
rem
rem Script: wide_table_5.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.)
