Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sachin.

Asked: April 07, 2017 - 8:20 am UTC

Last updated: April 27, 2017 - 6:24 am UTC

Version: 11gR2

Viewed 1000+ times

You Asked

Hello Tom,

We configured Table with BLOB Column with seperate TableSpace.


CREATE TABLE EV_LOG
   (    "ID" FLOAT(63),
        "MESSAGE_ID" VARCHAR2(128 BYTE),
        "EVENT_TYPE" VARCHAR2(128 BYTE) CONSTRAINT "SYS_C0014415" NOT NULL ENABLE,
        "CLASSIFIER" VARCHAR2(128 BYTE),
        "EVENT_TIME" TIMESTAMP (6) CONSTRAINT "SYS_C0014416" NOT NULL ENABLE,
        "EVENT_DATA" BLOB
   )
     LOB ("EVENT_DATA") STORE AS "EV_LOG_LOB"( TABLESPACE dwh_data04 DISABLE STORAGE IN ROW )
     PARTITION BY RANGE ("EVENT_TIME")
     INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
     STORE IN (dwh_data03, dwh_data02)
  (
   PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('01-01-2012', 'DD-MM-YYYY')),
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-02-2012', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('01-03-2012', 'DD-MM-YYYY'))
  );


We configured Scheduled Job to update BLOB Column to EMPTY, This Job runs every night and update 30 days old rows.

SET event_data = empty_blob()

Despite this we can see spike in tablespace usage, We have not 3month data and Every day it require 2-3 GB More space for inserting 0.4 to 0.42 millions of rows every day. 

  RUN_TIME   NAME            ALLOC_SIZE_GB CURR_USED_SIZE_GB PREV_USED_SIZE_GB VARIANCE
---------- --------------- ------------- ----------------- ----------------- --------------------
2017-03-29 dwh_data04           480            224.07            220.88 3.19
2017-03-30 dwh_data04           480            227.51            224.07 3.44
2017-03-31 dwh_data04           480            230.05            227.51 2.54
2017-04-01 dwh_data04           480            233.26            230.05 3.21
2017-04-02 dwh_data04           480            236.07            233.26 2.81
2017-04-03 dwh_data04           480             239.5            236.07 3.43
2017-04-04 dwh_data04           480            242.75             239.5 3.25
2017-04-05 dwh_data04           480            244.88            242.75 2.13


What could be the possible reason for space consumption, As per my understanding ORACLE should re-utilize empty space.

Regadrs,
Sachin

and Connor said...

We hang on to "older" lob data in order to provide read consistency. You have control over that with PCTVERSION and RETENTION settings. For example

SQL> create tablespace LOBS datafile 'C:\ORACLE\ORADATA\DB11\LOBS.DBF' size 50m autoextend on next 10m;

Tablespace created.

SQL> create table ev_log
  2     (    id float(63),
  3          event_time timestamp (6) constraint sys_c0014416 not null enable,
  4          event_data blob
  5     )
  6       lob (event_data) store as ev_log_lob( tablespace lobs disable storage in row )
  7     partition by range (event_time)
  8     interval(numtoyminterval(1, 'month'))
  9     store in (users)
 10    (
 11     partition pos_data_p1 values less than (to_date('01-01-2017', 'dd-mm-yyyy')),
 12     partition pos_data_p2 values less than (to_date('01-02-2017', 'dd-mm-yyyy')),
 13     partition pos_data_p3 values less than (to_date('01-03-2017', 'dd-mm-yyyy'))
 14    );

Table created.

SQL>
SQL> insert into ev_log
  2  select
  3    rownum,
  4    date '2017-02-28'-rownum/10,
  5    rpad('AB',4000,'AB')
  6  from dual
  7  connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
SYS_IL0000088709C00003$$       INDEX PARTITION         65536
SYS_IL0000088709C00003$$       INDEX PARTITION         65536
SYS_IL0000088709C00003$$       INDEX PARTITION         65536
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608

6 rows selected.

SQL>
SQL> update ev_log set event_data = empty_blob();

1000 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> insert into ev_log
  2  select
  3    rownum,
  4    date '2017-02-28'-rownum/10,
  5    rpad('AB',4000,'AB')
  6  from dual
  7  connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
SYS_IL0000088709C00003$$       INDEX PARTITION        131072
SYS_IL0000088709C00003$$       INDEX PARTITION        131072
SYS_IL0000088709C00003$$       INDEX PARTITION        196608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608

6 rows selected.

SQL>
SQL> update ev_log set event_data = empty_blob();

