| Oracle®
Database Concepts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
| Oracle®
Database Concepts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
< a id="sthref270" name="sthref270">This chapter describes the nature of and relationships a mong the logical storage structures in the Oracle database server.
This chapter contains the following topics:
Oracle allocates logical database space for all data in a database. The units of database space a llocation are data blocks, extents, and segments. Figure 2-1 shows the relationships among these data structure s:
Figure 2-1 The Relationships Among Segments, Extents, and Data Blocks
At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle block s, or pages). One data block corresponds to a specific number of bytes of physical database space on disk.< /p>
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 ext ents, 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 seg ment. If the table or index is partitioned, each partition is stored in its own segment.
Oracle allocates space for s egments in units of one extent. When the existing extents of a segment are full, Oracle allocates another extent for that segment. Be cause extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.
A segment and all its ex tents 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 alloca ted to the free list. However, if the extent is not allocated to a specific instance, then the blocks themselves are allocated only w hen the high water mark moves. The high water mark is the boundary between us ed and unused space in a segment.
Oracle 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 requests data in multiples of Oracle data blocks, not operating system blocks.
The s
tandard block size is specified by the DB_BLOCK_SIZE initialization parameter . In addition, you can specify of up to fi
ve nonstandard block sizes. The data block sizes should be a multiple of the operating system's block size within the maximum limit t
o avoid unnecessary I/O. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.
|
See Also:
|
The Oracle data block format is similar regardless of whether the data bl ock contains table, index, or clustered data. Figure 2-2 illustrates the format of a data block.
The header contains general bl ock 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 ro ws 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 d ata block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 5 0 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.
The data block header, table directory, and row directo ry 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.
Free space is allocated for insertion of new rows and for upd ates 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, fre
e 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 m
ore 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 segment s. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. Automatic segment-space management offers the f ollowing 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 autom atic segment-space management when you create a locally managed tablespace. The specification then applies to all segments subsequent ly created in this tablespace.
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 value
s. The released space from these types of statements is available for subsequent INSERT statements under the following c
onditions:
If the INSERT statement is in the same transaction and subsequent to the stateme
nt that frees space, then the INSERT statement can use the space made available.
If 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 r
ow 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 comp
ression of the free space in data blocks.
In two c
ircumstances, 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 o
f data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a co
lumn 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 over all 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 migra ted 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.
|
See Also:
|
<
a id="sthref338" name="sthref338">For manually managed tablespaces, two space management parameters, PCTFREE and PCTFREE when creating or altering an index (which has its own index segment).
|
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.
|
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 t
he 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 existi
ng rows already within each block. New rows can be added to the row data area, and corresponding information can be added to the vari
able 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 l
imit determined by PCTFREE, Oracle 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 uses the free space of the data bl
ock only for updates to rows already contained in the data block. For example, assume that you specify the following parameter in a <
code>CREATE TABLE statement:
PCTUSED 40
In this case, a data block use
d for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the blo
ck 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 spac
e 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<
/p>
Description of the illustration cncpt031.gif
In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead a
nd free space (PCTFREE). Updates to existing data can use any available space in the block. Therefore, updates can reduc
e 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 you issue an INSERT statement, Oracle checks a free list of the table for the fi
rst 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 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 processes the statement and checks to see if the space being used in the block is now les
s 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 tr
ansactions.
Oracle uses different algorithms to allocate extents, depending on whether they are locally manage d or dictionary managed.
With locally managed tablespaces, Oracle looks for free space to allocate to a new extent by first de termining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free bl ocks. If that datafile does not have enough adjacent free space, then Oracle looks in another datafile.
|
Note: Oracle strongly recommends that you use locally managed tablespaces. |
The 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 SQ L Reference for SQL syntax and semantics |
In ge
neral, 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 fo
llowing:
The owner of a table or cluster, or a user with the DELETE ANY privil
ege, 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;
Periodicall
y, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.
W hen extents are freed, Oracle modifies the bitmap in the datafile (for locally managed tablespaces) or updates the data dictionary (f or dictionary managed tablespaces) to reflect the regained extents as available space. Any data in the blocks of freed extents become s inaccessible.
As long as a nonclustered table exists or until you truncate the table, any data block allocated to its data segment rema ins allocated for the table. Oracle inserts new rows into a block if there is enough room. Even if you delete all rows of a table, Or acle does not reclaim the data blocks for use by other objects in the tablespace.
After you drop a nonclustered table, this sp ace can be reclaimed when other extents require free space. Oracle reclaims all the extents of the table's data and index segments fo r 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 ex tent larger than the available extents, Oracle identifies and combines contiguous reclaimed extents to form a larger one. This is cal led coalescing extents. Coalescing extents is not necessary in locally managed tablespaces, because all contiguous f ree 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. Th erefore, if you drop one table in a cluster, the data segment remains for the other tables in the cluster, and no extents are dealloc ated. You can also truncate clusters (except for hash clusters) to free extents.
Oracle deallocates the ex tents of materialized views and materialized view logs in the same manner as for tables and clusters.
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 reclaims the extents for other uses within the tablespace.
When Oracle completes the execution of a statement requiring a temporary segment, Or acle automatically drops the temporary segment and returns the extents allocated for that segment to the associated tablespace. A sin gle 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 oth er multiple sorts.
A temporary segment in a temporary table contains data for multiple statements of a single transaction or s ession. Oracle drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tablespace.
Oracle periodically chec ks the rollback segments of the database to see if they have grown larger than their optimal size. If a rollback segment is larger th an is optimal (that is, it has too many extents), then Oracle 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 allocates one or more extents to form that table's data segment, and for each index, Oracle allocates one or more extents to form its index segment.
This section contains the following topics:
A single data segment in an Oracle database ho lds all of the data for one of the following:
A table that is not partitioned or clustered
A cluster of t ables
Oracle creates this data segment
when you create the table or cluster with the CREATE statement.
The storage parameters for a table or cluster de
termine 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 se
gment associated with the object.
|
Note: Oracle creates segments for materialized views and materialized view logs in the same manner as for tables and cluster s. |
See Also:
Oracle Database Advanced Replication for information on materialized views and materialized view log s
Oracle Database SQL Reference for syntax
Every nonpartitioned index in an Oracle database has a single index segment t o hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.
Oracle creates the index segment for an index or an index partition when you issue the CREATE
Oracle can also allocate temporary segments for temporary tables and indexes created on temporary tables. Tempora ry tables hold data that exists only for the duration of a transaction or session.
Oracle allocates temporary segments differently for queries and temporary tables.
Oracle 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 TAB
LESPACE clause.
|
Note:< /p> 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 se
gment. Oracle 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 dev
ices, and you can avoid fragmentation of the SYSTEM and other tablespaces that otherwise hold temporary segments.
|
Note: When theSYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed |
Oracle 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 drops segments for a transaction-specific temporary table at the end of th e 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.
Oracle maintains information to nullify changes made to the database. Such information consists of records of the actions of transactions, collectively known as 'undo.' Oracle uses the undo to do the following:
Rollback an active transaction
Recover a terminated transaction
Provide read consistency
Recovery fr om logical corruptions
Automatic undo management is undo-tablespace based. You allocate space in the form of an undo tablespace, instead of allocating many rollback segments in different sizes.
|
See Also: Oracle Database Administrator's Guide |
A
utomatic undo management eliminates the complexities of managing rollback segment space and lets you exert control over how long undo
is retained before being overwritten. Oracle strongly recommends that you use undo tablespaces to manage undo rather than rollback s
egments. The system automatically tunes the period for which undo is retained in the undo tablespace to satisfy queries that require
undo information. You can set the UNDO_RETENTION parameter to a low threshold value so that the system retains the undo
for at least the time specified in the parameter, provided that the current undo tablespace has enough space.
Use the V$UNDOSTAT view to monitor and configure your d
atabase system to achieve efficient use of undo space. V$UNDOSTAT shows various undo and transaction statistics, such as
the amount of undo space consumed in the instance.
|
< strong>Note: Earlier releases of Oracle used rollback segments to store undo, also known as manual undo managemen t mode. Space management for these rollback segments was complex, and Oracle has now deprecated that method of storing undo. |
The Oracle Database contains an Undo Advisor that provides advice on and helps automate the establishment of your undo environment.
Undo mode provides a more flexible way to migrate from manual undo management to automatic undo
management. A database system can run in either manual undo management mode or automatic undo management mode. In manual undo manage
ment mode, undo space is managed through rollback segments. In automatic undo management mode, undo space is managed in undo tablespa
ces. To use automatic undo management mode, the database administrator needs only to create an undo tablespace for each instance and
set the UNDO_MANAGEMENT initialization parameter to AUTO. You are strongly encouraged to run in automatic u
ndo management mode.
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 Reso
urce 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.
Th
e default value of UNDO_POOL is UNLIMITED, where users are allowed to consume as much undo space as the und
o tablespace has. Database administrators can limit a particular user by using the UNDO_POOL directive.
Long-running queries sometimes fail because undo information required for consistent re ad operations is no longer available. This happens when committed undo blocks are overwritten by active transactions. The success of several flashback features can also depend upon older undo information.
Oracle automatically tunes undo retention by collectin
g database usage statistics and estimating undo capacity needs for the successful completion of the queries. You can set a low thresh
old value for the UNDO_RETENTION parameter so that the system retains the undo for at least the time specified in the pa
rameter, provided that the current undo tablespace has enough space. The setting of the UNDO_RETENTION parameter should
take into account any flashback requirements of the system.
Monitor transaction a
nd undo information with V$TRANSACTION and V$ROLLSTAT. For automatic undo management, the information in
The V$UNDOSTAT view
displays a histogram of statistical data to show how well the system is working. You can see statistics such as undo consumption rate
, transaction concurrency, and lengths of queries run in the instance. Using this view, you can better estimate the amount of undo sp
ace required for the current workload.
|
 Copyright © 1993, 2003 Oracle Corporation All Rights Reserved. |
|
