At present this is just a place holder to remind me to finish commenting on (and correcting) a mistake I made when I wrote a note about the number of bytes of data you could get into an “enable storage in row” LOB before it had to be stored out of row.
In a much earlier article discussing multi-byte, variable length character sets I showed that CLOBs stored in-row are likely to take far more space than the equivalent varchar2() columns, and that securefile CLOBs would use 6 bytes less than Basicfile CLOBs. These claims are (or were) true.
In another article I then discussed the number of bytes at which a LOB that was defined as “enable storage in row” would go “out of row” and showed some results that appeared to demonstrate that the length varied with version of Oracle. This was wrong. In my testing I had not dumped any datablocks to check their actual content, instead I had relied on the (undocumented) sys_op_opnsize() function and been fooled by the results.
This note is intended to correct my error. I’ll start with a test on a BLOB, enabling storage in row, and create some data that will be close to the breakpoint for the value going out of row:
rem
rem Script: blob_in_row.sql
rem Author: Jonathan Lewis
rem Dated: Jan 2023
rem
rem Last tested
rem 19.11.0.0
rem 11.2.0.4
rem
create table t1b (
id number(3,0),
pad varchar2(2000),
b1 blob
)
lob(b1) store as
basicfile
basblob (
enable storage in row
)
/
create table t1s (
id number(3,0),
padding varchar2(2000),
b1 blob
)
lob(b1) store as
securefile
secblob (
enable storage in row
)
/
alter table t1b add constraint t1b_pk primary key (id);
alter table t1s add constraint t1s_pk primary key (id);
declare
raw_source raw(4200);
begin
for i in 1..15 loop
raw_source := rpad('E',2 * 3960 + 2*i,'E');
insert into t1b values(i,rpad(' ',1500),raw_source);
insert into t1s values(i,rpad(' ',1500),raw_source);
commit;
end loop;
end;
/
select
id,
dbms_rowid.rowid_relative_fno(rowid) rel_file_no,
dbms_rowid.rowid_block_number(rowid) block_no,
sys_op_opnsize(b1),
dbms_lob.getlength(b1)
from
t1b
order by
id
/
select
id,
dbms_rowid.rowid_relative_fno(rowid) rel_file_no,
dbms_rowid.rowid_block_number(rowid) block_no,
sys_op_opnsize(b1),
dbms_lob.getlength(b1)
from
t1s
order by
id
/
I’ve created two tables, one to hold a basicfile blob and one to hold a securefile blob, then I’ve run a little PL/SQL loop to insert rows into the table where the first row holds a blob of 3961bytes and each row increases the length of the blob by 1. I’ve started with a string ‘E’s of twice the required length and allowed Oracle to do an implicit conversion (hextoraw) to bytes.
To avoid anomalies and hassle from chained rows etc. I’ve included a column of varchar2(1500) so that there’s no question of Oracle being able to squeeze partial rows into blocks.
After I’ve done this I’ve executed a pair of queries that report for each row the block it starts in, its apparent length according to the sys_op_opnsize(), and its length according o the dbms_lob.getlength() function.
Here are the results under 11.2.0.4, first the basicfile table, then the securefile table:
11.2.0.4
ID REL_FILE_NO BLOCK_NO SYS_OP_OPNSIZE(B1) DBMS_LOB.GETLENGTH(B1)
---------- ----------- ---------- ------------------ ----------------------
1 5 199 86 3961
2 5 195 86 3962
3 5 196 86 3963
4 5 197 86 3964
5 5 197 106 3965
6 5 198 106 3966
7 5 198 106 3967
8 5 198 106 3968
9 5 198 106 3969
10 5 22560 106 3970
11 5 22560 106 3971
12 5 22560 106 3972
13 5 22560 106 3973
14 5 22564 106 3974
15 5 22564 106 3975
ID REL_FILE_NO BLOCK_NO SYS_OP_OPNSIZE(B1) DBMS_LOB.GETLENGTH(B1)
---------- ----------- ---------- ------------------ ----------------------
1 5 231 86 3961
2 5 227 86 3962
3 5 228 86 3963
4 5 229 86 3964
5 5 230 86 3965
6 5 22536 86 3966
7 5 22540 86 3967
8 5 22541 86 3968
9 5 22541 86 3969
10 5 22542 86 3970
11 5 22542 86 3971
12 5 22542 86 3972
13 5 22542 86 3973
14 5 22543 86 3974
15 5 22543 86 3975
You can see that in both cases the sys_op_opnsize() call reports only a few dozen bytes even though we know the length of the BLOB is always more than 3,960 bytes. My original mistake was to assume that this very small number appeared when the BLOB went out of row and was reporting the length of the LOB locator that had been left behind in the row.
This time I’ve going to dump blocks; and a good target block to dump is the first block in each result set that holds two rows: block (5,197) for the basicfile BLOB, and block (5,22541) for the securefile BLOB. (Interestingly the basicfile BLOB shows the sys_op_opnsize() value change in the second of the two rows in (5,197) – is that a coincidence?)
Basicfile block dump (extract)
tab 0, row 0, @0xa10
tl: 5512 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 05
col 1: [1500]
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 2: [4000]
00 54 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 10 de 99 8d 0f 8c 09 00 00
00 00 00 0f 7c 00 00 00 00 00 01 ee ee ee ee ee ee ee ee ee ee ee ee ee ee
...
LOB
Locator:
Length: 84(4000)
Version: 1
Byte Length: 1
LobID: 00.00.00.01.00.00.10.de.99.8d
Flags[ 0x01 0x0c 0x00 0x00 ]:
Type: BLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: ReadWrite
Inode:
Size: 3980
Flag: 0x09 [ Valid DataInRow ]
Future: 0x00 (should be '0x00')
Blocks: 0
Bytes: 3964
Version: 00000.0000000001
Inline data[3964]
...
tab 0, row 1, @0x402
tl: 1550 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 06
col 1: [1500]
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 2: [40]
00 54 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 10 de 99 8f 00 14 05 00 00
00 00 00 0f 7d 00 00 00 00 00 02 01 40 00 d7
LOB
Locator:
Length: 84(40)
Version: 1
Byte Length: 1
LobID: 00.00.00.01.00.00.10.de.99.8f
Flags[ 0x01 0x0c 0x00 0x00 ]:
Type: BLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: ReadWrite
Inode:
Size: 20
Flag: 0x05 [ Valid InodeInRow(ESIR) ]
Future: 0x00 (should be '0x00')
Blocks: 0
Bytes: 3965
Version: 00000.0000000002
DBA Array[1]:
0x014000d7
end_of_block_dump
At line 7 you can see that the BLOB column for the first row is reporting 4,000 bytes, although line 29 tells us that the data content is only 3,964 of those bytes and line 24 tells us we have “Valid data in row”.
At line 41 we can see that the BLOB column for the second row holds only 40 bytes, line 59 tells use we have a “Valid inode in row”, and possibly the “Size: 20” at line 58 explains why the sys_op_opnsize() changed from 86 to 106 at this point. You’ll notice at line 62 that this is the BLOB where the actual data size is just one more byte than the previous row at 3,965 bytes.
Securefile block dump (extract)
tab 0, row 0, @0xa11
tl: 5511 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 09
col 1: [1500]
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 2: [3999]
00 54 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 10 de 99 96 0f 8b 48 90 0f
85 01 00 0f 80 01 ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee
...
LOB
Locator:
Length: 84(3999)
Version: 1
Byte Length: 1
LobID: 00.00.00.01.00.00.10.de.99.96
Flags[ 0x01 0x0c 0x00 0x80 ]:
Type: BLOB
Storage: SecureFile
Characterset Format: IMPLICIT
Partitioned Table: No
Options: ReadWrite
SecureFile Header:
Length: 3979
Old Flag: 0x48 [ DataInRow SecureFile ]
Flag 0: 0x90 [ INODE Valid ]
Layers:
Lengths Array: INODE:3973
01 00 0f 80 01 ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee ...
tab 0, row 1, @0x405
tl: 1548 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 0a
col 1: [1500]
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 2: [38]
00 54 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 10 de 99 98 00 12 40 90 00
0c 21 00 0f 81 01 00 01 01 40 58 11 01
LOB
Locator:
Length: 84(38)
Version: 1
Byte Length: 1
LobID: 00.00.00.01.00.00.10.de.99.98
Flags[ 0x01 0x0c 0x00 0x80 ]:
Type: BLOB
Storage: SecureFile
Characterset Format: IMPLICIT
Partitioned Table: No
Options: ReadWrite
SecureFile Header:
Length: 18
Old Flag: 0x40 [ SecureFile ]
Flag 0: 0x90 [ INODE Valid ]
Layers:
Lengths Array: INODE:12
INODE:
21 00 0f 81 01 00 01 01 40 58 11 01
At line 7 we can see the BLOB column for the first row in the block has a length of 3,999 bytes but line 37 tells use that the next row – with one extra byte – reports a BLOB column holding only 38 bytes. We know from our code that the 3,999 bytes was 3,968 bytes of data, with 31 bytes for various fragments LOB overhead. So there is a small difference in size between basicfile and securefile BLOBs before BLOBs declared as “enable storage in row” are forced out of the row. That’s in 11g, of course, but is it the same in 19c?
19.11.0.0
ID REL_FILE_NO BLOCK_NO SYS_OP_OPNSIZE(B1) DBMS_LOB.GETLENGTH(B1)
---------- ----------- ---------- ------------------ ----------------------
1 36 1672 114 3961
2 36 1676 114 3962
3 36 1680 114 3963
4 36 1684 114 3964
5 36 1684 134 3965
6 36 1688 134 3966
7 36 1688 134 3967
8 36 1688 134 3968
...
ID REL_FILE_NO BLOCK_NO SYS_OP_OPNSIZE(B1) DBMS_LOB.GETLENGTH(B1)
---------- ----------- ---------- ------------------ ----------------------
1 36 2184 114 3961
2 36 2188 114 3962
3 36 2192 114 3963
4 36 2196 114 3964
5 36 2200 114 3965
6 36 2204 114 3966
7 36 2208 114 3967
8 36 2212 114 3968
9 36 2212 132 3969
10 36 2216 132 3970
Again I’ve reported the basicfile results before the securefile results. The numbers we get from sys_op_opnsize() don’t agree with the 11g results, but again we see from the dbms_lob.getlength() numbers that the first point at which we get two consecutive rows into a block is 3,964 bytes of data for the basicfile BLOB (36, 1684) and 3968 bytes for the securefile BLOB (36,2212). In this case the sys_op_opnsize() value changes on the second row of the block for both basicfile and securefile.
Of course I need to dump blocks again – just to be sure – but this time I’m going to keep the extract very short – just a few bytes of each column for the two rows in each block:
Basicfile blockdump (extract)
tab 0, row 0, @0xa10
tl: 5512 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 05
col 1: [1500]
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 2: [4000]
00 70 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 0c 55 e4 28 0f 8c 09 00 00
00 00 00 0f 7c 00 00 00 00 00 01 ee ee ee ee ee ee ee ee ee ee ee ee ee ee
tab 0, row 1, @0x402
tl: 1550 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 06
col 1: [1500]
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 2: [40]
00 70 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 0c 55 e4 2a 00 14 05 00 00
00 00 00 0f 7d 00 00 00 00 00 02 09 00 07 88
Securefile blockdump (extract)
tab 0, row 0, @0xa11
tl: 5511 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 09
col 1: [1500]
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 2: [3999]
00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 0c 55 e4 31 0f 8b 48 90 0f
85 01 00 0f 80 01 ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee ee
tab 0, row 1, @0x405
tl: 1548 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 0a
col 1: [1500]
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 2: [38]
00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 0c 55 e4 33 00 12 40 90 00
0c 21 00 0f 81 01 00 01 09 00 09 c1 01
The symbolic dumps of LOBs has disappeared in the later versions of Oracle, but it’s quite easy from the raw stream of bytes that the raw dumps for 19c match the dumps for11g, and the break points from in-row to out of row are the same in the two versions – it’s just the sys_op_opnsize() function calls that show inconsistent (and somewhat strange) behaviour.
Summary
This note walks through an examination of “in-row” BLOBs to find out how large the data can get before the BLOB is forced out of row.
We find that the breakpoint for securefile BLOBs is a few bytes larger than it is for basicfile BLOBs, 3,968 bytes compared to 3,964 bytes. These sizes, and this difference is consistent between 11.2.0.4 and 19.11.0.0.
There does seem to be a tiny difference securefiles and basicfiles: LOB overhead + raw data = 4,000 for basicfiles while it is 3,999 for securefiles.
Repeating the tests for difference character sets (single byte, multibyte fixed, and multibyte varying) is left as an exercise to the reader.
Oracle Scratchpad 
[…] exceeds 4,000 bytes – which means (in many cases) when the original string content exceeds something between 1,938 and roughly 1,985 characters, depending on your version of Oracle and whether you are using basicfile […]
Pingback by In-row LOBs | Oracle Scratchpad — February 6, 2023 @ 10:24 am GMT Feb 6,2023 |
[…] not a safe indication of whether the lob is still in-row or not. I am currently (still) working on a note showing corrected information, while also correcting related blog […]
Pingback by LOB length | Oracle Scratchpad — February 6, 2023 @ 12:48 pm GMT Feb 6,2023 |