And in case of LOB "buffer busy waits" and "latch: cache buffers chains", we list the type of LOB data blocks in buffer cache.
Note: Tested in Oraclw 19.25 with following UNDO parameters:
temp_undo_enabled boolean TRUE
undo_management string AUTO
undo_retention integer 3600
1. Test Run
Run following test to insert 1000 rows (TEST_SEQ is cycle with maxvalue 1000), each row has one CLOB of 1MB, so total is about 1000MB.
-- see appended Test Code
truncate table test_tab_lob;
set serveroutput on size unlimited
alter sequence test_seq_1000 restart start with 1;
exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
-- 10000 inserts for total 1000 rows, each row is repeatedly overwritten.
exec test_lob_proc(1, 1000, 1e4);
exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
select round(sum(dbms_lob.getlength(mylob))/1024/1024, 2) LOB_MB, round(sum(length(mylob))/1024/1024, 2) MB
,count(id), min(id), max(id), min(ts), max(ts) from test_tab_lob;
SQL > exec test_lob_proc(1, 1000, 1e4);
Each mylob Length = 1056000, SUM Size (MB) =10000
SUM Size (MB) =10000, Real Contained Size (MB, due to TEST_SEQ_1000.Max Limit) = 1000
Elapsed: 00:08:02.09
SQL > exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
===========================================================================
segment_size_blocks = 1301880
used_blocks = 145718 (11.19 %)
expired_blocks = 1156162 (88.81 %)
unexpired_blocks = 0 (0 %)
===========================================================================
Segment Blocks/Bytes = 1301880 / 10665000960 (10170.94 MB)
Unused Blocks/Bytes = 0 / 0 (0 %)
Used Blocks/Bytes = 145718 / 1193721856 (11.19 %)
Expired Blocks/Bytes = 1156162 / 9471279104 (88.81 %)
Unexpired Blocks/Bytes = 0 / 0 (0 %)
===========================================================================
NON Data Blocks = 1156162 (88.81 %)
NON_data_blocks_2 (= segment_size_blocks - used_blocks) = 1156162
===========================================================================
LOBSEGMENT DBA_EXTENTS storage size Blocks = 1301880, CNT = 280, MIN_Blocks = 120, MAX_Blocks = 8192
LOBSEGMENT DBA_EXTENTS storage size Byets = 10665000960 (10170.94 MB)
LOBSEGMENT DBA_SEGMENTS storage size Blocks = 1301880
LOBSEGMENT DBA_SEGMENTS storage size Byets = 10665000960 (10170.94 MB)
SQL > select round(sum(dbms_lob.getlength(mylob))/1024/1024, 2) LOB_MB, round(sum(length(mylob))/1024/1024, 2) MB
,count(id), min(id), max(id), min(ts), max(ts) from test_tab_lob;
LOB_MB MB COUNT(ID) MIN(ID) MAX(ID) MIN(TS) MAX(TS)
------- ---------- ---------- -------- -------- -------------------- --------------------
1007.08 1007.08 1000 1 1000 17-FEB-2025 11:14:27 17-FEB-2025 11:15:15
By the way, previous Blog: "LOB ORA-22924: snapshot too old and Fix" (http://ksun-oracle.blogspot.com/2019/04/lob-ora-22924-snapshot-too-old-and-fix.html) showed the special behaviour of LOB snapshot too old.
We can also run a parallel job sessions to show 'buffer busy waits' / 'latch: cache buffers chains' and the contention data block CLASS#.
Run job test by:
--exec clearup_test;
truncate table test_tab_lob;
alter sequence test_seq_1000 restart start with 1;
exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
exec test_lob_proc_job(16, 1000, 1e3);
-- Wait till all Jobs finished
select count(*) from dba_scheduler_jobs where job_name like '%TEST_JOB%';
-- Show space usage
exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
select program, event, module, action, event, p1, p2, p3, p1text, p2text, p3text, t.*
from v$active_session_history t
where sample_time > sysdate -10/1440 and action like 'TEST_JOB%'
and event in ('buffer busy waits', 'latch: cache buffers chains')
order by t.sample_time desc;
With following query, we can display the type of data blocks in buffer cache:
with sq as (select /*+ materialize */ segment_name, index_name from dba_lobs where owner='K' and table_name='TEST_TAB_LOB'),
obj as (select /*+ materialize */ object_name||', '||object_type LOB_INFO, object_id, data_object_id
from dba_objects t, sq where object_name = 'TEST_TAB_LOB' or object_name = sq.segment_name or object_name = sq.index_name)
select LOB_INFO, objd, class#, status, dirty, file#, count(block#), count(distinct block#) from v$bh, obj
where objd = obj.data_object_id
group by LOB_INFO, objd, class#, status, dirty, file# order by 1, 6 desc, 2, 3, 4, 5 desc;
LOB_INFO OBJD CLASS# STATUS D FILE# COUNT(BLOCK#) COUNT(DISTINCTBLOCK#)
--------------------------------- ---------- ---------- ---------- - ---------- ------------- ---------------------
SYS_IL0005743559C00005$$, INDEX 5743561 1 xcur N 1625 1 1
SYS_IL0005743559C00005$$, INDEX 5743561 4 xcur N 1625 1 1
SYS_IL0005743559C00005$$, INDEX 5743561 8 xcur N 1625 1 1
SYS_LOB0005743559C00005$$, LOB 5743564 4 cr N 1625 5 1
SYS_LOB0005743559C00005$$, LOB 5743564 4 xcur Y 1625 1 1
SYS_LOB0005743559C00005$$, LOB 5743564 6 cr N 1625 205 46
SYS_LOB0005743559C00005$$, LOB 5743564 6 xcur Y 1625 56 56
SYS_LOB0005743559C00005$$, LOB 5743564 8 cr N 1625 34 9
SYS_LOB0005743559C00005$$, LOB 5743564 8 xcur Y 1625 9 9
SYS_LOB0005743559C00005$$, LOB 5743564 9 cr N 1625 424 106
SYS_LOB0005743559C00005$$, LOB 5743564 9 xcur Y 1625 126 126
SYS_LOB0005743559C00005$$, LOB 5743564 9 xcur N 1625 59 59
SYS_LOB0005743559C00005$$, LOB 5743564 10 cr N 1625 115 26
SYS_LOB0005743559C00005$$, LOB 5743564 10 xcur Y 1625 50 50
SYS_LOB0005743559C00005$$, LOB 5743564 12 xcur N 1625 1271 1271
TEST_TAB_LOB, TABLE 5743565 1 cr N 1625 114 28
TEST_TAB_LOB, TABLE 5743565 1 xcur Y 1625 28 28
TEST_TAB_LOB, TABLE 5743565 4 cr N 1625 1 1
TEST_TAB_LOB, TABLE 5743565 4 xcur Y 1625 1 1
TEST_TAB_LOB, TABLE 5743565 8 xcur Y 1625 2 2
TEST_TAB_LOB, TABLE 5743565 9 xcur Y 1625 1 1
21 rows selected.
We can also dump the segment header and bitmap blocks to reveal space distribution.
with sq as (select /*+ materialize */ segment_name, index_name from dba_lobs where owner='K' and table_name='TEST_TAB_LOB')
select t.segment_name, tablespace_name, header_file, relative_fno, header_block, bytes, blocks, extents from dba_segments t, sq
where t.segment_name = sq.segment_name or t.segment_name = sq.index_name;
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE RELATIVE_FNO HEADER_BLOCK BYTES BLOCKS EXTENTS
-------------------------- ---------------- ----------- ------------ ------------ ---------- ---------- ----------
SYS_LOB0005743559C00005$$ TEST_TBS 1625 1024 262153 1.0661E+10 1301376 276
SYS_IL0005743559C00005$$ TEST_TBS 1625 1024 262274 65536 8 1
alter session set max_dump_file_size = UNLIMITED;
alter session set tracefile_identifier = 'TEST_TAB_LOB_SEGMENT_DUMP_3';
EXECUTE DBMS_SPACE_ADMIN.SEGMENT_DUMP('TEST_TBS', 1024, 262153);
-- Dump DDL
select dbms_metadata.get_ddl('TABLE', 'TEST_TAB_LOB', 'KS') from dual;
select * from v$bh where objd=5743591 and class#=8 and status='xcur';
-- 1625 262159 8 xcur
alter session set max_dump_file_size = UNLIMITED;
alter session set tracefile_identifier = 'bmb_1_262159_dump';
alter system dump datafile 1625 block 262159;
select t.column_name, t.retention_type, retention_value, s.segment_name, s.tablespace_name, s.header_file, s.relative_fno, s.header_block--, t.*
from dba_lobs t, dba_segments s where t.owner='K' and t.table_name='TEST_TAB_LOB' and t.segment_name = s.segment_name;
COLUMN_NAME RETENTION_TYPE RETENTION_VALUE SEGMENT_NAME TABLESPACE_NAME HEADER_FILE RELATIVE_FNO HEADER_BLOCK
------------ --------------- --------------- -------------------------- ----------------- ----------- ------------ ------------
MYLOB DEFAULT SYS_LOB0005743559C00005$$ TEST_TBS 1625 1024 262153
alter table TEST_TAB_LOB modify lob(mylob) (retention none);
SQL > select t.column_name, t.retention_type, retention_value, s.segment_name, s.tablespace_name, s.header_file, s.relative_fno, s.header_block--, t.*
from dba_lobs t, dba_segments s where t.owner='K' and t.table_name='TEST_TAB_LOB' and t.segment_name = s.segment_name;
COLUMN_NAME RETENTION_TYPE RETENTION_VALUE SEGMENT_NAME TABLESPACE_NAME HEADER_FILE RELATIVE_FNO HEADER_BLOCK
------------ --------------- --------------- -------------------------- ----------------- ----------- ------------ ------------
MYLOB NONE SYS_LOB0005743559C00005$$ TEST_TBS 1625 1024 262153
SQL > exec test_lob_proc(1, 1000, 1e4);
Each mylob Length = 1056000, SUM Size (MB) =10000
SUM Size (MB) =10000, Real Contained Size (MB, due to TEST_SEQ_1000.Max Limit) = 1000
Elapsed: 00:08:02.12
SQL > exec check_space_securefile_2('K', 'TEST_TAB_LOB', 'MYLOB');
===========================================================================
segment_size_blocks = 163960
used_blocks = 144372 (88.05 %)
expired_blocks = 19588 (11.95 %)
unexpired_blocks = 0 (0 %)
===========================================================================
Segment Blocks/Bytes = 163960 / 1343160320 (1280.94 MB)
Unused Blocks/Bytes = 0 / 0 (0 %)
Used Blocks/Bytes = 144372 / 1182695424 (88.05 %)
Expired Blocks/Bytes = 19588 / 160464896 (11.95 %)
Unexpired Blocks/Bytes = 0 / 0 (0 %)
===========================================================================
NON Data Blocks = 19588 (11.95 %)
NON_data_blocks_2 (= segment_size_blocks - used_blocks) = 19588
===========================================================================
LOBSEGMENT DBA_EXTENTS storage size Blocks = 163960, CNT = 140, MIN_Blocks = 120, MAX_Blocks = 8192
LOBSEGMENT DBA_EXTENTS storage size Byets = 1343160320 (1280.94 MB)
LOBSEGMENT DBA_SEGMENTS storage size Blocks = 163960
LOBSEGMENT DBA_SEGMENTS storage size Byets = 1343160320 (1280.94 MB)
SQL > select round(sum(dbms_lob.getlength(mylob))/1024/1024, 2) LOB_MB, round(sum(length(mylob))/1024/1024, 2) MB
,count(id), min(id), max(id), min(ts), max(ts) from test_tab_lob;
LOB_MB MB COUNT(ID) MIN(ID) MAX(ID) MIN(TS) MAX(TS)
------- ---------- ---------- ---------- ---------- --------------------- --------------------
1007.08 1007.08 1000 1 1000 17-FEB-2025 11:41:04 17-FEB-2025 11:41:52
Strangely, once RETENTION_TYPE is set to NONE, setting back to DEFAULT gets error:
SQL > alter table TEST_TAB_LOB modify lob(mylob) (retention default);
alter table TEST_TAB_LOB modify lob(mylob) (retention default)
*
ERROR at line 1:
ORA-22853: invalid LOB storage option specification
select header_file, relative_fno, header_block, t.* from dba_segments t where segment_name = 'SYS_LOB0005742851C00004$$';
--1625 137
select lists
,decode(s.lists, 0, 'NONE', 1, 'AUTO',
2, 'MIN', 3, 'MAX',
4, 'DEFAULT', 'INVALID') ora_retention_type
,(select retention_type from dba_lobs t where table_name='TEST_TAB_LOB') dba_retention_type
,s.*
from sys.seg$ s where file# = 1024 and block# = 262153; -- lists stores RETENTION
--4 DEFAULT DEFAULT 1024 262153
--0 NONE NONE 1024 262153
update sys.seg$ set lists= 4 where file# = 1024 and block# = 262153; -- set RETENTION_TYPE to DEFAULT as DBA
commit;
2. Test Setup
drop tablespace test_tbs including contents and datafiles;
create bigfile tablespace test_tbs datafile '/oratestdb/oradata/testdb/test_tbs.dbf' size 10G online;
drop table test_tab_lob cascade constraints;
create table test_tab_lob(id number, cnt number, ts timestamp with local time zone default localtimestamp, lob_state varchar2(20), mylob clob)
segment creation immediate tablespace test_tbs
lob (mylob) store as securefile (
tablespace test_tbs enable storage in row chunk 8192 retention
nocache logging compress medium keep_duplicates
storage(initial 65536 next 131072 minextents 1 maxextents 2147483645
pctincrease 0 freelists 1 freelist groups 1
buffer_pool default flash_cache default cell_flash_cache default)) enable row movement;
drop sequence test_seq_1000;
create sequence test_seq_1000 start with 1 maxvalue 1000 minvalue 1 cycle cache 10 global;
3. Test Code
create or replace procedure test_lob_proc(p_job_nr number, p_kb_cnt number, p_loop_cnt number) as
l_mylob clob;
l_src_mylob clob;
l_lob_32kb clob := lpad('abc', 32000, 'x');
l_seq number;
l_ts_text varchar2(50);
begin
l_ts_text := to_char(localtimestamp, 'YYYY*MON*DD-HH24:MI:SS.FF9');
l_lob_32kb := dbms_random.string('p', 4000);
--CLOB stored in AL16UTF16 (wo bytes) in AL32UTF8 database. Use dbms_random.string due to CLOB compress
for i in 1..7 loop
dbms_lob.append(dest_lob => l_lob_32kb, src_lob => dbms_random.string('p', 4000));
end loop;
l_src_mylob := l_lob_32kb;
if p_kb_cnt >= 32 then
for i in 1..round(p_kb_cnt/32)+1 loop
dbms_lob.append(l_src_mylob, l_lob_32kb);
end loop;
else
l_src_mylob := dbms_lob.substr(l_lob_32kb, p_kb_cnt*1000, 1);
end if;
dbms_output.put_line('Each mylob Length = '||dbms_lob.getlength(l_src_mylob) ||', SUM Size (MB) ='|| (p_loop_cnt*p_kb_cnt/1000));
dbms_output.put_line('SUM Size (MB) ='|| (p_loop_cnt*p_kb_cnt/1000) ||', Real Contained Size (MB, due to TEST_SEQ_1000.Max Limit) = '|| (1000*p_kb_cnt/1000));
for k in 1..p_loop_cnt loop
l_seq := test_seq_1000.nextval;
merge
into test_tab_lob l
using (select l_seq id, 1 cnt, localtimestamp ts, 'pending' lob_state, empty_clob() mylob from dual) v
on (l.id = v.id)
when matched then
update set cnt = v.cnt + 1, mylob = v.mylob
when not matched then
insert (id, cnt, ts, mylob) values (l_seq, 1, localtimestamp, v.mylob);
--select mylob into l_mylob from test_tab_lob where id = l_seq;
--dbms_lob.append(dest_lob => l_mylob, src_lob => l_src_mylob);
-- @TODO First commit more important table scalar columns (fast by LGWR and DBWR) to make them visible, and then mise-a-jour LOB column.
commit;
update test_tab_lob set lob_state = 'updated', mylob = l_src_mylob where id = l_seq;
commit;
if k <= 10 then
dbms_output.put_line('Create id = '||l_seq||', mylob Length = '||dbms_lob.getlength(l_src_mylob));
end if;
if k = 10 then
dbms_output.put_line('Create id: ........ only print first 10 id');
end if;
end loop;
end;
/
-- exec test_lob_proc(1, 1000*3, 10); -- insert 10 rows with mylob length=3MB
-------------------- create test jobs ----------------------
create or replace procedure test_lob_proc_job(p_job_cnt number, p_kb_cnt number, p_loop_cnt number) as
l_job_name varchar2(50);
begin
for i in 1..p_job_cnt loop
l_job_name := 'TEST_JOB_ENQ_'||i;
dbms_scheduler.create_job (
job_name => l_job_name,
job_type => 'PLSQL_BLOCK',
job_action =>
'begin
test_lob_proc('||i||', '||p_kb_cnt||', '||p_loop_cnt||');
end;',
start_date => systimestamp,
--repeat_interval => 'systimestamp',
auto_drop => true,
enabled => true);
end loop;
end;
/
4. LOB Space Usage
------------------------------------ SECUREFILE LOB space usage ------------------------------------
-- MOS: How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized? (Doc ID 1453350.1)
CREATE OR REPLACE PROCEDURE check_space_securefile (u_name in varchar2, v_segname varchar2) IS
l_segment_size_blocks NUMBER;
l_segment_size_bytes NUMBER;
l_used_blocks NUMBER;
l_used_bytes NUMBER;
l_expired_blocks NUMBER;
l_expired_bytes NUMBER;
l_unexpired_blocks NUMBER;
l_unexpired_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_non_data_blocks NUMBER;
l_non_data_bytes NUMBER;
l_non_data_blocks_2 NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE( segment_owner =>u_name, --segment_owner IN VARCHAR2,
segment_name => v_segname, --segment_name IN VARCHAR2,
segment_type => 'LOB', --segment_type IN VARCHAR2,
suoption => DBMS_SPACE.SPACEUSAGE_EXACT, -- or DBMS_SPACE.SPACEUSAGE_FAST
segment_size_blocks => l_segment_size_blocks, --segment_size_blocks OUT NUMBER,
segment_size_bytes => l_segment_size_bytes, --segment_size_bytes OUT NUMBER,
used_blocks => l_used_blocks, --used_blocks OUT NUMBER,
used_bytes => l_used_bytes, --used_bytes OUT NUMBER,
expired_blocks => l_expired_blocks, --expired_blocks OUT NUMBER,
expired_bytes => l_expired_bytes, --expired_bytes OUT NUMBER,
unexpired_blocks => l_unexpired_blocks, --unexpired_blocks OUT NUMBER,
unexpired_bytes => l_unexpired_bytes --unexpired_bytes OUT NUMBER,
--partition_name IN VARCHAR2 DEFAULT NULL
);
DBMS_OUTPUT.PUT_LINE('===========================================================================');
DBMS_OUTPUT.PUT_LINE('segment_size_blocks = '||l_segment_size_blocks);
DBMS_OUTPUT.PUT_LINE('used_blocks = '||l_used_blocks ||' ('||(round(100*l_used_blocks/l_segment_size_blocks, 2))||' %)');
DBMS_OUTPUT.PUT_LINE('expired_blocks = '||l_expired_blocks ||' ('||(round(100*l_expired_blocks/l_segment_size_blocks, 2))||' %)');
DBMS_OUTPUT.PUT_LINE('unexpired_blocks = '||l_unexpired_blocks||' ('||(round(100*l_unexpired_blocks/l_segment_size_blocks, 2))||' %)');
DBMS_OUTPUT.PUT_LINE('===========================================================================');
l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);
l_unused_bytes := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);
l_non_data_blocks := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;
l_non_data_blocks_2 := l_segment_size_blocks - l_used_blocks;
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('Segment Blocks/Bytes = '||l_segment_size_blocks||' / '||l_segment_size_bytes||' ('||round(l_segment_size_bytes/1024/1024, 2) ||' MB)');
DBMS_OUTPUT.PUT_LINE('Unused Blocks/Bytes = '||l_unused_blocks ||' / '||l_unused_bytes ||' ('||(round(100*l_unused_blocks/l_segment_size_blocks, 2))||' %)');
DBMS_OUTPUT.PUT_LINE('Used Blocks/Bytes = '||l_used_blocks ||' / '||l_used_bytes ||' ('||(round(100*l_used_blocks/l_segment_size_blocks, 2))||' %)');
DBMS_OUTPUT.PUT_LINE('Expired Blocks/Bytes = '||l_expired_blocks ||' / '||l_expired_bytes ||' ('||(round(100*l_expired_blocks/l_segment_size_blocks, 2))||' %)');
DBMS_OUTPUT.PUT_LINE('Unexpired Blocks/Bytes = '||l_unexpired_blocks ||' / '||l_unexpired_bytes ||' ('||(round(100*l_unexpired_blocks/l_segment_size_blocks, 2))||' %)');
DBMS_OUTPUT.PUT_LINE('===========================================================================');
DBMS_OUTPUT.PUT_LINE('NON Data Blocks = '||l_non_data_blocks||' ('||(round(100*l_non_data_blocks/l_segment_size_blocks, 2))||' %)');
DBMS_OUTPUT.PUT_LINE('NON_data_blocks_2 (= segment_size_blocks - used_blocks) = '||l_non_data_blocks_2);
DBMS_OUTPUT.PUT_LINE('===========================================================================');
-- Determine the storage size of the LOBSEGMENT
for c in (SELECT round(SUM(BYTES)/8192) blocks, SUM(BYTES) bytes, count(*) cnt, min(blocks) MIN_Blocks, max(blocks) MAX_Blocks FROM DBA_EXTENTS WHERE SEGMENT_NAME = v_segname) loop
DBMS_OUTPUT.PUT_LINE('LOBSEGMENT DBA_EXTENTS storage size Blocks = '||c.blocks||', CNT = ' ||c.cnt ||', MIN_Blocks = '||c.MIN_Blocks ||', MAX_Blocks = '||c.MAX_Blocks);
DBMS_OUTPUT.PUT_LINE('LOBSEGMENT DBA_EXTENTS storage size Byets = '||c.bytes ||' ('||round(c.bytes/1024/1024, 2) ||' MB)');
end loop;
for c in (SELECT round(SUM(BYTES)/8192) blocks, SUM(BYTES) bytes FROM DBA_SEGMENTS WHERE SEGMENT_NAME = v_segname) loop
DBMS_OUTPUT.PUT_LINE('LOBSEGMENT DBA_SEGMENTS storage size Blocks = '||c.blocks);
DBMS_OUTPUT.PUT_LINE('LOBSEGMENT DBA_SEGMENTS storage size Byets = '||c.bytes ||' ('||round(c.bytes/1024/1024, 2) ||' MB)');
end loop;
END;
/
CREATE OR REPLACE PROCEDURE check_space_securefile_2 (v_owner_name in varchar2, v_table_name varchar2, v_column_name varchar2) IS
l_lob_segment_name varchar2(100);
begin
select segment_name into l_lob_segment_name from dba_lobs where owner=v_owner_name and table_name=v_table_name and column_name=v_column_name;
check_space_securefile(u_name=>v_owner_name, v_segname=>l_lob_segment_name);
end;
/
