Locally Managed Tablespaces

Locally managed tablespaces were introduced in Oracle 8, and have slowly been gaining popularity.

Tablespaces past and present
A tablespace is a logical unit of storage. It can span many data files, and contain many data segments. The available space in a tablespace is broken up into extents, and each data segment is made up of one or more extents, where an extent is simply a contiguous section of a single data file. Typically a data segment corresponds to a self-contained and cohesive collection of data (such as a table, or an index partition) that has some meaning to an end-user.

This outline of a tablespace immediately introduces two space management issues. First - which extents belong to which segment; secondly - which extents are in use and which are available. The methods of addressing the first issue have not changed (much) in recent years, but Oracle Corp. has introduced locally managed tablespaces (LMTs) to address problems associated with the second issue.

The Past
Historically, space management in a tablespace was handled through a couple of tables, uet$ (used extent table) and fet$ (free extent table).

When you needed to allocate some space to a segment Oracle would search fet$ for an entry describing an extent of an appropriate size in the correct tablespace,. In fact, there were a number of complicated little strategies that Oracle used in this search which could take some time to complete - but eventually, Oracle would delete (or modify) a row in fet$ and insert a row into uet$.

Similarly, when you freed up an extent (by dropping a table, say) Oracle would delete a row from uet$ and perform a row insert or modification on fet$.

In fact the entire process could also require some changes to be made to a row in seg$ (the table describing data segments) and tsq$ (the table describing space quotas that had been allowed to users). Moreover when you added an extent to a segment, a map in the segment's first block (the segment header block) had to be updated to record the fact that the extent was part of the segment.

So all the work regarding space management for all the tablespaces in the entire database focused on two critical tables in the data dictionary (hence dictionary managed tablespaces or DMTs.) Unless the DBA really knew what was going on and was totally in control of the system, this could cause problems. There were three main reasons why problems could appear.

First, Oracle Corp. had decided to protect all space management operations under a single space transaction enqueue ('ST' lock), rather than one lock per tablespace. So if a number of jobs were making heavy demands for space management, they could easily end up queueing up for the lock and wasting processing time.

Secondly, Oracle Corp. effectively encouraged the DBA's to generate a demand for space management tasks by introducing various segment-level storage parameters (such as initial, next, pctincrease) that promised a spurious degree of precision in storage requirements but defaulted to values that guaranteed that space management would be poor.

Finally, there was enough ignorance and uncertainty in the marketplace that it was easy for junior DBAs to follow procedures that more or less guaranteed that if something could go wrong, it would go wrong. All the problems relating to DMTs can be avoided - so long as someone gives you enough time to find out how they really work (and does the modern DBA ever get the time they need?)

The Present
An LMT is responsible for its own space management. In a clean system every file in a tablespace is sliced into equal-sized chunks (with the exception of the first 64K of the file which is used to store a bitmap identifying which of the other chunks are currently in use). Each bit in the bitmap corresponds to a chunk in file - if a bit is set the chunk is in use, and if a bit is clear the chunk is free. Fig. 1 shows a schematic of a newly created tablespace containing a single file, and the state of that file after some object creation and dropping has gone on.

You can specify the size of the file, and the size of the chunks to be used in the file. For example consider the script:

create tablespace demo_01
datafile 'c:\oracle\oradata\D9202\demo_01.dbf'
size 102464k
extent management local
uniform size 1024K
;
This creates a tablespace with a single file (with a very fussy size declaration), which is sliced up into exactly 100 chunks of 1024K, but has an extra 64K specified to cater for the file's bitmap. If we query the dba_free_space view to find out about the free space in this tablespace we will find that Oracle reports exactly 104,857,600 bytes. When we try to allocate an extent, we will find that the extent will be exactly one chunk - under uniform size management, one chunk equals one extent.

A common problem with LMTs is that DBAs declare the file size without catering for the bitmap space; consequently they 'lose' most of an extent's worth of space at the end of the file because the file is just 64K too small to create the last extent. If this happens to you, all you have to do is resize the file to add the missing 64K, and you may suddenly discover a whole extra extent appearing in dba_free_space.
Note - there is an autoallocate option for LMTs that can be used instead of uniform size X. This still slices the file up into uniform chunks (in this case always at 64K), and uses one bit per chunk. However, instead of equating one chunk with one extent, Oracle will consider past history and available gaps to decide what size extent to allocate. The extent will be one of a limited set of sizes - 64K, 1MB, 8MB, 64MB. For relatively small, simple systems where there isn't much information available about proper sizing requirements, this can be a minimum fuss mechanism to adopt; but in general I believe you should stick with uniform sizing.

So what difference do locally managed tablespaces make? Most significantly, whenever you allocate space in an LMT, Oracle does not have to search through a table to find a row which describes a suitable chunk; instead it just scans the first few blocks of the file looking for the first free bit, and sets it. This is a much more efficient method of finding and allocating space, and has the pleasant side-effect that free space near the start of the file will be preferentially allocated - which may help to keep file sizes small, and eliminate redundant effort in rman backups. Of course, Oracle still has to worry about the seg$ table and the segment header block, and may still have to update the tsq$ table, but the most labour-intensive part of the operation become a lot more efficient. Moreover, instead of using a single space transaction (ST) enqueue to cover the entire database, Oracle uses a new enqueue type - the TT enqueue - and allows one TT enqueue per tablespace to reduce problems of contention due to simultaneous space transactions.

Of course there are trade-offs. It is now much quicker and cheaper to allocate and de-allocate space, but some of the classic reports for summarising free space or used space just got more expensive. Instead of querying a single table (fet$ and uet$ respectively) you now have to visit every file header to get a summary of free space, and every segment header to get a summary of used space. However performance is not really the issue, and such reports need not be run frequently (I hope).

