Storage Management in Oracle -2

Storage Management in Oracle -2

In this article we will learn about Storage Parameters .

Before reading this article please go through with previous article.

Storage Management in Oracle -1

Storage Hierarchy Summary

In summary, the hierarchy of storage in Oracle is as follows:

1. A block is the smallest unit of allocation in the database. A block is the smallest unit of I/O used by a DB.

2. An extent is a logically contiguous set of blocks on disk. An extent is in a single tablespace and, furthermore, is always in a single file within that tablespace.

3. A segment (TABLE, INDEX, and so on) is made up of one or more extents. A segment exists in a tablespace, but may have data in many data files within that tablespace.

4. A tablespace is made up of one or more data files. These files might be cooked files in a file system, raw partitions, ASM managed database files, or a file on a clustered file system. A tablespace contains segments.

5. A database is made up of one or more tablespaces

How oracle allocates certain size for extent ?

Answer :  Storage Parameters

STORAGE PARAMETERS :

Every Tablespace has default storage parameters.

To override the system defaults in that Tablespace, a user can specify the parameters while creating the objects.

The following are the parameters:

INITIAL:

The size in bytes of the first extent allocated when a segment is created. Though default system values are given data blocks, use bytes to set a value for this Parameter. You can also use the abbreviations K and M to indicate Kilobytes and Megabytes.

(a)Default:5 datablocks  (b) Minimum:2 datablocks  (c)Maximum:Operating system specific

NEXT: The size of the next extent to be allocated for a segment. The second extent is equal to the original setting for next. From third extent onwards ‗Next‘ is set to the previous size of Next multiplied by (1+Pctincrease/100). You can also use K and M for kilobytes and megabytes as above. (a)Default:5 datablocks  (b)Minimum:1 datablock    (c)Maximum:Operating system specific

MAXEXTENTS:

The total number of extents, including the first, can ever be allocated for the segment.

Default:Dependent on the data block size and operating system

(a)Minimum:1 (extent)    (b)Maximum:Operating system specific

MINEXTENTS:

The total number of extents to be allocated when the segment is created. This allows for a large allocation of space at creation time, even if contiguous space is not available.

(a)Default:1 (extent)      (b)Minimum:1 (extent)     (c)Maximu:Operating system specific

If minextents are more than 1, then the specified number of incremental extents is allocated at creation time using initial, next, pctincrease.

PCTINCREASE:

The percent by which each incremental extent grows over the last incremental extent allocated for a segment. If pctincrease is 0, then all incremental extents are the same size. If pctincrease is greater than 0, then each time the next is calculated, it grows by pctincrease. It cannot be negative. It is specified in percentage. (a)Default:50 (%)        (b)Minimum:0 (%)        (c)Maximum:Operating system specific

Note:

Pctincrease for Rollback Segment is always 0. Pctincrease cannot be specified for Rollback Segments.

PCT FREE:

It is used to set percentage of a block to be reserved (kept free) for future updates. After this parameter is met the block is considered to be full and it is not available to insert new rows.

PCTUSED: It is used to allow a block to be reconsidered for the insertion of new rows. When the percentage of a block being used falls below PCTUSED either through row deletion or updates reducing column storage, the block is again available for insertion of new rows.

INITTRANS:

It reserves pre-allocated amount of space for initial number transaction entries to access rows in the data block concurrently. Space is reserved in the header of all data blocks of all associated data or index segment. The default value is 1 for tables and 2 for clusters.

MAXTRANS:

As multiple transactions concurrently access the rows of the same data block, space is allocated for each transaction‘s entry in the block.

Once the space is reserved by the inittrans is depleted, space for additional transaction entries is allocated out of the free space in a block, if available. Once allocated, this space effectively becomes a permanent part of the block header. The maxtrans parameter is used to limit the no. of transaction entries that can concurrently use data in a data block.

 

EXTENT MANAGEMENT :

Extent Management is categorized in two ways . They are :

1.Dictionary Managed tablespaces

2.Locally Managed tablespaces

 

In Oracle, from version 6-8 we have only DMTS

In Version 8i LMTS was introduced but it was optional .The Default is DMTS(8i).

