Oracle allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments.
The above figure shows the relation between segments , Extents and Data Blocks.
At the finest level of granularity, Oracle stores data in data blocks (also called logical
blocks, Oracle blocks, or pages). One data block corresponds to a specific number of
bytes of physical database space on disk.
The next level of logical database space is an extent. An extent is a specific number of contiguous data
blocks allocated for storing a specific type of information.
The level of logical database storage above an extent is called a
segment. A segment is a set of extents, each of
which has been allocated for a specific data structure and all of which are
stored in the same tablespace. For example, each table's data is stored in its
own data segment, while each index's data is stored
in its own index segment. If the table or index is
partitioned, each partition is stored in its own segment.
Oracle allocates space for segments in units of one extent. When the
existing extents of a segment are full, Oracle allocates another extent for that
segment. Because extents are allocated as needed, the extents of a segment may
or may not be contiguous on disk.
A segment and all its extents are stored in one tablespace. Within a
tablespace, a segment can include extents from more than one file; that is, the
segment can span datafiles. However, each extent can contain data from only one
datafile.
Although Oracle DBA can allocate additional extents, the blocks themselves
are allocated separately. If Oracle DBA allocate an extent to a specific instance, the
blocks are immediately allocated to the free list. However, if the extent is not
allocated to a specific instance, then the blocks themselves are allocated only
when the high water mark moves. The high water mark
is the boundary between used and unused space in a segment.
Data Blocks Overview
Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of I/O used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.
The standard block size is specified by the initialization parameter DB_BLOCK_SIZE. In addition, Oracle9i, Release 1 (9.0.1), permits specification of up to five nonstandard block sizes. The data block sizes should be a multiple of the operating system's block size within the maximum limit to avoid unnecessary I/O. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.
Data Block Format
The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 3-2 illustrates the format of a data block.
Header (Common and Variable)
The header contains general block information, such as the block address and the type of segment (for example, data, index, or rollback).
Table Directory
This portion of the data block contains information about the table having rows in this block.
Row Directory
This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).
Once the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to' have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.
Overhead
The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.
Row Data
This portion of the data block contains table or index data. Rows can span blocks.
Free Space
Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a nonnull value). Whether issued insertions actually occur in a given data block is a function of current free space in that data block and the value of the space management parameter PCTFREE.
In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes.
Free Space Management
Free space can be managed automatically or manually.
With Oracle9i, Release 1 (9.0.1), free space can be managed automatically inside database segments. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. Use of automatic segment-space management offers the following benefits:
Ease of use
Better space utilization, especially for the objects with highly varying size rows
Better run-time adjustment to variations in concurrent access
Better multi-instance behavior in terms of performance/space utilization
Preparation for future enhancements, such as in-space segment reorganization and in-place tablespace reorganization
Oracle DBA specify automatic segment-space management when Oracle DBA create a locally managed tablespace. The specification then applies to all segments subsequently created in this tablespace.
Availability and Compression of Free Space in a Data Block
Two types of statements can increase the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:
If the INSERT statement is in the same transaction and subsequent to the statement that frees space, the INSERT statement can use the space made available.
If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being executed by another user), the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.
Released space may or may not be contiguous with the main area of free space in a data block. Oracle coalesces the free space of a data block only when (1) an INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece, and (2) the free space is fragmented so the row piece cannot be inserted in a contiguous section of the block. Oracle does this compression only in such situations, because otherwise the performance of a database system decreases due to the continuous compression of the free space in data blocks.
Row Chaining and Migrating
In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.
However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.
When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.
PCTFREE, PCTUSED, and Row Chaining
For manually managed tablespaces, two space management parameters, PCTFREE and PCTUSED, enable Oracle DBA to control the use of free space for inserts of and updates to the rows in all the data blocks of a particular segment. Specify these parameters when Oracle DBA create or alter a table or cluster (which has its own data segment). Oracle DBA can also specify the storage parameter PCTFREE when creating or altering an index (which has its own index segment).
The PCTFREE Parameter
The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that Oracle DBA specify the following parameter within a CREATE TABLE statement:
PCTFREE 20
This states that 20% of each data block in this table's data segment will be kept free and available for possible updates to the existing rows already within each block. New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area, until the row data and overhead total 80% of the total block size
The PCTUSED Parameter
The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls below the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that Oracle DBA specify the following parameter in a CREATE TABLE statement:
PCTUSED 40
In this case, a data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached PCTFREE).
How PCTFREE and PCTUSED Work Together
PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. Figure 3-5 illustrates the interaction of these two parameters.
In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead and free space (PCTFREE). Updates to existing data can use any available space in the block. Therefore, updates can reduce the available space of a block to less than PCTFREE, the space reserved for updates but not accessible to inserts.
For each data and index segment, Oracle maintains one or more free lists--lists of data blocks that have been allocated for that segment's extents and have free space greater than PCTFREE. These blocks are available for inserts. When Oracle DBA issue an INSERT statement, Oracle checks a free list of the table for the first available data block and uses it if possible. If the free space in that block is not large enough to accommodate the INSERT statement, and the block is at least PCTUSED, Oracle takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.
After Oracle DBA issue a DELETE or UPDATE statement, Oracle processes the statement and checks to see if the space being used in the block is now less than PCTUSED. If it is, the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction. When the transaction commits, free space in the block becomes available for other transactions.
|
