| Oracle® Database Concepts 11g Release 1 (11.1) Part Number B28318-01 |
|
|
View PDF |
| Oracle® Database Concepts 11g Release 1 (11.1) Part Number B28318-01 |
|
|
View PDF |
This chapter describes the nature of and relationships among the logical storage structures in the Oracle database server.
This chapter contains the following topics:
Oracle Database allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments. Figure 2-1 shows the relationships among these data structures.
Figure 2-1 The Relationships Among Segments, Extents, and Data Blocks
At the finest level of granularity, Oracle Database 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 greater than 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 Database allocates space for segments in units of one extent. When the existing extents of a segment are full, Oracle Database 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 you can allocate additional extents, the blocks themselves are allocated separately. If you 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.
Note:
Oracle recommends that you manage free space automatically. See "Free Space Management".Oracle Database manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data 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 Database requests data in multiples of Oracle Database data blocks, not operating system blocks.
The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify 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 Database data blocks are the smallest units of storage that Oracle Database can use or allocate.
This section includes the following topics:
See Also:
Your Oracle Database operating system-specific documentation for more information about data block sizes
The Oracle Database data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 2-2 illustrates the format of a data block.
This section discusses the following components of the data block:
The header contains general block information, such as the block address and the type of segment (for example, data or index).
This portion of the data block contains information about the table having rows in this block.
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).
After 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 Database reuses this space only when new rows are inserted in the block.
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.
This portion of the data block contains table or index data. Rows can span blocks.
See Also:
"Row Chaining and Migrating"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).
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 can be managed automatically or manually.
Free space can be managed automatically inside database segments. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. Automatic segment-space management offers the following benefits:
Ease of use
Better space utilization, especially for the objects with highly varying row sizes
Better run-time adjustment to variations in concurrent access
Better multi-instance behavior in terms of performance/space utilization
You specify automatic segment-space management when you create a locally managed tablespace. The specification then applies to all segments subsequently created in this tablespace.
See Also:
Oracle Database Administrator's GuideThis section includes the following topics:
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, then 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 run by another user), then 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 Database 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 Database 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.
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 Database 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 Database migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle Database 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 Database must scan more than one data block to retrieve the information for the row.
See Also:
"Row Format and Size" for more information on the format of a row and a row piece
"Rowids of Row Pieces" for more information on rowids
"Physical Rowids" for information about rowids
Oracle Database Performance Tuning Guide for information about reducing chained and migrated rows and improving I/O performance
For manually managed tablespaces, two space management parameters, PCTFREE and PCTUSED, enable you to control the use of free space for inserts and updates to the rows in all the data blocks of a particular segment. Specify these parameters when you create or alter a table or cluster (which has its own data segment). You can also specify the storage parameter PCTFREE when creating or altering an index (which has its own index segment).
This section includes the following topics:
Note:
This discussion does not apply to LOB datatypes (
BLOB, CLOB, NCLOB, and BFILE). They do not use the PCTFREE storage parameter or free lists.
See "Overview of LOB Datatypes" for information.
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 you 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 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. Figure 2-3 illustrates PCTFREE.
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 Database considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle Database uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you 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). Figure 2-4 illustrates this.
PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. Figure 2-5 illustrates the interaction of these two parameters.
Figure 2-5 Maintaining the Free Space of Data Blocks with PCTFREE and PCTUSED
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 Database 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 you issue an INSERT statement, Oracle Database 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, then Oracle Database 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 you issue a DELETE or UPDATE statement, Oracle Database processes the statement and checks to see if the space being used in the block is now less than PCTUSED. If it is, then 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.
An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle Database allocates a new extent for the segment.
This section includes the following topics:
When you create a table, Oracle Database allocates to the table's data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle Database data blocks that correspond to the initial extent are reserved for that table's rows.
If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle Database automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.
For maintenance purposes, the header block of each segment contains a directory of the extents in that segment.
Note:
This chapter applies to serial operations, in which one server process parses and runs a SQL statement. Extents are allocated somewhat differently in parallel SQL statements, which entail multiple server processes.Storage parameters expressed in terms of extents define every segment. Storage parameters apply to all types of segments. They control how Oracle Database allocates free database space for a given segment. For example, you can determine how much space is initially reserved for a table's data segment or you can limit the number of extents the table can allocate by specifying the storage parameters of a table in the STORAGE clause of the CREATE TABLE statement. If you do not specify a table's storage parameters, then it uses the default storage parameters of the tablespace.
You can have dictionary managed tablespaces, which rely on data dictionary tables to track space utilization, or locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. Because of the better performance and easier manageability of locally managed tablespaces, the default for non-SYSTEM permanent tablespaces is locally managed whenever the type of extent management is not explicitly specified.
A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.
For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Ensure that each extent contains at least five database blocks, given the database block size. Temporary tablespaces that manage their extents locally can only use this type of allocation.
For system-managed extents, Oracle Database determines the optimal size of additional extents, with a minimum extent size of 64 KB. If the tablespaces are created with 'segment space management auto', and if the database block size is 16K or higher, then Oracle Database manages segment size by creating extents with a minimum size of 1M. This is the default for permanent tablespaces.
The storage parameters INITIAL, NEXT, PCTINCREASE, and MINEXTENTS cannot be specified at the tablespace level for locally managed tablespaces. They can, however, be specified at the segment level. In this case, INITIAL, NEXT, PCTINCREASE, and MINEXTENTS are used together to compute the initial size of the segment. After the segment size is computed, internal algorithms determine the size of each extent.
Oracle Database uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed.
With locally managed tablespaces, Oracle Database looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle Database looks in another datafile.
Note:
Oracle strongly recommends that you use locally managed tablespaces.Oracle Database provides a Segment Advisor that helps you determine whether an object has space available for reclamation based on the level of space fragmentation within the object.
See Also:
Oracle Database Administrator's Guide for guidelines on reclaiming segment space
Oracle Database SQL Language Reference for SQL syntax and semantics
In general, the extents of a segment do not return to the tablespace until you drop the schema object whose data is stored in the segment (using a DROP TABLE or DROP CLUSTER statement). Exceptions to this include the following:
The owner of a table or cluster, or a user with the DELETE ANY privilege, can truncate the table or cluster with a TRUNCATE...DROP STORAGE statement.
A database administrator (DBA) can deallocate unused extents using the following SQL syntax:
ALTER TABLE table_name DEALLOCATE UNUSED;
Periodically, Oracle Database deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.
When extents are freed, Oracle Database modifies the bitmap in the datafile (for locally managed tablespaces) or updates the data dictionary (for dictionary managed tablespaces) to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.
This section includes the following topics:
As long as a nonclustered table exists or until you truncate the table, any data block allocated to its data segment remains allocated for the table. Oracle Database inserts new rows into a block if there is enough room. Even if you delete all rows of a table, Oracle Database does not reclaim the data blocks for use by other objects in the tablespace.
After you drop a nonclustered table, this space can be reclaimed when other extents require free space. Oracle Database reclaims all the extents of the table's data and index segments for the tablespaces that they were in and makes the extents available for other schema objects in the same tablespace.
In dictionary managed tablespaces, when a segment requires an extent larger than the available extents, Oracle Database identifies and combines contiguous reclaimed extents to form a larger one. This is called coalescing extents. Coalescing extents is not necessary in locally managed tablespaces, because all contiguous free space is available for allocation to a new extent regardless of whether it was reclaimed from one or more extents.
Clustered tables store information in the data segment created for the cluster. Therefore, if you drop one table in a cluster, the data segment remains for the other tables in the cluster, and no extents are deallocated. You can also truncate clusters (except for hash clusters) to free extents.
Oracle Database deallocates the extents of materialized views and materialized view logs in the same manner as for tables and clusters.
See Also:
"Overview of Materialized Views"All extents allocated to an index segment remain allocated as long as the index exists. When you drop the index or associated table or cluster, Oracle Database reclaims the extents for other uses within the tablespace.
When Oracle Database completes the execution of a statement requiring a temporary segment, Oracle Database automatically drops the temporary segment and returns the extents allocated for that segment to the associated tablespace. A single sort allocates its own temporary segment in a temporary tablespace of the user issuing the statement and then returns the extents to the tablespaces.
Multiple sorts, however, can use sort segments in temporary tablespaces designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort, but remain available for other multiple sorts.
A temporary segment in a temporary table contains data for multiple statements of a single transaction or session. Oracle Database drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tablespace.
Oracle Database periodically checks the rollback segments of the database to see if they have grown larger than their optimal size. If a rollback segment is larger than is optimal (that is, it has too many extents), then Oracle Database automatically deallocates one or more extents from the rollback segment.
A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle Database allocates one or more extents to form that table's data segment, and for each index, Oracle Database allocates one or more extents to form its index segment.
This section contains the following topics:
A single data segment in an Oracle Database database holds all of the data for one of the following:
Oracle Database creates this data segment when you create the table or cluster with the CREATE statement.
The storage parameters for a table or cluster determine how its data segment's extents are allocated. You can set these storage parameters directly with the appropriate CREATE or ALTER statement. These storage parameters affect the efficiency of data retrieval and storage for the data segment associated with the object.
Note:
Oracle Database creates segments for materialized views and materialized view logs in the same manner as for tables and clusters.See Also:
Oracle Database Advanced Replication for information on materialized views and materialized view logs
Oracle Database SQL Language Reference for syntax
Every nonpartitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.
Oracle Database creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.) Setting the storage parameters directly affects the efficiency of data retrieval and storage.
When processing queries, Oracle Database often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle Database automatically allocates this disk space called a temporary segment. Typically, Oracle Database requires a temporary segment as a database area for sorting. Oracle Database does not create a segment if the sorting operation can be done in memory or if Oracle Database finds some other way to perform the operation using indexes.
This section includes the following topics:
The following statements sometimes require the use of a temporary segment:
CREATE INDEX
SELECT ... ORDER BY
SELECT DISTINCT ...
SELECT ... GROUP BY
SELECT . . . UNION
SELECT ... INTERSECT
SELECT ... MINUS
Some unindexed joins and correlated subqueries can require use of a temporary segment. For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle Database can require as many as two temporary segments.
Oracle Database can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session.
See Also:
"Temporary Tables"Oracle Database allocates temporary segments differently for queries and temporary tables.
This section includes the following topics:
Oracle Database allocates temporary segments as needed during a user session in one of the temporary tablespaces of the user issuing the statement. Specify these tablespaces with a CREATE USER or an ALTER USER statement using the TEMPORARY TABLESPACE clause.
Note:
You cannot assign a permanent tablespace as a user's temporary tablespace.If no temporary tablespace is defined for the user, then the default temporary tablespace is the SYSTEM tablespace. The default storage characteristics of the containing tablespace determine those of the extents of the temporary segment. Oracle Database drops temporary segments when the statement completes.
Because allocation and deallocation of temporary segments occur frequently, create at least one special tablespace for temporary segments. By doing so, you can distribute I/O across disk devices, and you can avoid fragmentation of the SYSTEM and other tablespaces that otherwise hold temporary segments.
Note:
When the
SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.Entries for changes to temporary segments used for sort operations are not stored in the redo log, except for space management operations on the temporary segment.
See Also:
Chapter 20, "Database Security" for more information about assigning a user's temporary segment tablespace
Oracle Database allocates segments for a temporary table when the first INSERT into that table is issued. (This can be an internal insert operation issued by CREATE TABLE AS SELECT.) The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.
Segments for a temporary table are allocated in a temporary tablespace of the user who created the temporary table.
Oracle Database drops segments for a transaction-specific temporary table at the end of the transaction and drops segments for a session-specific temporary table at the end of the session. If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table.
See Also:
"Temporary Tables"Oracle Database maintains information to reverse changes made to the database. This information consists of records of the actions of transactions, collectively known as undo. Undo is stored in undo segments in an undo tablespace. Oracle Database uses undo information to do the following:
Rollback an active transaction
Recover a terminated transaction
Provide read consistency
Recovery from logical corruptions
When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it. See "How Oracle Database Manages Data Concurrency and Consistency" for more information on read consistency.
Oracle Database provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. In this management mode, for all current sessions, the server automatically manages undo segments and space in the undo tablespace.
Automatic undo management eliminates the complexities of managing rollback segment space. In addition, the system automatically tunes itself to provide the best possible retention of undo information to satisfy long-running queries that may require this undo information. Automatic undo management is the default for new installations of Oracle Database. The installation process automatically creates an undo tablespace.
Oracle Database contains an Undo Advisor that provides advice on and helps automate the establishment of your undo environment.
This section includes the following topics:
See Also:
Oracle Database 2 Day DBA for information on the Undo Advisor and on how to use advisors and see Oracle Database Administrator's Guide for more information on using automatic undo managementA database system can also run in manual undo management mode. In manual undo management mode, undo space is managed through rollback segments, and no undo tablespace is used.
Earlier releases of Oracle Database defaulted to manual undo management mode. To change to automatic undo management, it was necessary to first create an undo tablespace and then change an initialization parameter. If your Oracle Database is release 9i or later and you want to change to automatic undo management, see Oracle Database Upgrade Guide for instructions.
Note:
Space management for rollback segments is complex. Oracle strongly recommends using automatic undo management.In automatic undo management mode, the system controls exclusively the assignment of transactions to undo segments, and controls space allocation for undo segments. An ill-behaved transaction can potentially consume much of the undo space, thus paralyzing the entire system. The Resource Manager directive UNDO_POOL is a more explicit way to control large transactions. This lets database administrators group users into consumer groups, with each group assigned a maximum undo space limit. When the total undo space consumed by a group exceeds the limit, its users cannot make further updates until undo space is freed up by other member transactions ending.
The default value of UNDO_POOL is UNLIMITED, where users are allowed to consume as much undo space as the undo tablespace has. Database administrators can limit a particular user by using the UNDO_POOL directive.
After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible. Old undo information can be retained provided that the undo tablespace has space available for new transactions. When available space in the tablespace becomes short, the database begins to overwrite old undo information for transactions that have been committed.
Oracle Database automatically tunes the system to provide the best possible undo retention for the current undo tablespace. The database collects usage statistics and tunes the undo retention period based on these statistics and the undo tablespace size. If the undo tablespace is configured with the AUTOEXTEND option, with maximum size not specified, undo retention tuning is slightly different. In this case, the database tunes the undo retention period to be slightly longer than the longest-running query, if space allows.
See Also:
Oracle Database Administrator's Guide for more details on automatic tuning of undo retention