Nine Key Points of Oracle performance adjustment: Five rollback (UNDO) segment optimization

1. Concepts
Transaction uses the extent in the rollback segment in a round-robin manner. When the current extent is full, it is moved to the next extent. Multiple transactions may write data to the same extent at the same time, but only one transaction can be saved in a rollback segment block.

Oracle saves a transaction table in each rollback segment header, including the transaction information contained in each rollback segment. The rollback segment header activity controls writing modified data to the rollbak segment. Rollback segment header is a database block that is frequently modified. Therefore, it should be left in the buffer cache for a long time. To avoid performance degradation caused by competition in the transaction table, there should be multiple rollback segment or the rollback segment automatically managed by Oracle server should be used whenever possible.

2. Diagnosis of rollback segment header Competition
If the rollback segment is manually managed, the following measures diagnose the competition of the rollback segment header:
Select class, count from V $ waitstat where class like '% undo % ';
Select sum (value) sum from V $ sysstat where name in ('db block gets', 'consistent gets ');
The ratio of the number of waits (count) to the total number of requests (SUM) cannot exceed 1%.
Or
Select sum (waits) * 100/sum (gets) "ratio", sum (waits) "waits", sum (gets) "gets" from V $ rollstat;
The ratio of waits summary to gets summary should be less than 1%. If it exceeds 1%, more rollback segment should be created.

If the value of the following field is greater than 0, it indicates competition on the rollback segment header:
Waits in A, V $ rollstat
The undo header line in B and V $ waitstat
C, V $ system_event In the Undo segment TX slot event

3. Consume less rollback segment
1) If you want to delete all the data in the table, try to use trauncate instead of Delete.
2) allow users to submit regular transactions in the application, and avoid long transactions as much as possible.
3) • Import
-Set commit = y
-Size the set of rows with buffer
• Export: Set consistent = N
• SQL * Loader: Set the commit intervals with rows

4. possible problems with small rollback segments
A. failed due to lack of rollback Space
B. The "snapshot too old" problem caused by the following reasons:
The transaction list in the block is refreshed, And the SCN in the block is updated to the SCN of the starting transaction in the Interested Transaction list (ITL;
The transaction slot in the rollback segment header is reused;
Rollback data has been overwritten;

5. 9i automatic rollback Management
Undo_managment specifies the rollback space management method: Auto: automatic management; Manual: manual management of rollback segments.
Undo_retention specifies the retention period of data rollback;
Undo_tablespace specifies the used rollback tablespace;

The tablespace automatically managed by Oracle can be created in common databases or separately. The rollback tablespace can be switched to each other, but only one of the rollback tablespaces is active at a certain time. You can delete tablespaces when they are not active. If you query committed transactions in the deleted tablespaces, Oracle returns an error.

Formula for estimating the size of the Undo tablespace:
Undo Space = (undo_retention * (UNDO blocks per second * db_block_size) + db_block_size;

You can use the following SQL statements to set undo_retention and undo tablespace:
Select (RD * (UPS * overhead) + overhead) "bytes" from (select value RD from V $ parameter where name = 'undo _ retention '), (select (sum (undoblks)/sum (end_time-begin_time) * 10800) ups from V $ undostat ), (select value overhead from V $ parameter where name = 'db _ block_size ');

Where:
RD: the time set by undo_retention;
UPS: Undo blocks per second;
Overhead: rollback segment header;