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高水位争用和空间浪费。
对不起,这篇文章暂时关闭评论。