Oracle Data Blocks

Data Block也称为page, 它是Oracle IO的最小单位，不同于OS的block，它是逻辑存储结构，由多个OS block组成，查看OS block大小：$ getconf PAGESIZE

At the physical level, database data is stored in disk files made up of operating system blocks.

An operating system block is the minimum unit of data that the operating system can read or write. In contrast, an Oracle block is a logical storage structure whose size and structure are not known to the operating system.

Data block called an Oracle block or page. A data block is the minimum unit of database I/O.

The following figure shows that operating system blocks may differ in size from data blocks.

Description of "Figure 12-6 Data Blocks and Operating System Blocks"

The logical separation of data blocks from operating system blocks has the following implications:

Applications do not need to determine the physical addresses of data on disk.

Database data can be striped or mirrored on multiple physical disks.

DB_BLOCK_SIZE想更改只能重建数据库

The DB_BLOCK_SIZE initialization parameter sets the data block size for a database when it is created. The database block size cannot be changed except by re-creating the database.

如果建库时没指定一般会设置为4KB或8KB，取决于操作系统

If DB_BLOCK_SIZE is not set, then the default data block size is operating system-specific. The standard data block size for a database is 4 KB or 8 KB. If the size differs for data blocks and operating system blocks, then the data block size must be a multiple of the operating system block size.

You can create individual tablespaces whose block size differs from the DB_BLOCK_SIZE setting.

A nonstandard block size can be useful when moving a transportable tablespace to a different platform.

（一）Data Block Format

Every data block has a format or internal structure that enables the database to track the data and free space in the block.

This format is similar whether the data block contains table, index, or table cluster data.

The following figure shows the format of an uncompressed data block.

Description of "Figure 12-7 Data Block Format"

1. Data Block Overhead

Oracle Database uses the block overhead to manage the block itself. The block overhead is not available to store user data. Some parts of the block overhead are fixed in size, but the total size is variable. On average, the block overhead totals 84 to 107 bytes.

As shown in "Data Block Format", the block overhead includes the following parts:

1. Block header

用于存储disk address, segment type, transaction entry

This part contains general information about the block, including disk address and segment type. For blocks that are transaction-managed, the block header contains active and historical transaction information.

关于interested transaction list(ITL):

每当事务要更新数据块里的数据时，必须先得到一个ITL槽，然后将当前事务ID，事务所用的undo数据块地址，SCN号，当前事务是否提交等信息写到ITL槽里

The block header of every segment block contains an interested transaction list (ITL).

Entries in the ITL describe which transactions have rows locked and which rows in the block contain committed and uncommitted changes.

The ITL points to the transaction table in the undo segment, which provides information about the timing of changes made to the database.

In a sense, the block header contains a recent history of transactions that affected each row in the block. The INITRANS parameter of the CREATE TABLE and ALTER TABLE statements controls the amount of transaction history that is kept in each block. Inittrans默认值为1,最大255

CREATE TABLE T1(ID NUMBER) INITRANS 255;   --没有MAXTRANS选项

Select INI_TRANS,MAX_TRANS from dba_tables where table_name='T1' and OWNER='SYS';

A transaction entry is required for every transaction that updates the block. Oracle Database initially reserves space in the block header for transaction entries. In data blocks allocated to segments that support transactional changes, free space can also hold transaction entries when the header space is depleted耗尽. The space required for transaction entries is operating system dependent. However, transaction entries in most operating systems require approximately 23 bytes.

1. Table directory

用于记录表的哪些行存于此block

For a heap-organized table, this directory contains metadata about tables whose rows are stored in this block. In a table cluster, multiple tables can store rows in the same block.

1. Row directory

用于记录行row number, 具体存于row directory向量的插槽中

For a heap-organized table, this directory describes the location of rows in the data portion of the block. The database can place a row anywhere in the bottom of the block. The row address is recorded in one of the slots of the row directory vector.

A rowid points to a specific file, block, and row number. For example, in the rowid AAAPecAAFAAAABSAAA, the final AAA represents the row number. The row number is an index into an entry in the row directory. The row directory entry contains a pointer to the location of the row on the data block.

If the database moves a row within a block, then the database updates the row directory entry to modify the pointer. The rowid stays constant.

