Do you know about Oracle free data blocks?

Before giving you a detailed description of Oracle free data blocks, let's first let you know the data stored in the rollback segment, and then give a comprehensive introduction to Oracle free data blocks, hoping to be useful to everyone. Here we will talk about the data stored in the rollback segment. If it is a delete operation, the rollback segment will record the data of the entire row, if it is an update, the rollback segment only records the pre-data image before the modified fields in the row), that is, the fields not modified will not be recorded. If it is insert, the rollback segment only records the rowid of the inserted record.

In this way, if the transaction is committed, the rollback segment simply indicates that the transaction has been committed; if it is a rollback, if the operation is delete, during rollback, the data in the rollback segment is re-written back to the data block. If the operation is update, the data before the change is modified back. If the operation is insert, delete the record based on its rowid. Note: In addition to triggering LGWR and DBWN to write SCN and commit scn to the data block header, the checkpoint also writes SCN to the control file and data file header, the user's DML and COMMIT only write SCN and commit scn to the data block header without updating the SCN of the control file and data file, the SMON rollback starts from the file header's SCN, that is, from the previous checkpoint. The SMON rollback is a data block not identified as submitted in all rollback segments, A user's rollback is a transaction-related rollback segment that is not identified as a committed data block.

The following describes how to write data files in DBWN. before writing data files, you must first find writable Oracle free data blocks. Oracle free data blocks can be maintained through Freelist or BITMAP, they are located in the header of a segment to identify which data blocks in the current segment can be inserted. In the local management tablespace, Oracle automatically manages the size of the partition allocated to the segment. You can select the segment for automatic management only in the locally managed tablespace, the Oracle free data block information in the segment in the local management tablespace that uses automatic segment space management is stored in the header of some areas in the segment, the most common case for BITMAP management is that the first block in the FIRST partition of a segment is the first level bitmap block, and the second block is the second level bitmap block, the third part is the pagetable segment header, and the next part is the data BLOCK that records the data. The parent data BLOCK address of the first level bitmap block points to the second level bitmap block, the parent data BLOCK address of the second level bitmap block points to the pagetable segment header. The first level bitmap block records the status of all the blocks it manages, including three headers, not just data blocks, The status of the identifiers include Metadata, 75-100% free, 50-75% free, 25-50% free, 0-25% free, full, and unformatted. There is a list in the second level bitmap block, it records the first level bitmap block it manages. The pagetable segment header records a lot of content, except for the second level bitmap block it manages, it also records the first BLOCK address of each zone and the number of DB blocks in each zone, the BLOCK address of the first level bitmap block corresponding to each region of the segment and the starting address of the data BLOCK recorded in the zone.

If a zone has many blocks, two or more first level bitmap blocks will appear in one partition. These first level bitmap blocks manage some blocks in one partition separately, when there are few data blocks in a zone, the first level bitmap block in a zone can manage data blocks in multiple zones across zones, and the bitmap bolck can be at most three levels ). The management of Oracle free data blocks in the segments in the tablespaces managed by manual local management and data dictionary management are all managed by the idle list located in the segment header, for example, the SYSTEM tablespace is a local tablespace, but it uses manual segment space management. Therefore, Freelist is also used to manage Oracle free data blocks in segments. The idle list is a logical linked list. a block address pointing to the first idle BLOCK is recorded in the header block of the segment, the block address pointing to the next idle BLOCK is also recorded in the First db block.

To form a one-way linked list. If there are two FREE lists on the segment, there will be two pointers in the header block of the segment HEADER pointing to Two Idle blocks and creating two independent one-way linked lists. Idle list: When a segment is created, the first block in the first allocated area will become the first block of the segment, all other blocks in the first allocated area will be added to the Free List. When a new area is expanded, all the blocks in this area will be added to the Free List immediately, and the expansion will be added once. Unlike bitmap management, the header of the time zone in the idle list does not record the information of idle blocks in the zone. When the idle space is less than the value set by PCTFREE, this block is deleted from the idle list, that is, the next idle block address recorded in the previous block pointing to it is changed to the address of other idle blocks, this block is similar to a short circuit. When the content in this block falls below the value set by PCTUSED, the data block is added to the idle list again and to the front end of the idle list, that is, the header block points to it directly, and then points to the idle block pointed to by the original header block. Data blocks in the idle list are all blocks that can be inserted into it, however, the INSERT operation starts from the first block pointed to by the idle list. When a block is removed from the idle list, UPDATE can be performed as long as there is space reserved, when updating a big data row, if the current block cannot completely put down the entire row, it will only migrate the entire row to a new data block, A pointer pointing to the new block is left at the original block location, which is called row migration. If a data block can be inserted, when a row that cannot be loaded with the current block is inserted, the row will overflow into two or two blocks. This is called a row link.

