7.3 Tablespace management

---

In Chapter 3 (Oracle Database Concepts) we have discussed the different types of tablespace management, it would be ideal to mention that LMT has considerable performance benefits over the traditional dictionary- managed tablespace (DMT) option. A locally managed tablespace tracks all extent information in the tablespace itself, using bitmaps in each of the data files. This results in increased performance because space allocations and deallocations modify local resources (bitmaps in the header) as opposed to requiring enqueues when the dictionary is updated with this information. Performance is also increased because recursive operations that are sometimes needed during dictionary-managed space allocation are no longer required. Tablespace storage can also be simplified by using an AUTOALLOCATE clause during tablespace creation that causes Oracle to automatically select and manage the extent sizes used in the tablespace with a minimum extent size of 64 KB. As the number of extents increases, the size of subsequent extents will also increase in multiples of 64 KB.

In a RAC implementation the traditional approach (DMT) provides even greater overhead because multiple instances would be inserting data into the same tablespace. If one or more instances encounter a need to add or modify an extent, then it causes considerable contention because the information needs to be written to the data dictionary. On the other hand, LMT maintains a local copy of the information in the data file header and would not require any interinstance updates to the data dictionary. In versions of the database prior to Oracle 9i, Oracle used data structures called FREELISTS to keep track of blocks within an object, which have enough free space to allow insertion of a new row. The numbers of FREELISTS and FREELIST GROUPS are defined when creating an object. The value of the parameter PCTUSED is used to place a block in and out of the FREELIST.

The new mechanism makes the space management within an object completely transparent by using bitmaps to track the space utilization of each data block allocated to the object. The state of the bitmap indicates how much free space exists in a given data block (i.e., > 75%, between 50 and 75%, between 25 and 50% or <25%) as well as whether it is formatted or not.

LMT eliminates the necessity to tune space-management-related controls (such as FREELISTS, FREELIST GROUPS and PCTUSED) thereby freeing database administrators from manually managing the space within a database object. At the same time, it improves the space utilization, since the database now has a more accurate knowledge of how free a data block is. This enables better reuse of the available free space, especially for objects with rows of highly varying size.

The improvements in performance and management by the automatic segment space management feature are particularly noticeable in a RAC environment. It removes the need to alter the number of FREELISTS and FREELIST GROUPS when new instances are brought online and thereby saves the downtime associated with such table reorganizations.

Using the clause EXTENT MANAGEMENT LOCAL with the tablespace create statement would create an LMT. Oracle also supports creation of uniform extents, meaning that each extent be of the same size.

For example, to create a tablespace for table JOB_HISTORY, the following command will create a locally managed tablespace of size 900 MB with a 2 MB uniform size and with the automatic segment management option:

CREATE TABLESPACE JOBHIST_DATA_P001 DATA FILE   '/dev/vx/oraracdb/partition3G_44' size 2867264K   EXTENT MANGEMENT LOCAL UNIFORM SIZE 2M   SEGMENT SPACE MANAGEMENT AUTO;

Note 

This creates a data file of size 2800 MB plus 64 KB, allowing for the additional size needed to contain the extent management.

When creating tablespaces with a uniform extent size, it is important to understand that Oracle tries to reserve at least 64 KB per data file for the metadata blocks, which includes both file space header as well as bitmap header.

So for a 8 KB block size, there are one space header block and seven bitmap blocks under normal circumstances. Oracle always tries to make sure that there is at least one unit/extent of free space for user data.

In a 100 KB data file, when a tablespace with 50 KB extent size is created, Oracle must leave seven blocks (50 KB rounded to nearest multiple of block size) for the user data. This leaves six blocks for metadata, of which one is used for file header and five for bitmap headers. In a 100 KB data file, when a tablespace with 10 KB extent size is created, there is enough space to allocate 64 KB for metadata. However, this leaves five unused blocks.

However, since extent sizes are internally rounded up to the nearest multiple of block size, the actual block size is 16 KB, since we need two blocks for each extent. This means that 40 KB of available free space can only accommodate two extents (i.e., four blocks) and only one block is wasted. When creating large database files, add an additional 64 KB to the size of your data file.

Consider the following (bad) example to illustrate the matter:

CREATE TABLESPACE USRPRL_DATA_P001 DATA FILE    '/dev/vx/oraracdb/partition10M_4' size 10M    EXTENT MANGEMENT LOCAL UNIFORM SIZE 5M;  Statement processed. 

On executing the following query to determine the amount of free space that is available after the tablespace creation:

WHERE TABLESPACE_NAME ='USRPRL_DATA_P001' BYTES ---------- 5242880 COL NAME FORMAT A45 SELECT DFS.TABLESPACE_NAME, DFS.BYTES, DFS.BLOCKS, DF.NAME FROM DBA_FREE_SPACE DFS, V$DATA FILE DF WHERE DFS.RELATIVE_FNO = DF.FILE#

From the output above, please note that the tablespace USRPL_DATA_P001 was created with 5 MB extents in a 10 MB file. After 64 KB are allocated for the metadata, it is left with one 5 MB extent and one less than 5 MB extent. Because the extents are created of uniform size and the 5 MB that should be remaining are actually less than 5 MB (5 MB–64 KB), it does not get displayed and is wasted.

As noted above, with larger uniform extents when the remainder of space in the data file is just 64 KB short of being able to accommodate an additional uniform extent size the remainder of the space remains unused.

If the same tablespace were created with the following script to allow for the extra 64 KB, there would be a better utilization of the space.

CREATE TABLESPACE USRPRL_DATA_P001 DATA FILE    '/dev/vx/oraracdb/partition10M_4' size 10304K    EXTENT MANGEMENT LOCAL UNIFORM SIZE 5M;  Statement processed.

Note 

This creates a data file of size 10,304 KB (10 MB plus 64 KB = 10,304 KB), allowing for the additional size needed to contain the extent management.

On executing the earlier query to determine the space utilization below:

SELECT BYTES FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME ='USRPRL_DATA_P001' COL NAME FORMAT A45 SELECT   DFS.TABLESPACE_NAME,   DFS.BYTES,   DFS.BLOCKS,   DF.NAME FROM DBA_FREE_SPACE DFS,   V$DATA FILE DF WHERE DFS.RELATIVE_FNO = DF.FILE# 

When 64 KB were added to the data file size, the full two extents were available (5 MB and 5 MB). LMT should have data files that are 64 KB larger than a multiple of their extent size when using uniform sizing and when the data file size is (Extent Size ) 64 KB) or greater.

There is a similar requirement when an additional data file needs to be added to a LMT.

ALTER TABLESPACE USPRL_DATA_P001 ADD DATA FILE    '/dev/vx/rdsk/oraracdg/vertpartition3G_14' size 2867264k

7.3.1 Migrating from dictionary managed

Oracle provides the PL/SQL package DBMS_SPACE_ADMIN for additional management of locally managed tablespaces. Almost all of the procedures in the package are used to modify or verify the tablespace bitmaps, in some cases to aid in the recovery process. The DBMS_SPACE_ADMIN package also contains a procedure to migrate a dictionary-managed tablespace to a locally managed tablespace (TABLESPACE_MIGRATE_TO_LOCAL), and a procedure that will migrate a locally managed tablespace to a dictionary- managed tablespace (TABLESPACE_MIGRATE_FROM_LOCAL).

---