Shulou(Shulou.com)06/01 Report--

Oracle Block Architecture fundamentals Overview Block Overview Oracle manages storage space in database data files (datafile) in blocks (data block). A data block is the smallest (logical) data unit in a database. Corresponding to data blocks, the minimum physical unit of storage for all data at the operating system level is bytes (byte). Each operating system has a parameter called block capacity (block size). Each time Oracle acquires data, it always accesses the entire Oracle block, rather than accessing the data according to the capacity of the operating system block. The standard block (data block) capacity in the database is specified by the initialization parameter DB_BLOCK_SIZE. In addition, users can specify five non-standard block capacities (nonstandard block size). The block capacity should be set to an integral multiple of the operating system block capacity (and less than the maximum limit for block capacity) in order to reduce unnecessary Imax O operations. Oracle blocks are the smallest unit of storage that Oracle can use and allocate. 2.2.1 data block structure in Oracle, regardless of whether the data block stores table (table), index (index) or cluster table (clustered data), its internal structure is similar. Figure 2-1 data block structure

This figure shows the various components of the data block, including: data block head (including standard and variable content) (common and variable header), table catalog area (table directory), row catalog area (row directory), free space area (free space), row data area (row data). The following sections will explain the components separately. The two arrows in the figure indicate that the capacity of the free space area in a data block is variable.

2.2.1.1 the header (including standard and variable content) contains summary information about the block, including the block address (block disk address) and the type of segment (segment) to which the block belongs (for example, a table or index). Block header

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.

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.

Data block head

This section contains general information about the block address (address on disk) and segment type of the block. For those transactional blocks, their blocks also contain current and historical transaction information.

A transaction record is required by each transaction that updates the data block. The oracle data block initially reserves space in the data block header for transaction records. When a block of data is allocated to a segment that supports transaction changes, the free space can also retain transaction records when the block space is exhausted. The space required for transaction records is dependent on the operating system. However, in most operating systems, transaction recording requires approximately 23bytes space.

2.2.1.2 Table catalog area if a data table stores rows in this data block, the information of the data table will be recorded in the table catalog area (table directory) of the data block. Table directory

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

Table catalogue

For heap tables, this directory contains metadata for tables stored in this block, and multiple tables can store rows in the same data block.

