Oracle undo Parsing

What is Undo used? Before introducing undo, let's talk about another thing transaction, which is translated into transactions or transactions. We need to apply for many resources in the process of a transaction, and a complicated transaction also needs to be completed in many steps. Therefore, a complex transaction has only two results: success or failure (equivalent to never happened ). A typical column transfer is actually two steps. The first step is to subtract the money from your account, and the second step is to add the money of the transferred account, (subtract and then add. If something goes wrong, the bank will not suffer. Haha !) This is a complete transaction. If the execution is half done, your money is reduced, and the money of the transferred user is not added, the transaction will be rolled back and rolled back to the original state. That is, before transferring funds, you need to record the amount between you and the account to be transferred. This guarantees that once the transaction fails, it will be rolled back to the status before the occurrence of the transaction. To ensure the primitive nature and integrity of a transaction, this concept of undo is introduced. Undo is used to record the data stored in the transaction operation process. If a transaction error occurs, you can fill in the previous data. Undo Segment restore Segment:
From the previous view, we can clearly see that we need to modify a data in the table. Before the modification, we should first put the old image) put it on the undo. Then add new image to the table. If the process fails, we can also take the old image on the undo and put it back in the original position, so that it looks like it has never happened. Www.2cto.com Undo segment is stored in the tablespace. The Undo size is fixed. Since it is fixed, it is limited. If a large number of records are saved, they will be fully occupied, and the data of the new record will overwrite the earliest data. Therefore, a circular disc can be more vividly expressed. Data is written from a single location. When it is fully written, the latest data overwrites the data written at the earliest. What can undo do?
The Transaction rollback Transaction is reversed, and the Transaction recovery Transaction is restored. Transaction reversal has the same effect as transaction recovery. Transaction reversal is performed by people on their own initiative. People regret it in the operation process, which is equivalent to pressing ctrl + z when writing a document. Transaction recovery is automatically completed by the machine. For example, when the transaction is in progress, the short fight suddenly loses power, and the system automatically rolls back after the service is restarted next time.
Read consistency Read and execute. Read consistency is very important for multi-user operations. If you have used the version control tool (git \ cvs \ svn) in multi-person development, the following concepts are easy to understand. Oracle read consistency I know that oracle allows multiple users to operate databases. It may take several minutes to query millions of records. In this process, other users modify the data you query. Make sure that the query results are modified. Www.2cto.com here is a clear concept. When we execute a (Update, modify, or delete) statement at the beginning of a transaction, the commit action (execute the commit or rollback command) must be executed at the end of the transaction)

The preceding flowchart shows how oracle ensures read consistency. When we execute a transaction, oracle will assign an SCN number, which is incremental. The number of the next transaction must be greater than that of the current transaction. When the first transaction is executed, the allocation number is 10023. During the execution of this transaction, the other transaction modifies the data blocks with SCN numbers 10008 and 10021. The SCN Number of the data block to be replaced is 10024, And the replaced data block is saved to the undo. When the first transaction is executed to the modified data block, it finds that the transaction is 10024 to 10023 large. At this time, it will go to the undo segment to find a data block smaller than its own SCN number for reading, therefore, the two SCN numbers are found: 10008 and 10021. This effectively ensures read consistency.
Of course, there will be a special case, that is, the undo segment is too small to put a maximum of 100 data records, and 120 data records can be generated at once, the first 20 data records are overwritten by the last 20 data records. At this time, an error will be reported, which is why data optimization is required. Redo or Undo www.2cto.com what is a RedoRedo record transaction logs, divided into online and archived. For restoration purpose. For example, if a machine loses power, you need to restore the system to the failure point after the machine restarts. For example, if the disk is broken, use the archived redo logs and online redo logs to restore data. What is Undo Redo is to re-implement your operations, while Undo is to cancel your operations. Undo is more like the commonly used ctrl + z. Undo is removed to the previous state. Redo records undo operations.

