Oracle Rollback Segment Management (top)

Oracle Rollback segment Management is always a difficult problem in Oracle database management, this paper introduces the concept, usage, planning and problem solving of Oracle rollback segment through an example. Rollback Segment Overview The rollback segment is used to hold values prior to data modification, including the location and value before data modification. The head of the rollback segment contains information about the rollback segment transaction that is in use. A transaction can only use a rollback segment to hold its rollback information, and a rollback segment may hold multiple transaction rollback information. Rollback segment ACTION Transaction rollback: When the transaction modifies the data in the table, the value before the data modification (that is, the front image) is stored in the rollback segment, and when the user rolls back the transaction (ROLLBACK), Oracle will use the data before the rollback segment to restore the modified data to its original value. Transaction recovery: When the transaction is being processed, the routine fails and the rollback segment information is saved in the Redo log file, and Oracle will use the rollback to recover the uncommitted data the next time the database is opened. Read consistency: When a session is modifying data, other sessions will not see the uncommitted changes to the session. Also, when a statement is executing, the statement does not see uncommitted modifications (statement-level read consistency) that have been executed since the statement started. When Oracle executes a SELECT statement, Oracle follows the current system change NUMBER-SCN to ensure that any uncommitted changes prior to the current SCN are not processed by the statement. As you can imagine: When a long-running query is executing, if another session changes a block of data to be queried by the query, Oracle constructs a read-consistent view with the data-fore image of the rollback segment. Read consistency at transaction level Oracle generally provides read consistency at the SQL statement level (SQL STATEMENT levels), and you can use the following statement to implement transactional-level read consistency. Set TRANSACTION read only; or: Set tannsaction SERIALIZABLE; All two statements will provide reading consistency after the transaction starts. It should be noted that using the second statement will have an impact on the concurrency and performance of the database. Type of rollback segment system rollback segment: When the database is created, a system rollback segment is automatically created, which is used only to hold the front image of objects in the system tablespace. Non-system rollback segment: A database that has more than one table space should have at least one non-system rollback segment for storing data-front images of objects in a system-table space. The non-system rollback segment is divided into a private rollback segment and a public rollback segment, and the private rollback segment should be listed in the rollback segments parameter of the parameter file so that the routine is automatically online when it is started. Public rollback segments typically appear in OPS (Oracle parallel server), will automatically be online when the routine starts. defered rollback segment: The rollback segment is automatically created by the system when the tablespace is offline (OFFLINE) and automatically deleted by the system when the tablespace is online again, which is used to store rollback information generated when the tablespace is offline. Rollback segment Use allocation rollback segment: When the transaction begins, Oracle assigns a rollback segment to the transaction and assigns the rollback segment with the least transaction to the transaction. A transaction can request a specified rollback segment with the following statement: Set transtraction use ROLLBACK SEGMENT rollback_segment transaction uses the area of the rollback segment (extents) in a sequential, circular manner, and moves to the next area when the current area is full. Several transactions can be written in the same area of the rollback segment, but the block of each rollback segment can only contain information for one transaction. For example (two transactions use the same rollback segment, and the rollback segment has four extents): 1. Transactions are in progress, they are using the third zone of the rollback segment, 2, when two transactions generate more rollback information, they will continue to use the third zone, 3, when the third is full, the transaction will be written to the fourth area, When a transaction begins to write to a new zone, called a flip (WRAP), 4, when the fourth area is full, if the first is idle or inactive (with all transactions in the zone complete without active transactions), the transaction will then use the first zone. Expansion of the rollback segment (EXTEND) when all the blocks of the current rollback segment are exhausted and the transaction requires more rollback space, the rollback segment's pointer moves to the next area. When the last area is run out, the pointer moves to the front of the first area. The rollback segment pointer moves to the next area if the next zone has no active transactions, and the pointer cannot cross the zone. When the next zone is in use, the transaction assigns a new zone to the rollback segment, which is called the extension of the rollback segment. The rollback segment extends to the number of the rollback segment area until the parameter maxextents of the rollback segment reaches the value. The recycle and optimal parameter optimal parameters of the rollback segment indicate where the rollback segment is shrunk to when it is idle, indicating that the optimal parameter of the rollback segment can reduce the waste of the rollback segment space.