2.2.1.3 Row catalog area this area contains information about the rows of data stored in the data block (the address of each row fragment (row piece) in the row data area (row data area)). [a complete row of data may be saved in a data block, or only a part of the data row may be saved, so row piece is used in this paper. When the row catalog area (row directory) space of a data block (data block) is used, even if the row is deleted (delete), the row directory area space will not be reclaimed. For example, when a data block that once contained 50 records is emptied, the row directory area of its header still takes up 100 bytes (byte) of space. Row catalog area space is reused only when new data is inserted (insert) into the block. Row directory

For a heap-organized table, this directory describes the location of rows in the data portion of the block.

After space has been allocated in the row directory, the database does not reclaim this space after row deletion. 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 new rows are inserted in the block.

Line directory

For a heap table, this directory describes the location of rows in the data block.

In a row directory, after the space is allocated, the database does not redeclare the space after the row is deleted. In addition, a data block is currently empty, but 50 rows of records have been reached before, and the allocated 100bytes space is maintained continuously. The database uses this space only when new rows are inserted. (HWM)

2.2.1.4 Management overhead data block (data block header), table catalog area (table directory), row directory area (row directory) are collectively referred to as administrative overhead (overhead). The capacity of some of the overhead is fixed, while the total capacity of some of the overhead is variable. The average capacity of fixed and variable management overhead in data blocks is between 84 and 107 bytes (byte). Oracle Database uses the block overhead to manage the block itself. The The block overhead is not available to store user data. Oracle database uses block management overhead to manage itself Administrative overhead cannot be used 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.

Some of the block overhead is fixed, but the total size is variable. Generally speaking, the block overhead is 84-107bytes.

2.2.1.5 the row data area (row data) in the row data block (data block) contains the actual data of the table or index. A row of data can span multiple blocks. This leads to "Row Chaining and Row Migrating Row Format 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 row pieces. Each row piece has a row header and column data.

The row data portion of the block contains real data, such as table rows or index records. Just as each data block has an internal format, each row has a row format that enables the database to track the data in the row (row is made up of one or more piece).

Figure 12-7 shows the format of a row.

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:

Columns in the row piece

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 (see "Chained and Migrated Rows"). A data block usually contains only one row piece per row.

Cluster keys for table clusters (see "Overview of Table Clusters")

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

The information contained in the header:

The column in the row fragment, the location of the row fragment in another data block, and the keyword of the clustered table.

A row is fully contained in the data block with at least the header space required for 3btyes.

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 Figure 12-7, 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.

Behind the header, the column data section stores the real data of the row. Row slices are usually stored in the columns listed in the CREATE TABLE command, but the command does not guarantee this. For example, when a column of type LONG is created.

As shown in the figure above, the oracle database stores the column length and data separately for each column in the row slice. The space required depends on the data type. If the data type of the column is variable length, then the space needs to be maintained at a value that can be grown and compressed by update.

Rowid Format Oracle Database uses a rowid to uniquely identify a row. Internally, the rowid is a structure that holds information that the database needs to access a row. A rowid is not physically stored in the database, but is inferred from the file and block on which the data is stored.

The oracle database uses rowid to uniquely specify a row. Rowid is an internal structure that retains the information that a database needs to retrieve a row. Rowid is not physically stored in the database, but is inferred from the block or file in which the data is stored (this means that rowid is not database data, but it still takes up disk space).

An extended rowid includes a data object number. This rowid type uses a base64 encoding of the physical address for each row. The encoding characters are Amurz, aMuz, 0-9, +, and /.

The extensible rowid includes the data object number. This rowid type uses base64 encoding for the physical address of each data row. This kind of coded characters include: a Mui Z1, a le le z, 0-9, and /.

Example queries the ROWID pseudocolumn to show the extended rowid of the row in the employees table for employee 100.

ROWID Pseudocolumn

SQL > SELECT ROWID FROM employees WHERE employee_id = 100; ROWID-AAAPecAAFAAAABSAAA Figure 12-8 illustrates the format of an extended rowid.

An extended rowid is displayed in a four-piece format, OOOOOOFFFBBBBBBRRR, with the format divided into the following components:

OOOOOO

The data object number identifies the segment (data object AAAPec in Example 12-1). A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.

FFF

The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).

BBBBBB

The data block number identifies the block that contains the row (block AAAABS in Example 12-1). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace.

RRR

The row number identifies the row in the block (row AAA in Example 12-1).

