JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 8: Leveraging Tablespaces.


Storage Clauses and LMTs (21-May-2001)

Complex Transportable Tablespaces (1-May-2001)

Transportable Tablespaces (1-May-2001 - updated)

SMON and tablespaces (2nd Feb 2001)

Extent Management (2nd Feb 2001)

Enhancements in 8.1.6

Bitmap Footers

Autoallocated tablespaces


Errata (updated 15th Aug 2001)


Back to Book Index


Storage Clauses and LMTs

There is some confusion about locally managed tablespaces and the use of storage clauses on objects stored in LMTs, which arises most frequently when use exp/imp to transfer data from dictionary managed tablespaces to locally managed tablespaces. The issue appears becuase imp will always apply a storage clause to the object it is creating, so if you have done an export with the compress = y option, then you may find, to your dismay, that the object created in your new clean locally managed tablespace is far too big, with a very large number of empty extents. So what happens ? Let's look at an example (which I ran recently under on an NT machine):

	create tablespace local 
	datafile 'c:\oracle\oradata\o8i\local_01.dbf'
	size 50064K
	extent management local
	uniform size 1000K

	create table jpl1 (n1 number)
	tablespace local
	storage (initial 3000K next 2000K minextents 3)

The nature of the table definition demands several extents of sizes that do not conform to the single size allowed to the locally managed tablespace. However Oracle does do the best it can for you. Iif you work out the 'perfect' size that the storage clause demands you get the answer 7,000K (3,000K + 2 x 2,000K). In this case Oracle will give you a table with 7 extents of 1,000K each. As a second example, try to build a table with the storage clause as follows:

	create table jpl1 (n1 number)
	tablespace local
	storage (initial 1000K next 2000K minextents 4 pctincrease 50)

Notionally you 'expect' extents of the following sizes:

Extent id 0 1 2 3
Size in KB 1000 2000 3000 4500

Oracle totals this up to 10,500K, and gives you 11 extents of 1,000K each.

In both cases, a quick check of the view user_tables would show that the next_extent column had been set to 1,000K.

Similar results hold for autoallocate tablespaces, Oracle calculates the total value for the starting space by looking at initial, next, minextents and pctincrease, and then allocating extents as appropriate to at least meet that demand. In an uniform LMT this means rounding the total up to the next uniform extent. With autoallocate tablespaces, you may get a fairly random scattering of 64K, 1MB, 8MB or 64MB depending on the demand and the current state of the tablespace.

Top of page


Complex Transportable tablespaces

Since writing the section on transportable tablespaces (p.160-164), I have been asked a couple of times how does Oracle handle the problem of exporting multiple tablespaces with multiple files. More precisely, how does it handling importing them as you export a list of tablespaces, but import a list of files, possibly with a complete change of filename along the way. What happens if you 'list the filenames in the wrong order' ?

The answer is simple - it works.

The export file contains (apart from special calls to 'create' the data segments in the exported tablespace) a number of calls to various procedures in the package sys.dbms_plugts, defined in $ORACLE_HOME/rdbms/admin/dbmsplts.sql. In particular you will find calls to:

	sys.dbms_plugts.beginImpTablespace 	-- 'import' a tablespace
	sys.dbms_plugts.checkDatafile		-- 'import' a datafile

The parameters to the tablespace procedure include the tablespace name, tablespace id, and number of files required by the tablespace. The parameters to the datafile procedure include the tablespace id, the absolute file number, and the relative file number. So how does that help.

If you dump the data file header blocks, you will find lines looking something like:

	tablespace 10, index=9 krfil=9 prev_file=0
	tablespace 10, index=9 krfil=10 prev_file=9

In other words, each data file contains information identifying the tablespace it belongs to (tablespace 10), the relative file number in the tablespace, (krfil=9, krfil=10), and the order that those files should be applied to the tablespace (prev_file=0 is the first file in the tablespace, prev_file=9 is the one that come after krfil=9).

So when you supply a list of files to the imp command, Oracle is able to read each file header in turn, and works out which file belongs to which tablespace, and the order to put them in.

Top of page


Transportable tablespaces

I have described the basics of transporting tablespaces from one database to another (p.160 - 164), but failed to point out the degree of privilege needed to do this. Under Oracle 8.1.5, you needed to have the DBA to be table to transport a tablespace, however I have recently come across a note pointing out that this is no longer sufficient in 8.1.6 onwards, where you have to connect as sysdba. You may run into difficulties when you first try to do this with a surprise error message of:

	LRM-00112: multiple values not allowed for parameter 'userid'

To include the as sysdba in the password, you need to quote the entire user definition string, for example, in a parameter file you would put:

	userid="sys/change_on_install as sysdba"

Top of page


SMON and tablespaces

