首页 » ORACLE 9i-23c » LOB 不当的RETENTION 会导致严重的空间浪费(二)

LOB 不当的RETENTION 会导致严重的空间浪费(二)

之前记录过一篇关于lob 《 LOB 不当的chunk size会导致严重的空间浪费》,最近一个案例关于enq:hw 的wait event在lob段,而SQL语句是一个update,发现也存另一种情况因为retention过大,导致的lob快速扩展,简单记录。

— oracle 19.3

SQL>  CREATE TABLE  anbob.test_tlob  ( "T1" VARCHAR2(2000) NOT NULL , "T2" CLOB, "T3" CLOB)  tablespace users
  2  ;

Table created.

SQL> @ddl anbob.test_tlob
PL/SQL procedure successfully completed.


DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
-----------------------------------------------------------------------------------

  CREATE TABLE "ANBOB"."TEST_TLOB"
   (    "T1" VARCHAR2(2000) NOT NULL ENABLE,
        "T2" CLOB,
        "T3" CLOB
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
 LOB ("T2") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES )
 LOB ("T3") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES ) ;

SQL> SHOW PARAMETER RETEN

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- --------------------------------------------------------------------------------------
..
undo_retention                                               integer     900

SQL> @pd SECUREFILE
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------
...
      2997   BB5 _sf_default_enabled                                      TRUE                           enable 12g securefile default
      2998   BB6 db_securefile                                            PREFERRED                      permit securefile storage during lob creation
...
30 rows selected.

SQL> @pvalid db_securefile
Display valid values for multioption parameters matching "db_securefile"...

  PAR# PARAMETER                                                 ORD VALUE                          DEFAULT
------ -------------------------------------------------- ---------- ------------------------------ -------
  2998 db_securefile                                               3 PERMITTED                      DEFAULT
       db_securefile                                               1 NEVER
       db_securefile                                               2 IGNORE
       db_securefile                                               4 ALWAYS
       db_securefile                                               5 PREFERRED
       db_securefile                                               6 FORCE

SQL> select owner,table_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner='ANBOB';

OWNER                          TABLE_NAME                     TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE IN_
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --- ------- --------------- ---
ANBOB                          TAB_LOB                        USERS                                8192                   900 NO  YES                     YES
ANBOB                          TAB_LOB                        USERS                               32768                   900 NO  YES                     YES
ANBOB                          TEST_TLOB                      USERS                                8192                       YES DEFAULT                 YES
ANBOB                          TEST_TLOB                      USERS                                8192                       YES DEFAULT                 YES

6 rows selected.

begin
for i in 1..10 loop
 insert into anbob.test_tlob values ('ABC',rpad('Z',32000,'L'),rpad('Z',32000,'L'));
 end loop;
 commit;
 end;
  7   /

PL/SQL procedure successfully completed.


SQL> @seg anbob.SYS_LOB0000079853

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- --
         1 ANBOB                SYS_LOB0000079853C00002$$                                     LOBSEGMENT           USERS                                 144
         1 ANBOB                SYS_LOB0000079853C00003$$                                     LOBSEGMENT           USERS                                 152

begin
for i in 1..10000 loop
 update anbob.test_tlob set t2= rpad('Z',32000,'X');
 end loop;
 commit;
 end;
  7   /

PL/SQL procedure successfully completed.

SQL> @seg anbob.SYS_LOB0000079853

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- --
         1 ANBOB                SYS_LOB0000079853C00003$$                                     LOBSEGMENT           USERS                                 152
       872 ANBOB                SYS_LOB0000079853C00002$$                                     LOBSEGMENT           USERS                              111632


SQL> r
  1* select dbms_lob.GETLENGTH(t2),dbms_lob.GETLENGTH(t3),t1 from anbob.test_tlob

