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 2013 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 declare your lob with “disable storage in row”.
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 |
Comment by Yuri — June 19, 2013 @ 11:02 am BST Jun 19,2013 |
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 |
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 |
[…] 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 |