--oracle Data Block (block) with Case study tuning

--oracle Data Block (block) with Case study tuning

Data Block Overview Oracle manages the storage space in a database data file (datafile) as a data block. A data block is the smallest (logical) unit of data in a database. Corresponding to the data block, the smallest physical storage unit of all data at the operating system level is byte (byte). Each operating system has a parameter called block size. Each time Oracle obtains data, it always accesses the entire number of data blocks (Oracle) rather than the capacity of the operating system block to access the data.

The standard data block capacity in the database is specified by the initialization parameter db_block_size. In addition, users can specify five non-standard block sizes (nonstandard block size). Block capacity should be set to an integer multiple of the capacity of the operating system block (at the same time less than the maximum limit for block capacity) in order to reduce unnecessary I/O operations. Oracle data blocks are the smallest storage units that Oracle can use and allocate.

See also: More information about block capacity (data block size) in Oracle documents for specific operating systems multiple data block capacity (multiple block Sizes)

Data block structure in Oracle, the internal structure of a table (table), index, or cluster table (clustered data) is similar, regardless of whether the data block is stored.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/47/B5/wKioL1P-nVjj2ZfaAACmmxlgC-w807.gif "title=" Block.gif "alt=" Wkiol1p-nvjj2zfaaacmmxlgc-w807.gif "/>

This diagram shows the various components of the data block, including: Data size (including standard content and variable content) (Common and variable header), Table directory area (table directory), row directory area (row directories), free space area ( Free space), row data area. The following sections explain each component separately. The two arrows in the figure indicate that the capacity of the free space area in a block is variable.

data block (includes standard content and variable content)

The header contains a summary of the data block, such as the block address and the type of segment (segment) to which the block belongs (for example, a table or index).

　　Table Directory Area

If a data table stores data rows in this data block, the data table information is recorded in the table directory area of the data Block (table directory).

Row Directory Area

This area contains information about the rows of data stored in the data block (row piece) addresses in the row data area of each data line fragment. [A data block may hold a complete row of data, or only part of the data row, so use row piece]

When the row directory space of a data block is used, the row directory space is not reclaimed, even if the data row is deleted (delete). For example, when a block of data that once contained 50 records is emptied, the row directory area of its header will still occupy 100 bytes (byte) of space. Row Directory area space is only re-used when new data is inserted (insert) in the data block.

The management overhead data block header, the table directory area (row directory), is collectively referred to as the administrative overhead (overhead). Some of the overhead capacity is fixed, while the total capacity of some of the overhead is variable. The capacity of fixed and variable management overhead in a data block averages between 84 and 107 bytes (byte).

Row data data block contains the actual data for a table or index. A data row can span multiple blocks of data. Here's the line link (row Chaining) and row migration (row migrating)

Space in free space is used when the free space area inserts new rows of data, or when more space is required to update a row of data (for example, the last character of a row blank (trailing null) and is now updated to a non-null value).

If a data block is part of a table or cluster table's data segment, or an index segment (index segment) that belongs to the index, the transaction entry (transaction Entry) may also be stored in its free space area. If the data row (row) in a data block is being insert,update,delete, and SELECT ... For UPDATE statement, you need to save the transaction entry in this data block. The storage space required for transaction entries depends on the operating system. In a common operating system, there are approximately two types of SQL statements that need to be used to increase the free space in the data block: The DELETE statement, and the UPDATE statement that updates the existing data value to a smaller size. In the following two conditions, the space freed by the above two operations can be used by subsequent INSERT statements:

If the INSERT statement is in the same transaction as the above two operations (transaction) and is located after the statement that freed the space, the INSERT statement can use the freed space.

If the INSERT statement and the statement that frees the space are in a different transaction (for example, the two are submitted by a different user), the INSERT statement uses the freed space only after the statement that freed the space is committed and the data block is required to insert the data.

The freed space in the data block may not be contiguous with the free space area. Oracle merges freed space into free space when the following conditions are met: (1) The INSERT or UPDATE statement selects a block of data with enough free space to hold the new data, (2) but the free space in the block is not contiguous and the data cannot be written to contiguous space in the data block. Oracle merges the available space in a block only when the above conditions are met, in order to avoid too frequent spatial consolidation affecting database performance.

This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1546050

--oracle Data Block (block) with Case study tuning