Oracle table is occupying too much space in disk

oracle

I have a simple table in Oracle. The table has 1 million records, with an average record size of 200 bytes. Adding column definition sizes, a record shall not have over 750 bytes.

According to a simple calculation the data in the table is around 200 MB, and a reserved space of 750 MB. However the table is using 102 segments of 100 MB each, for a total size of near 10 GB, according to the following query:

select segment_name, segment_type, tablespace_name, bytes/1024/1024 as size_in_mb, bytes, extents
  from user_segments
 where segment_name in (select table_name from all_tables where owner=user);

So, I have two questions:

  1. How can I explain the oversize. The table has no indexes, or anything that should add content to the table. The table was filled from an Informatica ETL process using bulk mode.
  2. How can I get rid of oversize.

I do not have DBM privileges on the database.

Best Answer

The culprit was Informatica bulk mode insertion and the segment configuration in Oracle.

This is what I found out, by analyzing the problem with dbms_rowid:

  • Informatica bulk mode insert data in blocks of 10,000 rows. My over 1 million records required 101 such blocks.
  • Informatica writes a block of rows in one segment and the next block in the next segment.
  • Segments were configured to 100 MB. 101 segments were 10 GB.
  • Each 100 MB segment consist of 12,800 blocks of 8 KB. Each block could accommodate between 29 and 33 records. 10,000 records needed nearly 323 blocks, give or take. So there was 12,477 unused blocks in each segment.

So, the possible solutions are:

  • Configure Informatica for normal mode insertion. It inserts a record letting Oracle handling the insertion. There is supposed to be slower but it is not significantly slower, even for over 1 million records.
  • Configure Informatica to insert larger blocks. I haven't try it and I am not sure it is possible.
  • Configure a smaller segment size in Oracle. For this particular table, a segment size of 3 MB or 4 MB might have been enough.
Related Question