Oracle Scratchpad

June 19, 2013

Wasted Space

Filed under: compression,fragmentation,Infrastructure,LOBs,Oracle — Jonathan Lewis @ 9:55 am BST Jun 19,2013

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');

        round(avg_row_len * num_rows / 8100)    used_space,
order by

-------------------- ----------- ---------- ---------- ----------
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.


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     Script:         waste_space.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2013

create table t1
select  *
        rownum <= 5000 -- > comment to avoid wordpress format issue

create table t2
        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
        rownum          id,
        empty_blob()    b1
        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
  Length:        84(36)
  Version:        1
  Byte Length:    1
  Flags[ 0x02 0x0c 0x00 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Enable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: ReadWrite
    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 declare your lob with “disable storage in row”.


  1. Jonathan, you could be using COMPRESS option on the table.
    The other possibility is there could be NULL values in the columns – increasing row length but no storage required. Not sure if this applies to the ‘right’ columns only.

    Comment by Pavel — June 19, 2013 @ 10:45 am BST Jun 19,2013 | Reply

  2. create table vv (owner,view_name, text_length, text)
      lob(text)store as (disable storage in row)
      select owner,view_name, text_length, to_lob(text)
        from dba_views where text_length>1000;
    -------------------- ----------- ---------- ---------- ----------
    VV                           115        729         10         9

    Comment by Yuri — June 19, 2013 @ 11:02 am BST Jun 19,2013 | Reply

  3. Hmm. The confusing bit to me is why you called this “wasted” space. Hyper-utilized space, or space out of thin air, right?

    I’m supposing out of line storage and compression each can produce this result. Perhaps if you describe the table and show us the sum of the vsize() for each column it will be obvious. I should never answer your quizzes: Guessing what underlies a description without being able to investigate is not my strong suit, and you lead us down the garden path so effectively!


    Comment by rsiz — June 19, 2013 @ 4:07 pm BST Jun 19,2013 | Reply

  4. […] Yet anpther example of looking at what you’ve got before you apply the usual arithmetic […]

    Pingback by Fragmentation 1 | Oracle Scratchpad — March 2, 2021 @ 6:35 pm GMT Mar 2,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: