Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 138,101
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

LMT vs DMT Tablespaces.

Posted by FatDBA on December 26, 2012

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
—————————— ———- ———
SYSTEM                         DICTIONARY USER
SYS_UNDOTS                     LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in “dictionary managed” mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

SQL> CREATE TABLESPACE etr1 DATAFILE ‘/oradata/etr1_01.dbf’ SIZE 80M
EXTENT MANAGEMENT DICTIONARY
Using LMT, each tablespace manages it’s own free and used space within a bitmap structure stored in one of the tablespace’s data files.

SQL> CREATE TABLESPACE etr2 DATAFILE ‘/oradata/etr2_01.dbf’ SIZE 80M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 

Advantages in using Locally Managed Tablespaces Over Dictionary Managed Tablespaces

  • Because locally managed tablespaces do not record free space in data dictionary, it reduces contention on these tables.
  • Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
  • Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
  • Reduced fragmentation
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: