Why is my ORACLE tablespace growing so unreasonably fast?

I have problem in understanding why my table entries occupy so much space. My DB is growing unexpectedly fast.

In detail:

There is the following table DATA_SENSOR:

create tablespace DATA_SENSOR datafile size 100M autoextend on maxsize unlimited extent management local autoallocate;
create tablespace DATA_SENSOR_INDEX datafile size 100M autoextend on maxsize unlimited extent management local autoallocate;

CREATE TABLE "HESDBA"."DATA_SENSOR"
   (
    "SEQUENCE_NO" NUMBER(20,0),
    "NODE_ID" VARCHAR2(64 CHAR),
    "DATA_ID" VARCHAR2(256 CHAR),
    "TIMESTAMP" NUMBER(20,0),
    "DATA" BLOB
   )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS
  TABLESPACE "DATA_SENSOR"
PARTITION BY RANGE (TIMESTAMP) INTERVAL (2635200)
(PARTITION P1 VALUES LESS THAN (1640991600));

CREATE UNIQUE INDEX "HESDBA"."DATA_SENSOR_INDEX1" ON "HESDBA"."DATA_SENSOR" ("SEQUENCE_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "DATA_SENSOR_INDEX" ;

CREATE INDEX "HESDBA"."DATA_SENSOR_INDEX2" ON "HESDBA"."DATA_SENSOR" ("TIMESTAMP")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "DATA_SENSOR_INDEX" ;

Currently there are 18201908 records in the table. Executing

select num_rows*avg_row_len /1024/1024/1024  as tot_gb from tabs where table_name='DATA_SENSOR';

gives

tot_gb = 2.9

and

select avg_row_len,  num_rows  from tabs where table_name='DATA_SENSOR';

gives

Note that last analyzing time was couple of days ago, so instead of 18201908 entries there are 16831418.

However, much more space must be allocated for that table, because by executing

select df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
       round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
  from (select tablespace_name,
               round(sum(bytes) / 1048576) TotalSpace
          from dba_data_files 
         group by tablespace_name) df,
       (select round(sum(bytes)/(1024*1024)) totalusedspace,
               tablespace_name
          from dba_segments 
         group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name 
   and df.totalspace <> 0;

I get

So there are 99 GByte of data, related to that tablespace. This would give 5.4 Kbyte per record.

Another query gives a similar number:

However, the BLOB is in no case bigger than about 100 Bytes.

Question is: Why is the average used space per record so big? I would expect about 200 Bytes per record - instead according to the queries above I' ending up with 5.4 kByte. Where is this huge space usage going to???

A few things:

1. avg_row_size does not include out-of-line LOB segments. It only includes in-line LOB data, which is stored inside the data block.

2. avg_row_size also doesn't take into account all the additional overhead of a block, nor freespace extent bitmaps and other extent, segment and file-level overhead.

3. if pctfree is large, you may be only partially populating your blocks with data, requiring more blocks than you think you need

4. if your LOB definition is configured to disable in-row storage, even 100 byte values would require a chunk-size allocation from the LOB segment, where chunk is equal to (or greater) than your tablespace block size. So if that's 8192 bytes, you'll allocate something close to 8192 bytes just for that 100 byte value. Clearly you want to ensure that you are enabling in-row storage, which should be the default (see dba_lobs)

5. Even inline LOBs have a minimum space overhead of 32 bytes. Even if your LOB is only 1 byte in data length, it will use 33 bytes.

6. Your indexes require space too - the width of the key columns plus 10 bytes for a ROWID per row

7. You may have other tables sharing the same tablespace

8. If you delete rows and then insert new rows in direct path mode, it won't reuse the empty space in those old blocks that the deletes opened up - it will allocate above the high water mark causing your segment to be much larger than it needs to be.

9. If your minimum extent size is large and you load direct path with small # of rows per insert, it may allocate a lot more space per insert operation than is needed.

10. Your table is partitioned. By default the minimum extent size for partitioned tables and indexes is 8MB. If your partitions are very narrow and there's a small # of rows loaded in each, you could be wasting a lot of space there as well. You can change this behavior with a parameter override.

So, lots of things could be happening. You really can't directly extrapolate from statistics' avg_row_size to how much space should be used in your tablespace. Oracle's space management is simply too complex to do that. Add in compression and it becomes nearly impossible to get from statistics avg_row_size to actual disk usage.

Recommendation: query dba_segments for your tablespace without aggregating, and see what segments are holding how much space. That will isolate it to the LOB segment, one of the indexes, or your table partitions (or some other table entirely). Then drill down. If it's the table partition, try a reorg alter table hesdba.data_sensor move partition p1 parallel (degree 8) , etc.. for each partition to make sure you don't have wasted space. Check dba_lobs (use segment_name to link it to dba_segments) to ensure in-row storage is enabled. If all else fails, do a block dump of a data block from the middle of an extent from one your table partitions and examine it to see exactly how block space is being used (advanced).