Oracle Fundamental I - Chapter 9: Storage Structure and Relationships

In this chapter, we are discussing the logical structure of the database, and segment types for their uses. The storage management is one important factor for DBA to manage the database and administrate the space usage in a efficient way.

The logical storage unit of Oracle database is like below
Tablespaces --> Segments --> Extents --> Blocks

Segments:
Table segment: A table is the most common means of storing data within a database. A table segment stores that data for a table that is neither clustered  nor partitioned. Data within a table segment is sotred in no particular order, and the DBA has very little control over the location of rows within the lbocks in a table. All the data in a table segment must be stored in one tablespace.

Table Partition segment: Scalability and availability are major concerns when there is a table in a database with high concurrent usage. In such cases, data within a table may be stored in several partitions, each of which resides in a different tablespace. The Oracle server currently supports partitioning by a range of key values, by a hashing algorithm, and by a lit of values. If a table is partitioned, each partition is a segment, and the storage parameters can be specified to control them independently. Using this type of segment requires the partitioning option within the Oracle enterprise edition.

Cluster segment: A cluster, like a table, is a type of data segment. Rows in a cluster are stored based on key column values. A cluster may contain one or more tables. TAbles in a cluster belong to the same segment and share the same storage characteristics. The rows in a clustered table can be accessed with an index or hashing algorithm.

Index segment: All the entries for a particular index are stored within one index segment. If a table has three indexes, three index segments are used. the purpose of this segment is to look up the location of rows in a table cased on a specified key.

Index-Organized Table segment: In an index-organied table, data is stored within the index based on the key value. An IOT does not need a table lookup, because all the data can be retrieved directly from the index tree.

Index-Partition segment: An index can be partitioned and spread across several tablespace. In this case, each partition in the index corresponds to a segment and cannot span multiple tablespaces. The primary use of a partitioned index is to minimize contention by spreading index input/output (I/O). Use of this type of segment requires the partitioning option within the Oracle9i Enterprise Edition.

Undo segment: An undo segment is used by a transaction that is making changes to a database. Before changing the data or index blocks, the old value is tored in the undo segment. This allows a user to undo changes made.

Temporary segment:
When a user executes commands such as CREATE INDEX, SELECT DISTINCT, and SELECT GROUP BY, the Oracle server tries to perform sorts in memory. When a sort needs more space than the space available in memory, intermecdiate results are written to the disk. Temporary segments are used to streo these intermediate results.

LOB segment:
One or more columns in a table can be used to store large objects (LOBs) such as text documents, images, or videos. If the column is large, the Oracle server stores these values in separate segmentsknown as LOB segments. The table contains only a locator or a pointer to the location of the corresponding LOB data.

Nested Table segment:
A column in a table may be made up of a user-defined table as in the case of items within an order. In such cases, the inner table, which is known as a nested table, is stored as a separate segment.

Bootstrap segment:
A bootstrap segment, also known as a cache segment, is created by the sql.bsq script when a database is created. This segment helps to initialize the data dictionary cache when the database isopened by an insance. The bootstrap segment cannot be queried or updated and does not require any maintenance by the database administrator.

A storage clause can be specified at the segment level to control how extents are allocated to a segment.

• Any storage parameter specified at the segment level overrides the corresponding option set at the tablespace level, except for the MINIMUM EXTENT or UNIFORM SIZE tablespace parameter.
• When storage parameters are not set explicitly at the segment level, they default to those at the tablespace level.
• When storage parameters are not set explicitly at the tablespace level, the Oracle server system defaults are used.
• If storage parameters are altered, the new options apply only to the extents not yet allocated.

Extent Allocation and Deallocation

• An extent is a chunk of space used by a segment within a tablespace
• An extent is allocated when the segment is:
• Created
• Exteneded
• Altered
• An extent is deallocated when the segment is:
• Dropped
• Altered
• Truncated

Used and Free Exxtents
When a tablespace is created, the data files in the tablespace contains a header, which is the first block or blocks in the file.
As segments are created, they are allocated space from the free extents in a tablespace. Continuous space used by a segment is referred to as a used extent. When segments release space, the extents that are released are added to the pool of free extents available in the tablespace.


Database Block

• Minimum unit of I/O
• Consists of one or more operating system blocks. 
• Set at tablespace creation
• DB_BLOCK_SIZE is the default block size 

Since Oracle 9i, Oracle support multiple block sizes.

• A database can be created with a standard block size and up to four nonstandard block sizes.
• Block sies can have any power-of-two value between 2KB and 32KB.
• Current block sizes supported includes 2, 4, 8, 16, 32KB.
• The block size of the SYSTEM tablespace is termed the standard block size.

Standard Block Size

