How Much Space is Required to Store a Whole Lot of Nothing?

29042010

April 29, 2010

While looking for something completely unrelated I encountered one of the test cases that I posted to an OTN forum thread about a year ago. There are a couple of interesting items in the test case, so I thought that I would reproduce it here, verifying the test case on Oracle 11.2.0.1. The OTN forum thread asked if NULL values in table columns take up space, and if so does it depend on the data type of the column. I answered the question like this:

It is my understanding that NULL values in the last columns (non-null values appear only in the first set of columns defined for the table) in of a row do not consume space, while NULL values in the first set of columns, if they are followed by columns containing non-NULL values will consume space. At this time, I am having trouble locating a reference which backs up my understanding of how NULLs affect storage space.

The test case was originally constructed to be executed against Oracle Database 10.2.0.4, and the output showed what was achieved with an ASSM autoallocate tablespace with an 8KB block size. So, what happens when the test case is executed on Oracle Database 11.2.0.1 with an ASSM autoallocate tablespace with an 8KB block size? The results may surprise you.

First, we need to create a table with a couple of different column data types, and then collect statistics for the table:

Probably not if you read about Oracle Database 11.2.0.1’s deferred segment creation feature. Yes, the above results differed from that of Oracle Database 10.2.0.4, which showed the following for the second SQL statement:

The above shows that the table is using 1630 blocks to store the rows with the completely NULL values, there are 1664 blocks allocated to extents used by the table, and 13,631,488 bytes used by the extents. NULLs consuming space?

A little fun with the ROWID pseudocolumn, dissecting the components of that pseudocolumn (side note, I do not recall where I originally determined how to break apart the 18 byte long displayed ROWID, but it is found in the documentation):

The above shows that Oracle was packing 660 of these completely NULL rows into each 8KB block with a default PCT_FREE of 10%, leaving about 7370 bytes of space for storing the 660 rows per block, indicating that each row was consuming about 11 bytes. Obviously from the above, the actual ROWID for a row does not occupy a full 18 bytes of data storage per row. It is actually 10 bytes (reference) that is externalized as a 18 byte (reference) base 64 encoded character string. In retrospect, I probably should have done the following instead to decode the ROWID using the DBMS_ROWID package, and calculate the per row overhead plus the space required (1 byte) to store the data:

The average row length increased from 1 to 4 when the second column of the table was set to a value of 1 for all rows in the table – now the NULL values in column 1 require a single byte of storage space.

Interestingly, looking at the complete block dump for the first couple of blocks, there seems to be an interesting “array insert” which operates around a limit of 255 rows or the end of block – whichever is smaller.

Thanks for suggesting the block dump – I think that I now understand what you are showing with your block dumps. I agree that it is interesting. I see slightly different results, but that might be a symptom of ASSM. If I understand the output that you posted correctly, each of the rows in the block is occupying only 3 bytes – even though Oracle considered the blocks filled as if each row would occupy 11 bytes. That would also explain why, when I set the second column of each row to a value of 1, the number of blocks did not increase. If I am calculating correctly, that should have increased the row length by at least 2 bytes, and if it was just a 2 byte increase, that would be (11 as we previously determined + 2) * 660 = 8,580 bytes – which of course will not fit into an 8KB block.

After recreating the table, this is what one of the above SQL statements showed:

Dropping and recreating the table put the table into the same set of blocks. Oddly, it seemed that I had to drop/purge the table, bounce the database, and then recreate the table so that the trace file showed the correct information – if I did not bounce the database I saw the block information from the dropped/purged table.)

Unless I am misunderstanding the above, each row grew by 4 bytes, and with 660 rows per block, that is an increase of 1,980 bytes per block. That seems to imply that there should still be about 2,790 bytes unused in the block, not 369, when the second column was set to a value of 1.

Interesting – when I repeated the experiment with ASSM, the insertion pattern changed: the “apparent 255 array size” disappeared and the physical placement of the rows in the blocks was reversed as you can see comparing these two extracts of row 23 to 26, one from non-ASSM, the other from ASSM:

Note how the location in block (@0xNNNN) increases as the row directory entry increases in the original non-ASSM, but decreases in the ASSM.

Regarding the space:
a) You multiplied by 3, not 4: you used an extra 2,640 bytes, not 1,980.
b) The four bytes are:

one byte to say that column one has no data
one byte to say that column two has two bytes
two bytes for the column two data

You can afford to add another two bytes to every row before the actual block usage expands – for example set c1 = ‘XX’, this will change the “length byte” for C1 from 0 to 2, and insert two bytes of data.

The apparent inconsistencies around fseo (end of free space), fsbo (beginning of free space), avsp (available space), and tosp (total space) relate to the way updates are done, how the space is reused, and the problems of other transactions sharing the blocks.