If the user's action is INSERT, the server process will first lock Freelist, find the address of the first idle block, and then release Freelist, when multiple server processes want to lock Freelist at the same time, the Freelist contention occurs. That is to say, multiple processes will only compete with Freelist at the same time when they are inserted, you can specify the number of Freelist when creating a table in a tablespace that does not use automatic segment space management. The default value is 1. If you create a table in a tablespace that uses automatic segment space management, even if Freelist is specified, it is ignored because BITMAP instead of Freelist is used to manage the free space in the segment. The parameters that will be ignored when automatic segment space management is adopted include PCTUSED and Freelist GROUPS. If the user action is an UPDATE, DELETE, or other operation, the server process will not use Freelist and BITMAP, because do not look for an idle block and use the lock queue. Transaction entries must be used for data operations in data blocks, that is, the transaction entry.

When creating a segment, we can use the MINTRANS and MAXTRANS parameters to specify its maximum and minimum values. MAXTRANS specifies the maximum number of concurrent transactions on each block in the segment, you can enter a value between 1 and 255. We can compare it to some transaction sockets long in the block header. Each socket is followed by a scalable operator, when a transaction process is inserted into a socket, it is equivalent to finding an operator who can operate the data row in the data block. Through this operator, transaction processes can perform INSERT, UPDATE, DELETE, and other operations on data in blocks. When the maximum value set by MAXTRANS is not exceeded, if transaction entries is not enough, one is automatically allocated on the block, but the number of transaction entries in other blocks is not affected. However, the INSERT operation must first find the idle block before the INSERT operation.

In what order does DBWN write dirty data in db buffer? Oracle adds a new data structure, Buffer Checkpoint Queue, from 8I ). The checkpoint queue is a link queue. This queue is arranged in the order in which the Buffer block is modified for the first time, pointing to the modified Buffer block respectively. When the data in DB_Buffer is modified for the first time, the location of the generated redo log entry RBA will be recorded as the Low RBA of the Buffer, recorded in the Buffer Header ), if the data continues to be modified, the RBA of the latest redo log modified by this block will be recorded in the Buffer header as the High RBA. If the block in DB_Buffer is not modified, the header of the block will not contain information of Low RBA and High RBA. The checkpoint queue modifies the block based on the incremental value of the modified Low RBA. The unmodified block is not added to the checkpoint queue because it does not have Low RBA.

When no checkpoint occurs, DBWR writes the modified block to the data file in ascending order of Low RBA In the checkpoint queue. When a block is written to a data file, it is disconnected from the checkpoint queue. DBWR continues to write the next block. The CKPT process records the minimum Low RBA In the checkpoint queue to the control file every three seconds, that is, updates the CheckPointRBA in the control file. When the instance crashes, recovery starts from the log location pointed to by CheckPointRBA. This is the behavior and definition of "incremental checkpoints. The CKPT process also records the position of the checkpoint to the header of the data file, but only writes it during log switching. Instead of every three seconds. When a checkpoint occurs, DBWN does not keep writing dirty data in the db buffer, it stops writing when the Low RBA value of the Start block written to the Checkpoint queue is greater than the Checkpoint RBA value of the Checkpoint, and then completes the Checkpoint, the CKPT process records information about the checkpoint to the control file. The preceding section describes Oracle free data blocks.

1. Brief description of Oracle materialized view logs
2. Brief Introduction to Oracle client
3. Five-minute Oracle Tuning
4. Oracle primary Index
5. Oracle Materialized View