DBMS_LOB.GETLENGTH(T2) DBMS_LOB.GETLENGTH(T3) T1
---------------------- ---------------------- ------------------------------
                  4000                   4000 ABC
                  4000                   4000 ABC
                  4000                   4000 ABC
                  4000                   4000 ABC
                  4000                   4000 ABC
                  4000                   4000 ABC
                  4000                   4000 ABC
                  4000                   4000 ABC
                  4000                   4000 ABC
                  4000                   4000 ABC

10 rows selected.

Note:
只是10条记录做了一些UPDATE, T2列4bytes宽度,但是lob段已达到872M, 因为lob 的undo 没有在undo tablespace而是在LOB 段自身,retention未明确指定时,依赖创建表时数据库系统参数undo_retention, 而后期修改undo_retention参数,LOB retention不会自动改变,需要手动alter修改。

SQL> ALTER system set undo_retention=1000;
System altered.

SQL> alter table anbob.test_tlob modify lob (t2) (retention);
Table altered.

SQL> select owner,table_name,column_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner=

OWNER      TABLE_NAME                     COLUMN_NAME                    TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI RETENTION_
---------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --- ------- ----------
ANBOB      TAB_LOB                        BLOB_8K                        USERS                                8192                   900 NO  YES
ANBOB      TAB_LOB                        BLOB_32K                       USERS                               32768                   900 NO  YES
ANBOB      TEST_TLOB                      T2                             USERS                                8192                       YES DEFAULT
ANBOB      TEST_TLOB                      T3                             USERS                                8192                       YES DEFAULT

6 rows selected.
SQL> alter table anbob.test_tlob modify lob (t2) (retention min 300);

Table altered.

SQL> select owner,table_name,column_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner='ANBOB';

OWNER      TABLE_NAME                     COLUMN_NAME                    TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE IN_
---------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --- ------- --------------- ---
ANBOB      TAB_LOB                        BLOB_8K                        USERS                                8192                   900 NO  YES                     YES
ANBOB      TAB_LOB                        BLOB_32K                       USERS                               32768                   900 NO  YES                     YES
ANBOB      TEST_TLOB                      T2                             USERS                                8192                       YES MIN                 300 YES
ANBOB      TEST_TLOB                      T3                             USERS                                8192                       YES DEFAULT                 YES

6 rows selected.


SQL> alter table anbob.tab_lob modify lob (blob_8k) (retention 300);
alter table anbob.tab_lob modify lob (blob_8k) (retention 300)
                                                          *
ERROR at line 1:
ORA-22853: invalid LOB storage option specification


SQL> alter table anbob.tab_lob modify lob (blob_8k) (retention min 300);
alter table anbob.tab_lob modify lob (blob_8k) (retention min 300)
*
ERROR at line 1:
ORA-43856: Unsupported LOB type for SECUREFILE LOB operation

SQL> alter table anbob.tab_lob modify lob (blob_8k) (retention);
Table altered.

SQL> select owner,table_name,column_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner='ANBOB';

OWNER      TABLE_NAME                     COLUMN_NAME                    TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE IN_
---------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --- ------- --------------- ---
ANBOB      TAB_LOB                        BLOB_8K                        USERS                                8192                  1000 NO  YES                     YES
ANBOB      TAB_LOB                        BLOB_32K                       USERS                               32768                   900 NO  YES                     YES
ANBOB      TEST_TLOB                      T2                             USERS                                8192                       YES MIN                 300 YES
ANBOB      TEST_TLOB                      T3                             USERS                                8192                       YES DEFAULT                 YES

6 rows selected.

SQL> alter table anbob.tab_lob modify lob (blob_8k) (PCTVERSION 50);

Table altered.

SQL> select owner,table_name,column_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner='ANBOB';

OWNER      TABLE_NAME                     COLUMN_NAME                    TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE IN_
---------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --- ------- --------------- ---
ANBOB      TAB_LOB                        BLOB_8K                        USERS                                8192         50            NO  NO                      YES
ANBOB      TAB_LOB                        BLOB_32K                       USERS                               32768                   900 NO  YES                     YES
ANBOB      TEST_TLOB                      T2                             USERS                                8192                       YES MIN                 300 YES
ANBOB      TEST_TLOB                      T3                             USERS                                8192                       YES DEFAULT                 YES