I have mention here (p.151) and in chapter 13 that smon executes a check every five minutes to search for tablespaces with free space that could be coalesced. There is a paper on this web site that goes into some detail about the behaviour of smon under version 7.3 which mentions, in particular, event 10269 which can be used to stop smon from doing this scan. With the increasing appearance of locally managed tablespaces, it certainly seems to make some sense to use this event if you want to build a database with a large number (1000+ tablespaces).

Top of page


Extent Management

I mentioned (p.152) the paper that originally exposed the myth about performance being improved by cramming each object into a single extent. I have recently discovered that the author (Cary Millsap) now has his own website at www.hotsos.com where you can see this paper and several other seminal papers on Oracle performance.

Top of page


Enhancements in 8.1.6 to locally managed tablespaces: In Oracle 8.1.5 it was not possible to convert a dictionary managed tablespace into a locally managed tablespace. This functionality has been added in Oracle 8.1.6 (There is a slight bug, however, fixed in 8.1.7 and backported on some platforms to regarding tablespaces which have been migrated from 7.3 to 8.1 - if you attempt to convert such tablespaces, the procedure will crash, although I don't know if it damages the tablespace as it does so).

Do not be misled by this enhancement: it is nice that it has appeared, but it has to be limited by the previous use of the tablespace, so it is NOT a magic bullet to solve the problems of mismanaged space allocation. There are three critical details in the implementation - first, the bitmap used to track space allocation no longer has to be at the start of the file, and in fact during conversion the entire bitmap has to fit into a single contiguous section of one of the files in the tablespace; second the tablespace continues to have its allocation_type set to "USER"; and third, the tablespace will become a pseudo-uniform sized tablespace, but the unit size will be the 'greatest common divisor' of all the currently existing extents (including the empty ones listed in uet$).

For example, we can:

	execute dbms_space_admin.tablespace_migrate_to_local('DICT'); 

When you make this call, note that the name of the tablespace has to be in capitals, other wise you will get error:

	ORA-00959: tablespace 'dict' does not exist 
	ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0

Oracle will scan the DICT tablespace to find the greatest common divisor of all the existing extents, then build a space bitmap for each file in the tablespace (which typically requires 64K per file), concatenate them, then look for a large enough space in the first file of the tablespace to store that bitmap, then end up by clearing out the fet$ and uet$ entries for the tablespace. This could take some time. If there isn't a large enough space in the file for the bitmap, the process will fail with:

	ORA-03244: No free space found to place the control information
	ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0

Note particularly, you will probably need a free extent of size 'number of files in tablespace' x 64K for the map. Of course, you could resize the first file in the tablespace to make sure there enough space at the end of the file, alternatively, you could use one of the optional parameters to the call to put the map into one of the other files in the tablespaces. There are actually three parameters to the procedure:

	execute dbms_space_admin.tablespace_migrate_to_local(
		tablespace_name 	=>'DICT',
		unit_size		=> 8,
		rfno			=> 17

The rfno is the relative_fno from dba_data_files of the file where you want to place the bitmap. If you pick a file that does not belong to the tablespace, you get error:

	ORA-02827: Invalid file number
	ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0

The most important thing to worry about with this procedure though is implied by the unit_size parameter and my comments about 'greatest common divisor'. When a tablespace is locally managed, it holds a bitmap representing the blocks in the tablespace, where every bit corresponds to a chunk of the tablespace that is made from exactly the same number of blocks as evey other chunk. In a uniform tablespace, this chunk size is the uniform size, and is used as the extent size for all segments in the tablespace; in an autoallocate tablespace this chunk size is implicitly set to 64K, but extents are then created as 1, 16, 128, or 1,024 chunks.

So what will happen if you convert a dictionary-managed tablespace containing one extent of 12 blocks, and one extent of 13 blocks ? The only way that Oracle can build these extents from a standard unit size is to pick a unit size of one block. If you had a slightly nicer setup (perhaps you had used the minimum extent option) you might find that you had some extent sizes of 32K, 64K, 96K, 256K - in this case, Oracle could determine that a 32K unit size would work, and set the unit size to the matching number of blocks. Be warned, therefore, that Oracle may have to choose a very small unit size for the tablespace.

If you want to set an explicit unit size yourself, you can use the unit_size parameter; this is set as a number of Oracle blocks, not bytes. If you pick an impossible value, then you get error:

	ORA-03241: Invalid unit size
	ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0

The final consequence of .converting a dictionary tablespace to a locally managed tablespace is that xxx_tablespaces will show the extent_mangement to be "LOCAL", but the allocation_type to be "USER". Clearly, the examples above show that Oracle will in general be unable to use autoallocate on the tablespace, and if it switched to uniform the consequences of having a unit size of one block could be disastrous. After conversion, extents will be allocated according to the normal next_extent methods, with the proviso that rounding to the unit size will occur.

In conclusion, the big benefit from converting tablespaces from dictionary managed to locally managed comes from the use of bitmap space allocation and reduced pressure on the data dictionary tables. There is no (explicit) benefit due to changes in space allocation algorithms.

Top of page


Bitmap Footers: In a recent Usenet discussion, I was introduced to Metalink note 109630.1 which introduces the phenomenon of bitmap headers and bitmap footers in locally managed tablespaces. You will already be familiar with the bitmap header (p.153), but this article may be enough to convince you to start working on the problem of how to calculate the size of the bitmap footer. Don't worry - there's no such thing !

Possibly this is a hangover from an early releae of the technology, but perhaps it is simple a mis-interpretation of the discrepancy between the space allocated to the file and the space reported as available in dba_free_space. For example, consider the tablespace creation script built on an 8K block size that folllows::

	Create tablespace locally_managed
	datafile /u02/app/oracle/oradata/auto_01.dbf' 
	size 1024M reuse
	extent management local
	uniform size 4M

The file will be built (usually with an extra block - which is completely hidden from Oracle). If you report the contents of dba_free_space immediately after creating this tablespace, you will find that the there is just one extent, starting at block 9 (the first 8 blocks, 64K have been allocated for generic file and bitmap header). The size of this extent will be 130560 blocks, even though 1GB is 131,072 blocks and we have only used 8 of them so far. Where have the rest gone ? If you work out the size of the 'lost' space it is:

131,072 blocks as defined - 130,560 blocks in the free space - 8 blocks in the bitmap = 504 blocks

So why is it lost ? Because the fixed extent size is 512 blocks. 504 blocks is not enought to make a complete extent, so it has to be discarded. Of course, if you resize the data file by adding another 8 blocks, the 504 blocks will be combined with the extra 8, and dba_free_space will suddenly show an extra 512 blocks of free space.

In case you are not convinced that there is no such thing as space reserved for a bitmap footer, you can always do a block dump of these trailing blocks. You will find that they are full of zeros - and will be reported as corrupt in a symbolic block dump. In comparison, the blocks in the bitmap 'header' will be marked as File Space Bitmap Block and full of hex FF.

Top of page


Autoallocated Tablespaces: I make some comments about the extent allocation that occurs in locally managed tablespaces with the autoallocate option. (p. 154) to the effect that Oracle uses 64KB, 1MB and 8MB as the possible extent sizes. Following a discussion on the Metalink Forum (Oracle Server Enterprise Edition) with Vlado Jelinek, I have tested this further with an Oracle 8.1.7 system using 8K Oracle blocks.

On this system Oracle can allocate extents of 64MB when the object get past the 1GB mark. The exact details of when 64MB extents start to appear is a little 'randomised' depending on the size of the initial extent, how extents are added and so on. Oracle 8.1.7 also seems to be slightly different in its timing of the switch from 1MB extents to 8MB extents.

It is nice to know that Oracle will keep the number of extents in very large objects relatively low when they are in autoallocate tablespaces. However this is in some ways a little irrelevant given my guideline about identifying special objects and keeping them in their own tablespaces. If an object is large enough to hit the automatic allocation of 64MB extents, you should have realised that it was going to be very large, and therefore not suitable for the 'odds and ends' bucket of an autoallocate tablespace. It should have been in its own separate tablespace, or sharing a (uniform) tablespace with a few objects of a similar size

Top of page.


When describing the values for the allocation_type column of the view family xxx_tablespaces (p. 154) I give one of the options as "USERS"; this should be "USER".

How big is a bit ? (15th Aug 2001)

I made the point that the typical bitmap at the start of a locally managed tablespace could hold information about 27,000 - 30,000 extents in two places, p.155 in this chapter and p.175 in the chapter on temporary space. In the chapter on temporary space I explained that this is because the bitmap actually takes two bytes per extent. This is true but only for temporary tablespaces. In ordinary locally managed tablespaces, the bitmap really is one bit = one extent.

Allowing for a little overhead, a single 8K block can hold information about 63,488 extents, and the typical bitmap (64K - 2 blocks) can therefore hold details of about 380,000 extents. To put this into perspective, if you created a tablespace with 16K extents on an 8K block size (and you probably wouldn't really want to do that), then the file size could be about 6 GB before the bitmap was exhausted and an extension had to be added.

A quirky little detail about bitmap sizes also came up recently on the Oracle-L mailing list. If you define a very small tablespace - in this context 'very small' means something between 5 blocks and '64K plus one extent' then Oracle will give you a bitmap of just one block - which still allows you to grow the file quite comfortably, of course.

Top of page

Back to Book Index