Oracle programming Art Study Notes (18)-REDO and UNDO

 

REDO

Oracle maintains two types of redo log files: online redo log files and archived redo log files.

Online redo logs are used to "correct" data files in the case of power failure (instance termination), while archived redo logs are used to delete data in the case of hard disk failure or misoperation, back up "corrected" data files with data files.

Why redo log files-> http://www.bkjia.com/database/201110/108941.html

 

Each Oracle database has at least two online redo log groups, each of which has at least one member (redo log files ). These online redo log groups are used cyclically.

You can use the following statement to query the redo size before and after an operation and calculate the difference value to obtain the size of the redo log generated by this operation:

Select B. value from v $ statname a, v $ mystat B where a. statistic # = B. statistic # and a. name = 'redo size ';

(For simple DML statements, you can also use AUTOTRACE in SQL * PLUS for measurement)

UNDO

When the data is modified, the database will generate the undo information, so that the transaction or statement fails for some reason, or the ROLLBACK statement is used to request ROLLBACK, the UNDO information can be used to store the data back to the original modification. UNDO is stored in a special segment in the database. This is called an UNDO segment or a rollback segment ).

It should be noted that UNDO cannot physically restore the database to the point where the statement or transaction is executed, but is logically restored. All modifications are logically canceled, however, the data structure and database block may change after rollback. This is required considering that there may be concurrent transactions.

 

UNDO will also be protected by REDO. In other words, the UNDO data will be treated as table data or index data, and some REDO will be generated for UNDO changes, these REDO will be included in the log.

 

How to estimate REDO volume

You need to consider the REDO of table data or index data, plus the REDO generated by modifying the UNDO to estimate the REDO generation.

INSERT generates a small number of UNDO records, but generates a large number of REDO records. DELETE generates a small number of records, but generates the most UNDO records. UPDATE generates a large number of REDO and UNDO records at the same time.

Therefore, we can estimate the following:

· Estimate the size of your "transaction" (how much data you want to modify ).

· Add 10% to the amount of data to be modified ~ The overhead of 20% depends on the number of rows to be modified. The more rows you modify, the smaller the overhead is.

· For UPDATE, double the estimated value.

Doubling the UPDATE value is just a guess. In fact, it depends on how much data you have modified. It is assumed that an X-byte row is to be taken and updated to another X-byte row. If you take a small row (a row with a small amount of data), update it to a large row (a row with a large amount of data ), you do not need to double this value (this is more like an INSERT ). If you take a row and update it to a small row, you do not need to double the value (this is more like a DELETE ). Doubling is just a "worst case ".

In addition, indexes, triggers, and implicit operations (such as ondelete cascade settings on Foreign keys) must be considered.

 

REDO and UNDO on temporary tables

Temporary tables> http://www.bkjia.com/database/201110/109554.html

Temporary tables do not generate redo logs for data blocks, but temporary tables generate undo logs (because rollback is supported, therefore, the temporary table generates some redo logs for the generated undo logs.

 

· INSERT only generates a small amount of data in the temporary table, because the temporary table only creates REDO logs for the UNDO data, and the insert undo log is very small.

· DELETE generates almost the same REDO as normal tables. Because the UNDO of DELETE is large, and the REDO is small.

· UPDATE will generate half of the REDO For normal table UPDATE.

Therefore, you should avoid deleting temporary tables (you can use TRUNCAT, or leave the temporary table empty after COMMIT or when the session ends). The temporary table is mainly used for INSERT) and SELECT to make full use of the temporary table's ability to not generate REDO.

 

 

What COMMIT does

In fact, the data in the database has been modified before COMMIT, so 99.9% of the work has been completed. For example, the following operations have been performed:

· The undo block has been generated in the SGA block cache.

· Modified data blocks have been generated in the SGA block cache.

· Cache redo For the first two items has been generated in the redo log cache area of SGA.

· Depending on the size of the first three items and the time spent on the work, each previous data (or some data) may have been refreshed and output to the disk.

· All required locks have been obtained.

When executing COMMIT, the remaining work is:

· LGWR writes all the remaining cache redo log entries to the disk and generates an SCN (System Change Number or System Commit Nunber) for the transaction. It can regard the SCN as a pendulum, and every time someone commits a COMMIT, 1) to record the SCN to the online redo log file. This step is the real COMMIT. TRANSACTION entries are "deleted" from V $ TRANSACTION, which indicates that the TRANSACTION has been committed.

· The Locks recorded in V $ LOCK will be released.

· If some blocks modified by the transaction are still in the buffer cache, they will access and "Clean up" in a fast mode ". Block cleanout refers to clearing the lock-related information of the Block header. In essence, we are clearing transaction information on the Block.

As you can see, there is very little work to be done to process COMMIT. The most time-consuming operation is the physical disk write operation of LGWR. However, as LGWR has been incrementally refreshing the output redo log buffer content in the background, even if we have a long running transaction that generates a large number of cached redo logs, before committing, many cache redo logs have been refreshed and output to the disk. You only need to wait for the remaining part to be output to the disk. (By default, there are exceptions-> http://www.bkjia.com/database/201110/108947.html)

 

It can be seen that if the transaction is committed too frequently, a large number of log files may be introduced for synchronization wait.

Therefore, it is best to determine the transaction size based on business needs, rather than mistakenly "compressing" transactions to reduce resource usage on the database.

 

What ROLLBACK does

The ROLLBACK time is a function of modifying the data volume, because ROLLBACK must physically cancel our work. Similar to COMMIT, the database has done a lot of work before it reaches ROLLBACK. When performing ROLLBACK, you need to do the following:

· Revoke all modifications made. Read back data from the undo segment, perform the previous operation in reverse order, and mark the undo entry as used. If a row is inserted, delete it. If a row is updated, cancel the update. If a row is deleted, insert it again.

· All locks held by the session will be released.

 

From NowOrNever