Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand Oracle rollback and undo". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In Oracle performance optimization, one of the performance metrics is the average transaction rollback rate (Rollback per Transaction), which is used to measure the commit and rollback rates of the database. The formula is:

Round (User rollbacks / (user commit + user rollbacks), 4) X 100%

This indicator should be close to 0. If the indicator is too high, it means that the database is being rolled back too much.

About the contents of the rollback segment storage

Insert operation: the rollback segment only needs to record the rowid that is inserted into the record. If you roll back, you only need to delete the record according to rowid.

Update operation: the rollback segment only needs to record the old value of the updated field (previous mirror image), and the rollback can be completed by overwriting the new value with the old value.

Delete operation: Oracle must record the whole record. On rollback, Oracle restores the deleted data through a reflection operation.

From the above information, it can be concluded that insert produces the least undo information, update is in the middle, and delete produces the most undo. When a large delete operation fails or rollback, it always takes a long time, and a large amount of Redo will be generated, so when large-scale data deletion is carried out, it is recommended to delete batches and commit in batches to reduce the occupation and impact on the rollback segment.

The function of rollback segment: thing rollback, thing recovery, consistent reading

Oracle implements concurrency control through lock mechanism and concurrency data access through multi-version model. Through the multi-version architecture, Oracle achieves the separation of read and write, so that write does not block read and read does not block modification. The multi-version model is implemented through consistent reading in Oracle.

Oracle does not allow reading of uncommitted data and ensures that the read data comes from the same point in time.

Automatic UNDO tablespace management has been introduced since Oracle 9i. The related parameters are: undo_management defines whether the rollback segment used by the database uses automatic management mode, with two values of auto and manual, undo_tablespace defines which undo tablespace is used in automatic management mode, and undo_retention defines the time, in seconds, before the data in the rollback segment is retained before being overwritten when the rollback segment becomes inactive (INACTIVE).

By default, Oracle initializes 10 rollback segments. You can see the automatically created rollback segments from the v$rollname view.

The default undo_retetion parameter does not necessarily save the mirror image within the set time. If you do not want to be overwritten, you need to set the property of the undo tablespace to guarantee. The command is as follows:

Alter tablespace undotbs1 retention gurarantee

Oracle 10g introduces In Memory Undo, where the database allocates space in a shared pool to store undo information, which is closely related to pvrs.

This is the end of "how to understand Oracle rollback and undo". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.