Storage parameter in Oracle.

Storage parameter in Oracle.

Locally vs. Dictionary Managed Tablespaces.

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 tablespace and Tablespaces that record extent allocation in the tablespace header, are called locally managed tablespace.

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

Dictionary Managed Tablespace(DMTS):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extent for tablespace that is in “dictionary managed” mode. Free space is recorded in the SYS.FET$table, and used space in the SYS.UET$table.whenever space is required in one of these tablespaces,the ST(Space transaction enqueuer latch must be obtained to do inserts and deletes against these tables.as only one process  can acquire the ST enque at a given time, this often lead to contention.

Execute the following statement to create a Dictionary ManagedTablespaces:

SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE 50M

      EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablespaces(LMTS):

Using LMTS, each tablespace manages its own free and used space within a bitmap structure stored in one of the tablespace data files. Each bit corresponds to a database block or group of blocks.

Execute one of the following statements to create a locally managed tablespaces:

SQL> CREATE TABLESPACE ts2 DATAFILE '/oradata/ts2_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SQL> CREATE TABLESPACE ts3 DATAFILE '/oradata/ts3_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Note the difference between AUTOALLACATE AND UNIFORM SIZE:

AUTOALLOCATE specifies that extent sizes are system managed. Oracle will choose “optimal” next extent sizes starting with 64KB. As the segment grows larger extent sizes will increase to 1MB, 8MB and eventually to 64MB. This is the recommended option for a low or unmanaged environment.

UNIFORM specifies that the tablespace is managed with uniform extents of size bytes (use KB or MB to specify the extent size in kilobytes or megabytes). The default size is 1MB. The uniform extent size of a locally managed tablespace cannot be overridden when a schema object,such as a table or an index is created.

Also not, if you specify, LOCAL you cannot specify DEFAULT STORAGE, MINIMUM EXTENT or TEMPORARY.

Advantages of LOCALLY MANAGED TABLESPACES:

·         Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)

·         Reduce contention on data dictionary tables (SINGLE ST ENQUEUE)

·         Locally managed tablespaces eliminated the need to periodically coalesce free space (automatically tracks adjacent free space)

·         Change to the extent bitmaps does not generate rollback information.

Locally Managed System Tablespaces:

From oracle 9i release 9.2 one can change the SYSTEM tablespace to locally manage. Further, if you create a database with DBCA (Database Configuration Assistant), it will have locally Managed SYSTEM Tablespace by default.

The following restrictions apply:

·         No dictionary-managed tablespace in the database can be RW (Read Write).

·         You cannot create new dictionary managed tablespaces

·         You cannot convert any dictionary managed tablespaces to local.

Thus, it is best only to convert the SYSTEM tablespace to LMTS after all other tablespaces are migrated to LMTS.

Segment Space Management in LMTS:

From oracle 9i, one can not only have bitmap managed tablespaces,but also bitmap managed segments when setting segment space management to AUTO for a tablespace.

Look at this example:

SQL> CREATE TABLESPACE ts4 DATAFILE '/oradata/ts4_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Segment Space Management eliminates the need to specify and tune the PCTUSED, FREELISTS and FREELISTS GROUPS storage parameters for schema objects. The automatic Segment Space Management feature improves the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously eliminating serialization for free space lookups against the FREELISTS. This is of particular importance when using RAC, or if “buffer busy waits” are detected.

Convert between LMTS and DMTS:

The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert between LMTS and DMTS mode.

Look at these examples:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');

PL/SQL procedure successfully completed. 

Thank you.

Plz Commands