The insert of one row with a small (1byte) LOB increases the complexity.
berx.source_blobs contains some BLOBs of different size.
insert into berx.TEST_BLOBS select 1, comments, file_content from berx.source_blobs where id=1; --1 byte commit;
we have a new extent now
col owner for A10 col segment_name for A30 select owner, segment_name, file_id, block_id, blocks from dba_extents where owner='BERX' and segment_name='SYS_LOB0000454368C00003$$';
OWNER SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS ---------- ------------------------------ ---------- ---------- ---------- BERX SYS_LOB0000454368C00003$$ 4 255360 16 BERX SYS_LOB0000454368C00003$$ 4 256512 128
The new extent has some new blocks:
ALTER SESSION SET TRACEFILE_IDENTIFIER = "ONE_BLOB_EXT2"; alter system dump datafile 4 block min 256512 block max 257023;
grep -E 'Block dump from disk|buffer tsn|frmt' ONE_BLOB_EXT2.trc Block dump from disk: buffer tsn: 4 rdba: 0x0103ea00 (4/256512) frmt: 0x02 chkval: 0xf2bc type: 0x45=NGLOB: Lob Extent Header Block dump from disk: buffer tsn: 4 rdba: 0x0103ea01 (4/256513) frmt: 0x02 chkval: 0x6d43 type: 0x3e=NGLOB: Committed Free Space Block dump from disk: buffer tsn: 4 rdba: 0x0103ea02 (4/256514) frmt: 0x02 chkval: 0x6d41 type: 0x3e=NGLOB: Committed Free Space Block dump from disk: buffer tsn: 4 rdba: 0x0103ea03 (4/256515) frmt: 0x02 chkval: 0x6d40 type: 0x3e=NGLOB: Committed Free Space Block dump from disk: buffer tsn: 4 rdba: 0x0103ea04 (4/256516) frmt: 0x02 chkval: 0x6d47 type: 0x3e=NGLOB: Committed Free Space Block dump from disk: buffer tsn: 4 rdba: 0x0103ea05 (4/256517) frmt: 0x02 chkval: 0xc593 type: 0x40=NGLOB: Persistent Undo Block dump from disk: buffer tsn: 4 rdba: 0x0103ea06 (4/256518) frmt: 0x02 chkval: 0x641e type: 0x40=NGLOB: Persistent Undo Block dump from disk: ... more Persistent Undo blocks ... Block dump from disk: buffer tsn: 4 rdba: 0x0103ea37 (4/256567) frmt: 0x02 chkval: 0x6ae3 type: 0x06=trans data Block dump from disk: buffer tsn: 4 rdba: 0x0103ea38 (4/256568) frmt: 0x02 chkval: 0x09a4 type: 0x06=trans data Block dump from disk: buffer tsn: 4 rdba: 0x0103ea39 (4/256569) frmt: 0x02 chkval: 0xcef1 type: 0x06=trans data Block dump from disk: buffer tsn: 4 rdba: 0x0103ea3a (4/256570) frmt: 0x02 chkval: 0x2928 type: 0x06=trans data ... many more trans data blocks ... Block dump from disk: buffer tsn: 4 rdba: 0x0103ea80 (4/256640) frmt: 0x02 chkval: 0x6bb3 type: 0x45=NGLOB: Lob Extent Header ... many more trans data blocks ... Block dump from disk: buffer tsn: 4 rdba: 0x0103eb00 (4/256768) frmt: 0x02 chkval: 0x6a33 type: 0x45=NGLOB: Lob Extent Header ... many more trans data blocks ... Block dump from disk: buffer tsn: 4 rdba: 0x0103eb80 (4/256896) frmt: 0x02 chkval: 0x6ab3 type: 0x45=NGLOB: Lob Extent Header ... many more trans data blocks ...
1st NGLOB: Lob Extent Header
Block dump from disk: buffer tsn: 4 rdba: 0x0103ea00 (4/256512) scn: 0xa9c.1f0271f4 seq: 0x02 flg: 0x04 tail: 0x71f44502 frmt: 0x02 chkval: 0xf2bc type: 0x45=NGLOB: Lob Extent Header Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800 ... Dump of Extent Header Block -------------------------------------------------------- sdba: 0x0103ea01 len:127 flag:0x0 synctime:1504000457 updtime:1504000457 objd:454499 inc:0 total:1 opcode:0 xid: 0x0000.000.00000000 entry 0: sdba: 0x0103ea01 len:127 fdba: 0x00000000 --------------------------------------------------------
In comparison to the Lob Extent Header of 1st extent this one has sdba: 0x0103ea01 - this points direct to the next block. But it's not of type 0x06=trans data but 0x3e=NGLOB: Committed Free Space. len:127 makes sense, as the next block (after 128 == 0x80) is again 0x45=NGLOB: Lob Extent Header. There is no data yet.
1st NGLOB: Committed Free Space
Block dump from disk: buffer tsn: 4 rdba: 0x0103ea01 (4/256513) scn: 0xa9c.1f0271f4 seq: 0x02 flg: 0x04 tail: 0x71f43e02 frmt: 0x02 chkval: 0x6d43 type: 0x3e=NGLOB: Committed Free Space Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800 ... Dump of NGLOB: Uncommitted Free Space Block -------------------------------------------------------- FSB Header -------------- objd:454499 inc:0 hdba: 0x0103e582 Rng:-1 prev: 0x0103ea01 next: 0x0103ea01 edba: 0x0103ea00 head: 0x0103ea01 Xid: 0x0000.000.00000000 Op:0 -------------- Free Space Chunk Summary Inst:1 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:0 UFS List --------------hba: points back to 0x0103e582 - the 1st NGLOB: Hash Bucket of UFS. prev:, next: and head: is the block itself.
The block seems empty.
2nd NGLOB: Committed Free Space
Block dump from disk: buffer tsn: 4 rdba: 0x0103ea02 (4/256514) scn: 0xa9c.1f0271f8 seq: 0x01 flg: 0x04 tail: 0x71f83e01 frmt: 0x02 chkval: 0x6d41 type: 0x3e=NGLOB: Committed Free Space Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800 ... Dump of NGLOB: Uncommitted Free Space Block -------------------------------------------------------- FSB Header -------------- objd:454499 inc:0 hdba: 0x0103e582 Rng:-1 prev: 0x00000000 next: 0x00000000 edba: 0x0103ea00 head: 0x0103ea01 Xid: 0x0000.000.00000000 Op:0 -------------- Free Space Chunk Summary Inst:1 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:0 UFS List --------------Is similar to previous block, prev: and next: are empty.
The next 2 Committed Free Space blocks are identical.
1st NGLOB: Persistent Undo
Block dump from disk: buffer tsn: 4 rdba: 0x0103ea05 (4/256517) scn: 0xa9c.1f027207 seq: 0x05 flg: 0x04 tail: 0x72074005 frmt: 0x02 chkval: 0xc593 type: 0x40=NGLOB: Persistent Undo Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800 ... Dump of Persistent Undo Block -------------------------------------------------------- Inst: 1 Objd:454499 Inc:3472328295419215872 SyncTime:3976733684276738505 Flag:0x1 Total: 1 LoadTime:1504000457 Opcode:1 Xid: 0x01b3.01d.00002ca6 -------------------------------------------------------- Chunk: dba: 0x103ea37: len: 73: Xid: 0x0000.000.00000000: Scn: 2708.1852084450: Flag: FREE: FBR: FalseUnfortunately I don't know anything about this block, yet. LoadTime seems to be epoc again.
2nd NGLOB: Persistent UNDO
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea06 (4/256518)
scn: 0xa9c.1f027209 seq: 0x01 flg: 0x04 tail: 0x72094001
frmt: 0x02 chkval: 0x641e type: 0x40=NGLOB: Persistent Undo
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800
7FDC79DC4800 0000A240 0103EA06 1F027209 04010A9C [@........r......]
7FDC79DC4810 0000641E 00000002 00000001 59A539C9 [.d...........9.Y]
7FDC79DC4820 00000000 59A539C9 00000005 00000001 [.....9.Y........]
7FDC79DC4830 00000001 001D01B3 00002CA6 0006EF63 [.........,..c...]
7FDC79DC4840 00000000 00000000 00000000 00000000 [................]
Repeat 15 times
7FDC79DC4940 00000000 0103E58B 00000000 00000001 [................]
7FDC79DC4950 000301B3 00002C9B 1F027209 00000A9C [.....,...r......]
7FDC79DC4960 00000002 0103E58C 00000000 00000004 [................]
7FDC79DC4970 00000000 00000000 6E6490E2 00000A94 [..........dn....]
7FDC79DC4980 00000000 00000000 00000000 00000000 [................]
Repeat 486 times
7FDC79DC67F0 00000000 00000000 00000000 72094001 [.............@.r]
Dump of Persistent Undo Block
--------------------------------------------------------
Inst: 1 Objd:454499 Inc:3472328295419215872 SyncTime:4120848872352594377 Flag:0x1
Total: 2 LoadTime:1504000457 Opcode:1 Xid: 0x01b3.01d.00002ca6
--------------------------------------------------------
Chunk: dba: 0x103e58b: len: 1: Xid: 0x01b3.003.00002c9b: Scn: 2716.520253961: Flag: IN USE: FBR: False
Chunk: dba: 0x103e58c: len: 4: Xid: 0x0000.000.00000000: Scn: 2708.1852084450: Flag: FREE: FBR: False
There are 50 such blocks - all looking similar, just no Chunk: dba: entries. This matches the PUA Batchsize: 50 from 1st extents NGLOB: Segment Header.
The trans data blocks are equal to the empty blocks in 1st extent.
The other NGLOB: Lob Extent Header have a sdba: which points to their following block and a len:127 - so they describe the next bunch of trans data blocks.
It's unclear why this extent is required right now as it does not hold any valuable data.
My assumption is the transaction (INSERT) I did - Every transaction on a LOB might require a NGLOB: Persistent Undo (even there is nothing to store in case of an insert). In the 2nd NGLOB: Persistent Undo in memory dump there I can see 0103E58B - the rba of 1st trans data.
Back to the insert:
To get some more information about a specific LOB I have a small script LOBMAP.sql
It gets the INODE of a LOB using DBMS_LOBUTIL.GETINODE. With this INODE it loops through all extents of this LOB, gets additional information with DBMS_LOBUTIL.GETLOBMAP and prints it.
In this context the extent is a chunk inside the LOB storage, not an extend (as in DBA_EXTENTS).
As DBMS_LOBUTIL is not documented, other sources are useful.
The LOB in the table is quite small, so there is only 1 LOB extent:
ROWID = AABu7gAAEAAA+N7AAA ROWNUM = 1 LOBID = 00000001000099BF1091 EXTENT# = 0 HOLE? = n Superchunk cont? = n Overallocation = n rdba = 17032587 - 0x 103E58B File = 4 Block = 255371 nblks = 1 offset = 0 length = 1 ---ROWID and ROWNUM are information about the table row - they are more a reference to the row than a property of the LOB. The LOBID uniquely identifies the LOB. EXTENT# is the number of (LOB) extent - in case the LOB does not fit in a single extent. rdba points to the block where this extent starts. the length is 1 - as it's a 1 byte BLOB.
The blocks in the first extent changed as follows.
ALTER SESSION SET TRACEFILE_IDENTIFIER = "ONE_BLOB"; alter system dump datafile 4 block min 255360 block max 255375;
The NGLOB: Lob Extent Header did not change.
NGLOB: Segment Header
is different than before:Block dump from disk:
buffer tsn: 4 rdba: 0x0103e581 (4/255361)
scn: 0xa9c.1f027207 seq: 0x01 flg: 0x04 tail: 0x72073f01
frmt: 0x02 chkval: 0x3306 type: 0x3f=NGLOB: Segment Header
Hex dump of block: st=0, typ_found=1
...
NGLOB Segment Header
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 144
last map 0x00000000 #maps: 0 offset: 8168
Highwater:: 0x0103ea80 ext#: 1 blk#: 128 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 144
mapblk 0x00000000 offset: 1
Unlocked
Map Header:: next 0x00000000 #extents: 2 obj#: 454499 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0103e580 length: 16
0x0103ea00 length: 128
---------------
CFS hbbcnt:7 hbbmx:7 Mxrng:7 UFS hbbcnt:2 PUA cnt:1 Flag:2
Segment Stats
-------------
Retention: -1
Free Space: 133
PUA Batchsize: 50
UFS Array
---------
DBA: 0x0103e582 Inst:1
DBA: 0x0103e583 Inst:1
Ufs off:152 sz:512
CFS Array
---------
Range: 0 DBA: 0x0103e584
Range: 1 DBA: 0x0103e585
Range: 2 DBA: 0x0103e586
Range: 3 DBA: 0x0103e587
Range: 4 DBA: 0x0103e588
Range: 5 DBA: 0x0103e589
Range: 6 DBA: 0x0103e58a
Cfs off:664 sz:576
PUA Array
---------
DBA: 0x0103ea05 Inst:1
pua off:1240 sz:8
--------------------------------------------------------
Of course scn: changed. seq: changed from 0x04 to 0x01 ( it's the 1st change in this scn). #extents: and #blocks: changed to reflect the new extent.Highwater:: now points to the 2nd NGLOB: Lob Extent Header in 2nd extent. #blocks below: changed from 16 to 144. There is a 2nd entry in Extent Map now. PUA cnt: is 1 - so there is 1 Undo Array available.
In the PUA Array there is the 1st NGLOB: Persistent Undo referenced.
NGLOB: Hash Bucket
Block dump from disk: buffer tsn: 4 rdba: 0x0103e582 (4/255362) scn: 0xa9c.1f027204 seq: 0x02 flg: 0x04 tail: 0x72043d02 frmt: 0x02 chkval: 0x4368 type: 0x3d=NGLOB: Hash Bucket Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007F381BADB800 to 0x00007F381BADD800 ... Dump of Hash Bucket Block -------------------------------------------------------- Hash Bucket Header Dump Inst:1 Flag:2 Total:0 FSG_COUNT:1 OBJD:454499 Inc:0 fsg_count:1 Head:0x 0x0103ea01 Tail:0x 0x0103ea01 Opcdode:1 Locker Xid: 0x01b3.008.00002c95 Fsbdba: 0x103ea01 Fbrdba: 0x0 Head Cache Entries ------------------- Head: 0 fsg_db: 0x0103ea01 ------------------- Tail Cache Entries ------------------- Tail: 0 fsg_db: 0x0103ea01 ------------------- Free Space Chunk Summary Inst:0 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:0 UFS List -------------- --------------------------------------------------------
In comparison to previous block, Flag: changed from 6 to 2. (for whatever reason)
FSG_COUNT: increased from 0 to 1. Not sure what FSG stands for, but it seems to be related to Free Space [whatever?]. Opcdode: increased from 0 to 1 and Xid: does contain a real transaction ID now. Fsbdba: and the Head Cache Entries points to the 1st NGLOB: Committed Free Space of 2nd extent (which pointed to this block as well).
Still the UFS List is empty.
All the other NGLOB: Hash Buckets - both IFS and CFS - are equal to before.
trans data
the block with the LOB of the 1st row:Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58b (4/255371)
scn: 0xa9c.1f027209 seq: 0x02 flg: 0x04 tail: 0x72090602
frmt: 0x02 chkval: 0x1e84 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F381BADB800 to 0x00007F381BADD800
7F381BADB800 0000A206 0103E58B 1F027209 04020A9C [.........r......]
7F381BADB810 00001E84 00000005 0006EF63 1F027209 [........c....r..]
7F381BADB820 00000A9C 00220001 FFFFFFFF 000301B3 [......".........]
7F381BADB830 00002C9B 00000000 00000000 00004000 [.,...........@..]
7F381BADB840 00000000 00000000 00000000 1F027209 [.............r..]
7F381BADB850 00200A9C 01000000 BF990000 00009110 [.. .............]
7F381BADB860 00000000 00000000 00000000 00000000 [................]
7F381BADB870 00000000 00000000 00000000 00000001 [................]
7F381BADB880 85ED8C41 1A98D5F8 34C25268 EE84234D [A.......hR.4M#..]
7F381BADB890 215E09F4 85AA94A7 5B51EEDA D1D0A149 [..^!......Q[I...]
...
7F381BADD7F0 291DF1A5 CEEDA316 EE84DB30 72090602 [...)....0......r]
Block header dump: 0x0103e58b
Object id on Block? Y
seg/obj: 0x6ef63 csc: 0xa9c.1f027209 itc: 1 flg: E typ: 5 - LOCAL LOBS
fsl: 0 fnx: 0xffffffff ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x01b3.003.00002c9b 0x00000000.0000.00 -B-- 0 fsc 0x0000.00000000
========
bdba [0x0103e58b]
kdlich [0x7f381badb84c 56]
flg0 0x20 [ver=0 typ=data lock=n]
flg1 0x00
scn 0x0a9c.1f027209
lid 00000001000099bf1091
rid 0x00000000.0000
kdlidh [0x7f381badb864 24]
flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
flg3 0x00
pskip 0
sskip 0
hash 0000000000000000000000000000000000000000
hwm 1
spr 0
data [0x7f381badb880 52 8060]
41 8c ed 85 f8 d5 98 1a 68 52 c2 34 4d 23 84 ee f4 09 5e 21 a7 94 aa 85 da ee
51 5b 49 a1 d0 d1 38 e1 62 9d a1 8b 29 31 c7 c5 d6 d8 48 c3 2f 66 09 15 3a 8c
...
In this block, in the header csc:, Xid and scn changed. The hwm is 1 now (was 8060 before).
The 1st byte of data is 41 - ASCII for A - that's the content of my 1 byte file.
This block really contains the LOB. lid has the same value (just lowercase) as LOBID in the table row - so it's an indirect pointer back to the row it belongs to.
Unfortunately I did not find any indication which shows the block is used now (in comparison to previous dump) - any hint is very welcome!
In fact it was always important, just as the expected problems increase (by count or value) management seems to be more aware now. Due to many discussions I started to have a look at