DBA Tips Archive for Oracle
Dictionary vs. Locally Managed Tablespaces
by Jeff Hunter, Sr. Database Administrator
Prior to Oracle8i, extent management was always controlled by dictionary tables, which in many cases caused, contention within the data dictionary for busy systems with lots of inserts and updates. Oracle8i introduced a new method of extent management called Locally Managed Tablespaces (LMT). With this new method, the EXTENT MANAGEMENT clause was introduced as an option to the
CREATE TABLESPACEstatement. Using this new clause allows the DBA to create tablespaces with extent management defined as either DICTIONARY or LOCAL
The following article provides a brief overview of the differences between both Dictionary Managed and Locally Managed tablespaces.
Dictionary Managed Tablespaces
By declaring a tablespace as DICTIONARY managed, you are specifying that extent management for segments in this tablespace will be managed using the following dictionary tables:
sys.fet$ Every extent for a particular segment is represented by a row in the
sys.fet$is actually clustered in the
sys.uet$ Data dictionary view used to store used extents.
sys.uet$is actually clustered in the
Oracle updates the above tables in the data dictionary whenever an extent is allocated, or freed for reuse. This is the default in Oracle8i when no extent management clause is used while creating the tablespace.
Creating a Dictionary Managed Tablespace
The following is an example of creating a DICTIONARY managed tablespace in Oracle8i:CREATE TABLESPACE users DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 2 MAXEXTENTS 121 PCTINCREASE 0);
The following is an example of creating a DICTIONARY managed tablespace in Oracle9i:CREATE TABLESPACE users DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 2 MAXEXTENTS 121 PCTINCREASE 0);
Locally Managed Tablespaces
A Locally Managed Tablespace is a tablespace that manages its own extents by maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle simply changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for tablespace quota information). Some of the benefits of using a locally managed tablespace are:
- Reduced recursive space management.
- Reduced contention on data dictionary tables.
- No rollback generated.
- No coalescing required
While free space is represented in a bitmap within the tablespace, used extents are only recorded in the extent map in the segment header block of each segment, and if necessary, in additional extent map blocks within the segment.
Keep in mind though, that this information is not cached in the dictionary cache. It must be obtained from the database block every time that it is required, and if those blocks are not in the buffer cache, that involves I/O and potentially lots of it.
This is the default in Oracle9i. If COMPATIBLE is set to 9.0.0, then the default extent management for any new tablespace is locally managed in Oracle9i. If COMPATIBLE is less than 9.0.0, then the default extent management for any new tablespace is dictionary managed in Oracle9i.
Prior to Oracle 9.2, you may use the EXTENT MANAGEMENT clause when creating permanent and temporary tablespaces other than SYSTEM. In Oracle 9.2, all tablespaces, including the SYSTEM tablespace, can be locally managed. When you specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement, you cause Oracle to create a locally managed SYSTEM tablespace wherein Oracle determines extent sizes. The COMPATIBLE initialization parameter must be set to 9.2 or higher for this statement to be successful. A locally managed SYSTEM tablespace is created AUTOALLOCATE by default. It is not possible to create a locally managed SYSTEM tablespace and specify UNIFORM extent size.
The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for segments stored in locally managed tablespaces.
To create a locally managed tablespace, you specify LOCAL in the extent management clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option, or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).
If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice. If it is not important to you to have a lot of control over space allocation and deallocation, AUTOALLOCATE presents a simplified way for you to manage a tablespace. Some space may be wasted but the benefit of having Oracle manage your space most likely outweighs this.
On the other hand, if you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. It ensures that you will never have an unusable amount of space in your tablespace.
For system-managed extents, Oracle determines the optimal size of extents, with a minimum extent size of 64K. This is the default for permanent tablespaces. For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.
If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default.
NOTE: When you allocate a datafile for a locally managed tablespace, you should allow space for metadata used for space management (the extent bitmap or space header segment) which are part of user space. For example, if you do not specify the SIZE parameter in the extent management clause when UNIFORM is specified, the default extent size is 1MB. Therefore, in this case, the size specified for the datafile must be larger (at least one block plus space for the bitmap) than 1MB.
Creating a Locally Managed Tablespace
The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.CREATE TABLESPACE users DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.CREATE TABLESPACE users DATAFILE '/u10/app/oradata/TESTDB/user01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
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.
- Avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.
- 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.
- Changes to the extent bitmaps do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).
- Reduced fragmentation .
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Friday, 20-Jan-2006 10:22:14 EST
Page Count: 61810