AmZone

Web Hosting by Netfirms | Free Domain Names by Netfirms

AmZone

A m Z o n e

Understanding Locally Managed Tablespaces

Locally Managed Tablespace (LMT) is one of the key features in Oracle database. These have been made available from Oracle 8i. It is worth using LMTs considering the benefits in doing so. I have put forward some scenarios that may be worth noting for systems that are already using LMTs or planning to shift to LMTs.

Benefits of LMTs

Below are the key benefits offered by LMTs. Not all are achievable when migrating to LMTs.

Dictionary contention reduced.
Extent management in DMTs is maintained and carried out at data dictionary level. This requires exclusive locks on dictionary tables. Heavy data processing that results in extent allocation/deallocation may sometimes result in contentions in dictionary.

Extents are managed at datafile level in LMTs. Dictionary tables are no more used for storing extent allocation/deallocation information. The only information still maintained in the dictionary for LMTs is tablespace quota for users.

Space wastage removed.
In DMTs, there is no implied mechanism to enforce uniform extent sizes. The extent sizes may vary depending on the storage clause provided at the object level or the tablespace level, resulting in space wastage and fragmentation.

Oracle enforces the uniform extents allocation in the LMTs (when created with UNIFORM SIZE clause). Space wastage is removed as this would result in all same sized extents in the tablespace.

No Rollback generated.
In DMTs, all extent allocations and deallocations are recorded in the data dictionary. This generates undo information thus using vital resources and may compete with other processes.

In LMTs, no rollback is generated for space allocation and deallocation activities.

ST enqueue contention reduced.
In DMTs, Space Transaction (ST) enqueue is acquired when there is a need for extent allocations in DMTs. It is also exclusively acquired by SMON process for coalescing free space in DMTs. Only one such enqueue exists per instance, and may sometimes result in contention and performance issues if heavy extent processing is being carried out. The following error is common in such scenario.

ORA-01575: timeout warning for space management resource

As ST enqueue is not used by LMTs it reduces the overall ST enqueue contention.

Recursive space management operations removed.
In DMTs, SMON process wakes up every 5 minutes for coalescing free space in DMTs. Optionally, the ALTER TABLESPACE COALESCE command is also used to coalesce DMTs and reduce fragmentation.

On the other hand, LMTs avoid recursive space management operations and automatically track adjacent free space, thus eliminating the need to coalesce free extents. This further reduces fragmentation.

Fragmentation reduced.
Fragmentation is reduced in LMTs but not completely eliminated. Since adjacent free spaces are automatically tracked there is no need to do coalescing, as is required in case of DMTs.

Management of Extents in LMTs

Oracle maintains a bitmap in each datafile to track used and free space availability in an LMT. The initial blocks in the datafiles are allocated as File Space Bitmap blocks to maintain the extent allocation information present in the datafile. Each bit stored in the bitmap corresponds to a block or a group of blocks. Whenever the extents are allocated or freed, oracle changes the bitmap values to reflect the new status. Such updates in the bitmap header do not generate any rollback information.

The number of blocks that a bit represents in a bitmap depends on the database block size and the uniform extent size allocated to the tablespace. For example, if the DB_BLOCK_SIZE parameter is set to 8K, and the tablespace is created with uniform extent sizing of 64K, then 1 bit will map to one 64K extent, i.e., 64K (extent size)/8K (block size) = 8 database blocks.

Allocation Types in LMTs

Allocation type plays a very important role in how the LMT is behaving. It basically means in what way is the extent being allocated by the system. There are three types of allocating extents in LMTs- USER, SYSTEM and UNIFORM.

USER- The LMT behaves as DMT, allocating extents as per the storage clause provided with the object or defaulted at tablespace level. Advantage is that allocation of extents is managed at datafile level and such tablespaces will not compete for ST enqueue. Disadvantage is that such tablespaces are not subject to uniform extent allocation policy. DMTs that are converted to LMTs fall under this type.

SYSTEM- Oracle manages the space. The extents are auto allocated by the system based on an internal algorithm. Allocation of extents is managed at datafile level and such tablespaces will not compete for ST enqueue. Such tablespaces would have extents of varying sizes and would result in fragmentation and some space being wasted. This is a good alternative if the extent sizes of the various objects to be placed in the tablespace cannot be determined.

UNIFORM- All extents are of fixed size in the system. The size is provided when creating the LMT. This type gives all the benefits offered by LMT and one should aim at achieving this.

Storage parameters usage in LMT

Storage parameters are used in DMTs to specify the object sizing. These parameters are not of much importance in UNIFORM type LMTs but play a role in deciding the initial allocation of space. Oracle considers the storage clause for the initial number of extents that should be allocated. For example, LMT is created with 32K extent size. The database block size is 8k.

SQL> create table am05 (col1 number)  
  2  storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0); 