• Set at database creation using the DB_BLOCK_SIZE parameter: cannot be changed without recreating the database.
• Used for SYSTEM and TEMPORARY TABLESPACES
• db_CACHE_SIZE specifies the size of the DEFAULT buffer cache for standard block size:
• Minimum size = one granule (4MB or 16 MB)
• Default value = 48MB

Nonstandard Block Size

• Configure additional caches with the following dynamic parameters:
• DB_2K_CACHE_SIZE for 2KB blocks
• DB_4K_CACHE_SIZE for 4KB blocks
• DB_8K_CACHE_SIZE for 8KB blocks
• DB_16K_CACHE_SIZE for 16KB blocks
• DB_32K_CACHE_SIZE for 32KB blocks
• DB_nK_CACHE_SIZE is not allowed if nK is the standard block sie
• Minimum size for each cache isone granule

Creating Non-standard block size tablespace:
Ex:

CREATE TABLESPACE data01 DATAFILE ‘/oradata/u01/data01.dbf’ SIZE 10m BLOCKSIZE 4K;

Some rules of multiple block sizing:

• All partitions of a partitioned object must reside in tablespaces of the same block size.
• All temporary tablespaces, including the permanent ones that are being used as default temporary tablespaces, must be of standard block size.
• Index-organized table overflow and out-of-line LOB segments can be stored in a tablespace with a block size different from teh base table.

• Block header: The header contains the data block address, table directory, row directory, and transaction slots that are used when transactions make changes to rows in the block. Block headers grow from the top down.
• Data space: Row data is inserted into the block from the bottom up.
• Free space: The free space in a block is in the middle of the block. Thus both the deader and the row data space can grow when necessary. The free space in a block is contiguousinitially. However, deletions and upddates may fragment the free space in the block. The free space in the block is coalesced by the Oracle server when necessary.

Block space utilization paramters:
Some space utilization parameters like INITRANS, MAXTRANS, PCTFREE and PCTUSED.

INITRANS and MAXTRANS: Specify the initial and the maximum number of transaction slots that are created in an index or a data block.

PCTFREE: Specify for a data segment, the percentage of space in each data block that is reserved for growth resulting from updates to rows in the block. The default for PCTFREE is 10%

PCTUSED: For a data segment, this parameter represents the minimum percentage of used space that the Oracle server tries to maintain for each data block of the table. The default is $40%.


Data Block management:
There are two ways to managing data blocks:
Automatic segment-space management

• It is a method of managing free space inside database segments
• Tracking in-segment free and used space is done using bitmaps as opposed to using free lists
• This method provides:
• Ease of management
• Better space utiliation
• Better performance for concurrent INSERT operations
• Bitmap segments contain a bitmap that describes the status of each blocks in the segment with respect to its available space.
• The map is contained in a separate set of blocks referred to as bitmapped blocks (BMBs).
• When inserting a new row, the server searches the map for a block with sufficient space.
• As the amount of space available in a block changes, its new state is reflected in the bitmap.
• Automatic segment-space management can be enabled at the tablespace level only, for locally managed tablespaces

EX:

CREATE TABLESPACE data01 DATAFILE ‘/oradata/u01/data01.dbf’ SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;

Manual Data Block management:

• Allows you to configure data blocks manually using parameters such as:
• PCTFREE
• PCTUSED
• FREELIST

The example makes it easier to understand how PCTFREE and PCTUSED to work:
PCTFREE=20%, PCTUSED=40%
1. Rows are inserted into block until the free space in the block is equal to or less than 20%. The block is no longer available for inserts when rows occupy 80% (100 - PCTFREE) or more of the available data space in the block.
2. The remaining 20% can be used when the size of a row increases. For example, a column that was originally NULL is updated to be assigned a value. Thus block utiliation may be in excess of 80% as a result of updates.
3. If rows are deleted in the block or if rows decrease in sie as a result of udpates, block utilization may fall below 80%. HOwever, a block is not used for inserts until the utilization falls below PCTUSED, which in this example, is 40%
4. When the utiliation falls below PCTUSED, the block is available for inserts. As rows are inserted into the bloc, the utiliation of the block increases and the cycle repeats starting with step 1.

Some views are very useful for obtaining storage inforamtion:

• DBA_EXTENTS
• DBA_SEGMENETS
• DBATABLESPACES
• DBA_DATA_FILES
• DBA_FREE_SPACE

Ex: DBA_SEGMENTS
SQL> select segment_name, tablespace_name, extents, blocks from dba_segments;
SEGMENT_NAME   TABLESPACE_NAME      EXTENTS   BLOCKS
------------------------ ------------------------------- -------------  ----------
REGIONS                  SAMPLE                              79               8192
LOCATIONS             SAMPLE                              79               8192
JOBS                          SAMPLE                              79               8192