From Version 9i-12c ,The Default tablespace is LMTS and DMTS will be optional.

 

Dictionary Managed Tablespace :

Tablespaces that record extent allocation in the dictionary are called Dictionary Managed Tablespaces (DMT). With this approach the data dictionary contains tables that store information that is used to manage extent allocation and deallocation manually.

Extents are managed in the data dictionary

Each segment stored in the tablespace can have different storage clause Coalescing is required

Example

The DEFAULT STORAGE clause enables you to customize the allocation of extents.  This provides increased flexibility, but less efficiency than locally managed tablespaces.

The tablespace will be in a single, 50M datafile.

The EXTENT MANAGEMENT DICTIONARY clause specifies the management.

All segments created in the tablespace will inherit the default storage parameters unless their storage parameters are specified explicitly to override the default.

The storage parameters specify the following:

INITIAL – size in bytes of the first extent in a segment.

NEXT – size in bytes of second and subsequent segment extents.

PCTINCREASE – percent by which each extent after the second extent grows.

o SMON periodically coalesces free space in a dictionary-managed tablespace, but only if the PCTINCREASE setting is NOT zero.

Use ALTER TABLESPACE <tablespacename> COALESCE to manually coalesce adjacent free extents.

MINEXTENTS – number of extents allocated at a minimum to each segment upon creation of a segment.

MAXEXTENTS – number of extents allocated at a maximum to a segment – you can specify UNLIMITED.

 

Locally Managed Tablespace :

• Tablespaces that record extent allocation in the tablespace header are called Locally Managed Tablespaces (LMT). The extents allocated to a locally managed tablespace are managed through the use of bitmaps. Locally Managed Tablespace (LMT) is one of the key features in Oracle database. These have been made available since Oracle 8i. It is worth using LMTS considering the benefits in doing so.
• Each bit corresponds to a block or group of blocks (an extent).
• The bitmap value (on or off) corresponds to whether or not an extent is allocated or free for reuse.
• Reduced contention on data dictionary tables
• No undo generated when space allocation or deallocation occurs
  No coalescing required Local management is the default for the SYSTEM tablespace beginning with Oracle 12C.
• If the SYSTEM tablespace is locally managed, the other tablespaces must also be either locally managed or read-only.
• Local management reduces contention for the SYSTEM tablespace because space allocation and deallocation operations for other tablespaces do not need to use data dictionary tables.
• The LOCAL option is the default so it is normally not specified.
• With the LOCAL option, you cannot specify any DEFAULT STORAGE, MINIMUM EXTENT, or TEMPORARY clauses.
• UNIFORM – a specification of UNIFORM means that the tablespace is managed in uniform extents of the SIZE specified.
• Use UNIFORM to enable exact control over unused space and when you can predict the space that needs to be allocated for an object or objects.
• Use K, M, G, etc to specify the extent size in kilobytes, megabytes, gigabytes, etc. The default is 1M; however, you can specify the extent size with the SIZE clause of the UNIFORM clause.
• AUTOALLOCATE – a specification of AUTOALLOCATE instead of UNIFORM, then the tablespace is system managed and you cannot specify extent sizes.
• AUTOALLOCATE is the default – this simplifies disk space allocation because the database automatically selects the appropriate extent size.
• AUTOALLOCATE – this does waste some space but simplifies management of tablespace

o Tablespaces with AUTOALLOCATE are allocated minimum extent sizes of 64K – dictionarymanaged tablespaces have a minimum extent size of two database blocks

 

LMTS with Automatic Segment Space Management

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. You can either specify: MANUAL: Specifying these keywords tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows.

AUTO: This keyword tells Oracle to use bitmaps to manage the free space within segments. A bitmap is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows.

Free lists have been the traditional method of managing free space within segments. Bitmaps, however, provide a simpler and more efficient way of managing segment space. They provide better space utilization and completely eliminate any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS attributes for segments created in the tablespace. AUTO is the default.

Note:  For LOBs, you cannot specify automatic segment-space management.

o To create a locally managed permanent tablespace with auto allocate feature:

o To create a locally managed temporary tablespace with uniform feature:

To view information about tablespaces query dba_tablespaces:

 

Demo will continue in next article…….