After the database allocates space in the row directory, the database does not reclaim this space after deleting rows. Thus, a block that is currently empty but formerly had up to 50 rows continues to have 100 bytes allocated for the row directory. The database reuses this space only when a session inserts new rows in the block.

1. Row Format

用于存储实际数据, 一行数据会分为一个或多个row piece，每个row piece均包含一个row header与column data.

The row data part of the block contains the actual data, such as table rows or index key entries. Just as every data block has an internal format, every row has a row format that enables the database to track the data in the row.

Oracle Database stores rows as variable-length records. A row is contained in one or more sections. Each section is called a row piece. Each row piece has a row header and column data.

The following figure shows the format of a row.

1) Row Header

Oracle Database uses the row header to manage the row piece stored in the block.

The row header contains information such as the following:

1. Columns in the row piece
2. Pieces of the row located in other data blocks

If an entire row can be inserted into a single data block, then Oracle Database stores the row as one row piece. However, if all of the row data cannot be inserted into a single block or an update causes an existing row to outgrow its block, then the database stores the row in multiple row pieces. A data block usually contains only one row piece per row.

1. Cluster keys for table clusters

A row fully contained in one block has at least 3 bytes of row header.

1. Column Data

After the row header, the column data section stores the actual data in the row. The row piece usually stores columns in the order listed in the CREATE TABLE statement, but this order is not guaranteed. For example, columns of type LONG are created last.

每列会分别存储列长度与数据，如果数据类型为变长，则占用空间是可变的

As shown in the figure in "Row Format", for each column in a row piece, Oracle Database stores the column length and data separately. The space required depends on the data type. If the data type of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.

Each row has a slot in the row directory of the data block header. The slot points to the beginning of the row.

（二）Space Management in Data Blocks

As the database fills a data block from the bottom up, the amount of free space between the row data and the block header decreases.

Free space in a data block can also shrink during updates, as when changing a trailing null value to a non-null value. The database manages free space in the data block to optimize performance and avoid wasted space.

Note: This section assumes the use of automatic segment space management.

1. Percentage of Free Space in Data Blocks

Pctfree：数据块空闲百分比，小于此值不再视为free block，不能再insert只能update, 默认会为10。Pctfree设置过小，则容易产生行链，太大容易靠成空间浪费，可以查看查看表的user_tables的max_row_len大小再设值

Pctused：数据块使用百分比，大于此值被重新视为free blocks，可以insert, 默认40，在ASSM表空间会被忽略. MSSM表空间建议pctused+pctfree<90, 即留至少10空闲用于更新

Figure 12-10 shows how a PCTFREE setting of 20 affects space management.

This discussion does not apply to LOB data types, which do not use the PCTFREE storage parameter or free lists.

1. Optimization of Free Space in Data Blocks

Optimization by Increasing Free Space

Some DML statements can increase free space in data blocks：

1. DELETE statements
2. UPDATE statements that either update existing values to smaller values or increase existing values and force a row to migrate
3. INSERT statements on a table that uses advanced row compression. If INSERT statements fill a block with data, then the database invokes block compression, which may result in the block having more free space.

The space released is available for INSERT statements under the following conditions:

1. If the INSERT statement is in the same transaction, and if it is after the statement that frees space, then the statement can use the space.
2. If the INSERT statement is in a separate transaction from the statement that frees space (perhaps run by another user), and if space is needed, then the statement can use the space made available, but only after the other transaction commits.

Optimization by Coalescing Fragmented Space

Released space may or may not be contiguous with the main area of free space in a data block. Noncontiguous free space is called fragmented space.

The following figure shows a data block with noncontiguous free space.

Description of "Figure 12-11 Data Block with Fragmented Space"

Oracle是对block内的空闲空间自动合并的，合并不会产生更多空闲空间，但使它们连续

Oracle Database automatically and transparently coalesces the free space of a data block only when the following conditions are true:

1. An INSERT or UPDATE statement attempts to use a block that contains sufficient free space to contain a new row piece.
2. The free space is fragmented so that the row piece cannot be inserted in a contiguous section of the block.

After coalescing, the amount of free space is identical to the amount before the operation, but the space is now contiguous. Figure 12-12 shows a data block after space has been coalesced.

Description of "Figure 12-12 Data Block After Coalescing Free Space"

