2 Data Blocks, Extents, and Segments
001 |
This chapter describes the nature of and relationships among the logical
storage structures in the Oracle database server. |
本章讲述Oracle数据库中各种逻辑存储结构(logical storage structure)的特性和相互关系。 |
002 | This chapter contains the following topics: | 本章包含以下主题: |
003 |
Introduction to Data Blocks, Extents, and Segments |
2.1 数据块,数据扩展,段简介 |
004 |
Oracle 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: |
Oracle数据库负责为所有数据分配逻辑存储空间。数据库空间的分配单位有数据块(Data Block),数据扩展(Extent),和段(Segment)。图2-1 说明了这些数据结构之间的关系。 |
005 |
Figure 2-1 The Relationships
Among Segments, Extents, and Data Blocks |
图2-1 段,数据扩展,数据块之间的关系 |
006 |
|
|
007 |
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. |
Oracle存储数据的最小粒度(finest level of granularity)被称为数据块(data block)(也叫做逻辑块(logical block),Oracle块(Oracle block)或页(page))。一个数据块对应于磁盘上数个字节(byte)的物理数据库空间。 |
008 |
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. |
处于数据块之上的逻辑数据库空间是数据扩展(extent)。数据扩展是为存储数据而分配的一组连续的数据块。 |
009 |
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. |
位于数据扩展之上的逻辑数据库存储结构是段(segment)。段由一组数据扩展 (extent)构成,这些数据扩展位于同一表空间(tablespace)中,用于存储各种逻辑数据结构。例如每个表(table)的数据都存储在其自身的数据段(data segment)中,每个索引(index)的数据都存储在其自身的索引段(index segment)中。如果表或索引是分区存储(partitioned)的,则每个分区拥有自己的段。 |
010 |
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. |
Oracle为段(segment)分配空间时以数据扩展(extent)为单位。当段内已有的数据扩展没有可用空间时,Oracle为此段分配一个新的数据扩展。因为数据扩展是随需分配的,所以一个段内的数据扩展在磁盘上未必是连续的。 |
011 |
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. |
一个段(segment)以及属于她的所有数据扩展(extent)必须包含在同一表空间(tablespace)中。但在一个表空间内,属于同一个段的数据扩展可以分布 在多个数据文件(datafile)上,即段可以跨文件存储。但是每个数据扩展只能包含于同一个数据文件中。 |
012 |
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. |
在用户分配一个新的数据扩展(extent)时,其中的数据块(data block)未必被同时分配。如果用户是为某个数据库对象分配数据扩展 ,那么数据块也同时被立即分配并加入可用块列表(free list)中。如果数据扩展并非专为某数据库对象分配,那么数据块只在高水位线(high water mark)移动时才被分配。高水位线是段(segment)中已用和未用空间的边界。 |
013 |
|
|
014 |
Overview of Data Blocks |
2.2 数据块概述 |
015 |
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. |
Oracle对数据库数据文件(datafile)中的存储空间进行管理的单位是数据块(data block)。数据块是数据库中最小的(逻辑)数据单位。与数据块对应的,所有数据在操作系统级的最小物理存储单位是字节(byte)。每种操作系统都有一个被称为块容量(block size)的参数。Oracle每次获取数据时,总是访问整数个(Oracle)数据块,而不是按照操作系统块的容量访问数据。 |
016 |
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 data blocks are the smallest
units of storage that Oracle can use or allocate. |
数据库中标准的数据块(data block)容量是由初始化参数 DB_BLOCK_SIZE 指定的。除此之外,用户还可以指定五个非标准的数据块容量(nonstandard block size)。数据块容量应该设为操作系统块容量的整数倍(同时小于数据块容量的最大限制),以便减少不必要的I/O操作。Oracle数据块是Oracle可以使用和分配的最小存储单位。 |
017 |
|
|
018 |
Data Block Format |
2.2.1 数据块结构 |
019 |
The Oracle 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. |
在Oracle中,不论数据块中存储的是表(table)、索引(index)或簇表(clustered data),其内部结构都是类似的。图2-2 说明了数据块的结构。 |
020 |
Figure 2-2 Data Block Format |
图2-2 数据块结构 |
021 |
|
|
022 |
|
|
023 |
Header (Common and Variable) |
2.2.1.1 数据块头(包括标准内容和可变内容) |
024 |
The header contains general
block information, such as the block address and the type of segment
(for example, data or index). |
数据块头(header)中包含了此数据块的概要信息,例如块地址(block address)及此数据块所属的段(segment)的类型(例如,表或索引)。 |
025 |
Table Directory |
2.2.1.2 表目录区 |
026 |
This portion of the data block contains information about the table
having rows in this block. |
如果一个数据表在此数据块中储存了数据行,那么数据表的信息将被记录在数据块的表目录区(table directory)中。 |
027 |
Row Directory |
2.2.1.3 行目录区 |
028 |
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). |
此区域包含数据块中存储的数据行的信息(每个数据行片断(row piece)
在行数据区(row data area)中的地址)。[一个数据块中可能保存一个完整的数据行,也可能只保存数据行的一部分
,所以文中使用row piece] |
029 |
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 reuses this space only when new rows are inserted in
the block. |
当一个数据块(data block)的行目录区(row directory)空间被使用后,即使数据行被删除(delete),行目录区空间也不会被回收。举例来说,当一个曾经包含50条记录的数据块被清空后,其块头(header)的行目录区仍然占用100字节(byte)的空间。只有在数据块中插入(insert)新数据时,行目录区空间才会被 重新利用。 |
030 |
Overhead |
2.2.1.4 管理开销 |
031 |
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. |
数据块头(data block header),表目录区(table directory),行目录区(row directory)被统称为管理开销(overhead)。其中 有些开销的容量是固定的;而有些开销的总容量是可变的。数据块中固定及可变管理开销的容量平均在84到107字节(byte)之间。 |
032 |
Row Data |
2.2.1.5 行数据 |
033 |
This portion of the data block contains table or index data. Rows can
span blocks. |
数据块(data block)中行数据区(row
data)包含了表或索引的实际数据。一个数据行可以跨多个数据块。 |
034 |
See Also: |
另见: |
035 |
Free Space |
2.2.1.6 可用空间区 |
036 |
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). |
在插入新数据行,或在更新数据行需要更多空间时(例如,原来某行最后一个字段为空(trailing null),现在要更新为非空值),将 使用可用空间区(free space)中的空间。 |
037 |
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. |
如果一个数据块(data block)属于表或簇表的数据段(data segment),或属于索引的索引段(index segment),那么在其可用空间区中还可能会存储事务条目(transaction entry)。如果一个数据块中的数据行(row)正在由 INSERT,UPDATE,DELETE,及 SELECT...FOR UPDATE 语句访问,此数据块中就需要保存事务条目。事务条目所需的存储空间依据操作系统而定。在常见的操作系统中事务条目大约需要占用23字节(byte)。 |
038 |
Free Space Management |
2.2.2 可用空间管理 |
039 |
Free space can be managed automatically or manually. |
可用空间可以被手动或自动管理。[注意本节中的“可用空间”与“可用空间区”的区别] |
040 |
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:
|
数据库中,每个段(segment)的可用空间可以被自动管理。段内的可用/已用空间以位图(bitmap)形式记录,这与可用块以列表方式的管理不同。段空间自动管理(Automatic
segment-space management)具备以下优势:
|
041 |
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. |
用户可以在创建一个本地管理的表空间(locally managed tablespace)时选择自动段空间管理(automatic segment-space management)功能。这样在此表空间内创建的段都将默认地设置为自动段空间管理。 |
042 |
|
|
043 |
Availability and Optimization of Free Space in a Data Block |
2.2.2.1 数据块可用空间的有效性及优化 |
044 |
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:
|
有两种SQL语句可以增加数据块中的可用空间:分别是
DELETE 语句,和将现有数据值更新为占用容量更小值的
UPDATE 语句。在以下两种条件下,上述两中操作释放的空间可以被后续的 INSERT
语句使用:
|
045 |
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. |
数据块(data block)中被释放出的空间未必与可用空间区(free space)相连续。Oracle在满足以下条件时才会将释放的空间合并到可用空间区:(1)INSERT 或 UPDATE 语句选中了一个有足够可用空间容纳新数据的数据块,(2)但是此块中的可用空间不连续,数据无法被写入到数据块中连续的空间里。Oracle只在 满足上述条件时才对数据块中的可用空间进行合并,这样做是为了避免过于频繁的空间合并工作影响数据库性能。 |
046 |
Row Chaining and Migrating |
2.2.2.2 行链接(Row Chaining)及行迁移(Row Migrating) |
047 |
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. |
有两种情况会导致表中某行数据过大,一个数据块(data block)无法容纳。第一种情况,当一行数据被插入时一个数据块就无法容纳。在这种情况下Oracle将这行数据存储在段内的一个数据块链(chain)中。在插入数据量大的行时常会发生行链接(row chaining),例如一个包含数据类型为 LONG 或 LONG RAW 列的数据行。此时行链接不可避免。 |
048 |
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. |
第二种情况,原本存储在一个数据块(data block)内的数据行,因为更新操作导致长度增长,而所在数据块的可用空间也不能容纳增长后的数据行。在这种情况下,Oracle将此行数据迁移(migrate)到新的数据块中。Oracle在被迁移数据行原来所在位置保存一个指向新数据块的指针。被迁移数据行的 rowid 保持不变。 |
049 |
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. |
当数据行发生链接(chain)或迁移(migrate)时,对其访问将会造成 I/O 性能降低,因为Oracle为获取这些数据行的数据时,必须访问更多的数据块(data block)。 |
050 |
|
|
051 |
PCTFREE, PCTUSED, and Row Chaining |
2.2.3 PCTFREE,PCTUSED,及行链接(Row Chaining) |
052 |
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). |
在手动管理的表空间(manually managed tablespaces)中,用户可以使用 PCTFREE 和 PCTUSED 这两个存储管理参数来控制对某段(segment)进行插入和更新操作时,如何利用属于此段的数据块(data block)中的可用空间。用户也可以在创建或修改索引时为其设定 PCTFREE 参数(索引存储在索引段(index segment)中)。 |
053 |
|
提示: |
054 |
The PCTFREE Parameter |
2.2.3.1 PCTFREE 参数 |
055 |
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 参数用来设置一个数据块(data block)中至少需要保留(reserve)多少可用空间(百分比值),为数据块中已有数据更新时可能发生的数据量增长做准备。例如,当用户用 CREATE TABLE 语句创建表时指定了以下参数: |
056 |
|
PCTFREE 20 |
057 |
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. |
这个参数设定了此表对应的数据段(data segment)中的每个数据块(data block)至少保留20%的可用空间,以备块中已有数据更新时使用。只要数据块中行数据区与数据块头的容量之和不超过数据块总容量的80%,用户就可以向其中插入新数据,数据行被放入行数据区(row data area),相关信息被写入数据块头(overhead area)。图 2-3 说明了 PCTFREE 的作用。 |
058 |
Figure 2-3 PCTFREE |
图 2-3 PCTFREE |
059 |
|
|
060 |
The PCTUSED Parameter |
2.2.3.2 PCTUSED 参数 |
061 |
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 beneath 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 you specify the following parameter in a CREATE TABLE statement: |
PCTUSED 参数用于决定一个数据块(data block)是否可被用于插入新数据,她的依据是数据区(row data)与数据块头(overhead)的容量之和占数据块全部容量的最大百分比。当一个数据块中的可用空间比例小于 PCTFREE 参数的规定时,Oracle就认为此数据块无法被用于插入新数据,直到数据块中的占用容量比例小于 PCTUSED 参数的限定。在占用容量比例大于 PCTUSED 参数的限定之前,Oracle只在更新数据块内已有数据时才会使用此数据块的可用空间。例如,当用户用 CREATE TABLE 语句创建表时指定了以下参数: |
062 |
|
PCTUSED 40 |
063 |
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. |
在例子中,当此表的某数据块占用容量比例高于40%时,Oracle不会将此数据块用于插入新数据行(假设此数据块的可用空间曾经低于 PCTFREE 的限定)。图 2-4 说明了 PCTUSED 的作用。 |
064 |
Figure 2-4 PCTUSED |
图 2-4 PCTUSED |
065 |
|
|
066 |
How PCTFREE and PCTUSED Work Together |
2.2.3.3 PCTFREE 和 PCTUSED 如何协同发挥作用 |
067 |
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. |
PCTFREE 和 PCTUSED 共同作用可以优化数据块(data block)的空间使用。图 2-5 说明了这两种参数的交互作用。 |
068 |
Figure 2-5 Maintaining the Free Space of Data
Blocks with PCTFREE and PCTUSED |
图 2-5 使用 PCTFREE 和 PCTUSED 参数管理数据块的可用空间 |
069 |
|
|
070 |
|
上图说明了 PCTFREE 和 PCTUSED 如何共同作用以管理数据块(data block)可用空间的使用。 |
071 |
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. |
在新分配的数据块中(data block),可用于插入(insert)数据的空间等于数据块总容量减去数据块头(block overhead)再减去预留可用空间(PCTFREE)。而更新(update)数据块内已有数据可使用数据块中的所有可用空间。因此,更新操作能够使数据块内的可用空间低于的 PCTFREE 限制,因为这些空间是专为更新操作而预留的。 |
072 |
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 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 takes the block off the free list. Multiple free lists for
each segment can reduce contention for free lists when concurrent
inserts take place. |
在每个数据段(data segment)与索引段(index segment)中,Oracle管理着一个或多个可用块列表(free list)--其中列出了所有属于此段的数据扩展(extent),且可用空间比例大于 PCTFREE 限定的数据块。这些块可以被插入(insert)操作使用。当用户提交了 INSERT 语句后,Oracle从可用块列表中选择第一个有效的数据块使用。如果此数据块的可用空间不够容纳 INSERT 语句提交的数据,且此块的占用容量已经超过PCTUSED 的限定,Oracle就将其从可用块列表中移出。一个段可以同时使用多个可用块列表,以减少对一个表进行并发插入(concurrent insert)时产生的竞争。 |
073 |
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
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. |
当用户提交了 DELETE 或 UPDATE 语句后,Oracle处理语句并检查相关数据块中的占用空间比例是否小于 PCTUSED 的规定。如果满足,那么这个数据块就被放入当前事务(transaction)正在使用的可用块列表(free list)的头部,如果当前事务还需要写入数据,此块将被首先使用。当事务提交后,此数据块中的可用空间还可被其他事务使用。 |
074 |
Overview of Extents |
2.3 数据扩展概述 |
075 |
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 allocates a new extent for the segment. |
数据扩展(extent)是由一组连续的数据块(data block)构成的数据库逻辑存储分配单位。而段(segment)则是由一个或多个数据扩展构成。当一个段中已有空间已经用完,Oracle为这个段分配新的数据扩展。 |
076 |
When Extents Are Allocated |
2.3.1 数据扩展何时被分配 |
077 |
When you create a table, Oracle 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 data blocks that correspond to the
initial extent are reserved for that table's rows. |
当用户创建数据表时,Oracle为此表的数据段(data segment)分配一个包含若干数据块(data block)的初始数据扩展(initial extent)。虽然此时数据表中还没有数据,但是在此初始数据扩展中的数据块已经为插入新数据做好了准备。 |
078 |
If the data blocks of a segment's initial extent become full and
more space is required to hold new data, Oracle 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. |
如果一个段(segment)的初始数据扩展(initial extent)中的数据块(data block)都已装满,且有新数据插入需要空间时,Oracle自动为这个段分配一个增量数据扩展(incremental extent)。增量数据扩展是一个段中继已有数据扩展之后分配的后续数据扩展,她的容量大于或等于之前的数据扩展。 |
079 |
For maintenance purposes, the header block of each segment contains a
directory of the extents in that segment. |
为了管理的需要,每个段(segment)的段头(header block)中包含一个记录此段所有数据扩展(extent)的目录。 |
080 |
Note: |
提示: |
081 |
Determine the Number and Size of Extents |
2.3.2 如何决定数据扩展分配时的数量与容量 |
082 |
Storage parameters expressed in terms of extents define every
segment. Storage parameters apply to all types of segments. They control
how Oracle 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. |
每个段(segment)的定义中都包含了数据扩展(extent)的存储参数(storage parameter)。存储参数适用于各种类型的段。这个参数控制着Oracle如何为段分配可用空间。例如,用户可以在 CREATE TABLE 语句中使用 STORAGE 子句设定存储参数,决定创建表时为其数据段(data segment)分配多少初始空间,或限定一个表最多可以包含多少数据扩展。如果用户没有为表设定存储参数,那么表在创建时使用所在表空间(tablespace)的默认存储参数。 |
083 |
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. |
用户既可以使用数据字典管理的表空间(dictionary managed tablespace)(依赖数据字典表监控空间的利用情况),也可以使用本地管理的表空间(locally managed tablespace)(使用位图(bitmap)来标记可用与已用空间)。由于本地管理的表空间性能较好且易于管理,当用户没有显式地设定数据扩展(extent)管理参数时,除了 SYSTEM 之外的所有永久表空间(permanent tablespace)默认使用本地管理方式。 |
084 |
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.
|
在一个本地管理的表空间中,其中所分配的数据扩展(extent)的容量既可以是用户设定的固定值,也可以是由系统自动决定的可变值。当用户创建表空间(tablespace)时可以使用
UNIFORM (用户指定)或 AUTOALLOCATE
(由系统管理)子句设定数据扩展的分配方式。
|
085 |
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. |
在本地管理的表空间(locally managed tablespace)中,INITIAL,NEXT,PCTINCREASE,和 MINEXTENTS 这四个存储参数可以作用于段(segment),但不能作用于表空间。INITIAL,NEXT,PCTINCREASE,和 MINEXTENTS 相结合可以用于计算段的初始容量。当段容量确定后,Oracle使用内部算法确定其中每个初始数据扩展(extent)的容量。 |
086 |
|
|
087 |
How Extents Are Allocated |
2.3.3 数据扩展如何被分配 |
088 |
Oracle uses different algorithms to allocate extents, depending on
whether they are locally managed or dictionary managed. |
Oracle依据表空间管理方式的不同(本地管理(locally managed)或数据字典管理(dictionary managed)),选择不同的算法分配数据扩展(extent)。 |
089 |
With locally managed tablespaces, Oracle 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 looks in another datafile. |
对于本地管理的表空间(locally managed tablespace),Oracle在为新的数据扩展(extent)寻找可用空间时,首先选择一个属于此表空间的数据文件(datafile),再搜索此数据文件的位图(bitmap)查找连续的数据块(free block)。如果此数据块中没有足够的连续可用空间,Oracle将查询其他数据文件。 |
090 |
|
提示: |
091 |
When Extents Are Deallocated |
2.3.4 数据扩展如何被回收 |
092 |
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. |
Oracle 数据库提供了 Segment Advisor 工具,她依据方案对象(schema object)存储空间中的碎片程度来判断此对象中是否包含可回收的空间。 |
093 |
|
|
094 |
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:
|
一般来说,在用户将一个段(segment)对应的方案对象(schema object)移除(使用 DROP TABLE 或
DROP CLUSTER
语句)之前,此段的数据扩展(extent)不会被回收到表空间(tablespace)中,但是以下情况例外:
|
095 |
When extents are freed, Oracle 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. |
当数据扩展(extent)被释放后,Oracle修改数据文件(datafile)中的位图(bitmap)(对于本地管理的表空间)或更新数据字典(对于数据字典管理的表空间),将回收的数据扩展视为可用空间。被释放的数据扩展中的数据无法继续访问。 |
096 |
|
|
097 |
Extents in Nonclustered Tables |
2.3.4.1 非簇表中的数据扩展 |
098 |
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 inserts new rows into a block if there is enough room.
Even if you delete all rows of a table, Oracle does not reclaim the data
blocks for use by other objects in the tablespace. |
只要用户没有移除(drop)或清空(truncate)非簇表(nonclustered table),那么对应数据段(data segment)内的任何数据块(data block)都不会被回收。Oracle会在插入数据时使用这些数据块(只要数据块有足够的空间)。即使用户删除(delete)了表的所有行,Oracle也不会回收此表的数据块供表空间内的其他方案对象(schema object)使用。 |
099 |
After you drop a nonclustered table, this space can be reclaimed when
other extents require free space. Oracle 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. |
当用户移除(drop)了一个非簇表(nonclustered table),Oracle将在需要空间时回收此表对应的数据扩展(extent)。Oracle将此表对应的数据段(data segment)、索引段(index segment)中包含的数据扩展(extent)全部收回,并供存储于此表空间(tablespace)的其他方案对象(chema object)使用。 |
100 |
In dictionary managed tablespaces, when a segment requires an extent
larger than the available extents, Oracle 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. |
对于数据字典管理的表空间(dictionary managed tablespace),当一个段(segment)需要的数据扩展(extent)比现有可用扩展的都大时,Oracle通过寻找、合并连续的被回收的数据扩展来形成更大的数据扩展。这个过程被称为数据扩展合并(coalescing)。本地管理的表空间(locally managed tablespace)没有必要进行数据扩展合并,因为所有连续空间都可以被用于分配新数据扩展,而无需关心所用空间是如何被回收的。 |
101 |
Extents in Clustered Tables |
2.3.4.2 簇表中的数据扩展 |
102 |
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. |
簇表(clustered table)的数据存储在为整个簇(cluster)分配的数据段(data segment)中。因此当用户移除(drop)簇中的一个表时,数据段还要为簇中的其他表保留,因此没有数据扩展(extent)被回收。用户可以通过清空(truncate)整个簇来回收属于簇的数据扩展(哈希簇(hash cluster)除外)。 |
103 |
Extents in Materialized Views and Their Logs |
2.3.4.3 物化视图及其日志的数据扩展 |
104 |
Oracle deallocates the extents of materialized views and materialized
view logs in the same manner as for tables and clusters. |
Oracle回收属于物化视图(materialized view)及其日志(materialized view log)的数据扩展的方式与表或簇相同。 |
105 |
|
另见: |
106 |
Extents in Indexes |
2.3.4.4 索引的数据扩展 |
107 |
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. |
只要索引存在,那么对应索引段(index segment)的所有数据扩展(extent)都不会被回收。当用户移除(drop)索引或相关的表、簇表时,Oracle才将索引使用的数据扩展回收。 |
108 |
Extents in Temporary Segments |
2.3.4.5 临时段(temporary segment)的数据扩展 |
109 |
When Oracle completes the execution of a statement requiring a temporary
segment, Oracle 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. |
当Oracle执行完一个 SQL 语句,且在执行过程中使用的临时段(temporary segment)时,Oracle自动地移除(drop)临时段,并将属于此临时段的数据扩展(extent)空间还给临时段所在的表空间(tablespace)。当用户执行单一排序(single sort)语句时,Oracle将在此用户的临时表空间(temporary tablespace)中创建为这个排序服务的临时段(temporary segment),并在执行后将此临时段使用的数据扩展回收。 |
110 |
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. |
但是对于多重排序(multiple sort),可能会使用临时表空间(temporary tablespace)中专为排序而创建的排序段(sort segment)。在每个实例中只会分配一个排序段,在排序操作使用后也不会被回收,而是继续供以后的多重排序使用。 |
111 |
A temporary segment in a temporary table contains data for multiple
statements of a single transaction or session. Oracle drops the
temporary segment at the end of the transaction or session, returning
the extents allocated for that segment to the associated tablespace. |
同一会话或同一事务的多个 SQL 语句,有可能共同使用一个临时表(temporary table)的临时段(temporary segment)存储临时数据。在这种情况下,Oracle在会话或事务结束后 才移除(drop)临时段,并为临时段所在表空间回收使用的数据扩展(extent)。 |
112 |
|
|
113 |
Extents in Rollback Segments |
2.3.4.6 回滚段(rollback segment)的数据扩展 |
114 |
Oracle 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 automatically deallocates one or more extents from the rollback
segment. |
Oracle周期性地检查数据库回滚段(rollback segment)的容量是否超过了最优值(optimal size)。如果回滚段超过了这个最优值(即回滚段中的数据扩展(extent)过多),Oracle将自动地从回滚段回收一些数据扩展。 |
115 |
Overview of Segments |
2.4 段概述 |
116 |
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. |
段(segment)由一组数据扩展(extent)构成,其中存储了表空间(tablespace)内各种逻辑存储结构的数据。例如,Oracle能为每个表的数据段(data segment)分配数据扩展,还能为每个索引的索引段(index segment)分配数据扩展。 |
117 | This section contains the following topics: | 本节包含以下主题: |
118 |
Introduction to Data Segments |
2.4.1 数据段简介 |
119 |
A single data segment in an Oracle database holds all of the data for
one of the following:
|
在Oracle数据库中,一个数据段(segment)可以供以下方案对象(或方案对象的一部分)容纳数据:
|
120 |
Oracle creates this data segment when you create the table or cluster
with the CREATE statement. |
当用户使用 CREATE 语句创建表或簇表时,Oracle创建相应的数据段(data segment)。 |
121 |
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. |
表或簇表的存储参数(storage parameter)用来决定对应数据段(data segment)的数据扩展(extent)如何被分配。用户可以使用 CREATE 或 ALTER 语句直接设定这些存储参数。这些参数将会影响与方案对象(object)相关的数据段的存储与访问效率。 |
122 |
Note: |
提示: |
123 |
|
|
124 |
Introduction to Index Segments |
2.4.2 索引段简介 |
125 |
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 数据库中每个非分区索引(nonpartitioned index)使用一个索引段(index segment)来容纳其数据。而对于分区索引(partitioned index),每个分区使用一个索引段来容纳其数据。 |
126 |
Oracle 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. |
用户可以使用 CREATE INDEX 语句为索引或索引的分区创建索引段(index segment)。在创建语句中,用户可以设定索引段(index segment)的数据扩展(extent)的存储参数(storage parameter)以及此索引段应存储在哪个表空间(tablespace)中。(表的数据段和与其相关的索引段不一定要存储在同一表空间中。)索引段的存储参数将会影响数据的存储与访问效率。 |
127 |
Introduction to Temporary Segments |
2.4.3 临时段简介 |
128 |
When processing queries, Oracle often requires temporary workspace for
intermediate stages of SQL statement parsing and execution. Oracle
automatically allocates this disk space called a temporary segment.
Typically, Oracle requires a temporary segment as a database area for
sorting. Oracle does not create a segment if the sorting operation can
be done in memory or if Oracle finds some other way to perform the
operation using indexes. |
当Oracle处理一个查询时,经常需要为SQL语句的解析与执行的中间结果(intermediate stage)准备临时空间。Oracle会自动地分配被称为临时段(temporary segment)的磁盘空间。例如,Oracle在进行排序操作时就需要使用临时段。当排序操作可以在内存中执行,或Oracle设法利用索引就执行时,就不必创建临时段。 |
129 |
Operations that Require Temporary Segments |
2.4.3.1 需要使用临时段的操作 |
130 |
The following statements sometimes require the use of a temporary
segment:
|
以下语句的执行过程中可能会使用临时段(temporary
segment):
|
131 |
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
can require as many as two temporary segments. |
有些不能使用索引的关联操作(unindexed join),或者需要在子查询间建立相互关系(correlated subqueries),也可能需要使用临时段(temporary segment)。所以当查询包含DISTINCT,GROUP BY,或 ORDER BY 子句时,Oracle有可能使用两个临时段。 |
132 |
Segments in Temporary Tables and Their Indexes |
2.4.3.2 临时表及其索引使用的段 |
133 |
Oracle 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. |
Oracle可以为临时表(temporary table)及其索引分配临时段(temporary segment)。临时表中的数据只在会话(session)或事务(transaction)的运行期内存在。 |
134 |
|
另见: |
135 |
How Temporary Segments Are Allocated |
2.4.3.3 临时段如何被分配 |
136 |
Oracle allocates temporary segments differently for queries and
temporary tables. |
Oracle为查询(query)与临时表(temporary table)分配临时段(temporary segment)的过程是不同的。 |
137 |
Allocation of Temporary Segments for Queries |
2.4.3.3.1 为查询分配临时段 |
138 |
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 TABLESPACE clause. |
在用户会话(session)期间,如果用户需要,Oracle可以在用户的临时表空间(temporary tablespace)内为用户创建临时段(temporary segment)。管理员可以使用 CREATE USER 或 ALTER USER 语句中的 TEMPORARY TABLESPACE 子句指定用户默认使用的临时表空间。 |
139 |
|
提示: |
140 |
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 drops temporary segments when the statement completes. |
如果没有为用户指定临时表空间(temporary tablespace),那么默认的临时表空间为 SYSTEM。临时表空间的默认存储参数决定了临时段(temporary segment)的数据扩展(extent)如何分配。在语句结束时Oracle负责移除(drop)临时段。 |
141 |
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. |
因为临时段(temporary segment)的分配与回收经常发生,所以有必要为创建临时段准备一个专用的表空间(tablespace)。这样可以分流磁盘设备的I/O,也可以减少由于在 SYSTEM 或其他表空间内频繁创建临时段而造成的碎片。 |
142 |
|
提示: |
143 |
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. |
因为排序等操作引起的临时段(temporary segment)内的数据变化并不记录到重做日志(redo log)中,只有对临时段进行空间管理的操作才被记录。 |
144 |
|
|
145 |
Allocation of Temporary Segments for Temporary Tables and Indexes |
2.4.3.3.2 为临时表及临时索引分配临时段 |
146 |
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. |
当用户首次使用 INSERT 语句向一个临时表(temporary table)插入数据时,Oracle为这个临时表创建临时段(这个插入操作也可能是隐式地由 CREATE TABLE AS SELECT 语句执行)。当首个 INSERT 执行时,Oracle为临时表及其索引分配临时段,创建索引的root page,同时创建所有的 LOB 段(LOB segment)。 |
147 |
Segments for a temporary table are allocated in a temporary tablespace
of the user who created the temporary table. |
临时表(temporary table)的段存储在创建者的临时表空间(temporary tablespace)中。 |
148 |
Oracle 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. |
当事务(transaction)结束时,Oracle负责移除(drop)属于此事务的临时表(transaction-specific temporary table)使用的段,同样地,当会话(session)结束时,Oracle负责移除(drop)属于此会话的临时表(session-specific temporary table)使用的段。但是如果其他会话或事务也在使用此临时表,此段将继续保存数据。 |
149 |
|
另见: |
150 |
Introduction to Automatic Undo Management |
2.4.4 自动撤销管理简介 |
151 |
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:
|
Oracle
中保存了用于恢复对数据库操作的信息。例如事务(transaction)中所有操作的记录(被统称为撤销信息(undo))。Oracle可以利用撤销信息完成以下工作:
|
152 |
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. |
自动撤销管理(automatic undo management)是基于撤销表空间(undo tablespace)的。用户使用撤销表空间管理撤销信息,而不必创建多个大小不一的回滚段(rollback segment)。 |
153 |
Automatic 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 segments. The
system automatically tunes the period for which undo is retained in the
undo tablespace to satisfy queries that require undo information. If the
current undo tablespace has enough space, then 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. |
自动撤销管理(automatic undo management)消除了管理回滚段(rollback segment)的复杂性,使用户只需专注于控制撤销信息(undo)的保存期限。Oracle强烈建议用户使用撤销表空间(undo-tablespace),避免使用回滚段(rollback segment)。系统能够自动调整撤销信息在撤销表空间内的保存时间,供需要使用撤销信息的查询使用。如果正在使用的撤销表空间中有足够的空间,用户可以为 UNDO_RETENTION 参数设定一个最低阀值(low threshold value),这样系统中的撤销信息至少在此值指定的时期内将被保留。 |
154 |
Use the V$UNDOSTAT view to monitor and
configure your database 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. |
用户可以使用 V$UNDOSTAT 视图来监视撤销空间(undo space)的使用情况,并对对其进行配置以提高效率。V$UNDOSTAT 能够显示各种与撤销空间及相关事务(transaction)有关的统计信息,例如当前实例(instance)中使用了多少撤销空间。 |
155 |
|
提示: |
156 |
The Oracle Database contains an Undo Advisor that provides advice on and
helps automate the establishment of your undo environment. |
Oracle数据库提供了 Undo Advisor,能为用户提供建议,并自动地建立用户的撤销环境(undo environment)。 |
157 |
|
另见: |
158 |
Undo Mode |
2.4.4.1 撤销管理模式 |
159 |
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 management mode, undo space is managed through rollback
segments. In automatic undo management mode, undo space is managed in
undo tablespaces. 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 undo management mode. |
用户通过设定撤销管理模式(undo mode)就可以灵活地选择使用手动撤销管理(manual undo management)或自动撤销管理(automatic undo management)。数据库系统既可以运行在手动撤销管理模式下,也可以运行于自动撤销管理模式下。在手动撤销管理模式下,撤销空间通过回滚段(rollback segment)管理。在自动撤销管理模式下,撤销空间通过撤销表空间(undo tablespace)管理。如需使用自动撤销管理模式,数据库管理员只需为每个实例(instance)创建一个撤销表空间,并将初始化参数 UNDO_MANAGEMENT 设为 AUTO 即可。Oracle建议用户使用自动撤销管理模式。 |
160 |
Undo Quota |
2.4.4.2 撤销空间配额(Undo Quota) |
161 |
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. |
在自动撤销管理(automatic undo management)模式下,为事务提供撤销段(undo segment)及为此段分配空间的工作完全由数据库系统控制。然而一个存在问题的事务(ill-behaved transaction)有可能消耗大量撤销空间(undo space),甚至导致整个系统瘫痪。在资源管理器(Resource Manager)中可以设置 UNDO_POOL 参数,从而以更直接的方式控制大事务(large transaction)的管理。数据库管理员可以将用户编为消费者组(consumer group),并为每个组设定最大撤销空间限制。当一个组使用的撤销空间之和超过了设定的限制值时,这个组的用户就不能执行新的更新(update)操作,直到本组内其他用户的事务结束并释放撤销空间。 |
162 |
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. |
UNDO_POOL
参数的默认值是
UNLIMITED,即用户可以使用整个撤销表空间(undo
tablespace)来存储其撤销信息。数据库管理员也可以使用
UNDO_POOL 参数对每一个用户进行限制。[另见:Oracle
Database Administrator's Guide,Chapter 24 Using the Database Resource
Manager,了解关于 UNDO_POOL 的信息。] |
163 |
Automatic Undo Retention |
2.4.4.3 自动撤销信息保存周期管理 |
164 |
Oracle Database 10g automatically tunes a parameter called the undo
retention period. The undo retention period indicates the amount of time
that must pass before old undo information—that is, undo information for
committed transactions—can be overwritten. The database collects usage
statistics and tunes the undo retention period based on these statistics
and on undo tablespace size. Provided that automatic undo management is
enabled, the database automatically tunes the undo retention period as
follows:
|
Oracle 10g
数据库自动地调整用于控制撤销信息保存周期(undo retention
period)的参数。撤销信息保存周期是指,撤销表空间中旧的撤销信息(即已提交事务的撤销信息)在被覆盖之前至少需要被保存的时间。数据库会收集撤销信息的使用情况,并根据统计结果及撤销表空间(undo
tablespace)的大小对撤销信息保存周期进行调整。当数据库处于自动撤销管理(automatic undo
management)模式下,其撤销信息保存周期的调整规则如下:
|
165 |
For fixed size and AUTOEXTEND undo
tablespaces of equal size, depending on the queries that you run, the
tuning method used in fixed size tablespaces tends to provide a longer
retention period. This enables flashback operations to flash back
farther in time, and maximizes the amount of undo data available for
long-running queries. |
当一个固定容量的撤销表空间(undo tablespace)与一个自动扩展的撤销表空间(存储参数为 AUTOEXTEND)容量相同时,前者使用的撤销信息保存周期(undo retention period)调整方法通常能够提供更长的保存时间。这使回闪(flashback)操作可以追溯的更远,也使运行时间长的查询有更多的撤销数据(undo data )可用。 |
166 |
External Views |
2.4.4.4 外部视图 |
167 |
Monitor transaction and undo information with
V$TRANSACTION and V$ROLLSTAT. For
automatic undo management, the information in
V$ROLLSTAT reflects the behaviors of the automatic undo
management undo segments. |
用户可以使用 V$TRANSACTION 及 V$ROLLSTAT 视图监控事务(transaction )及撤销空间(undo)的信息。对于自动撤销管理(automatic undo management)模式,V$ROLLSTAT 视图能够展现自动撤销管理所使用的各个撤销段(undo segment)的情况。 |
168 |
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 space required for the current workload. |
V$UNDOSTAT 可以显示撤销空间运行的历史统计信息。用户可以查询撤销空间使用率(undo consumption rate),事务并发性(transaction concurrency),实例中最长的查询的运行时间等统计信息。通过这个视图,用户可以更好地估计在当前工作负荷下系统所需的撤销空间(undo space)容量。 |
169 |
|
另见: |
[012] free list 可用块列表 [022] common and variable header [022] table directory [022] row directory [022] free space [022] row data [028] row piece [034] Row Chaining and Migrating [037] transaction entries [040] Automatic segment-space management [077] initial extent [078] incremental extent [083] permanent tablespaces [092] Segment Advisor [094] owner [109] single sort [110] Multiple sort [110] sort segments [146] root page [156] Undo Advisor [161] Resource Manager [165] flashback |
[040] Better run-time adjustment to
variations in concurrent access [040] Better multi-instance behavior in terms of performance/space utilization [164] The UNDO_RETENTION initialization parameter is ignored unless retention guarantee is enabled. |
[028] [039] [040] [162] |
1、DROP/TRUNCATE时Extent的回收情况 http://www.itpub.net/514580.html 2、drop index 后,如何回收空间 http://www.itpub.net/507280.html 3、9.2.0的UNDO表空间不能自动释放 http://www.itpub.net/515307.html 4、ITPUB提问 http://www.itpub.net/showthread.php?s=&threadid=515368 5、表空间这样创建是不会自动扩展的吗? http://www.itpub.net/482386.html |
[017] Multiple Block Sizes [042] Oracle Database Administrator's Guide [050] Row Format and Size [050] Rowids of Row Pieces [050] Physical Rowids [050] Oracle Database Performance Tuning Guide [053] Overview of LOB Datatypes [086] Managing Space in Tablespaces [086] Bigfile Tablespaces [086] Oracle Database Administrator's Guide [093] Oracle Database Administrator's Guide [093] Oracle Database SQL Reference [096] Oracle Database Administrator's Guide [096] Oracle Database SQL Reference [105] Overview of Materialized Views [112] Introduction to Temporary Segments [112] Temporary Tables [123] Oracle Database Advanced Replication [123] Oracle Database SQL Reference [134] Temporary Tables [144] Bigfile Tablespaces [144] Database Security [149] Temporary Tables [157] Oracle Database 2 Day DBA [157] Oracle Database Administrator's Guide [169] Oracle Database Administrator's Guide |
1、关于高水位线及数据块的分配,有待更详细的内容 [012] 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. 2、有待验证 [015] In contrast, at the physical, operating system level, all data is stored in bytes. 3、有待学习 [015] Each operating system has a block size. 4、为什么会引起unnecessary I/O;maximum limit是多少? [016] The data block sizes should be a multiple of the operating system's block size within the maximum limit to avoid unnecessary I/O. 5、数据块头的内容、格式 [024] The header contains general block information, such as the block address and the type of segment (for example, data or index). 6、哪些管理开销固定,哪些可变? [031] Some block overhead is fixed in size; the total block overhead size is variable. 7、locally managed/dictionary managed管理的对象是什么?可用块列表的管理的对象是什么? [040] The in-segment free/used space is tracked using bitmaps, as opposed to free lists. 答:locally managed/dictionary managed方式管理的对象是data block,可用块列表(free list)的管理的对象也是data block。但二者的管理目的不同。前者是为了找出可用块形成extent这种逻辑存储结构;后者是为了找出可用块向其中写入数据。 7.1、又出现新问题了。如果locally managed/dictionary managed方式管理的对象是可用/已用data block,难道磁盘上已经存在了很多data block? 8、被迁移的数据行,在原数据块中的空间是否释放? [048] Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. 9、含义不清 [100] 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. 10、含义不清 [111] A temporary segment in a temporary table contains data for multiple statements of a single transaction or session. Oracle drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tablespace. 答:解决 11、含义不清。为什么例子中说可能使用俩个临时段? [131] For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle can require as many as two temporary segments. 12、root page是什么?LOB segment又是什么? [146] 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. 13、临时表不是私有的么? [148] If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table. 14、什么是retention guarantee? [164] The UNDO_RETENTION initialization parameter is ignored unless retention guarantee is enabled. 15、一个data block中包含的数据只可能属于一个表吧? [026] This portion of the data block contains information about the table having rows in this block. 16、自动段空间管理(automatic segment-space management)的详细信息,如设置方式,参数等。 [041] |