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

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 to 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:

create table t1
as
select	*
from
	 all_objects
where
	rownum <= 5000
;

create table t2
compress
as
select
	*
from
	all_objects
where
	rownum <= 5000
;

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
;

A column that holds a LOB always holds a “lob locator” even when the LOB is stored out of 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 line in a block dump 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 + 1 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 to the lob segment if you disable storage in row.

4 Comments »

  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)
      as
      select owner,view_name, text_length, to_lob(text)
        from dba_views where text_length>1000;
    
    TABLE_NAME           AVG_ROW_LEN   NUM_ROWS USED_SPACE     BLOCKS
    -------------------- ----------- ---------- ---------- ----------
    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!

    mwf

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

    • Mark,
      To answer your supplementary puzzle: it’s because the opening script (or similar) is typically used to calculate the amount of “wasted” space in a table.

      Comment by Jonathan Lewis — June 19, 2013 @ 6:51 pm BST Jun 19,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,011 other followers