Friday, October 5, 2012

LMT Vs ASSM

Two methods to manage space :


  • Locally managed tablespace (LMT)—The LMT is implemented by adding the EXTENT MANAGEMENT LOCAL clause to the tablespace definition syntax. Unlike the older dictionary managed tablespaces (DMTs), LMTs automate extent management and keep the Oracle DBA from being able to specify the NEXT storage parameter to govern extent sizes. The only exception to this rule is when NEXT is used with MINEXTENTS at table creation time.

  • Automatic segment space management (ASSM)—The ASSM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition syntax. ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.(method used by Oracle to manage space inside data blocks. It eliminates the need to specify parameters like PCTUSED, Freelists and Freelist groups for objects created in the tablespace. )

both of these space management methods are optional features, and Oracle gurus may still use the more detailed methods should they desire to do so. It is important to note that bitmap segment management in Oracle9i is optional and can only be implemented at the tablespace level. Existing systems may continue to use the traditional method of freelist management.

 Example :

CREATE TABLESPACE ts1 
DATAFILE '/app/orafata/ts1.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL       -- Enable LMT
SEGMENT SPACE MANAGEMENT AUTO -- Enable ASSM
 Some Tips For LMT :

From DMT to LMT: 
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

Benfits Of LMT : 
1-Dictionary contention is reduced.
2-Space wastage removed.
3-ST enqueue contention reduced.

Limitations of ASSM :

1-Once allocated, the DBA has no control over the storage behavior of individual tables and indexes inside the tablespace.
2-Large objects cannot use ASSM, and separate tablespaces must be created for tables that contain LOB datatypes.
3-You cannot create a temporary tablespace with ASSM. This is because of the transient nature of temporary segments when sorting is performed.
4-Only locally managed tablespaces can use bitmap segment management.
5-There may be performance problems with super high-volume DML (e.g., INSERTs, UPDATES, and DELETEs).

Enjoy
Osama Mustafa



No comments:

Post a Comment