Oracle DML Statement (insert, update, delete) rollback Estimation

Oracle DML Statement (insert, update, delete) rollback Estimation

1. Introduction to Oracle dml SQL rollback Logic

Database transactions consist of one or more DML (insert, update, delete) SQL statements. We know that the Oracle database uses the Undo tablespace to store transaction rollback information during DML operations, the undo information for each DML operation rollback is different, roughly as follows:

The insert operation is simple. You only need to save the record and insert it into the data block and the slot number in the data block. During rollback, you only need to delete the record according to the data block number and slot number.

The update operation needs to save the record location and the original content of the changed field. The original value can be used for rollback.

The delete operation is troublesome. Not only do you need to save the record location, but you also need to save all the content of the original record so that new data can be inserted during rollback.

If the table has an index, the DML operation also needs to save the index-related rollback information in the Undo tablespace.

DML operations mainly involve the following Overhead:
Lock acquisition (CPU overhead)
Locate the record to be changed (discrete Io overhead)
Record rollback information (CPU + IO overhead)
Change History (CPU overhead)
Record redo logs (sequential Io overhead)
Data Block writing (asynchronous discrete Io overhead)

Because the record rollback information in the DML operation takes up a large amount of resources, in order to better estimate the size of the rollback space required by the DML operation, this section describes the estimation methods and verification examples for some common operations.

Ii. How to view the transaction undo Space

How to view the Undo space used by transaction operations, Oracle provides the System View v $ transaction, which stores the main information of the active transactions in the current database. We can use the following SQL to view the space:

Select B. sid, -- session number B. serial #, B. username, B. machine, B. SQL _hash_value,. start_time, -- transaction start time. used_ublk, -- number of Undo blocks used. used_urec, -- the number of Undo records used is the next major estimation index in this article. start_ubafil, -- use the Undo file number. start_ubablk -- start block number of the Undo used from V $ transaction A, V $ session B where. ses_addr = B. saddr and B. SID = ?;

Because I use the test environment by myself, there is no concurrency. to simplify the operation, ignore the live parameters. The simplified SQL statement is as follows:

Select used_urec from V $ transaction;

Through start_ubafil and start_ubablk, we can dump data block rollback analysis, as shown below:
Alter system dump datafile start_ubafil block start_ubablk;
After dump is completed, the detailed information in the data block is analyzed through the log file. The author also confirms the calculation formula using this method, because the content dumped by dump is complicated, it is the specific implementation details of Oracle, so this article does not introduce dump content. If you are interested, you can test it on your own.

Iii. Test preparation

SQL> -- Create Table t1sql> Create Table T1 as select * From dba_objects; Table created SQL> select count (*) from T1; count (*) ---------- 29495 SQL> DESC T1; name type nullable default comments -------------- bytes -------- owner varchar2 (30) y object_name varchar2 (128) y subobject_name varchar2 (30) y object_id number y data_object_id number y object_type varchar2 (18) Y created date y last_ddl_time date y timestamp varchar2 (19) y status varchar2 (7) y temporary varchar2 (1) y generated varchar2 (1) y secondary varchar2 (1) y SQL> -- object_id create index SQL> Create index idx_t1_object_id on T1 (object_id); index created SQL> -- object_name create index SQL> Create index idx_t1_object_name on T1 (object_name ); index created SQL>

Iv. calculation method and test script

The following describes the calculation methods of Undo records for various DML statements (insert, update, delete) in transactions. Each operation introduces the estimation formula and a simple example:

4.1 delete operation
4.1.2. General Deletion

Calculation formula: used_urec = number of table records deleted + number of table index records deleted (one record for each index)

Assume that the table has two indexes and 10 records are deleted.

Used_urec = 10 + 2*10 = 30

SQL> delete from t1 where rownum<=10; 10 rows deletedSQL> select USED_UREC  from v$transaction;  USED_UREC----------        30SQL> commit; Commit complete

4.1.2 deleting a record using the index range Condition

Used_urec = number of deleted table records + number of updated index Blocks

Assume that the table has one common index for a single field, and 10000 records are queried and deleted from the index range. Each index block stores 200 records.

Used_urec = 10000 + 10000/200 = 10050

SQL>  delete from t1 where object_id between 10000 and 20000; 19871 rows deleted SQL> select USED_UREC  from v$transaction;  USED_UREC----------     20242SQL> commit; Commit complete

4.2 update operations
4.2.1 General updates

