Dienstag, 29. August 2017

SecureFile LOB - the empty table

I have to dig into structures of SecureFile LOBs right now. Unfortunately I do not find a lot of resources available, so I have to investigate at my own.
This post is to document my findings. There is a lot of guesswork and interpretation here, so be careful if you use or refer to any information here.

The whole testcase is on 12.1.0.2.170418 BundlePatch EE, Linux, 2 node RAC.

I start with a simple test table:
create table test_blobs (
        id              number(5),
  comments        varchar2(80),
        file_content    blob
) 
SEGMENT CREATION IMMEDIATE 
NOCOMPRESS 
NOPARALLEL 
TABLESPACE  USERS
lob (file_content) store as SECUREFILE(
        disable storage in row
        chunk 16K
        tablespace users
)
;

let's get the LOB segment:

col column_name for A20
col segment_name for A30
SELECT column_name, segment_name
FROM DBA_LOBS
where owner='BERX' 
  and table_name='TEST_BLOBS';

COLUMN_NAME          SEGMENT_NAME                  
-------------------- ------------------------------
FILE_CONTENT         SYS_LOB0000454368C00003$$     

and it's extent:

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  

this segment only has 16 blocks, so it should be easy to have a look at it:

ALTER SESSION SET TRACEFILE_IDENTIFIER = "EMPTY_BLOB";  
alter system dump datafile 4 block min 255360 block max 255375;

What different blocktypes are there:
grep -E 'Block dump from disk|buffer tsn|frmt' EMPTY_BLOB.trc
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e580 (4/255360)
frmt: 0x02 chkval: 0xfec6 type: 0x45=NGLOB: Lob Extent Header
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e581 (4/255361)
frmt: 0x02 chkval: 0x12dd type: 0x3f=NGLOB: Segment Header
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e582 (4/255362)
frmt: 0x02 chkval: 0x86ba type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e583 (4/255363)
frmt: 0x02 chkval: 0x86bb type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e584 (4/255364)
frmt: 0x02 chkval: 0x86be type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e585 (4/255365)
frmt: 0x02 chkval: 0xbb23 type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e586 (4/255366)
frmt: 0x02 chkval: 0x86be type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e587 (4/255367)
frmt: 0x02 chkval: 0x86be type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e588 (4/255368)
frmt: 0x02 chkval: 0x86b6 type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e589 (4/255369)
frmt: 0x02 chkval: 0x86b6 type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58a (4/255370)
frmt: 0x02 chkval: 0x86b6 type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58b (4/255371)
frmt: 0x02 chkval: 0x54e9 type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58c (4/255372)
frmt: 0x02 chkval: 0x91be type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58d (4/255373)
frmt: 0x02 chkval: 0x3ba9 type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58e (4/255374)
frmt: 0x02 chkval: 0x7817 type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58f (4/255375)
frmt: 0x02 chkval: 0x3418 type: 0x06=trans data

Let's go into detail of these blocks:

NGLOB: Lob Extent Header

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e580 (4/255360)
scn: 0xa9c.1cf87e98 seq: 0x02 flg: 0x04 tail: 0x7e984502
frmt: 0x02 chkval: 0xfec6 type: 0x45=NGLOB: Lob Extent Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
7F6247D60800 0000A245 0103E580 1CF87E98 04020A9C  [E........~......]
7F6247D60810 0000FEC6 0103E58B 00000005 00000000  [................]
7F6247D60820 59A53020 59A53020 00000000 00000000  [ 0.Y 0.Y........]
7F6247D60830 00000000 0006EF63 00000000 00000001  [....c...........]
7F6247D60840 0103E58B 00000005 00000000 00000000  [................]
7F6247D60850 00000000 00000000 00000000 00000000  [................]
        Repeat 505 times
7F6247D627F0 00000000 00000000 00000000 7E984502  [.............E.~]
  Dump of Extent Header Block
  --------------------------------------------------------
  sdba: 0x0103e58b len:5 flag:0x0 synctime:1503997984 updtime:1503997984
  objd:454499 inc:0 total:1 opcode:0 xid: 0x0000.000.00000000
  entry 0: sdba: 0x0103e58b len:5 fdba: 0x00000000
  --------------------------------------------------------

