interpreting block dumps of Securefile LOB?

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Fri, 25 Aug 2017 08:16:16 +0200
Message-ID: <CALH8A90vfEa6JEfj2DdQxCX6n7hCqUX8+sUHx+Yb59xHb9uRVg_at_mail.gmail.com>

Hi List,

I am facing an issue where a securefile LOB segment is constant growing, even there should be free/available space.

I managed to identify some extents which "should" be filled only partial. But when I do a block dump of these extents, I did not manage yet to distinguish which blocks hold "current" data and which has deleted data only and should be able to be reused.

Can anyone help me identify those blocks which are contain Expired Blocks?

In my SR Oracle mentions _highthreshold_undoretention might be related. In my DB I have
_highthreshold_undoretention 4294967294 undo_management AUTO
undo_retention 900
_undo_autotune TRUE

Does anyone knows about _highthreshold_undoretention and how it affects Securefile LOBs?

any help is very welcome,
  Martin

*Technical details below*

---

Table definition:
CREATE TABLE "PSFT"."psiblogibinfo"

  (  "guid"        *VARCHAR2*(36 CHAR),
     "logtype"     *VARCHAR2*(1 CHAR),
     "pubdatalong" *BLOB*

  ) segment creation IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS logging STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT flash_cache DEFAULT cell_flash_cache DEFAULT) TABLESPACE "PSDEFAULT" lob ( "PUBDATALONG")
  store AS securefile ( TABLESPACE "PSIMAGE2" ENABLE STORAGE IN ROW     chunk 8192 NOCACHE logging NOCOMPRESS keep_duplicates     STORAGE(INITIAL 104857600 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645

      PCTINCREASE 0 buffer_pool DEFAULT flash_cache DEFAULT
      cell_flash_cache DEFAULT)) ENABLE ROW movement;

---

My analysis (for those who are interested):

*1) A summary about the LOB segment using dbms_space.space_usage:*
set serveroutput ON
DECLARE

    v_segment_size_blocks *NUMBER*;
    v_segment_size_bytes  *NUMBER*;
    v_used_blocks         *NUMBER*;
    v_used_bytes          *NUMBER*;
    v_expired_blocks      *NUMBER*;
    v_expired_bytes       *NUMBER*;
    v_unexpired_blocks    *NUMBER*;
    v_unexpired_bytes     *NUMBER*;

BEGIN
    dbms_space.*Space_usage* ('PSFT', 'SYS_LOB0001268053C00003$$', 'LOB',     v_segment_size_blocks, v_segment_size_bytes, v_used_blocks, v_used_bytes ,

    v_expired_blocks, v_expired_bytes, v_unexpired_blocks, v_unexpired_bytes );

    dbms_output.*Put_line*('Segment size in blocks = ' || v_segment_size_blocks);

    dbms_output.*Put_line*('Used Blocks            = '  ||v_used_blocks);
    dbms_output.*Put_line*('Expired Blocks         = '  ||v_expired_blocks);

    dbms_output.*Put_line*('Unxpired Blocks        = '  ||v_unexpired_blocks

);
END;
/

---

Segment size in blocks = 200125184
Used Blocks            =  74046571
Expired Blocks         = 125460863
Unxpired Blocks        =    327384

*2) Information about LOB EXTENTS*

I have created a table LOBEXTENTS which holds data about each LOB.

CREATE TABLE "SYSTEM"."LOBEXTENTS"
   ( "RID" VARCHAR2(32 BYTE),

"ROW#" NUMBER,
"LOBID" RAW(10),
"EXTENT#" NUMBER,
"HOLE" VARCHAR2(1 BYTE),
"CONT" VARCHAR2(1 BYTE),
"OVER" VARCHAR2(1 BYTE),
"RDBA" NUMBER,
"NBLKS" NUMBER,
"OFFSET" NUMBER,
"LENGTH" NUMBER,
"FILE_NUM" NUMBER,
"BLOCK_NUM" NUMBER

   )

This table I populated by
* looping through all LOBs of the table,

• getting the dbms_lobutil_inode_t TYPE for every LOB using dbms_lobutil.getinode(<LOB>)
• getting the dbms_lobutil_lobmap_t TYPE for every _LOB_ extent (don't confuse them with extents like in dba_extents) using dbms_lobutil.getlobmap(<LOB>,[0..inode.extents - 1) and writing these data into LOBEXTENTS

a sample data set is

---
ROWID   = AAE1lVACkAACR9jAAI
ROWNUM  = 9
LOBID   = 000000010001B83F8F93
EXTENT# = 0
HOLE?   = n
Superchunk cont? = n
Overallocation  = n
rdba   = 1226238224
File   = 292
Block  = 1501456
nblks  = 2
offset = 0
length = 10350

The columns FILE_NUM and BLOCK_NUM are calculated using
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(RDBA)



*3) checking extent usage*


I tried to check if there are extents which are not fully used:

SELECT ext.extent_id,
       ext.file_id,
       ext.block_id,
       ext.bytes,
       ext.blocks,
       ext.relative_fno,
       *SUM*(lx.nblks)
FROM   dba_extents ext,
       lobextents lx
WHERE  ext.segment_name = 'SYS_LOB0001268053C00003$$'
       AND ext.file_id = lx.file_num
       AND lx.block_num BETWEEN ext.block_id AND ( ext.block_id + ext.blocks
 )
GROUP  BY ext.extent_id,
          ext.file_id,
          ext.block_id,
          ext.bytes,
          ext.blocks,
          ext.relative_fno;

So if I'm right, I get all extents from 'SYS_LOB0001268053C00003$$' and the
number of used blocks.

I dumped one of those segments which should be empty,
but now I need to distinguish expired from used blocks.


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 25 2017 - 08:16:16 CEST