http://www.databasejournal.com/features/oracle/article.php/10893_2223631_1/Understanding-Oracles-Locally-Managed-Tablespaces.htm
Understanding Oracle's Locally Managed Tablespaces
By Amar Kumar Padhi
Locally Managed Tablespace (LMT) is one of the key features in Oracle database. These have been made available since 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. Below are the key benefits offered by LMTs. Not all are achievable when migrating to LMTs. Dictionary contention is reduced. Extent management in DMTs is maintained and carried out at the 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 the dictionary. Extents are managed at the datafile level in LMTs. Dictionary tables are no longer used for storing extent allocation/deallocation information. The only information still maintained in the dictionary for LMTs is the 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 the 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. 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 <tablespace name> 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 the case of DMTs. 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 type plays a very important role in how the LMT is behaving. It specifies how the extent is 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. The advantage is that allocation of extents is managed at the datafile level and such tablespaces will not compete for ST enqueue. The 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 the 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 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. Oracle allocates four 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 example below to clarify this. As per the storage clause, the table should be allocated 200K + 100K of space (since minextents is 2). Oracle rounds off on the higher side and allocates 10 extents of 32K, totaling 320K. Even pctincrease plays a role in uniform LMTs as the below example shows. As per the storage clause the required initial size of the table should be 146K (16 + 16 + 24 + 36 + 54), Oracle rounds on the higher side to 160K (5 32K extents). Hence, storage could be used to allocate the initial size for an object. The Default Storage clause cannot be specified for LMTs at tablespace level. Please refer the example section for LMT creations and migration examples.Benefits of LMTs
ORA-01575: timeout warning for space management resource
Management of Extents in LMTs
Allocation Types in LMTs
Storage parameters usage in LMT
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
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
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
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