6 rows selected.

SQL> alter table anbob.test_tlob modify lob (t2) (PCTVERSION 20);

Table altered.

SQL> select owner,table_name,column_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner='ANBOB';

OWNER      TABLE_NAME                     COLUMN_NAME                    TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE IN_
---------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --- ------- --------------- ---
ANBOB      TAB_LOB                        BLOB_8K                        USERS                                8192         50            NO  NO                      YES
ANBOB      TAB_LOB                        BLOB_32K                       USERS                               32768                   900 NO  YES                     YES
ANBOB      TEST_TLOB                      T2                             USERS                                8192         10            YES MIN                 300 YES
ANBOB      TEST_TLOB                      T3                             USERS                                8192                       YES DEFAULT                 YES

6 rows selected.


select bitand(flags,32),OBJ#,COL# from sys.lob$ where OBJ#= (select OBJECT_ID from dba_objects where
  2  OWNER='ANBOB' and OBJECT_NAME='TEST_TLOB');

BITAND(FLAGS,32)       OBJ#       COL#
---------------- ---------- ----------
               0      79853          2
              32      79853          3

-- if the output is 0 then its PCTVERSION , if its 32 then its RETENTION

Note:
修改了undo_retention 参数后,需要alter table modify lob (retention)使retention生效,但是对于securefile LOB dba_lob.retention列不显示,需要查看RETENTION_TYPE,RETENTION_VALUE值,增加了min/max等值, 注意securefile LOB只能在ASSM管理的表空间, 如
alter table modify lob () (retention min );

与LOB UNDO相关的两个参数PCTVERSION和RETENTION
RETENTION – time-based: this specifies how long older versions are to be retained.
PCTVERSION – space-based: this specifies what percentage of the LOB segment is to be used to hold older versions.
— PCTVERSION=0: the space allocated for older versions of LOB data in LOBSEGMENTS can be reused by other transactions and can cause’snapshot too old? errors.
— PCTVERSION=100: the space allocated by older versions of LOB data can never be reused by other transactions. LOB data storage space is never reclaimed and it always increases.

不能同时指定PCTVERSION和RETENTION。
您可以指定PCTVERSION参数,无论数据库是在手动或自动撤销模式下运行。PCTVERSION是手动撤销模式下的缺省值。在自动撤销模式下,RETENTION是缺省值。只支持BASEFILE LOBS.
也可以指定 RETENTION参数,只有数据库运行在automatic undo mode.

从上面测试看SECUREFILES LOB改pctversion时未报错,改basefiles lob 的 retention min提示了不支持。 但是以MOS How to change retention of securefile Lob segment (Doc ID 2175438.1) 中明确pctversion不支持Securefiles LOB

This Note explains the retention property of securefile LOBs as we can only specify RETENTION parameter For SECUREFILE LOBs. Also note that you can specify either PCTVERSION or RETENTION for BASICFILE LOBs, but not both.

Securefiles cannot specify PCTVERSION parameter, Under 11g compatibility, this parameter is ignored when SecureFiles LOBs are created.

测试一下19c中的现象。

 CREATE TABLE  anbob.test_tlob  ( "T1" VARCHAR2(2000) NOT NULL , "T2" CLOB, "T3" CLOB)  tablespace users
    LOB ("T2")
    STORE AS  ( TABLESPACE "USERS" CHUNK 16K PCTVERSION 50 CACHE )
    LOB ("T3")
    STORE AS  ( TABLESPACE "USERS" CHUNK 16K PCTVERSION 50 CACHE );

begin
for i in 1..10 loop
 insert into anbob.test_tlob values ('ABC',rpad('Z',32000,'L'),rpad('Z',32000,'L'));
 end loop;
 commit;
 end;
 /

 SQL> @seg anbob.SYS_LOB0000079863C

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS                               HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- --                          -------- ----------
         1 ANBOB                SYS_LOB0000079863C00002$$                                     LOBSEGMENT           USERS                                 152                                   12       9017
         1 ANBOB                SYS_LOB0000079863C00003$$                                     LOBSEGMENT           USERS                                 152                                   12       9033