SQL> select segment_name, segment_type, extent_id,  bytes, blocks
  2  from user_extents where segment_name = 'AM05';

SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
-------------------- ------------------ ---------- ---------- ----------
AM05                 TABLE                       0      32768          4
AM05                 TABLE                       1      32768          4
AM05                 TABLE                       2      32768          4
AM05                 TABLE                       3      32768          4

Oracle allocates 4 extents, the total size being 128K that is closer to the 100K provided for initial extent size. Please note that all the extents allocated have the uniform extent size of 32K. Only the number of extents to be allocated is decided based on the storage clause. See below one more example to clarify this.

SQL> create table am06 (col1 number)
  2  storage(initial 200k next 100k minextents 2 maxextents unlimited pctincrease 0);

SQL> select segment_name, segment_type, extent_id,  bytes, blocks
  2  from user_extents where segment_name = 'AM06';

SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
-------------------- ------------------ ---------- ---------- ----------
AM06                 TABLE                       0      32768          4
AM06                 TABLE                       1      32768          4
AM06                 TABLE                       2      32768          4
AM06                 TABLE                       3      32768          4
AM06                 TABLE                       4      32768          4
AM06                 TABLE                       5      32768          4
AM06                 TABLE                       6      32768          4
AM06                 TABLE                       7      32768          4
AM06                 TABLE                       8      32768          4
AM06                 TABLE                       9      32768          4

10 rows selected.

SQL> select sum(bytes)/1024 from  user_extents where segment_name = 'AM06';

SUM(BYTES)/1024
---------------
            320

As per the storage clause, the table should be allocated 200K + 100K of space (since minextents is 2). Oracle rounds it of on the higher side and allocates 10 extents of 32K, totalling to 320K.

Even pctincrease plays a role in uniform LMTs as the below example shows.

SQL> create table am07  (col1 varchar2(200))
  2  storage(initial 16K next 16K minextents 5 maxextents unlimited pctincrease 50);

Table created.

SQL> select segment_name, segment_type, extent_id,  bytes, blocks
  2  from user_extents where segment_name = 'AM07';

SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
-------------------- ------------------ ---------- ---------- ----------
AM07                 TABLE                       0      32768          4
AM07                 TABLE                       1      32768          4
AM07                 TABLE                       2      32768          4
AM07                 TABLE                       3      32768          4
AM07                 TABLE                       4      32768          4

SQL> select sum(bytes)/1024 from  user_extents where segment_name = 'AM07';

SUM(BYTES)/1024
---------------
            160

As per the storage clause the required initial size of the table should be 146K (16 + 16 + 24 + 36 + 54), Oracle rounds it on the higher side to 160K (5 32K extents).

Hence, storage could be used to allocate the initial size for an object. Default Storage clause cannot be specified for LMTs at tablespace level.

SQL> create tablespace users4
  2  datafile 'D:\oracle\oradata3\users4.dfb' size 5M
  3  autoextend off
  4  extent management local uniform size 32K
  5  default storage(initial 100k next 100k minextents 2 maxextents unlimited pctincrease 50);
create tablespace users4
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

Please refer the example section for LMT creations and migration examples.

DBMS_SPACE_ADMIN Package

This Oracle supplied package is used for managing LMTs. The following key options are available.

TABLESPACE_VERIFY
The first parameter is the tablespace name and the next is the verify option (this defaults to the constant TABLESPACE_VERIFY_BITMAP). This routine verifies the bitmap at tablespace level with the extent maps of the segments present in the tablespace. This ensures the consistency of the bitmap.

	exec dbms_space_admin.tablespace_verify('GLD');

TABLESPACE_REBUILD_BITMAPS
This procedure rebuilds the appropriate bitmap(s). If no bitmap block DBA is specified, then it rebuilds all bitmaps for the given tablespace.

	exec dbms_space_admin.tablespace_rebuild_bitmaps('ECXX');

TABLESPACE_REBUILD_QUOTAS
This procedure rebuilds quota allocations for the given tablespace.

	exec dbms_space_admin.tablespace_rebuild_quotas('USERS');

TABLESPACE_MIGRATE_FROM_LOCAL
To migrate from LMT to DMT. The tablespace should be online and read write during migration.

	exec dbms_space_admin.tablespace_migrate_from_local('USERS');

TABLESPACE_MIGRATE_TO_LOCAL
To move from DMT to LMT. The tablespace should be online and read write during migration. SYSTEM tablespace migration is not supported in 8i releases, this is available in 9i. Migration of temporary tablespace (contents temporary) is not supported, these could be dropped and rebuilt as LMTs.

Tablespaces migrated to locally managed format are USER managed. Thus uniform extent size allocation should be manually achieved. The tables and indexes in such tablespaces will grow according to the storage clause specified.

This procedure takes 3 parameters: tablespace name, the allocation unit size in bytes (optional) and the relative file number (optional) where the bitmap block should be placed for the tablespace.

The relative file number is not required when only one datafile exists in a tablespace. For multiple datafiles, if it is not specified, system will automatically choose one to place the bitmap into. Only one bitmap header is created for all existing files.

The allocation unit size specified should be a factor of the unit size calculated by the system. By default the system calculates the allocation unit size based on the highest common divisor of all extents for the concerned tablespace. This number is further trimmed based on the Minimum Extent of the tablespace. If the specified unit size allocation is not a factor of the unit size calculated by the system, error message is returned. Preferably allow system to compute this value for you.

	exec dbms_space_admin.tablespace_migrate_to_local('ECXX');

Please refer examples below for using the DBMS_SPACE_ADMIN package.

Checking space availability in LMTs

The existing DBA_FREE_SPACE is still available for checking available space in LMT AND DMT tablespaces. Specifically, two more views were introduced by Oracle - DBA_LMT_FREE_SPACE and DBA_DMT_FREE_SPACE. These views show the available blocks that should be multiplied with the block size to get the total bytes.

select name, (sum(a.blocks * 8192))/1024/1024  "size MB"
from   dba_lmt_free_space a, v$tablespace b
where  a.tablespace_id = b.ts#
group  by name;

select name, (sum(a.blocks * 8192))/1024/1024  "size MB"
from   dba_dmt_free_space a, v$tablespace b
where  a.tablespace_id = b.ts#
group by name;

Beware of ORA-600 error that may be encountered when using DBA_LMT_FREE_SPACE. For example the following statement gave me trouble till I found out the reason to be an internal problem that would get resolved in higher releases.

SQL> select * from dba_lmt_free_space where tablespace_id = 1000;
select * from dba_lmt_free_space where tablespace_id = 1000
              *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsitbs_info1], [1000], [], [], [], [], [], []

Examples

The examples below are tried on database version 8.1.7.0.0 with block size of 8K.

To create a new LMT with uniform extents of 32K.

SQL> create tablespace users2
  2  datafile 'D:\oracle\oradata3\user2.dbf' size 5M
  3  autoextend off
  4  extent management local uniform size 32k;

Tablespace created.

SQL> select initial_extent, next_extent, min_extents, max_extents, pct_increase
  2         min_extlen, status, contents, logging, extent_management, allocation_type
  3  from   dba_tablespaces
  4  where  tablespace_name = 'USERS2';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MIN_EXTLEN STATUS    CONTENTS  LOGGING   EXTENT_MAN ALLOCATIO
-------------- ----------- ----------- ----------- ---------- --------- --------- --------- ---------- ---------
         32768       32768           1  2147483645          0 ONLINE    PERMANENT LOGGING   LOCAL      UNIFORM

To create a new LMT that is SYSTEM managed.

SQL> create tablespace users3
  2  datafile 'D:\oracle\oradata3\users3.dbf' size 5M
  3  autoextend off
  4  extent management local autoallocate;

Tablespace created.

SQL> select initial_extent, next_extent, min_extents, max_extents, pct_increase
  2         min_extlen, status, contents, logging, extent_management, allocation_type
  3  from   dba_tablespaces
  4  where  tablespace_name = 'USERS3';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MIN_EXTLEN STATUS    CONTENTS  LOGGING   EXTENT_MAN ALLOCATIO
-------------- ----------- ----------- ----------- ---------- --------- --------- --------- ---------- ---------
         65536                       1  2147483645            ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM

To find out the list of DMTs in the database.

SQL> select tablespace_name, status, contents
  2  from   dba_tablespaces
  3  where  extent_management= 'DICTIONARY';

TABLESPACE_NAME      STATUS    CONTENTS
-------------------- --------- ---------
SYSTEM               ONLINE    PERMANENT
RBS                  ONLINE    PERMANENT
USERS                ONLINE    PERMANENT
TEMP                 ONLINE    TEMPORARY
TOOLS                ONLINE    PERMANENT
INDX                 ONLINE    PERMANENT
DRSYS                ONLINE    PERMANENT

To find out the list of LMTs in the database.

SQL> select tablespace_name, status, contents
  2  from   dba_tablespaces
  3  where  extent_management= 'LOCAL';

TABLESPACE_NAME      STATUS    CONTENTS
-------------------- --------- ---------
OEM_REPOSITORY       ONLINE    PERMANENT
USERS2               ONLINE    PERMANENT
USERS3               ONLINE    PERMANENT

Migrating DMT to LMT. Please note the error given for wrong allocation unit size provided.

SQL> select tablespace_name, status, contents, extent_management, allocation_type
  2  from   dba_tablespaces
  3  where  tablespace_name = 'USERS';