The most significant detail in your case is that there is a free space area between the row stack (end of block) and the block header – this is definded by fsbo and fseo. When you update your rows, they get bigger, so the new version has to be copied into the free space area leaving a little hole behind. THe holes account for a lot of space which (to you) is part of the total space and the available space – but Oracle doesn’t tidy up the block until it really has to, so all you see is that there is lots of space available but the gap between fseo and fsbo is getting small.

Your final comment in the other comment about rowids and the lack of clarity in the documents and metalink notes is appropriate. Bottom line – if I am a row in a block, I know which object I belong to, which file I’m in, and which block I’m in (all these are written just once on the block) so the only space “rowid” takes up is the two bytes that is my entry in the “row directory” at the top of the block. If other people need to make a note of where to find me then there are lots of variations. I’ll have to write a blog note to see if I can list them all – I bet I miss a couple the first time around !

For your point a) above – that was a good catch. I stated that the difference was 4 bytes, but apparently typed 3 into the calculator when performing the calculation. I recall that something did not look correct about the result, but it was getting to be late at night, so I did not double-check the post as well as I should have.

The link that you provided above, as well as the comments, are very helpful. I am looking forward to your future blog note.

I appreciate your comments – there is always something new to learn about Oracle Database, and your contributions certainly help make the journey of learning about the database behavior interesting. I still need quite a bit of practice with reading block dumps.

Your description of the reason why each row requires 11 bytes makes a lot of sense, but I have encountered conflicting information – where the documentation seems to conflict with itself. My article above stated that each ROWID required 10 bytes (the documentation reference was provided in the article), but with the additional information that you stated are stored with each row, it would not be possible to place 660 of the test rows into a single block as happened in my test case.

The journey through the documentation:http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT1130
“Rowids of Row Pieces
A rowid is effectively a 10-byte physical address of a row. As explained in “Rowid Data Types”, every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. For table clusters, rows in different tables that are in the same data block can have the same rowid.”

–

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i3855
“You can also create tables with columns defined using the ROWID datatype. For example, you can define an exception table with a column of datatype ROWID to store the rowids of rows in the database that violate integrity constraints. Columns defined using the ROWID datatype behave like other table columns: values can be updated, and so on. Each value in a column defined as datatype ROWID requires six bytes to store pertinent column data.”

Note that the above two quotes seem to conflict with each other.

–

http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm#918
“A physical rowid datatype has one of two formats:
* The extended rowid format supports tablespace-relative data block addresses and efficiently identifies rows in partitioned tables and indexes as well as nonpartitioned tables and indexes. Tables and indexes created by an Oracle8i (or higher) server always have extended rowids.
* A restricted rowid format is also available for backward compatibility with applications developed with Oracle7 or earlier releases.”

–

http://blog.tanelpoder.com/2008/10/21/transportable-tablespaces-and-rowid-uniqueness/
“It’s a well known fact that the old Oracle7 style restricted rowids (which contained only File#, block# and row#) may not be unique in Oracle8+ databases which can have 1022 datafiles per tablespace not per database as previously. That’s why the 10-byte extended rowids were introduced, which also included the data object ID of a segment inside the rowid.
…
Note that with local indexes and non-partitioned tables the rowids stored in indexes are old 6-byte restricted rowids. They have 4 bytes for data block address consisting of 10bits for file# and 22bits for block#. The other 2 bytes specify the row# in block.”

–

Metalink Doc ID 10640.1 – Extent and Block Space Calculation and Usage in Oracle Databases:
“Once the combined column length of an average index entry has been calculated,
the total average entry size can be calculated, using the formula:

Here again it applies that there would be one byte for columns
with an actual length between 1 and 250. Then for lengths from
251 to 64K, we use 3 bytes (0xFE marker byte, 2 bytes of actual length).
Note 231214.1 describes this aspect more accurately.

For example, given that D is calculated to be 22 bytes (from the table
calculations above), and that the index is comprised of three CHAR columns, the
total average entry size of the index is:

bytes per entry = 1 + 6 + (3 * 1) + (3 * 0) + 22 bytes
= 32 bytes”

The above Metalink article points back to Metalink Doc ID 231214.1 as the ultimate source, and that Doc ID covers Oracle Database 7.3.

OK, I just realized what is happening with my original logic. There is no point in the full ROWID being recorded with each row in a block as described in the Metalink article (and also in this article) – the full ROWID is needed to find the specific datafile and block within that datafile, but for the row entry in the block we would only need to identify the row within the block.

Doc bug, Metalink bug.

Thanks Jonathan. I need to do a bit more research about what you posted here.

[…] — Jonathan Lewis @ 7:47 pm UTC May 9,2010 In a recent discussion in the comments of a blog item by Charles Hooper, I made a comment about how hard it is to be accurate (and unambiguous) when talking about the […]

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: