Oracle Undo Principle

Starting from 9i, Oracle also provides a new Undo Data Management method, that is, using the Automatic Oracle Undo tablespace Management, usually abbreviated as AUM ).

(1) check whether the empno = 7788 record exists in the Buffer Cache. If it does not exist, it is read to the Buffer Cahce;
(2) Allocate transaction slots in the transaction table of the corresponding rollback segment in the rollback tablespace. This operation requires Redo information to be recorded;
(3) read from the rollback segment or create a pre-image with sal = 3000 in the Buffer Cache, which generates Redo information and records it in the Redo Log Buffer;
(4) Modify Sal = 4000. This is a data update change and must be recorded in the Redo Log Buffer;
(5) When the user commits the transaction, the commit information is recorded in the Redo Log Buffer, and the transaction is marked as INACTIVE in the rollback segment ).

If a user rolls back a transaction, Oracle needs to read the previous image data from the Rollback space, modify the data buffer, and complete the Rollback. This process also generates a Redo, therefore, rollback is expensive.

In performance optimization, a performance indicator is called the average Transaction Rollback rate (Rollback per Transaction), which is used to measure the database commit and Rollback efficiency.

For the insert operation, the rollback segment only needs to record the rowid of the inserted record. If it is rolled back, you only need to delete the record according to the rowid. For the update operation, the rollback segment only needs to record the old value of the updated field (the former image), and the old value can be used (the former image) during rollback ), rollback can be completed by overwriting the new value through the old value. For delete operations, Oracle must record the entire row of data. During rollback, Oracle uses a reverse operation to restore the deleted data.

For data operations with the same data volume, insert usually produces the least Undo, the Undo generated by update is centered, and the delete operation produces the most Undo. This is what we often see. When a large Delete operation fails or is rolled back, it will always take a long time and there will be a lot of Redo generation.

Rollback segments are allocated in the Undo tablespace. The data in the Buffer Cache memory is managed in the same way as the user data and is written to the data file in the Undo tablespace according to the same rules. The storage space in the Undo tablespace is also allocated and used according to the Segment. Rollback segments not only roll back transactions, but also participate in transaction recovery and provide read consistency.

 

Concurrency Control and consistent read
On the one hand, Oracle implements database concurrency control through the locking mechanism, and on the other hand, it accesses concurrent data through the Multi-versioning Model.

With a multi-version architecture, Oracle implements read and write separation, so that the read and write operations are not blocked. This is an important feature that distinguishes Oracle databases from other databases. Multi-version models are implemented through consistent reading in Oracle Data. Consistent reading is also one of the main functions of table space rollback.

Previous and present rollback segments
Before Oracle 9i, after the tablespace is created, Oracle then creates a rollback segment for the database to use,
You can use the following command to specify a specific rollback segment:
Set transaction use rollback segment <rollback_segment_name>;

In 11g, the naming rules for the Undo segment have been further changed. Now, the creation timestamp of the rollback segment is included in the rollback segment name.
SYS @ orcl> select segment_name, tablespace_name, status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
-----------------------------------------------------------------------
SYSTEM ONLINE
_ SYSSMU10_4131489474 $ UNDOTBS1 ONLINE
_ SYSSMU9_1735643689 $ UNDOTBS1 ONLINE
_ SYSSMU8_3901294357 $ UNDOTBS1 ONLINE
_ SYSSMU7_3517345427 $ UNDOTBS1 ONLINE
_ SYSSMU6_2897970769 $ UNDOTBS1 ONLINE
_ SYSSMU5_538557934 $ UNDOTBS1 ONLINE
_ SYSSMU4_1003442803 $ UNDOTBS1 ONLINE
_ SYSSMU3_1204390606 $ UNDOTBS1 ONLINE
_ SYSSMU2_967517682 $ UNDOTBS1 ONLINE
_ Syssmustm592353410 $ UNDOTBS1 ONLINE

From Oracle 9i, Oracle introduces automatically managed Undo tablespace. If you choose to use automatic Undo tablespace management, you can no longer create or delete rollback segments, it also does not need to write rollback segments for transactions, which are automatically performed by Oracle.

SYS @ orcl> show parameter undo
NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string undotbs1

In the automatically managed Undo tablespace, the number of rollback segments is automatically allocated or recycled by Oracle based on the database's busy schedule. 10 rollback segments are initialized by default during database creation.

SYS @ orcl> select * from v $ rollname;

USN NAME
----------------------------------------
0 SYSTEM
1 _ syssmustm592353410 $
2 _ SYSSMU2_967517682 $
3 _ SYSSMU3_1204390606 $
4 _ SYSSMU4_1003442803 $
5 _ SYSSMU5_538557934 $
6 _ SYSSMU6_2897970769 $
7 _ SYSSMU7_3517345427 $
8 _ SYSSMU8_3901294357 $
9 _ SYSSMU9_1735643689 $
10 _ SYSSMU10_4131489474 $

When the system is busy, you can view the dynamic creation and release of rollback segments in the alarm log file of the database.

Created Undo Segment _ SYSSMU11 $

SMON offlining US = 11

# The rollback segments of the original Undo tablespace are gradually offline, and the Undo segments of the new tablespace are created sequentially.
SQL> crreate undo tablespace undotbs2 datafile size 500 M
SQL> alter system set undo_tablespace = 'undotbs2 ';

 

Oracle 10 Gb UNDO_RETENTION management enhancement
In AUM mode, UNDO_RETENTION is a NO Guaranteed restriction by default.

Starting from Oracle 10 Gb, if undo_retention is set to 0, Oracle Enables automatic adjustment to meet the maximum query running requirements.
The introduction of this feature is accompanied by several new implicit initialization parameters:

SYS @ orcl> @ GetHidPar
Enter value for par: undo_autotune
NAME VALUE PDESC
-----------------------------------------------------------------------------------------------
_ Undo_autotune TRUE enable auto tuning of undo_retention

SYS @ orcl> @ GetHidPar
Enter value for par: collect_undo_stats
NAME VALUE PDESC
-----------------------------------------------------------------------------------------------
_ Collect_undo_stats TRUE Collect Statistics v $ undostat

Alter tablespace undotbs1 retention guarantee | noguarantee;

SYS @ orcl> select tablespace_name, contents, retention from dba_tablespaces;
TABLESPACE_NAME CONTENTS RETENTION
--------------------------------------------------
SYSTEM PERMANENT NOT APPLY
SYSAUX PERMANENT NOT APPLY
UNDOTBS1 UNDO NOGUARANTEE
TEMP TEMPORARY NOT APPLY
USERS PERMANENT NOT APPLY

When the data file autoextend off, a ORA-30036 error occurs when guarantee is set up when there is not enough space, and no error occurs when noguarantee, which is the difference between GUARANTEE and NOGUARANTEE.

1 2 3 4 5 6 Next Page