The data object number specifies a segment. The data object number is assigned to each segment. Scheme objects in the same segment, such as clustering tables, have the same data object number. The data file number associated with the tablespace specifies the data file that contains the row. The row number specifies the rows in the data block. After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example, if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on. If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities. After a rowid is assigned to a row slice, the rowid can be changed in a special environment. For example, if row movement is turned on, rowid can change due to partitioning key updates, flashback table operations, compressed tables, and so on. If row migration is turned off, rowid changes when rows are imported and exported using the oracle tool. 2.2.1.6 when a new data row is inserted, or when more space is needed to update the data row (for example, the last field of a row was trailing null, now it will be updated to a non-null value), the space in the available space zone (free space) will be used. If a data block (data block) belongs to a data segment (data segment) of a table or cluster table, or an index segment (index segment) of an index, transaction entries (transaction entry) may also be stored in its free space area. If the data rows (row) in a data block are being accessed by INSERT,UPDATE,DELETE and SELECT...FOR UPDATE statements, transaction entries need to be saved in that data block. The storage space required for transaction entries depends on the operating system. In common operating systems, transaction entries require about two kinds of SQL statements to increase the available space in the data block: the DELETE statement, and the UPDATE statement that updates the existing data value to a smaller capacity value. The space freed by the above two operations can be used by subsequent INSERT statements under two conditions: if the INSERT statement is in the same transaction (transaction) as the above two operations, and after the statement that frees up space, then the INSERT statement can use the freed space. If the INSERT statement and the free space statement are in different transactions (for example, they are submitted by different users), the INSERT statement will use the freed space only if the free space statement is committed and the inserted data must use this data block. The space released in the data block (data block) may not be contiguous with the available space area (free space). Oracle merges the free space into the free space zone only if the following conditions are met: (1) the INSERT or UPDATE statement selects a data block with enough free space to hold new data; (2) but the free space in this block is not contiguous and the data cannot be written to contiguous space in the data block. Oracle merges the free space in the data block only when the above conditions are met, in order to avoid the impact of too frequent space consolidation on database performance. 2.2.2.2 Row chaining (Row Chaining) and row migration (Row Migrating) can cause a row of data in a table to be too large for a data block (data block). One case is Row Chaining, and the other is row migration (Row Migrating). When data rows are linked (chain) or migrated (migrate), accessing them will degrade the performance of Oracle O, because Oracle must access more data blocks (data block) to obtain the data of these data rows. Row Chaining: if we INSERT a row of data into the database, which is so large that one block cannot hold an entire row, Oracle will divide the row into segments and store it in several blocks, a process called Row Chaining. In this case, Oracle stores this line of data in a block chain (chain) within the segment. Row chaining (row chaining) often occurs when inserting rows with a large amount of data, such as a row containing columns of data type LONG or LONG RAW. At this point, line links are inevitable. Row links are also known as row spreads (Oracle allows row spreads, but DB2 does not).

Row migration (Row Migrating): a row of data originally stored in a data block (data block), because the update operation results in an increase in length, and the free space of the data block cannot accommodate the increased rows. In this case, Oracle migrates this row of data (migrate) to a new block. Oracle keeps a pointer to the new data block in the original location of the migrated data row. The rowid of the migrated data row remains the same.