Used_urec = number of updated table records + number of updated index records * 2 (there are two records for each index change. One is to record the original index pointer, and the other is to record the new index pointer)

Assume that the table has two indexes and two fields of 10 records are updated. One is a common field and the other is an index field.

Used_urec = 10 + 2*10 = 30

SQL> update  t1 set  object_name='test',owner='MK' where rownum<=10; 10 rows updated SQL> select USED_UREC  from v$transaction;  USED_UREC----------        30SQL> commit; Commit complete

Note: In a statement, an undo record is generated for a common field regardless of whether the field changes before or after the update. However, only when the index field changes, the Undo record is generated. For the following test, object_name is updated but does not change, so the index record will not change.

SQL> update  t1 set  object_name=object_name,owner='MK' where rownum<=10; 10 rows updated SQL> select USED_UREC  from v$transaction;  USED_UREC----------        10SQL> commit; Commit complete

4.2.2 update the index field using the index range Condition

Used_urec = number of updated table records + number of updated index blocks * 2

Assume that the table has one common index for a single field. query 10000 records in the index range and update the corresponding index field. Each index block stores 200 records.

Used_urec = 10000 + 2 * (10000/200) = 10100

SQL> update  t1 set  object_id=object_id+1 where object_id>10000; 19584 rows updated SQL> select USED_UREC  from v$transaction;  USED_UREC----------     19862 SQL> commit; Commit complete

Note: it can be seen that there are very few accesses and updates to this index field through the index range, and the requirements are very special. Assuming that the statement just made a small change does not meet the requirements, the following adds a rownum condition, the actual number of records updated is the same, but the number of Undo records used can only be calculated based on general updates.

SQL> update  t1 set  object_id=object_id+1 where object_id>10000 and rownum<1000000; 19584 rows updated SQL> select USED_UREC  from v$transaction;  USED_UREC----------     58752 SQL> commit; Commit complete

4.3 insert operation
4.3.1. insert (insert into T1 values ...)
Used_urec = number of new records + number of table indexes * Number of new records
Assume that the table has two indexes and three new records are added.
Used_urec = 3 + 3*2 = 9

SQL> insert into t1(owner,object_name,object_id) values('MK','test1',123456); 1 row inserted SQL> insert into t1(owner,object_name,object_id) values('MK','test2',1234567); 1 row inserted SQL> insert into t1(owner,object_name,object_id) values('MK','test3',12345678); 1 row inserted SQL> select USED_UREC  from v$transaction;  USED_UREC----------         9 SQL> commit; Commit complete

4.3.2. Batch insert (insert into T1 Select ...)
This is very difficult to calculate accurately, because new records use idle data blocks. Only when there is a change in the data block, the rollback records corresponding to the data block must be saved, the index rollback record is also saved, so
Used_urec ≈ new records change the number of data blocks in the table + Σ each change table data block corresponds to the number of changed index Blocks
Assume that the table has two indexes and 1000 new records are added. Each data block can save about 600 records, and the first data block can save 600 records, at the same time, 30 index blocks were changed for 1st indexes, 40 data blocks for 2nd indexes, and 2nd new data blocks saved 400 records, at the same time, 20 index blocks were changed for 1st indexes and 60 data blocks for 2nd indexes.
Used_urec ≈ 2 + (30 + 40 + 20 + 60) = 152

SQL> insert into t1 select * from dba_objects where rownum<=10000; 10000 rows inserted SQL> select USED_UREC  from v$transaction;  USED_UREC----------      7837 SQL> commit; Commit complete

V. Summary
The above script is tested on oracle9.2, and Oracle's undo processing is very complicated. Here we will introduce some common DML generation undo estimation methods. From the estimation formula, we can see that, the index has a great impact on DML operations. When a table has an index, the Undo overhead of the DML operation is very large because the database must ensure the feasibility of transaction rollback, there are a lot of additional things to do with indexes, especially when updating index fields and performing batch insert operations. Batch Data Import is performed on tables with or without indexes. The performance may be several times better. By estimating the number of Undo records required for Oracle DML operations, you can also estimate the time required for a DML operation. At work, a DML operation may take a very long time. If the V $ session_longops view does not have any tracing information, you can use the used_urec information of V $ transaction to estimate the SQL progress. During DML operations, used_urec is always increasing. When the transaction starts to roll back, used_urec starts to drop until it is equal to 0, the rollback is complete, therefore, we can also judge the rollback progress based on the changes in this field.