2000 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> insert into ev_log
  2  select
  3    rownum,
  4    date '2017-02-28'-rownum/10,
  5    rpad('AB',4000,'AB')
  6  from dual
  7  connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
SYS_IL0000088709C00003$$       INDEX PARTITION        196608
SYS_IL0000088709C00003$$       INDEX PARTITION        262144
SYS_IL0000088709C00003$$       INDEX PARTITION        327680
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION        16777216

6 rows selected.

SQL>
SQL> update ev_log set event_data = empty_blob();

3000 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
SYS_IL0000088709C00003$$       INDEX PARTITION        262144
SYS_IL0000088709C00003$$       INDEX PARTITION        327680
SYS_IL0000088709C00003$$       INDEX PARTITION        458752
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION        16777216

6 rows selected.

SQL>
SQL>
SQL>


So you can see the lob grew as we updated and inserted. Let's now tell the database that we want to hold on to *nothing*, ie, pctversion = 0 

SQL> create table ev_log
  2     (    id float(63),
  3          event_time timestamp (6) constraint sys_c0014416 not null enable,
  4          event_data blob
  5     )
  6       lob (event_data) store as ev_log_lob( tablespace lobs disable storage in row pctversion 0)
  7     partition by range (event_time)
  8     interval(numtoyminterval(1, 'month'))
  9     store in (users)
 10    (
 11     partition pos_data_p1 values less than (to_date('01-01-2017', 'dd-mm-yyyy')),
 12     partition pos_data_p2 values less than (to_date('01-02-2017', 'dd-mm-yyyy')),
 13     partition pos_data_p3 values less than (to_date('01-03-2017', 'dd-mm-yyyy'))
 14    );

Table created.

SQL> insert into ev_log
  2  select
  3    rownum,
  4    date '2017-02-28'-rownum/10,
  5    rpad('AB',4000,'AB')
  6  from dual
  7  connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
SYS_IL0000088697C00003$$       INDEX PARTITION         65536
SYS_IL0000088697C00003$$       INDEX PARTITION         65536
SYS_IL0000088697C00003$$       INDEX PARTITION         65536
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608

6 rows selected.

SQL>
SQL> update ev_log set event_data = empty_blob();

1000 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> insert into ev_log
  2  select
  3    rownum,
  4    date '2017-02-28'-rownum/10,
  5    rpad('AB',4000,'AB')
  6  from dual
  7  connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
SYS_IL0000088697C00003$$       INDEX PARTITION        131072
SYS_IL0000088697C00003$$       INDEX PARTITION        131072
SYS_IL0000088697C00003$$       INDEX PARTITION        196608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608

6 rows selected.

SQL>
SQL> update ev_log set event_data = empty_blob();

2000 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> insert into ev_log
  2  select
  3    rownum,
  4    date '2017-02-28'-rownum/10,
  5    rpad('AB',4000,'AB')
  6  from dual
  7  connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
SYS_IL0000088697C00003$$       INDEX PARTITION        196608
SYS_IL0000088697C00003$$       INDEX PARTITION        196608
SYS_IL0000088697C00003$$       INDEX PARTITION        262144
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608

6 rows selected.

SQL>
SQL> update ev_log set event_data = empty_blob();

3000 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
SYS_IL0000088697C00003$$       INDEX PARTITION        262144
SYS_IL0000088697C00003$$       INDEX PARTITION        262144
SYS_IL0000088697C00003$$       INDEX PARTITION        327680
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608
EV_LOG_LOB                     LOB PARTITION         8388608

6 rows selected.

SQL>
SQL>
SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for updates, it clears all my concerns

Sachin, April 26, 2017 - 8:47 am UTC

Hello ,

Thanks for the reply it worked and help me to understand.

I have added " ALTER TABLE EV_LOG MODIFY LOB (EVENT_DATA) (SHRINK SPACE);" to the code and it worked as expected.

SQL> insert into ev_log
    select
      rownum,
      date '2017-02-28'-rownum/10,
      rpad('AB',4000,'AB')
    from dual
    connect by level <= 1000;
1000 rows created.

SQL>
SQL> commit;
SQL> update ev_log set event_data = empty_blob();

4000 rows updated.

SQL> commit;

SQL> ALTER TABLE EV_LOG MODIFY LOB (EVENT_DATA)  (SHRINK SPACE);

SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

SYS_IL0000021808C00003$$                                                          LOBINDEX               720896
EV_LOG_LOB                                                                        LOBSEGMENT            9437184

Connor McDonald
April 27, 2017 - 6:24 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.