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:
CREATE TABLE T1 ( C1 VARCHAR2(300), C2 NUMBER(22,4), C3 DATE, C4 BLOB, C5 CLOB); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
Surprised by the results yet?
SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN, NUM_FREELIST_BLOCKS FROM USER_TABLES WHERE TABLE_NAME='T1'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS ---------- ---------- ------------ ----------- ------------------- 0 0 0 0 0 SELECT EXTENT_ID, BYTES, BLOCKS FROM USER_EXTENTS WHERE SEGMENT_NAME='T1' ORDER BY EXTENT_ID; no rows selected
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:
EXTENT_ID BYTES BLOCKS ---------- ---------- ---------- 0 65536 8
Next, insert 1,000,000 rows containing NULL values in all columns and re-gather the statistics:
INSERT INTO T1 (C1) SELECT NULL FROM DUAL CONNECT BY LEVEL<=500000; INSERT INTO T1 (C1) SELECT NULL FROM DUAL CONNECT BY LEVEL<=500000; COMMIT; EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
Now let’s check the table’s statistics:
SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN, NUM_FREELIST_BLOCKS FROM USER_TABLES WHERE TABLE_NAME='T1'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS ---------- ---------- ------------ ----------- ------------------- 1000000 1630 0 1 0
Oracle Database 10.2.0.4 was not quite as accurate when estimating the number of rows in the table:
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS ---------- ---------- ------------ ----------- ------------------- 984619 1630 0 1 0
Next let’s take a look at the extent allocation, which was the same for both releases of Oracle Database:
SELECT EXTENT_ID, BYTES, BLOCKS FROM USER_EXTENTS WHERE SEGMENT_NAME='T1' ORDER BY EXTENT_ID; EXTENT_ID BYTES BLOCKS ---------- ---------- ---------- 0 65536 8 1 65536 8 2 65536 8 3 65536 8 4 65536 8 5 65536 8 6 65536 8 7 65536 8 8 65536 8 9 65536 8 10 65536 8 11 65536 8 12 65536 8 13 65536 8 14 65536 8 15 65536 8 16 1048576 128 17 1048576 128 18 1048576 128 19 1048576 128 20 1048576 128 21 1048576 128 22 1048576 128 23 1048576 128 24 1048576 128 25 1048576 128 26 1048576 128 27 1048576 128 SELECT SUM(BYTES) BYTES, SUM(BLOCKS) BLOCKS FROM USER_EXTENTS WHERE SEGMENT_NAME='T1' ORDER BY EXTENT_ID; BYTES BLOCKS ---------- ---------- 13631488 1664
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):
SELECT SUBSTR(ROWID,1,6) OBJECT_ID, SUBSTR(ROWID,7,3) FILE_ID, SUBSTR(ROWID,10,6) BLOCK_ID, SUBSTR(ROWID,16,3) ROW_ID, LENGTHB(ROWID) LEN, COUNT(*) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) ROWN, SUM(LENGTH(ROWID)) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) BYTES FROM T1 WHERE ROWNUM<=1400; OBJECT FIL BLOCK_ ROW LEN ROWN BYTES ------ --- ------ --- ---------- ---------- ---------- AAAULA AAH AAGLHT AAA 18 1 18 AAAULA AAH AAGLHT AAB 18 2 36 AAAULA AAH AAGLHT AAC 18 3 54 AAAULA AAH AAGLHT AAD 18 4 72 AAAULA AAH AAGLHT AAE 18 5 90 AAAULA AAH AAGLHT AAF 18 6 108 AAAULA AAH AAGLHT AAG 18 7 126 AAAULA AAH AAGLHT AAH 18 8 144 AAAULA AAH AAGLHT AAI 18 9 162 AAAULA AAH AAGLHT AAJ 18 10 180 AAAULA AAH AAGLHT AAK 18 11 198 AAAULA AAH AAGLHT AAL 18 12 216 AAAULA AAH AAGLHT AAM 18 13 234 AAAULA AAH AAGLHT AAN 18 14 252 AAAULA AAH AAGLHT AAO 18 15 270 AAAULA AAH AAGLHT AAP 18 16 288 AAAULA AAH AAGLHT AAQ 18 17 306 AAAULA AAH AAGLHT AAR 18 18 324 AAAULA AAH AAGLHT AAS 18 19 342 AAAULA AAH AAGLHT AAT 18 20 360 ... AAAULA AAH AAGLHT AKP 18 656 11808 AAAULA AAH AAGLHT AKQ 18 657 11826 AAAULA AAH AAGLHT AKR 18 658 11844 AAAULA AAH AAGLHT AKS 18 659 11862 AAAULA AAH AAGLHT AKT 18 660 11880 AAAULA AAH AAGLHU AAA 18 1 18 AAAULA AAH AAGLHU AAB 18 2 36 AAAULA AAH AAGLHU AAC 18 3 54 AAAULA AAH AAGLHU AAD 18 4 72 AAAULA AAH AAGLHU AAE 18 5 90 AAAULA AAH AAGLHU AAF 18 6 108 AAAULA AAH AAGLHU AAG 18 7 126 AAAULA AAH AAGLHU AAH 18 8 144 AAAULA AAH AAGLHU AAI 18 9 162 AAAULA AAH AAGLHU AAJ 18 10 180 AAAULA AAH AAGLHU AAK 18 11 198 AAAULA AAH AAGLHU AAL 18 12 216 AAAULA AAH AAGLHU AAM 18 13 234 AAAULA AAH AAGLHU AAN 18 14 252 ... AAAULA AAH AAGLHU AKP 18 656 11808 AAAULA AAH AAGLHU AKQ 18 657 11826 AAAULA AAH AAGLHU AKR 18 658 11844 AAAULA AAH AAGLHU AKS 18 659 11862 AAAULA AAH AAGLHU AKT 18 660 11880 AAAULA AAH AAGLHV AAA 18 1 18 AAAULA AAH AAGLHV AAB 18 2 36 AAAULA AAH AAGLHV AAC 18 3 54 AAAULA AAH AAGLHV AAD 18 4 72 AAAULA AAH AAGLHV AAE 18 5 90 AAAULA AAH AAGLHV AAF 18 6 108 AAAULA AAH AAGLHV AAG 18 7 126 AAAULA AAH AAGLHV AAH 18 8 144 AAAULA AAH AAGLHV AAI 18 9 162 AAAULA AAH AAGLHV AAJ 18 10 180 AAAULA AAH AAGLHV AAK 18 11 198 AAAULA AAH AAGLHV AAL 18 12 216 AAAULA AAH AAGLHV AAM 18 13 234 AAAULA AAH AAGLHV AAN 18 14 252 ... AAAULA AAH AAGLHV ABM 18 77 1386 AAAULA AAH AAGLHV ABN 18 78 1404 AAAULA AAH AAGLHV ABO 18 79 1422 AAAULA AAH AAGLHV ABP 18 80 1440
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:
SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_ID, DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NUMBER, DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ROW_ID, COUNT(*) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) ROWN, SUM(11) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) BYTES FROM T1 WHERE ROWNUM<=1400; OBJECT_ID FILE_ID BLOCK_NUMBER ROW_ID ROWN BYTES ---------- ---------- ------------ ---------- ---------- ---------- 82624 7 1618387 0 1 11 82624 7 1618387 1 2 22 82624 7 1618387 2 3 33 82624 7 1618387 3 4 44 82624 7 1618387 4 5 55 82624 7 1618387 5 6 66 82624 7 1618387 6 7 77 82624 7 1618387 7 8 88 82624 7 1618387 8 9 99 82624 7 1618387 9 10 110 82624 7 1618387 10 11 121 82624 7 1618387 11 12 132 82624 7 1618387 12 13 143 82624 7 1618387 13 14 154 82624 7 1618387 14 15 165 ... 82624 7 1618387 655 656 7216 82624 7 1618387 656 657 7227 82624 7 1618387 657 658 7238 82624 7 1618387 658 659 7249 82624 7 1618387 659 660 7260 82624 7 1618388 0 1 11 82624 7 1618388 1 2 22 82624 7 1618388 2 3 33 82624 7 1618388 3 4 44 82624 7 1618388 4 5 55 82624 7 1618388 5 6 66 82624 7 1618388 6 7 77 82624 7 1618388 7 8 88 82624 7 1618388 8 9 99 82624 7 1618388 9 10 110 ... 82624 7 1618388 656 657 7227 82624 7 1618388 657 658 7238 82624 7 1618388 658 659 7249 82624 7 1618388 659 660 7260 82624 7 1618389 0 1 11 82624 7 1618389 1 2 22 82624 7 1618389 2 3 33 82624 7 1618389 3 4 44 82624 7 1618389 4 5 55 82624 7 1618389 5 6 66 82624 7 1618389 6 7 77 82624 7 1618389 7 8 88 82624 7 1618389 8 9 99 82624 7 1618389 9 10 110 ... 82624 7 1618389 77 78 858 82624 7 1618389 78 79 869 82624 7 1618389 79 80 880
A comparison test with a second table containing a single column:
CREATE TABLE T2 ( C1 VARCHAR2(1)); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2') INSERT INTO T2 (C1) SELECT NULL FROM DUAL CONNECT BY LEVEL<=500000; INSERT INTO T2 (C1) SELECT NULL FROM DUAL CONNECT BY LEVEL<=500000; COMMIT; EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')
If the columns containing NULL values required storage space in table T1, then we should expect that table T2 should require less space than that for table T1.
SELECT SUM(BYTES) BYTES, SUM(BLOCKS) BLOCKS FROM USER_EXTENTS WHERE SEGMENT_NAME='T2' ORDER BY EXTENT_ID; BYTES BLOCKS ---------- ---------- 13631488 1664
The 13,631,488 bytes and 1,664 blocks appears to be the same values as displayed for table T1.
For extra credit we might try something like this:
UPDATE T1 SET C2=1; COMMIT; EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1') SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN, NUM_FREELIST_BLOCKS FROM USER_TABLES WHERE TABLE_NAME='T1'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS ---------- ---------- ------------ ----------- ------------------- 1000000 1630 0 4 0
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.
Feel free to experiment with this example.
Charles,
The eleven bytes per row comes from the following data requirement for each row (when it’s completely null)
Row directory entry 2 bytes
Column count byte
Lock byte
Flag byte
Reserved 6 bytes for rowid that might be needed for row migration.
I don’t know exactly how Oracle tracks it, but it knows that the smallest possible row that can go into a block will be this 11 bytes – i.e. no data abd a forwarding rowid.
It’s interesting to dump one of your “empty” blocks and check the free space information:
Note how there’s 0x12AA bytes between the beginning and end of free space, but the block still records only 0x332 bytes since (decimal) 660 * 6 bytes have been reserved for forwarding rowids.
The row entries look like this:
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.
Regards
Jonathan Lewis
Jonathan,
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:
OBJECT_ID FILE_ID BLOCK_NUMBER ROW_ID ROWN BYTES ---------- ---------- ------------ ---------- ---------- ---------- 82729 7 1618387 0 1 11 82729 7 1618387 1 2 22 82729 7 1618387 2 3 33 82729 7 1618387 3 4 44 82729 7 1618387 4 5 55 82729 7 1618387 5 6 66 82729 7 1618387 6 7 77 82729 7 1618387 7 8 88 82729 7 1618387 8 9 99 82729 7 1618387 9 10 110 ...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.)
To dump the blocks, I executed the following:
This is what I see in the trace file:
The above seems to show that the addresses are in descending order, while your output shows ascending order (this might be an ASSM characteristic).
17dc – 53a = 12A2 = 4770
1f50 – 1f4d = 3
—
This is from the first block dump that I performed before dropping/purging table T1, after column C2 was set to 1:
6ab – 53a = HEX171 = 369
1ef0 – 1ee9 = 7
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.
Charles,
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.
(I wrote a short note about heap block compression a little while ago that makes some noises about the timing of housekeeping: http://jonathanlewis.wordpress.com/2010/03/30/heap-block-compress/ )
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 !
Jonathan,
I appreciate the time that you spent providing the above comments.
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.
Jonathan,
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:
bytes per entry = entry header + ROWID length + F + V + D
where:
entry header = 1 byte
ROWID length = 6 bytes
F = total length bytes of all columns with 1 byte column
lengths (CHAR, NUMBER, DATE, and ROWID types)
V = total length bytes of all columns with 3 byte column
lengths (VARCHAR2 and RAW datatypes)
D = combined data space of all columns (from above)
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.
—
Here is a small test case that creates a column of type ROWID:
This output is from 11.1.0.7:
It could very well be the case that I am misunderstanding…
—
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 […]
[…] une récente discussion sur le blog un article de Charles Hooper , j’ai fait un commentaire disant qu’il est difficile d’être précis et […]