Elapsed: 00:00:00.98
SQL>  select owner,table_name,column_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner                          ='ANBOB';

OWNER                          TABLE_NAME           COLUMN_NAME                    TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI                           RETENTION_VALUE IN_
------------------------------ -------------------- ------------------------------ ------------------------------ ---------- ---------- ---------- --- -------                           --------------- ---
ANBOB                          TEST_TLOB            T2                             USERS                               16384         50            YES DEFAULT                                           YES
ANBOB                          TEST_TLOB            T3                             USERS                               16384         50            YES DEFAULT                                           YES

6 rows selected.

SQL> alter table anbob.test_tlob modify lob(t2) (PCTVERSION 0);
Table altered.

SQL>  select owner,table_name,column_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner='ANBOB';

OWNER                          TABLE_NAME           COLUMN_NAME                    TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE IN_
------------------------------ -------------------- ------------------------------ ------------------------------ ---------- ---------- ---------- --- ------- --------------- ---
ANBOB                          TEST_TLOB            T2                             USERS                               16384         50            YES DEFAULT                 YES
ANBOB                          TEST_TLOB            T3                             USERS                               16384         50            YES DEFAULT                 YES

SQL> alter table anbob.test_tlob modify lob(t2) (retention min 1500);
Table altered.
SQL> alter table anbob.test_tlob modify lob(t3) (retention min 1500);
Table altered.

SQL>  select owner,table_name,column_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner='ANBOB';

OWNER                          TABLE_NAME           COLUMN_NAME                    TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE IN_
------------------------------ -------------------- ------------------------------ ------------------------------ ---------- ---------- ---------- --- ------- --------------- ---
ANBOB                          TEST_TLOB            T2                             USERS                               16384                       YES MIN                1500 YES
ANBOB                          TEST_TLOB            T3                             USERS                               16384                       YES MIN                1500 YES

6 rows selected.

Elapsed: 00:00:00.16
SQL> alter table anbob.test_tlob modify lob(t2) (PCTVERSION 0);
Table altered.

Elapsed: 00:00:00.01
SQL>  select owner,table_name,column_name,TABLESPACE_NAME,CHUNK,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE,IN_ROW from dba_lobs where owner='ANBOB';

OWNER                          TABLE_NAME           COLUMN_NAME                    TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE IN_
------------------------------ -------------------- ------------------------------ ------------------------------ ---------- ---------- ---------- --- ------- --------------- ---
ANBOB                          TEST_TLOB            T2                             USERS                               16384         50            YES MIN                1500 YES
ANBOB                          TEST_TLOB            T3                             USERS                               16384                       YES MIN                1500 YES

SQL> @ddl anbob.test_tlob

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.95

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  CREATE TABLE "ANBOB"."TEST_TLOB"
   (    "T1" VARCHAR2(2000) NOT NULL ENABLE,
        "T2" CLOB,
        "T3" CLOB
   ) 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 "USERS"
 LOB ("T2") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384 RETENTION MIN 1500
  CACHE  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
 LOB ("T3") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384 RETENTION MIN 1500
  CACHE  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

begin
for i in 1..10000 loop
 update anbob.test_tlob set t2= rpad('Z',32000,'X');
 end loop;
 commit;
 end;
  7   /

PL/SQL procedure successfully completed.

Elapsed: 00:01:28.13
SQL> @seg anbob.SYS_LOB0000079863C

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
       872 ANBOB                SYS_LOB0000079863C00002$$                                     LOBSEGMENT           USERS                              111640         12       9017
         1 ANBOB                SYS_LOB0000079863C00003$$                                     LOBSEGMENT           USERS                                 152         12       9033

Elapsed: 00:00:01.20
SQL>

