Hello,
we have an application that was migrated to Oracle 12 and we make
use of the new extended VARCHAR feature[1].
One of our tables undergoes a flush and fill procedure on a regular basis
where the delete and insert is done in one transaction.
However it seems that when doing this, it seems that Oracle doesn't cleanup
old data from the LOB segment that is associated with an "extended VARCHAR"
column.
The following script can be used to reproduce this problem:
Table setup:
create table blob_test (id integer primary key, content varchar2(32000));
To simulate our daily flush and fill process, run the following script:
begin
for i in 1..10 loop
delete from blob_test;
insert into blob_test (id, content)
select level, rpad('*', dbms_random.value(50,30000))
from dual
connect by level <= 10000;
commit;
end loop;
end;
/
Note that in real life, the transaction doing this is much bigger (it involves
several tables)
It also doesn't matter how much time lies between the individual transactions,
this typically
happens once a day, and during that time the table is only read, never written.
To check the allocated space vs. the actual space I use the following statement:
select 'real size' as what,
sum(vsize(content)) / 1024 / 1024 as size_mb
from blob_test
union all
select 'size on disk',
sum(bytes) / 1024 / 1024
from user_segments
where segment_name = (select segment_name from user_lobs where table_Name =
'BLOB_TEST');
and this is what I get:
WHAT | SIZE_MB
-------------+--------
real size | 141.77
size on disk | 1984.19
So the segment grew to nearl 2GB even though the actual space needed is less
then 150MB.
When committing the delete _before_ running the insert, the space is properly
released by Oracle.
But as the table is used (queried) during this process, we cannot do that in
two separate transactions.
Oracle won't let me alter the options for the implicit LOB segment associated
with an
extended VARCHAR column, so I can't change e.g. the retention property.
The problem is, that the tablespace where the data ist stored is growing and
growing.
The only solution we found so far, was to take the application offline, then do
the
delete all data and commit that right away, then re-insert the data.
But this is only a temporary workaround. If we can't fix this behaviour we will
have
to use a CLOB column instead, which we would like to avoid because the read
performance
of an extended VARCHAR is indeed much better than for a CLOB column.
Any other ideas?
Thomas
[1] http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO09739
--
//www.freelists.org/webpage/oracle-l
