0

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.

Improve this question
4
  • You can use dbms_rowid to calculate the skew in rows per block. That will probably lead you to discover more of the STORAGE clause and how it affects the thing you care about. Depending on the usage of the application that accesses the table, having a densely packed table can have unfavorable consequences. People sometimes think only of the favorable ones.
    – Jeff Holt
    Feb 27, 2020 at 14:08
  • 1
    Goorgle for "reorganize table". This could happen after large inserts followed by large deletes.
    – Marmite Bomber
    Feb 27, 2020 at 14:20
  • 2
    Do you have more than one table in that schema? if so, it looks like your query is pulling back segments for all tables, not just one table.
    – Boneist
    Feb 27, 2020 at 15:28
  • @jeffholt, thank you for the advise. I found out the apparent reason and a solution. I will write an answer with that.
    – Carlos Eugenio Thompson Pinzón
    Feb 27, 2020 at 22:41

1 Answer 1

Reset to default
0

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.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.