Note:
可见即使改PCTVERSION未报错也并未生效,还是创建时的属性,DDL 也并不是pctversion, 通过测试数据也并不是pctversion 0保存历史版本,而是使用了retention, lob segment有增加。

CHUNK 清理与扩展
在oracle11g前的版本有提到过配置44951 event, 表示lob在清理或扩展时在MSSM一次是128个chunk, 但是在ASSM一次是1个chunk, 所以会出现更多扩展相关的HW争用,需要手动配置44951 event level 指定一次操作的chunk数量。如:
alter system set events ‘44951 trace name context forever, level 1024’;

查看当前19c中basefile和securefile lob在ASSM表空间中未配置44951 event

-- basefile lob
SQL> select segment_type,EXTENT_ID,file_id,block_id,bytes/8192 blks from dba_extents where segment_name='SYS_LOB0000079303C00002$$' ;

SEGMENT_TYPE        EXTENT_ID    FILE_ID   BLOCK_ID       BLKS
------------------ ---------- ---------- ---------- ----------
LOBSEGMENT                  0         12      27224          8
LOBSEGMENT                  1         12      27504          8
LOBSEGMENT                  2         12      27640          8
LOBSEGMENT                  3         12      28920          8
LOBSEGMENT                  4         12      29168          8
LOBSEGMENT                  5         12      29176          8
LOBSEGMENT                  6         12      29672          8
LOBSEGMENT                  7         12      29680          8
LOBSEGMENT                  8         12      29688          8
LOBSEGMENT                  9         12      29776          8
LOBSEGMENT                 10         12      29784          8
LOBSEGMENT                 11         12      29792          8
LOBSEGMENT                 12         12      29800          8
LOBSEGMENT                 13         12      29808          8
LOBSEGMENT                 14         12      29816          8
LOBSEGMENT                 15         12      29888          8
LOBSEGMENT                 16         12      21376        128
LOBSEGMENT                 17         12      25472        128
LOBSEGMENT                 18         12      29312        128
LOBSEGMENT                 19         12      30976        128
LOBSEGMENT                 20         12      31744        128
LOBSEGMENT                 21         12      32512        128
LOBSEGMENT                 22         12      33408        128
..

-- securefiles lob
SQL> select segment_type,EXTENT_ID,file_id,block_id,bytes/8192 blks from dba_extents where segment_name='SYS_LOB0000079853C00002$$';

SEGMENT_TYPE        EXTENT_ID    FILE_ID   BLOCK_ID       BLKS
------------------ ---------- ---------- ---------- ----------
LOBSEGMENT                  0         12      24760         16
LOBSEGMENT                  1         12      24832        128
LOBSEGMENT                  2         12      25088        128
LOBSEGMENT                  3         12      25600        128
LOBSEGMENT                  4         12      25728        128
LOBSEGMENT                  5         12      25856        128
LOBSEGMENT                  6         12      25984        128
LOBSEGMENT                  7         12      26240        128
LOBSEGMENT                  8         12      26368        128
LOBSEGMENT                  9         12      39296       1024
LOBSEGMENT                 10         12      42240       1024
LOBSEGMENT                 11         12      43264       1024
LOBSEGMENT                 12         12      44288       1024
..
LOBSEGMENT                143         12     332288       8192
LOBSEGMENT                144         12     340480       4224
LOBSEGMENT                145         12     344704       8192
LOBSEGMENT                146         12     352896       4992
LOBSEGMENT                147         12     357888       8192
LOBSEGMENT                148         12     366080       5504
LOBSEGMENT                149         12     371584       8192
LOBSEGMENT                150         12     379776       6272
LOBSEGMENT                151         12     386048       8192


Note:
可见并不再需要配置44951 event.

SUMMARY:
对于lob 段update多的表,注意retention保留时长,如果过长当高并发时,会导致LOB 段膨胀,有点像postgresql 非原地更新一样,update隐式的insert多版本,产生enq: HW高水位争用和空间浪费。

打赏

,

对不起,这篇文章暂时关闭评论。