Friday, June 24, 2011

No mysteries of Oracle Locally managed tablespace (LMT) and Automatic segment space management (ASSM)

Initially I called this post "Mysteries of ...", but I decided to change it to "No mysteries of...". Really there are no mysteries there with understanding.

The syntax to create a tablespace,
CREATE TABLESPACE xxx DATAFILE ...
EXTENT MANAGEMENT LOCAL [AUTOALLOCATE|UNIFORM SIZE nn]
[SEGMENT SPACE MANAGEMENT AUTO|MANUAL]
/

With LMT:
1. The NEXT storage parameter becomes obsolete.

2. The default extent allocation is AUTOALLOCATE, meaning Oracle will automatically determine the size of the extents. In my database here, for an object without INITIAL specified, the default size of an extent and the INITIAL is 640k. For objects with INITIAL from 100M~700M, the average extent size is 8M; For an object with INITIAL set to 8G, the average extent size is 62M – by looking at BYTES/EXTENTS in DBA_SEGMENTS.

3. When specifying UNIFORM SIZE nn, all the extent size is nn

4. Some experts including Tom Kyte recommended to use AUTOALLOCATE over UNIFORM SIZE: Just use system allocated extent sizes, segments will start small and grow in extent size as needed.

With ASSM:
1. It automates freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists.

2. Storage parameters including PCTUSED, FREELISTS, and FREELIST GROUPS are out. Columns DBA_TABLES(PCT_USED,FREELISTS, FREELIST_GROUPS) and DBA_SEGMENTS(FREELISTS, FREELIST_GROUPS) are NULL.

No comments:

Post a Comment