LMT Headers

Here’s another one of those little changes that sidles in when you’re not looking.

When locally managed tablespaces first appeared, there were a number of posts on Usenet (comp.databases.oracle.server) and the metalink lists (the OTN database forum didn’t exist at the time) about “missing” space in data files. The commonest sort of comment was along the lines of

“I’ve created a data file of 100 MB, how come I can only create 99 extents of 1 MB each?”

“I’ve created a data file of 10 GB, how come I can only create 9 extents of 1 GB each?”

The answer was that Oracle reserved the first few blocks of the file (typically totalling 64KB of space, but 128KB if you were on a platform that supported a 32KB block size) for space management blocks holding a bitmap identifying which (notional) extents in the file were free and which were allocated to data segments. For uniform extents each bit in the map represented the given unit size of an extent, for system-managed extent allocation each bit represented 64KB (the “lowest common denominator” for an extent). As a side effect of this bitmap/extent mapping you can “lose” a whole extent – less one block – if you don’t work out your file size quite carefully.

Here’s a little demo script, with some results, cut from a session using 10.2.0.3 to demonistrate the point:

create
	tablespace tiny
	datafile 'C:\ORACLE\ORADATA\D10g\tiny.dbf'
	SIZE 1114112 reuse
	extent management local
	autoallocate
	segment space management auto
;

select
	file_id, block_id, bytes, blocks
from
	dba_free_space
where
	tablespace_name = 'TINY'
;

SQL> @demo

Tablespace created.

   FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         8          9    1048576        128

1 row selected.

SQL>

If you’re wondering about my choice of initial file size, it’s the equivalent of 1MB + 64KB. As you can see this results in 1MB of “user” space available, starting in block 9 of the file (leaving 8 blocks – 64KB given my block size – for any file-based overheads).

Let’s check the results (after changing the filename) for an 11g instance – 11.1.0.7 in this case:

SQL> @temp

Tablespace created.

   FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         7        128      65536          8

1 row selected.

SQL>

When we shift to 11g we see 1MB of overhead at the start of the file, and only 64KB left for user space.

I haven’t done a lot of testing to see how many different special cases there are but based on a couple of quick checks it looks as if Oracle will allocate 1MB of space management overhead at the start of file if the initial file size is at least 1MB + 64KB. (For smaller files the overhead is still 64KB.)

I suspect the reason behind this is that if a file were to become very large Oracle would eventually have to allocate a secondary space management bitmap somewhere in the middle of the file (which might make it hard to shrink the file at a later date), so it pre-emptively allocates a space management area large enough for a very large file when you create the initial file.

As a little throwaway test (to see if I could get a secondary map allocated) I created a single file of 2,047 MB, in a tablespace using locally managed extents of uniform size 16KB (yes, deliberately silly sizing), and freelist management. Here’s the slightly surprising result I got from querying dba_free_space for that tablespace:

select
	block_id, blocks,
	block_id + blocks - 1	end_block,
	bytes/1048576  		MB
from
	dba_free_space
where
	tablespace_name = 'TINY'
order by
	block_id
;

  BLOCK_ID     BLOCKS  END_BLOCK         MB
---------- ---------- ---------- ----------
       128     126976     127103        992
    127104     126976     254079        992
    254080       7936     262015         62
                                 ----------
sum                                    2046

3 rows selected.

I was expecting a single free space chunk – but that’s not the way it seems to work. For an 8KB block size (in this version of Oracle), the bitmap space management blocks start at block 3 of the file, and each bitmap block can map 63,488 extents – which in my case amounted to 126,976 blocks – and the code that reports dba_free_space seems to look at one bitmap block at a time to generate the report.

It’s probably the case that none of this matters to anyone – but it’s just another couple of details that go into my catalogue of implementation details just in case I find the boundary condition one day, perhaps relating to LOBs or SecureFiles, where something strange happens because of some side effect of this implementation.

Footnote: If you want to create a file on an 8KB block size with a secondary bitmap, you should start with a small file (1MB or less), 16KB uniform extents and freelist management; this will give you 6 blocks of bitmaps, which can each map 63,488 extent, or 126,976 blocks, for a total of 5,952MB. Add 64KB for the initial file header information and then a bit more – call it 6GB to make it easy – and you’ve got a targer to resize the file to the point where you get a secondary map. But I think that means you’ll need a 64-bit O/S.