It starts with some inserts:
insert into berx.TEST_BLOBS select 4, comments, file_content from berx.source_blobs where id=4; commit; insert into berx.TEST_BLOBS select 5, comments, file_content from berx.source_blobs where id=1; insert into berx.TEST_BLOBS select 6, comments, file_content from berx.source_blobs where id=4; insert into berx.TEST_BLOBS select 7, comments, file_content from berx.source_blobs where id=1; insert into berx.TEST_BLOBS select 8, comments, file_content from berx.source_blobs where id=4; insert into berx.TEST_BLOBS select 9, comments, file_content from berx.source_blobs where id=1; insert into berx.TEST_BLOBS select 10, comments, file_content from berx.source_blobs where id=4; commit;
This leads to 10 lines with 10 LOBs. The LOBs are 1 or 2 blocks in size.
The LOBMAP summary of the rows and LOBs is this:
1 11.1 kB AABu7gAAEAAA+N7AAA
ROWID = AABu7gAAEAAA+N7AAA ROWNUM = 1 LOBID = 00000001000099BF531D EXTENT# = 0 rdba = 17032588 - 0x 103e58c Block = 255372 nblks = 2 offset = 0 length = 11389
2 2-byte AABu7gAAEAAA+N/AAA
ROWID = AABu7gAAEAAA+N/AAA ROWNUM = 2 LOBID = 00000001000099BF67D1 EXTENT# = 0 rdba = 17032590 - 0x 103e58e Block = 255374 nblks = 1 offset = 0 length = 2
3 close to 4 kB AABu7gAAEAAA+N/AAB
ROWID = AABu7gAAEAAA+N/AAB ROWNUM = 3 LOBID = 00000001000099C090E3 EXTENT# = 0 rdba = 17032587 - 0x 103e58b Block = 255371 nblks = 1 offset = 0 length = 4038
4 11.1 kB AABu7gAAEAAA+N9AAA
ROWID = AABu7gAAEAAA+N9AAA ROWNUM = 4 LOBID = 00000001000099C56EFB EXTENT# = 0 rdba = 17032591 - 0x 103e58f Block = 255375 nblks = 1 offset = 0 length = 8060 --- ROWID = AABu7gAAEAAA+N9AAA ROWNUM = 5 LOBID = 00000001000099C56EFB EXTENT# = 1 rdba = 17033787 - 0x 103ea3b Block = 256571 nblks = 1 offset = 8060 length = 3329
This one is somehow special, as the LOB is 11kB in size, so it requires 2 blocks.
These 2 blocks are spread over 2 tablespace extents (and 2 LOB extents also).
In this case space optimization won against avoiding fragmentation.
5 1-byte AABu7gAAEAAA+N/AAC
ROWID = AABu7gAAEAAA+N/AAC ROWNUM = 6 LOBID = 00000001000099C633A9 EXTENT# = 0 rdba = 17033788 - 0x 103ea3c Block = 256572 nblks = 1 offset = 0 length = 1
6 11.1 kB AABu7gAAEAAA+N/AAD
ROWID = AABu7gAAEAAA+N/AAD ROWNUM = 7 LOBID = 00000001000099C633AA EXTENT# = 0 rdba = 17033789 - 0x 103ea3d Block = 256573 nblks = 2 offset = 0 length = 11389
7 1-byte AABu7gAAEAAA+N/AAE
ROWNUM = 8 LOBID = 00000001000099C633AB EXTENT# = 0 rdba = 17033791 - 0x 103ea3f Block = 256575 nblks = 1 offset = 0 length = 1
8 11.1 kB AABu7gAAEAAA+N/AAF
ROWNUM = 9 LOBID = 00000001000099C633AC EXTENT# = 0 rdba = 17033792 - 0x 103ea40 Block = 256576 nblks = 2 offset = 0 length = 11389
9 1-byte AABu7gAAEAAA+N/AAG
ROWNUM = 10 LOBID = 00000001000099C633AD EXTENT# = 0 rdba = 17033794 - 0x 103ea42 Block = 256578 nblks = 1 offset = 0 length = 1
10 11.1 kB AABu7gAAEAAA+N/AAH
ROWID = AABu7gAAEAAA+N/AAH ROWNUM = 11 LOBID = 00000001000099C633AE EXTENT# = 0 rdba = 17033795 - 0x 103ea43 Block = 256579 nblks = 2 offset = 0 length = 11389
After row 4s commit, the NGLOB: Persistent Undo block changed:
Persistent Undo information
after row 4th commit
Block dump from disk: buffer tsn: 4 rdba: 0x0103ea05 (4/256517) scn: 0xa9d.9b8d2fe9 seq: 0x01 flg: 0x04 tail: 0x2fe94001 frmt: 0x02 chkval: 0x39b1 type: 0x40=NGLOB: Persistent Undo Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007FDB0460C800 to 0x00007FDB0460E800 ... Dump of Persistent Undo Block -------------------------------------------------------- Inst: 1 Objd:454499 Inc:3472328295419215872 SyncTime:4126781815622353101 Flag:0x1 Total: 2 LoadTime:1505116365 Opcode:1 Xid: 0x01b4.014.00002aca -------------------------------------------------------- Chunk: dba: 0x103ea3b: len: 1: Xid: 0x01b4.00d.00002abc: Scn: 2717.-1685245975: Flag: IN USE: FBR: False Chunk: dba: 0x103ea3c: len: 68: Xid: 0x0000.000.00000000: Scn: 2708.1852084450: Flag: FREE: FBR: False
Block 0x103ea3b is marked as IN USE. This is the 2nd block from the LOB just inserted.
The remaining blocks in this LOB extent are marked as FREE.
After the other 6 inserts, some meta information changed again:
Persistent Undo information
after row 10th commit
Block dump from disk: buffer tsn: 4 rdba: 0x0103ea05 (4/256517) scn: 0xa9d.9d4b7340 seq: 0x01 flg: 0x04 tail: 0x73404001 frmt: 0x02 chkval: 0x5850 type: 0x40=NGLOB: Persistent Undo Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007F880DC72800 to 0x00007F880DC74800 ... Dump of Persistent Undo Block -------------------------------------------------------- Inst: 1 Objd:454499 Inc:3472328295419215872 SyncTime:3473457525430831671 Flag:0x1 Total: 5 LoadTime:1505118775 Opcode:1 Xid: 0x01af.000.00003334 -------------------------------------------------------- Chunk: dba: 0x103ea3c: len: 1: Xid: 0x01af.021.00003338: Scn: 2717.-1655999709: Flag: IN USE: FBR: False Chunk: dba: 0x103ea3d: len: 2: Xid: 0x01af.021.00003338: Scn: 2717.-1655999697: Flag: IN USE: FBR: False Chunk: dba: 0x103ea3f: len: 4: Xid: 0x01af.021.00003338: Scn: 2717.-1655999697: Flag: IN USE: FBR: False Chunk: dba: 0x103ea43: len: 8: Xid: 0x01af.021.00003338: Scn: 2717.-1655999680: Flag: IN USE: FBR: False Chunk: dba: 0x103ea4b: len: 53: Xid: 0x0000.000.00000000: Scn: 2708.1852084450: Flag: FREE: FBR: False
There are several Persistent Undo Chunks now, with size of 1, 2, 4, 8 as IN USE and 53 FREE.
The LOBs from this insert are in the Chunks with len: 1, 2,4 and partially 8. To reflect the Chunk starting at 0x103ea43 with len: 8 is only partially filled (2 blocks for LOBID: 00000001000099C633AE) another structure is changed:
Hash Bucket - Range: 32k to 64k
Block dump from disk: buffer tsn: 4 rdba: 0x0103e585 (4/255365) scn: 0xa9d.9d4b7347 seq: 0x02 flg: 0x04 tail: 0x73473d02 frmt: 0x02 chkval: 0xdefc type: 0x3d=NGLOB: Hash Bucket Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007F880DC72800 to 0x00007F880DC74800 ... Dump of Hash Bucket Block -------------------------------------------------------- Hash Bucket Header Dump Range: 32k to 64k Inst:1 Flag:5 Total:0 FSG_COUNT:0 OBJD:454499 Inc:0 Self Descriptive fsg_count:0 Head:0x 0x00000000 Tail:0x 0x00000000 Opcdode:0 Locker Xid: 0x0000.000.00000000 Fsbdba: 0x0 Fbrdba: 0x0 Head Cache Entries ------------------- ------------------- Tail Cache Entries ------------------- ------------------- Free Space Chunk Summary Inst:1 Minc:0 Maxc:0 Count:1 Tot:6 MAXC:1 CFS Chunk List -------------- Chunk:0 DBA: 0x0103ea45 Len:6 Xid: 0x01af.021.00003338 Ctime:0 Scn: 0x0000.00000000
This CFS Chunk list points to a chunk of 6 blocks (Len: 6) (size: 48k) starting with DBA: 0x103ea45 as free.
Here some meta information about used and unused blocks is stored in NGLOB: Persistent Undo and NGLOB: Hash Bucket blocks. It's also shown LOBs can be split up to fit into the first space available (against filling continuous blocks / least count of extents to avoid fragmentation).
Keine Kommentare:
Kommentar veröffentlichen