RE: Oracle 12c extended VARCHAR - LOB segment grows indefinitely

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "thomas.kellerer@xxxxxxxxxx" <thomas.kellerer@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 25 Jan 2016 13:21:17 +0000


A CLOB column will have the same problem. I think I came across this problem 
quite a long time ago so it may be logged as a bug somewhere.
You could try to create the table in a tablespace that uses freelist management 
- there's still some space wastage on the delete/insert cycle, but it's not the 
catastrophe you get from ASSM.
If you can't find it as a bug on MoS raise it.


If you're licensed for the partition option you could avoid the growth and the 
down-time by adding in a step to recreate the table:
(I haven't tested this with a CLOB/extended varchar2 in place):

Create the table as a hash partitioned table with one partition 
Create a non-partitioned clone
Do your delete/insert
truncate the clone
insert append from the partition to the clone
exchange partition with table - online (12c feature).



Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle

________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf 
of Thomas Kellerer [thomas.kellerer@xxxxxxxxxx]
Sent: 25 January 2016 08:34
To: oracle-l@xxxxxxxxxxxxx
Subject: Oracle 12c extended VARCHAR - LOB segment grows indefinitely

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


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


Other related posts: