5.oracle dump understanding five data block theory

5.oracle dump understanding five data block theory

Welcome reprint, Reprint please indicate source: http://blog.csdn.net/notbaron/article/details/51228514

The first two articles describe what we see at the operational level, but without theoretical guidance, the more we see, the more confused we will be. As a result, toad extracts from official documents some of the content of brain.

Block is the smallest unit of data block IO.

1 Data blocks and operating system blocks

From the physical level, the block storage of a database is made up of operating system blocks. The operating system block is the smallest unit of data that the operating system can read and write. An Oracle block is a logical storage structure with a size and structure that is transparent to the operating system.

Figure 1below:

The block size and the operating system block size can be different, and the block requests multiple chunks of data, not multiple operating system blocks.

When the database requests a block of data, the operating system translates this request operation to the store. Logical separation of data blocks there are two points:

n the application plane does not need to determine the physical address of the data on disk

n Data blocks can be striped or mirrored to multiple physical disks

1.1 Data Block size

Each block of data has a chunk size. There are initialization parameters db_block_size initialization parameters are set when the library is created. If not set, use the default block size of the operating system to make it. The standard data block size is 4KB or 8KB. If the size of the operating system block is different, it must be a multiple of the operating system.

1.2 table space block size

Use a block size different from the size of the db_block_size when creating a stand-alone tablespace.

2 block Format

Each block has a format or internal structure to enable data blocks to track data and free space. Storage tables, indexes, or table family data are similar.

Figure 2below:

2.1 Data Block overhead

Data block of the overhead toad did not know what to translate into, it is not translated.

Data blocks use overhead to manage the block itself. cannot be used to store user data. This means that although each block size hypothesis is 8KB, there is actually not so much available.

The overhead contains the following sections:

2.1.1 .

Block size contains common information about blocks, such as block addresses, segment types. For transaction management blocks, the size of the block contains the activity and historical trading information.

Each update block requires a trade entry (transaction Entry). Oracle block initialization preserves these spaces. The space required for each trade entry depends on the operating system platform. It is generally about 23 bytes.

2.1.2 Table Directory

For heap tables, this directory contains the metadata blocks of the tables that exist for this block. For a clustered table, multiple tables store rows in the same block.

2.1.3 Rows of directories

For a heap table, this directory describes the position of the row in the block. The data block places the row anywhere on the bottom of the block. The row address is recorded in the directory vector.

A rowid points to a specific file, block, and line position. For example rowid:aaapecaafaaaabsaaa the last 3 AAA represents the line number. The line number is the index in the row directory. The row directory contains a pointer to the data in the block. If you move a row from a block, the data block modifies the pointer in the row directory, and rowID remains unchanged.

After the database allocates space in the row directory, the data block does not reclaim the space after the rows are deleted. Therefore, a block that is currently empty (a block that has 50 rows) may have 100 bytes allocated in the row directory. The data block will re-use this space when inserting rows.

Some parts of the overhead are fixed, but the whole is changed. Average in 84~107 bytes.

2.2-row format

In addition to overhead in a block, the rest is OK. is the place where the data is actually put.

The row data holds true data, such as table rows or index keys. Each block has an internal format, and each row has a row format that allows the data block to track the data.

Oracle data blocks store rows through variable-length records. A row contains one or more segments. Each segment is called a row slice (rowpiece), and each row piece has a wardrobe and column data.

Figure 3below:

2.2.1 Wardrobe

Oracle data uses a wardrobe to manage the storage of row slices in blocks. The outfit contains the following information:

L Number of columns in the row slice

L row slices in other blocks (if the complete row is in a data block, then the row acts as a row slice, and if it cannot be stored in a block, it is stored in multiple row slices)

Cluster keys for cluster tables

A block that contains a line of at least 3 bytes of wardrobe.

2.2.2 Columns of data

After the wardrobe, the column data segment holds the actual data. In general, the order of storage columns is consistent with the CREATE TABLE statement, but the order is not guaranteed. For example, a long type is generally last created.

The database stores column lengths and data separately for each column in the row slice. The space depends on the data type. If it is mutable, it will require space to grow or become smaller.

There is a slot on the row directory in the header that points to the row.

2.2.3 rowID formatting

Oracle uses ROWID to uniquely specify a row. ROWID is the structure in which blocks of data are used to access rows. A rowid physically does not exist on the data block, but is related to the file and block where the data resides.

The ROWIW format is as follows in Figure 4:

Divided into 4 segments, the first is the data object number, and the table cluster has the same data object number.