Where are the benefits?
There are always three areas in which a new feature might be of benefit: (a) strategic direction, (b) performance and (c) administrative ease. I shall address each topic in turn

Strategically, you should be moving your systems to LMTs.Under Oracle 9.2, the database creation assistant will by default create your database with the system tablespace declared as an LMT.If the system tablespace is an LMT, you will not be able to create any DMTs in the database.Clearly Oracle Corp. expects everyone to migrate to LMTs in the near future - quite possibly DMTs will cease to exist in Oracle 10 - so it would be a smart move to get the migration over and done with before the next version of Oracle arrives. By the way, even if system is an LMT, you will still be able to use the transportable tablespace mechanism to attach a DMT to the database but that tablespace will have to remain read-only.

As far as LMTs are concerned, performance is pretty much a non-issue. Although the 'amazing' performance benefit of the bitmap management seems to be a commonly touted reason for switching from DMTs to LMTs, it only takes a couple of minutes thought about when, where, and how often you get this benefit to make you realise that it is pretty irrelevant. Just ask yourself - how often should you be allocating and de-allocating space ? The correct answer is - hardly ever. Consider the commonest occasions.

1)You have allocated a permanent tablespace instead of any form of temporary tablespace as the users' temporary_tablespace (An option that is blocked in v9 with error ORA-12911: permanent tablespace cannot be temporary tablespace). Consequently all sorting, hashing, temporary lobs and temporary tables get dumped into permanent data segments instead of using the sort extent pool. This could result in a performance problem that could be reduced somewhat by using LMTs, but this isn't an LMT/DMT issue, it is a temporary/permanent issue.

You have allocated a tablespace of contents type temporary for the users' temporary_tablespace, but the extent size you have allocated is extremely small and some sort operations push the extent demand up to tens of thousands, or even hundreds of thousands, of extents. The next time you restart the database, smon runs at 100% CPU for ages with a serious blocking effect on most database activity. This could result in a performance hit that could be reduced dramatically by using LMTs, but this is generally an administrative error, not an inherent performance issue. Admittedly - a user's temporary tablespace has to cope with temporary LOBs, temporary tables, sorting and hashing, and these uses may not be compatible: consequently you may make a deliberate decision to accept this issue - in which case you definitely do need LMTs for your temporary tablespaces.

You have created several important, high-volume data objects with a small initial and next extent, and a pctincrease of one (following a well-known and frequently quoted piece of mis-direction). Consequently you have many objects which keep allocating extents because each extent request is for a small extent which is soon filled. Moreover, each request is for an odd sized extent, and therefore typically requires close to maximum work before Oracle decides how to allocate it. This could result in a performance hit that could be reduced significantly by using LMTs, but it is an administrative error, not an inherent performance issue.

Your application frequently creates and drops tables on the fly to store transient results. This results in high-stress activity on the space management system. This strategy probably will result in a performance hit, but it is a design error, not an inherent performance error. However for 3rd party applications where you can't get the error corrected this is the one case where the normally marginal performance benefit of LMTs could be a necessary and important damage-limitation exercise (in the short term).

All the above 'performance threats' can be avoided, or minimised, without resorting to LMTs; and many DBAs have been taking the necessary steps to avoid them for many years. There is a well-known paper on the topic available through Metalink or OTN (How to stop defragmenting and start living), but in short:
1.Don't use storage clauses with objects, always use tablespace defaults
2.Set pctincrease = 0 as the tablespace default
3.Set initial = next as the tablespace default
4.Set the minimum extent clause on tablespaces, to match the initial/next
5.Put objects in tablespaces which are appropriate for the expected object size.
6.Don't export with compress = y if you plan to recreate tables from an import.
Make sure you understand the requirements for temporary space, and declare and allocate (multiple) temporary tablespaces accordingly
7.Avoid using permanent tables for transient data - look at global temporary tables

But why is it so convenient to force every extent in the tablespace to be the same size ?
First, ease of monitoring space; secondly, convenience of data packing, and third, reliability of object rebuilds.
Do you have a complicated little script for working out whether or not the next extent for any object in the database will be able to find a large enough space in the right tablespace ? If you use uniform LMTs, then this script simplifies to - "Is there any freespace in the tablespace, if so, then it is usable." You could even go so far as to base your reports on a couple of very simple queries:

select tablespace_name, initial_extent
from user_tablespaces
where extent_management = 'LOCAL'
and allocation_type = 'UNIFORM'
-- you might include 'SYSTEM'
;

select tablespace_name, count(*)
from dba_segments
group by tablespace_name
;

select tablespace_name, sum(bytes)
from dba_free_space
group by tablespace_name
;

By making (some localised variant of) these three queries into /*+ no_merge */ in-line views and joining them with a suitable outer join, you could, for example, produce a report showing how many data segments you have per tablespace, and how many of them could extend simultaneously without causing a problem.

Similarly, start with a simple query such as:
select
tablespace_name, segment_name, partition_name, extents
from dba_segments

You could then combine this with the first of the three queries above to capture a daily, or weekly, list showing number of extents per object, which gives you the option for producing a 'diff' report of segment growth that can be used to predict future space requirements. Once you have a mechanism that allows you to equate number of extents with size of object it is so much easier to recognise patterns,

The target then, is to ensure that you pick uniform sizes that make it possible to produce warning reports and predictive reports that are useful. And a key feature of usefulness means they should appear only when they have something important to say, and then don’t hide it away under a huge volume of trivia and irrelevancy