oracle undo analysis

What is Undo used for?

Before introducing UNDO, let's talk about another thing. We need to apply for many resources in the process of conducting a transaction, and a complex transaction also requires many steps to complete. Then a complex transaction has only two results, either success or failure (which is equivalent to never happening).

A very typical example, bank transfer, actually requires two steps. The first step is to subtract the money in your account, and the second step is to add the money to the transferred account. This is a complete transaction.

If it is executed halfway and your money is reduced but the money from the transferred account is not added, the transaction will be rolled back at this time and rolled back to the original state. That is to say, before transferring money, you need to record the amount on you and the transferred account. This ensures that once a transaction fails, it will be rolled back to the state before the transaction occurred.

In order to ensure the originality and integrity of a transaction, the concept of undo is introduced. Undo is used to record and save data during transaction operations. If an error occurs in the transaction, it can be filled with previous data.

Undo Segment Undo Segment:

From the view above, we can clearly see that we want to delete a The data is modified. Before modifying, put the old image on undo. Then put new image in the table.

If the process fails, we can also take back the old image on undo and put it in its original position, so that it looks like it never happened.

Undo segment is saved in the table space. The size of Undo is fixed, and since it is fixed, it is limited. If there are too many records saved, it will be full and the new recorded data will overwrite the oldest data.

So using a circular disk can express it more vividly. Data is written starting from a position. When a circle is filled, the latest data will overwrite the earliest written data.

What can you do with undo?​ ​ ​

Transaction rollback transaction reversal, Transaction recovery transaction recovery. The effect of transaction reversal and transaction recovery is the same. Transaction reversal is something people take the initiative to do. People regret it during the operation, which is equivalent to me pressing ctrl z when writing a document. Transaction recovery is automatically completed by the machine. For example, if Jianpin suddenly loses power during the transaction, the system will automatically roll 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 version control tools (git\cvs\svn) in multi-person development, you will easily understand the following concepts.

Oracle Read Consistency Concept                                                                             , this process may take several minutes. During this process, other users made modifications to the data you queried. Here you need to ensure that the results of your query are before they are modified.

Here is a clear concept. The beginning of a transaction is when we execute an (update, modify, delete) statement; at the end of the transaction, a commit action must be executed (execute commit or rollback command)

Let’s understand how Oracle ensures read consistency from the flow chart above.

When we execute a transaction, Oracle will assign an SCN number, which is increasing. The next transaction number must be greater than the current transaction number.

In the above figure, the number assigned to the first transaction executed is 10023. During the execution of this transaction, another transaction modified the data blocks with SCN numbers 10008 and 10021. The SCN number of the data block used for replacement is 10024, and the replaced data block will be saved to undo.

When the first transaction executes the modified data block, it is found that 10024 is larger than 10023. At this time, it will go to the undo segment to find a data block smaller than its own SCN number to read, so it finds it. The SCN numbers are 10008 and 10021. This effectively ensures read consistency.

Of course, there will be a special situation, that is, the undo segment is too small, and a maximum of 100 pieces of data can be placed. But 120 pieces of data come in all at once, so the first 20 pieces of data written are replaced by the last 20 pieces of data written. cover. An error will be reported at this time, which is why the data needs to be tuned.

Redo or Undo d. For the purpose of recovery.

For example, if the machine loses power, online redo logs are needed to restore the system to the point of failure after restarting.

For example, if the disk is damaged, you need to use archived redo logs and online redo logs areas to recover data.

What is Undo

Redo is to re-implement your operation, while Undo, on the contrary, is to undo the operation you did. Undo is more like the commonly used ctrl z, which undoes the previous step. Redo will also record undo operations.

When we insert a piece of data, first the action will be recorded in the redo log, the operation will also be recorded in undo, and the action of undo itself will also be When a piece of data is recorded in the redo log, when a piece of data is inserted, the indexes will change, and the changes in the indexes will also cause a piece of data to be recorded in the redo log. Redo records all relevant information of an operation, so that the scene 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 turned off, all information will disappear. Only the information written to the disk will not be lost due to power outage.