The PCTFREE parameter is used to specify the minimum percentage of free space that must be reserved in the block. The reason for reserving such space is that it is needed for UPDATE. If there is no free space when UPDATE, Oracle will allocate a new block, which will result in row migration (Row Migrating). Space reservation can ensure the efficiency of database access to some extent. PCTUSED is also used to set a percentage that is identified as valid when the proportion of space used in the block is less than this percentage. Only valid blocks are allowed to insert data. 2.2.3 PCTFREE,PCTUSED, and Row Chaining in manually managed tablespaces (manually managed tablespaces), users can use two storage management parameters, PCTFREE and PCTUSED, to control how to make use of the available space in the data blocks (data block) belonging to a segment when inserting and updating a segment (data block). You can also set the PCTFREE parameter for an index when you create or modify it (the index is stored in the index segment (index segment)). Tip: this section does not apply to LOB data types (BLOB,CLOB,NCLOB, and BFILE). These types of data are stored without the PCTFREE parameter and the list of available blocks (free list). 2.2.3.1 the PCTFREE parameter PCTFREE parameter is used to set at least how much free space (percentage value) to be reserved (reserve) in a data block (data block), in preparation for the possible increase in the amount of data that may occur when the existing data in the block is updated. For example, when a user creates a table with the CREATE TABLE statement, the following parameter is specified: the parameter PCTFREE 20 sets each data block in the corresponding data segment of the table to reserve at least 20% of the free space for use when there is an existing data update in the block. As long as the sum of the capacity of the row data area and the data block head in the data block does not exceed 80% of the total capacity of the data block, the user can insert new data into it, the data row is put into the row data area (row data area), and the relevant information is written into the data block (overhead area). The 2.2.3.2PCTUSED parameter PCTUSED parameter is used to determine whether a block (data block) can be used to insert new data, based on the maximum percentage of the total capacity of the data block as the sum of the capacity of the data area (row data) and the data block head (overhead). When the proportion of free space in a data block is less than that specified by the PCTFREE parameter, Oracle believes that the data block cannot be used to insert new data until the percentage of capacity in the data block is less than the limit of the PCTUSED parameter. Until the footprint ratio is greater than the limit of the PCTUSED parameter, Oracle uses the free space of the data block only if it updates the data already in the block. For example, when a user creates a table with the CREATE TABLE statement, the following parameter is specified: PCTUSED 40, when the capacity ratio of a data block in this table is higher than 40%, Oracle will not use this data block to insert new data rows (assuming that the free space of this data block has been lower than the limit of PCTFREE). 2.2.3.4 how PCTFREE and PCTUSED work together PCTFREE and PCTUSED work together to optimize the spatial use of data blocks (data block). How PCTFREE and PCTUSED work together to manage the use of free space in blocks (data block). For the above example of PCTFREE=20,PCTUSED=40, oracle uses the following mechanism to manage block space: a. New data can only be inserted when the block occupies less than 80% of the space, because the PCTFREE parameter limits that 20% of the free space must be reserved for updating existing data within the block. b. The reserved space in the data block can be used for updating the data already in the data block. New data can be inserted into a data block only if the percentage of space occupied within it is less than 40%. c. When the proportion of space in the data block is less than 40%, the data block can be used to insert new data again. d. New data can only be inserted when the block occupies less than 80% of the space, because the PCTFREE parameter limits that 20% of the free space must be reserved for updating existing data within the block. The process goes back and forth. In the newly allocated block (data block), the space available to insert (insert) the data is equal to the total capacity of the block minus the block head (block overhead) and minus the reserved available space (PCTFREE). On the other hand, all available space in the update block can be used by the existing data within the block. Therefore, the update operation can make the free space within the data block below the PCTFREE limit because it is reserved for the update operation. In each segment (data segment) and index segment (index segment), Oracle manages a list of one or more available blocks (free list), which lists all data extensions (extent) that belong to this segment, and the free space ratio is larger than the blocks limited by PCTFREE. These blocks can be used by insert operations. When the user submits the INSERT statement, Oracle selects the first valid block to use from the list of available blocks. If this block does not have enough free space to accommodate the data submitted by the INSERT statement, and the capacity of the block exceeds the limit of PCTUSED, Oracle removes it from the list of available blocks. A segment can use multiple lists of available blocks at the same time to reduce competition when concurrently inserting (concurrent insert) a table. When the user submits a DELETE or UPDATE statement, Oracle processes the statement and checks whether the percentage of space occupied in the relevant data blocks is less than that specified by PCTUSED. If so, the data block is placed in the header of the list of available blocks (free list) being used by the current transaction (transaction), and will be used first if the current transaction still needs to write data. When a transaction commits, the free space in this block can also be used by other transactions. Data extension Overview data extension (extent) is a logical storage allocation unit for a database consisting of a contiguous set of data blocks (data block) (typically consisting of 8 block). A segment consists of one or more data extents. When the space in a segment has been used up, Oracle allocates a new data extent to the segment. 2.3.1 when data extensions are assigned when the user creates a data table, Oracle allocates an initial data extension (initial extent) containing several data blocks (data block) to the data segment (data segment) of the table. Although there is no data in the data table at this time, the data blocks in this initial data extension are ready to insert new data. If the data block in the initial data extension (initial extent) of a segment (segment) is full and new data is inserted in need of space, Oracle automatically allocates an incremental data extension (incremental extent) to the segment. An incremental data extension is a subsequent data extension allocated after a segment relays an existing data extension, and its capacity is greater than or equal to the previous data extension. For administrative purposes, the header block of each segment (segment) contains a directory that records all the data extensions (extent) of the segment.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.