TABLESPACE_NAME                STATUS    CONTENTS  EXTENT_MAN ALLOCATIO
------------------------------ --------- --------- ---------- ---------
USERS                          ONLINE    PERMANENT DICTIONARY USER


SQL> select tablespace_name, status, contents, extent_management, allocation_type
  2  from dba_tablespaces
  3  where tablespace_name = 'ECXX';

TABLESPACE_NAME               |STATUS   |CONTENTS |EXTENT_MAN|ALLOCATIO
______________________________|_________|_________|__________|_________
ECXX                          |ONLINE   |PERMANENT|DICTIONARY|USER

SQL> exec dbms_space_admin.tablespace_migrate_to_local('ECXX', 512);
BEGIN dbms_space_admin.tablespace_migrate_to_local('ECXX', 512); END;

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

SQL> exec dbms_space_admin.tablespace_migrate_to_local('ECXX');

PL/SQL procedure successfully completed.

Migrating tablespace from LMT to DMT
To migrate from LMT to DMT. The tablespace should be online and read write during migration.

SQL> select tablespace_name, status, contents, extent_management, allocation_type
  2  from dba_tablespaces
  3  where tablespace_name = 'ECXX';

TABLESPACE_NAME               |STATUS   |CONTENTS |EXTENT_MAN|ALLOCATIO
______________________________|_________|_________|__________|_________
ECXX                          |ONLINE   |PERMANENT|LOCAL     |USER

SQL> exec dbms_space_admin.tablespace_migrate_from_local('ECXX');

PL/SQL procedure successfully completed.

SQL> select tablespace_name, status, contents, extent_management, allocation_type
  2  from dba_tablespaces
  3  where tablespace_name = 'ECXX';

TABLESPACE_NAME               |STATUS   |CONTENTS |EXTENT_MAN|ALLOCATIO
______________________________|_________|_________|__________|_________
ECXX                          |ONLINE   |PERMANENT|DICTIONARY|USER

Creating LMT with default clause, this would result in an error.

SQL> create tablespace users3
  2  datafile 'D:\oracle\oradata3\users3.dbf' size 5M
  3  autoextend off
  4  extent management local uniform size 32K
  5  default storage (initial 32K next 32k minextents 1 maxextents unlimited pctincrease 10);
create tablespace users3
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

Converting dictionary managed temporary tablespace is not supported as of Oracle 8.1.7.

SQL> exec dbms_space_admin.tablespace_migrate_to_local('TEMPTM');
BEGIN dbms_space_admin.tablespace_migrate_to_local('TEMPTM'); END;

*
ERROR at line 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

Storage parameters do not play a role in UNIFORM and SYSTEM LMTs as extents are handled at tablespace level.

SQL> alter table am1 storage(next 100k);
  alter table am1 storage(next 100k)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

COMPATIBLE parameter should be set to 8.1.6.0.0 or greater when migrating tablespaces.

SQL> select name, value from v$parameter where name = 'compatible';

NAME                                                             VALUE
---------------------------------------------------------------- ---------
compatible                                                       8.1.0

SQL> exec dbms_space_admin.tablespace_migrate_to_local('users', 512);
BEGIN dbms_space_admin.tablespace_migrate_to_local('users', 512); END;

*
ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 8.1.6.0.0 or greater
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

Notes

To move an existing DMT to LMT without losing on any of the LMT features, you may consider creating a new LMT and then moving the objects from the existing DMT to it. This way both uniform extent allocation and local management of extents features are available.
As of Oracle 8.1.7, SYSTEM tablespace cannot be Dictionary managed. It is supported in higher releases.
SMON Process coalesces only DMT tablespaces every 5 minutes, where pctincrease is not set to 0.
As of Oracle 8.1.5, it is possible to create LMTs but not possible to migrate an existing DMT to LMT.
As of Oracle 8.1.6, it is possible to create and migrate to LMT.
Tablespaces are by default created as LMTs in Oracle 9i.
Restriction of having the SYSTEM tablespace as LMT.
Creating or migrating the SYSTEM to LMT is a no return process. Make sure that all the existing DMTs are first converted to LMT before converting SYSTEM tablespace. If any DMT is present in the database after conversion of the SYSTEM to LMT, then it will be marked as READ-ONLY and it cannot be changed to READ-WRITE. The SYSTEM once created or converted to LMT cannot be converted back to DMT. Once the SYSTEM is LMT no more DMTs can be created in the database.
Once all the tablespaces are converted to LMTs, the table FET$ would not contain anymore records.

Conclusion

LMT is highly beneficial and powerful feature, way to go. The management of object extents will become much easier. With implementation of LMTs, one should re-evaluate and revise the extent management and object sizing policies that were followed for DMTs.

This article was hosted on 20/06/03. Press the Back button of you Browser to go to previous page.
Home