There is some information already:
sdbaI don't know what this stands for, but it points to the 1st block of type 0x06=trans data.
lenprobably a length of something - as it's 5 and the extent has 5 blocks of type 0x06=trans data, I guess it's the length (count) of blocks capable of data.
flagunknown to me right now
synctimethis looks like an unix epoc time - it matches to GMT: Tuesday, August 29, 2017 9:13:04 AM when I created the table.
objidis the OBJECT_ID (or DATA_OBJECT_ID? I don't know) of the LOB segment.
incunknown to me right now
totalunknown to me right now
opcodeunknown to me right now
xidmight be a transaction ID when used.
entry 0same as sdba and len from above - there might be multiple entries in the future?

NGLOB: Segment Header

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e581 (4/255361)
scn: 0xa9c.1cf87e98 seq: 0x04 flg: 0x04 tail: 0x7e983f04
frmt: 0x02 chkval: 0x12dd type: 0x3f=NGLOB: Segment Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
...
NGLOB Segment Header
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 16
                  last map  0x00000000  #maps: 0      offset: 8168
      Highwater::  0x0103e590  ext#: 0      blk#: 16     ext size: 16
  #blocks in seg. hdr's freelists: 0
  #blocks below: 16
  mapblk  0x00000000  offset: 0
      Disk Lock:: Locked by xid:  0x01b3.015.00002ca0
     Map Header:: next  0x00000000  #extents: 1    obj#: 454499 flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0103e580  length: 16

  ---------------
CFS hbbcnt:7 hbbmx:7 Mxrng:7 UFS hbbcnt:2 PUA cnt:0 Flag:2
Segment Stats
-------------
Retention: -1
Free Space: 5
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
---------
pua off:1240 sz:8
  --------------------------------------------------------  

There is nothing of great interest for me in Extent Control Header. The first interesting part is CFS.
CFS means Committed Free Space (blocks) - there are different "counts" - all of them 7 right now.
UFS means Uncommitted Free Space (blocks) - it seems to have a count of 2.
PUA might mean Persistent Undo Array (my guessing!) - no count right now.
In Segment Stats, Free Space: is 5 which matches the 5 trans data blocks available. They are where the real data will go.
UFS Array contains 2 blocks, so it matches the count above.
CFS Array contains 7 blocks, which matches the count above also.
PUA Array is empty - there are no NGLOB: Persistent Undo blocks right now.
I have no idea what the different off: and sz: mean. Offset and Size?

I start with a simple

1st NGLOB: Hash Bucket

(part of UFS)

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e582 (4/255362)
scn: 0xa9c.1cf87e98 seq: 0x02 flg: 0x04 tail: 0x7e983d02
frmt: 0x02 chkval: 0x86ba type: 0x3d=NGLOB: Hash Bucket
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
...
  Dump of Hash Bucket Block
  --------------------------------------------------------
Hash Bucket Header Dump
Inst:1 Flag:6 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:0 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:0
UFS List
--------------

it seems quite empty.

2nd NGLOB: Hash Bucket 

(part of UFS)

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e583 (4/255363)
scn: 0xa9c.1cf87e98 seq: 0x02 flg: 0x04 tail: 0x7e983d02
frmt: 0x02 chkval: 0x86bb type: 0x3d=NGLOB: Hash Bucket
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
...
  Dump of Hash Bucket Block
  --------------------------------------------------------
Hash Bucket Header Dump
Inst:1 Flag:6 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:0 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:0
UFS List
--------------

Also empty.

3rd NGLOB: Hash Bucket 

(part of CFS)

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e584 (4/255364)
scn: 0xa9c.1cf87e98 seq: 0x02 flg: 0x04 tail: 0x7e983d02
frmt: 0x02 chkval: 0x86be type: 0x3d=NGLOB: Hash Bucket
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
...
  Dump of Hash Bucket Block
  --------------------------------------------------------
Hash Bucket Header Dump
Range: 2k to 32k
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:0 Tot:0 MAXC:0
CFS Chunk List
--------------

Again an empty block, but it contains a Range: 2k to 32k!

the next NGLOB: Hash Bucket blocks are similar (empty) but with different ranges:

0x0103e584 Range: 2k to 32k
0x0103e585 Range: 32k to 64k
0x0103e586 Range: 64k to 128k
0x0103e587 Range: 128k to 256k
0x0103e588 Range: 256k to 512k
0x0103e589 Range: 512k to 1m
0x0103e58a Range: 1m to 64m
There might be a good reason why the range starts at 2k: this is the smallest blocksize you can define at creation of a DB. As a LOB (when not stored in ROW) always uses at least one block, 2k is the smallest unit which needs to be managed. These different space chunks are designed to avoid fragmentation when deleting/inserting LOBs of different size.

trans data

is empty right now:
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58b (4/255371)
scn: 0xa5d.aa1c5d7d seq: 0x02 flg: 0x04 tail: 0x5d7d0602
frmt: 0x02 chkval: 0x54e9 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
...
Block header dump:  0x0103e58b
 Object id on Block? Y
 seg/obj: 0x1c334  csc: 0xa5d.aa1c5d7c  itc: 1  flg: E  typ: 5 - LOCAL LOBS
     fsl: 0  fnx: 0xffffffff ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0014.019.0002bdd9  0x00000000.0000.00  -B--    0  fsc 0x0000.00000000
========
bdba    [0x0103e58b]
kdlich  [0x7f6247d6084c 56]
  flg0  0x20 [ver=0 typ=data lock=n]
  flg1  0x00
  scn   0x0a5d.aa1c5d7c
  lid   000000010000147f95a5
  rid   0x00000000.0000
kdlidh  [0x7f6247d60864 24]
  flg2  0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
  flg3  0x00
  pskip 0
  sskip 0
  hash  0000000000000000000000000000000000000000
  hwm   8060
  spr   0
  data  [0x7f6247d60880 52 8060]
It's of typ: 5 - LOCAL LOBS.
I don't think there is anything of interest in the empty block right now. Maybe more later.

These are the 16 blocks we have.

Kommentar veröffentlichen