When we insert a piece of data, the first action will be recorded in the redo log, and the operation will also be recorded to the undo, the action of undo itself will also be recorded as a redo log and inserted as a data, and the index (indexes) will change, the index changes will also make a data record to the redo log. Redo records all the information related to an operation so that the scenario can be completely reproduced.
It should be noted that in the above chart, both undo and redo are written in the memory. Once the power is down, all information will disappear. Only the information written to the disk will not disappear due to power failure. The redo log information is first written to the disk, because it has the most comprehensive information and can completely ensure scenario reproduction. We can use various mechanisms to control redo writes to disks, such as writing once every 3 seconds, or writing once if the redo log file is greater than 1 MB. How to configure and use undo? To use undo on www.2cto.com, you must first create an undo tablespace. We can create multiple undo tablespaces, but only one undo tablespace is in use. View undo configuration information: SQL> show parameter undo NAME TYPE VALUE contains unknown undo_management string AUTO undo_retention integer 900undo_tablespace string UNDOTBS1 Undo Configuration parameter meaning-DNDO_MANAGEMENT undo management mode, the automatic and manual-UNDO_TABLESPACE undo tables currently in use-UNDO_RETENTION specifies how long the data cannot be overwritten. -----------------------------------------
AUTO indicates that undo is in automatic management mode. 900 indicates that undo data cannot be overwritten within 900 seconds. UNDOTBS1 is the currently used undo tablespace. Create an undo tablespace, similar to creating a common tablespace. The command is as follows: SQL> create undo tablespace myundotbs 2 datafile '/ora10/product/oradata/ora10/myundotbs1.dbf' size 10 M; www.2cto.com Tablespace created. view the newly created undo tablespace SQL> select tablespace_name, contents from dba_tablespaces; TABLESPACE_NAME CONTENTS tables ---------------- SYSTEM PERMANENTUNDOTBS1 UNDO // The old undo tablespace SYSAUX PERMANENTTE Mp temporaryusers permanentpaul specified UNDO // the newly created undo tablespace SQL> show parameter undo again view the currently used tablespace NAME TYPE VALUE specified undo_management string AUTO undo_retention integer 900undo_tablespace string UNDOTBS1 tablespace switch undo tablespace: SQL> alter system set undo_tablespace = myundotbs; System altered. SQL> show par Ameter undo view the currently used tablespace name type value currently used undo_management string AUTO undo_retention integer 900undo_tablespace string MYUNDOTBS // The undo tablespace that has been switched; tablespace deleted undo tablespace: SQL> drop tablespace myundotbs; tablespace dropped. after dropping an undo tablespace, it still exists on the disk. We need to use the rm command at the operating system level to delete the file.
Thinking: The table space switching and deletion commands are very simple, but here we need to think about the actual switching scenario. When we execute a transaction, half of the transaction execution has not yet been committed. can we successfully switch the undo tablespace at this time? Theoretically, the undo tablespace is in use and cannot be switched. However, the undo tablespace can be switched during use, but is deleted immediately after the switch. An error is prompted. After the transaction is committed and then deleted, the system still prompts an error. Only when the replaced undo tablespace is switched to the use status and then to the discard status can it be deleted. If you are interested in the above situation, you can verify it. Undo Optimization of www.2cto.com Undo settings depends on our actual production system. How can we set undo to work more rationally for us? Undo tablespace size: When we create an undo tablespace, we specify its size. Once created, this size cannot be changed. Setting too large is a waste. If setting too small, for example, deleting 1 million records, these deleted records must be temporarily stored in the undo tablespace. If the size of undo records cannot be 1 million, then there will be problems.
Undo data storage time: that is, the time corresponding to the undo_retention parameter. The undo data storage time is closely related to the undo size. The longer the storage time, the larger the tablespace is. Just like the relationship between the number of hairdressers and the efficiency of hairdressers. The barber is very efficient. If you solve a customer in one second, there is no need for too many barber masters. Historical Undo tablespace information: how to properly set the size and storage time of the undo tablespace? You need to refer to the historical records

This data is collected every 10 minutes, and the end time minus the start time. How many undo data blocks are used during this time period. How many data blocks are used per second? Max: SQL> select MAX (undoblks/(end_time-begin_time) * 24*3600) from v $ undostat; max (UNDOBLKS/(END_TIME-BEGIN_TIME) * 24*3600 )) average 14.15833333 www.2cto.com: SQL> select sum (undoblks)/sum (end_time-begin_time) * 24*3600) from v $ undostat; SUM (UNDOBLKS) /SUM (END_TIME-BEGIN_TIME) * 24*3600) -------------------------------------------------- 4.122282389