Why is my table size nearly 10 times bigger than expected from the avg_row_len?

Question

In my Oracle database, I have a table with a size of 3.44 GB. It's 1784486 rows, and 450146, which corresponds to a 8 kB block size, and an average of 4 rows per block, or 2 kB per row. But the AVG_ROW_LEN is only 369 bytes. What's causing the discrepency?

More details:

The PCT_FREE is only 10, so that shouldn't be responsible. Compression is currently turned off. The table layout is (column names generalized)

KEY            NUMBER(38)           NOT NULL,
DATE1          DATE                 NOT NULL,
DATE2          DATE                 NOT NULL,
POINT          NUMBER(4)            NOT NULL,
NAME           VARCHAR2(200 BYTE)   NOT NULL,
BLOB_SIZE      NUMBER(38)           NOT NULL,
BLOB_TYPE      VARCHAR2(8 BYTE)     NOT NULL,
BLOB_FILTERS   VARCHAR2(64 BYTE)    NOT NULL,
BLOB_DATA      BLOB                 NOT NULL,
PRECOMPUTED    RAW(2000)    -- currently no more than ~200 bytes
                            -- (15 doubles, plus some headers)

Answer 1

OK. Let's start by figuring out where TOAD is getting the numbers you're looking at

What does

SELECT sum(bytes)/1024/1024/1024 size_in_gb,
       sum(blocks) size_in_blocks
  FROM dba_segments
 WHERE owner = <<owner of table>>
   AND segment_name = <<name of table>>

return for the size of the table?

What does

SELECT num_rows, blocks, empty_blocks, avg_row_len, last_analyzed
  FROM all_tables
 WHERE owner = <<owner of table>>
   AND table_name = <<name of table>>

return for the statistics on the table?

What does

SELECT COUNT(*)
  FROM <<owner of table>>.<<name of table>>

return for the actual number of rows in the table?

What does

DECLARE
  l_unformatted_blocks number;
  l_unformatted_bytes number;
  l_fs1_blocks number;
  l_fs1_bytes number;
  l_fs2_blocks number;
  l_fs2_bytes number;
  l_fs3_blocks number;
  l_fs3_bytes number;
  l_fs4_blocks number;
  l_fs4_bytes number;
  l_full_blocks number;
  l_full_bytes number;
BEGIN
  dbms_space.space_usage (<<table owner>>, <<table name>>, 'TABLE',
                          l_unformatted_blocks, l_unformatted_bytes,
                          l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes,
                          l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes,
                          l_full_blocks, l_full_bytes);
  dbms_output.put_line('Unformatted Blocks = '||l_unformatted_blocks);
  dbms_output.put_line('FS1 Blocks = '||l_fs1_blocks);
  dbms_output.put_line('FS2 Blocks = '||l_fs2_blocks);
  dbms_output.put_line('FS3 Blocks = '||l_fs3_blocks);
  dbms_output.put_line('FS4 Blocks = '||l_fs4_blocks);
  dbms_output.put_line('Full Blocks = '||l_full_blocks);
END;

show for how the blocks in the table are being utilized?

Were the rows updated after January 14th at 14:02:29? In particular, is it possible that a number of rows were inserted that were relatively small but then subsequently were updated to be much larger in size? Does anything change if you re-gather statistics on the table

BEGIN
  dbms_stats.gather_table_stats( ownname          => <<owner of table>>,
                                 tabname          => <<name of table>>,
                                 estimate_percent => null,
                                 granularity      => 'ALL' );
END;