The redo log information is written to the disk first, because it has the most comprehensive information and can completely guarantee the scene reproduction. We can control redo writing to disk through various mechanisms, such as writing every 3 seconds, or writing redo log files larger than 1MB.

How to configure and use undo ? We can create multiple undo table spaces, but only one undo table space can be in use.

View undo configuration information:

SQL> show parameter undo

NAME                           TYPE                VALUE
------------------------------------ ----------------------------------------------------
undo_management                 string            AUTO   
undo_retention                   integer           900
undo_tablespace                  string           UNDOTBS1

Undo configuration parameter meaning

-DNDO_MANAGEMENT The management mode of undo is divided into automatic and manual.-UNDO_TABLESPACE is currently being used The undo table used

-UNDO_RETENTION specifies how long the data cannot be overwritten.

AUTO means undo is in automatic management mode.

900 means that the data on undo cannot be overwritten within 900 seconds.

UNDOTBS1 is the undo table space currently in use.

Creating an undo table space is similar to creating a general table space. The command is as follows:

SQL> create undo tablespace myundotbs
  2  datafile '/ora10/product/oradata/ora10/myundotbs1.dbf' size 10M;

Tablespace created.

View the newly created undo table space

SQL>  select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME                    CONTENTS
------------------------------------------------------------ ------------------
SYSTEM                                 PERMANENT
UNDOTBS1                               UNDO   　　//老的undo表空间
SYSAUX                                 PERMANENT
TEMP                                    TEMPORARY
USERS                                  PERMANENT
PAUL                                   PERMANENT
MYUNDOTBS                              UNDO   // 新创建的undo表空间

SQL> show parameter undo      再次查看当前使用的表空间

NAME                           TYPE          VALUE
------------------------------------ ----------------------------------------------------
undo_management                 string            AUTO   
undo_retention                   integer             900
undo_tablespace                  string           UNDOTBS1

Switch undo table space:

SQL> alter system set undo_tablespace=myundotbs;

System altered.
SQL> show parameter undo   再次查看当前使用的表空间

NAME                           TYPE               VALUE
------------------------------------ ----------------------------------------------------
undo_management                 string            AUTO   
undo_retention                   integer          900
undo_tablespace                  string           MYUNDOTBS    //已经切换的了undo表空间

Delete undo table space:

SQL> drop tablespace myundotbs;
Tablespace dropped.

After dropping an undo table space, it still exists on the disk. We need to use the rm command to delete the file through the operating system level.

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 table space at this time?

Theoretically, the undo table space is in use and switching is not allowed. But in fact, the undo table space can be switched during use, but if it is deleted immediately after switching, the system will prompt an error. After committing the transaction and then deleting it, the system still prompts an error. Here, the replaced undo tablespace can only be deleted if it is switched to the used state and then to the abandoned state.

Undo tuning

                                                                                                                                                                                                                                                                                          . How can we set up undo to work more rationally for us?

The size of the Undo table space:

When we create an undo table space, we specify its size. Once created, this size cannot be changed. It is a waste to set it too large. If it is set too small, for example, if you delete 1 million records, these deleted records will be temporarily stored in the undo table space. If the undo size cannot store 1 million records, problems will occur. .

Undo data storage time:

也就是undo_retention 参数所对应的时间，undo上有数据存放时间与undo大小的密切关系。存放时间越长，需要的表空间越大。就像理发师的数量与理发师的效率的关系一样。理发师效率很高，一秒钟解决一个客户，那么就不需要太多的理发师傅。

Undo表空间的历史信息：

如何合理设置undo表空间的大小和存放时间呢？那么就需要参考历史记录

这个数据每隔10分钟采集一次，结束时间减去开始时间，在这段时间内使用了多少个undo数据块。

计算每秒钟使用数据块的多少？

求最大值：

SQL> select max(undoblks / ((end_time-begin_time)*24*3600)) from v$undostat;

MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))
---------------------------------------------
                                   14.15833333

求平均值：
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

The above is the detailed content of oracle undo analysis. For more information, please follow other related articles on the PHP Chinese website!