Wasted Space

Here’s a little quiz: If I take the average row length of the rows in a table, multiply by the number of rows, and convert the result to the equivalent number of blocks, how can the total volume of data in the table be greater than the total number of blocks below the table high water mark ? I’ve got three tables in a schema, and they’re all in the same (8KB block, 1M uniform extent, locally managed) tablespace, but here’s a query, with results, showing their space utilisation – notice that I gather schema stats immediately before running my query:

execute dbms_stats.gather_schema_stats(user, method_opt=>'for all columns size 1');

select
        table_name,
        avg_row_len,
        num_rows,
        round(avg_row_len * num_rows / 8100)    used_space,
        blocks
from
        user_tables
order by
        table_name
;

TABLE_NAME           AVG_ROW_LEN   NUM_ROWS USED_SPACE     BLOCKS
-------------------- ----------- ---------- ---------- ----------
T1                            85       5000         52         63
T2                            85       5000         52         18
T3                           107       5000         66         32

The division by 8,100 is a fairly crude approximation to convert from bytes to blocks – it allows for the basic block overhead, but doesn’t make any allowance for PCTFREE, ITL entries, etc. It’s generally good enough for a ball-park figure though. So one of my tables uses about 52 blocks’ worth of space, below a highwater mark of 63 blocks – but two of the tables are, apparently, using more space than has been made available !

There are probably several mechanisms that could produce these results – without using any elaborately contrived sequences of activity – all I did was “create as select” to come up with two very simple options which fell outside the boundaries of what the simple SQL diagnostic was able to handle. Any ideas ? I’ll be posting my examples later on today.

Answers

It didn’t take long for the two answers I had in mind to appear – compression and LOBs – here are the examples I had prepared:

rem
rem     Script:         waste_space.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2021
rem

create table t1
as
select	*
from
	 all_objects
where
	rownum <= 5000 -- > comment to avoid wordpress format issue
;

create table t2
compress
as
select
	*
from
	all_objects
where
	rownum <= 5000 -- > comment to avoid wordpress format issue
;

To calculate the row lengths, dbms_stats sums the column lengths using a simple function call (sys_op_opnsize) to get the internal column size. When the table is compressed the basic query execution code reconstructs each row before applying the function – so Oracle ends up reporting the space that would be used if the table weren’t compressed. This is why I used the same data for two different tables – so that you could compare the results from the compressed and uncompressed tables.

create table t3
as
select
	rownum		id,
	empty_blob()	b1
from
	all_objects
where
	rownum <= 5000 -- > comment to avoid wordpress format issue
;

A column that holds a LOB always holds a “lob locator” even when the LOB is stored in row, and even when the lob is the “empty lob” (which is different from NULL). To reduce the impact on the lob index for small LOBs, a lob locator can hold a variable number of pointers to lob chunks, so the size of the basic locator may vary quite dramatically – from 20 bytes to 84 bytes. When calculating the size of the row dbms_stats always bases its calculations on the maximum possible size of a LOB locator.

In my example the in-row empty_blob() actually used 20 bytes for the locator plus a further 16 for the lob content, but Oracle used 84 + 16 in its arithmetic rather than 20 + 16, resulting in a dramatic over-estimate for the avg_row_len.

If you’re interested, this is what the empty_blob() column looked like in a block dump for this version of Oracle when in row:

In row empty_blob()
===================
col  1: [36]
 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 42 7b b0 00 10 09 00 00
 00 00 00 00 00 00 00 00 00 00 00
LOB
Locator:
  Length:        84(36)
  Version:        1
  Byte Length:    1
  LobID: 00.00.00.01.00.00.00.42.7b.b0
  Flags[ 0x02 0x0c 0x00 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Enable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: ReadWrite
  Inode:
    Size:     16
    Flag:     0x09 [ Valid DataInRow ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    0
    Version:  00000.0000000000
    Inline data[0]

Oracle reported the column size as 103 (84 + 16, plus one for the lock byte, one for the flags, and one for the column length) when it should have been 39.
The Inode section (16 bytes) goes into the lob segment if you disable storage in row.