Chained and Migrated Rows

Insert大行到block导致chaning，update导致migration，后者是把全部数据放到新块并在原块使用指针指向新块，所以rowid不变

Oracle Database uses chaining and migration to manage rows that are too large to fit into a single block. The following situations are possible:

1. The row is too large to fit into one data block when it is first inserted.

In row chaining, Oracle Database stores the data for the row in a chain of one or more data blocks reserved for the segment. Row chaining most often occurs with large rows.

1. A row that originally fit into one data block is updated so that the overall row length increases, but insufficient free space exists to hold the updated row.

In row migration, Oracle Database moves the entire row to a new data block, assuming the row can fit in a new block. The original row piece of a migrated row contains a pointer or "forwarding address" to the new block containing the migrated row. The rowid of a migrated row does not change.

1. A row has more than 255 columns.

Oracle Database can only store 255 columns in a row piece. Thus, if you insert a row into a table that has 1000 columns, then the database creates 4 row pieces, typically chained over multiple blocks.

Figure 12-13 depicts the insertion of a large row in a data block. The row is too large for the left block, so the database chains the row by placing the first row piece in the left block and the second row piece in the right block.

Figure 12-14, the left block contains a row that is updated so that the row is now too large for the block. The database moves the entire row to the right block and leaves a pointer to the migrated row in the left block.

Description of "Figure 12-14 Row Migration"

When a row is chained or migrated, the I/O needed to retrieve the data increases. This situation results because Oracle Database must scan multiple blocks to retrieve the information for the row. The Segment Advisor can offer advice about objects that have significant free space or too many chained rows.

（三）Index Blocks

1. Types of Index Blocks

有三种类型的index block:

1. Root block: This block identifies the entry point into the index.
2. Branch blocks:

The databases navigates through branch blocks when searching for an index key.

1. Leaf blocks: These blocks contain the indexed key values rowids that point to the associated rows. The leaf blocks store key values in sorted order so that the database can search efficiently for all rows in a range of key values.

2. Storage of Index Entries

Index entries are stored in index blocks in the same way as table rows in a data block. The index entries in the block portion are not stored in binary order, but in a heap.

The database manages the row directory in an index block differently from the directory in a data block. The entries in the row directory (not the entries in the body of the index block) are ordered by key value. For example, in the row directory, the directory entry for index key 000000 precedes the directory entry for index key 111111, and so on.

The ordering of entries in the row directory improves the efficiency of index scans. In a range scan, the database must read all the index keys specified in the range. The database traverses the branch blocks to identify the leaf block that contains the first key. Because entries in the row directory are sorted, the database can use a binary search to find the first index key in the range, and then progress sequentially through the entries in the row directory until it finds the last key. In this way, the database avoids reading all the keys in the leaf block body.

1. Reuse of Slots in an Index Block

The database can reuse space within an index block.

For example, an application may insert a value into a column and then delete the value. When a row requires space, the database can reuse the index slot formerly occupied by the deleted value.

这个特点保证了index不会经常split

An index block usually has many more rows than a heap-organized table block. The ability to store many rows in a single index block makes it easier for the database to maintain an index because it avoids frequent splits of the block to store new data.

An index cannot coalesce itself, although you can manually coalesce it with an ALTER INDEX statement with the REBUILD or COALESCE options. For example, if you populate a column with values 1 to 500000, and if you then delete the rows that contain even numbers, then the index will contain 250,000 empty slots. The database reuses a slot only if it can insert data that fits into an index block that contains an empty slot.

1. Coalescing an Index Block

Index coalescing compacts existing index data in place and, if the reorganization frees blocks, leaves the free blocks in the index structure. Thus, coalescing does not release index blocks for other uses or cause the index to reallocate blocks.

Oracle Database does not automatically compact the index: you must run an ALTER INDEX statement with the REBUILD or COALESCE options.

Figure 12-15 shows an index of the employees.department_id column before the index is coalesced. The first three leaf blocks are only partially full, as indicated by the gray fill lines.

Description of "Figure 12-15 Index Before Coalescing"

Figure 12-16 shows the index in Figure 12-15 after the index has been coalesced. The first two leaf blocks are now full, as indicated by the gray fill lines, and the third leaf block has been freed.

Description of "Figure 12-16 Index After Coalescing"