The second one is the relative file number, the third is the block number, and the fourth is the line number.

rowID points to a row slice, rowid can change in some cases. For example, a row movement occurs, a partition key is updated, a flashback table operation, and so on.

The internal data block performs a row move that is physically removed and then inserted in. But row movement is treated as update, and triggers may be triggered.

3 Block Compression

Data blocks can use table compression to eliminate duplicate values in a block of data.

The format of a data block is the same regardless of whether it is compressed. The difference is that a symbol table is stored when the block starts to store. Use the short symbol in the symbol table instead of the duplicate value.

For example:

2190,13770,25-nov-00,s,9999,23,161

2225,15720,28-nov-00,s,9999,25,1450

34005,120760,29-nov-00,p,9999,44,2376

9425,4750,29-nov-00,i,9999,11,979

1675,46750,29-nov-00,s,9999,19,1121

Compress as follows:

2190,13770,25-nov-00,s,%,23,161

2225,15720,28-nov-00,s,%,25,1450

34005,120760,*,p,%,44,2376

9425,4750,*,i,%,11,979

1675,46750,*,s,%,19,1121

Use * to replace 29-nov-00

Use% to replace 9999

The symbol table is shown in Figure 5:

4 space Management in data blocks

The block fills the data block starting at the bottom, and the free space is between the row data and the volume. Free space will be reduced at update time. The database manages free space to optimize performance to avoid wasting space.

4.1 free Space percentage

The Pctfree parameter specifies how free space is managed. Pctfree is important to avoid row migrations and wasted space.

If you have fewer updates, you can set the following:

CreateTable test_table (n number) PCTFREE 20;

As shown in Figure 6 below

The Pctfree is set to 20 and requires at least 20% of the blocks to be idle. Reserved 20% is used for existing row updates.

optimize free space in 4.2 data blocks

The proportion of space space should not be less than pctfree, but can exceed 20%.

The following command can increase the free space in a block:

L DELETE

L UPDATE (change the existing value to a smaller value, or let the row migrate)

L Insert (Insert wake-up block compression, resulting in more free space)

Insert free space Note the following 2 points:

1. After the INSERT statement of this transaction is released, the space will be used for this transaction;

2. If it is free space for other transactions, it will be used after other transaction commit

4.3 Merging fragmentation space

The freed space may not be contiguous, as shown in Figure 7 below

Oracle automatically merges free space when conditions occur

L INSERT or UPDATE command attempts to use block space to contain new rows

L free space is too scattered and cannot be inserted into contiguous spaces in blocks

After merging, the free space is contiguous.

A merge executes only if the condition is satisfied, because the action affects performance.

4.4 Row connections and row migrations

The data block must manage too large rows stored in a single block.

L when the first insertion , the line is too large. Oracle stores multiple block connections that the row retains using segments. Row connections often occur in large rows. For example, include a long or longraw. Unavoidable.

l when the length of the line is updated, the remaining space cannot keep the updated row. A row migration occurs, the entire row is moved to a new block of data, and the original row slice holds a pointer to the new location. ROWID has not changed.

L More than 255 columns in a row, only 255 columns can be maintained in a single line slice. If you do, you need to link more than one block.

IO increases when a row is migrated or linked.

5 Index Block

The index block is a special type of block, which is somewhat different from the table block. The data block uses an index block to manage the logical storage space for the index.

The index block types are as follows:

Root BLOCK: Index entry

Branch blocks: Navigation blocks when searching for index keys

Yushang: Contains the index key value rowID, which points to the related row in the table.

5.1 Index Portal Storage

The index entry is stored in the same way that the table row is stored in the data block. The index is not stored in binary order in the block, but in a heap way.

The way data blocks manage row catalogs differs somewhat from data blocks. The row directory is sorted by key value. Improves the efficiency of index scanning.

5.2 Index block-slot multiplexing

The index block has more rows than the table block of the heap table. Storing many rows in a single index block makes it easier to manage chunks of data because you can avoid frequent splitting blocks.

Data blocks can be reused for space in an index block. If you insert a value into the column, then delete a value. When a row requires space, the data block is reused before it is deleted and the released index slot is removed.

Indexes cannot merge themselves unless you manually perform the ALTER index rebuild or COALESCE option.

5.3 Merging index blocks

Index merge compaction currently exists index data, and if you reorganize the freed blocks, the freed blocks remain in the index structure and are not released to other users. Does not automatically execute, you need to enter the alter INDEX rebuild or COALESCE option.

5.oracle dump understanding five data block theory