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