Oracle DML語句(insert,update,delete) 復原開銷估算

Oracle DML語句(insert,update,delete) 復原開銷估算

一、Oracle DML SQL復原邏輯簡介

      資料庫事務由1個或多個DML(insert,update,delete) SQL組成，我們知道Oracle資料庫在進行DML操作需要使用UNDO資料表空間來儲存交易回復的資訊，對於每種DML操作復原的UNDO資訊都不一樣，大致如下：

insert操作很簡單，只要儲存記錄插入到資料區塊及資料區塊內的槽號，復原時只要根據資料區塊號及槽號做刪除就可以了。

update操作需要儲存記錄位置，還需要儲存變更的欄位原內容，復原時採用原值即可。

delete操作麻煩一些，不僅要儲存記錄位置，還需要將原有記錄的內容全部儲存下來，復原時才能組成新的資料插入進去。     

如果表上有索引，則DML操作同時需要在UNDO資料表空間中儲存索引相關的復原資訊。

DML操作主要有以下幾方面的開銷構成：
擷取鎖(CPU開銷)
定位要變更的記錄(離散IO開銷)
記錄復原資訊(CPU+IO開銷)
變更記錄(CPU開銷)
記錄重做日誌(順序IO開銷)
資料區塊寫入(非同步離散IO開銷)

因為DML操作過程中記錄復原資訊佔用了非常大的一塊資源，為了更好的估算DML操作需要復原空間的大小，本文介紹了一些常用操作的估算方法及驗證樣本。

二、如何查看事務UNDO使用空間

如何查看事務操作使用的UNDO空間，Oracle提供了系統檢視表V$TRANSACTION，裡面儲存了當前資料庫活動事務的主要資訊，我們可以用如下SQL來查看：

select b.sid,--會話編號       b.SERIAL#,       b.USERNAME,       b.MACHINE,       b.sql_hash_value,       a.START_TIME,--事務啟動時間       a.USED_UBLK, --使用的UNDO塊數       a.USED_UREC, --使用的UNDO記錄條數，是本文接下來的主要估算指標       a.START_UBAFIL, --使用的UNDO檔案號       a.START_UBABLK --使用的UNDO起始塊號  from v$transaction a, v$session b where a.ses_addr = b.saddr and b.sid=?;

由於測試環境就我一個人使用，不存在並發，為簡化操作，忽略會活參數，簡化的SQL如下：

  
select USED_UREC  from v$transaction;

通過START_UBAFIL及START_UBABLK我們可以dump復原資料區塊的分析，如下所示：
alter system dump datafile START_UBAFIL block START_UBABLK;
dump好後再通過記錄檔分析資料塊內的詳細資料，筆者也是通過這樣的方法來確認計算公式，因為dump出來的內容比較複雜，是Oracle的具體實現細節，所以本文不介紹dump內容，有興趣的同學可以自己測試。

三、測試準備

SQL> --建立表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 -------------- ------------- -------- ------- -------- 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建立索引 SQL> create index idx_t1_object_id on t1(object_id); Index created SQL> --object_name建立索引SQL> create index idx_t1_object_name on t1(object_name); Index created SQL> 

四、計算方法及測試指令碼

下面介紹事務中各種DML語句(insert,update,delete)使用UNDO記錄的計算方法，每種操作會介紹估算公式並簡單樣本解釋：

4.1、delete 操作
4.1.2、一般刪除

計算公式：USED_UREC=刪除表記錄數+刪除表索引記錄數(每個索引每行記錄算一條記錄)

假設表有2個索引，刪除10條記錄

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、通過索引範圍條件刪除記錄

USED_UREC=刪除表記錄數+更新索引塊數

假設表有1個單欄位普通索引，通過索引範圍查詢10000條記錄並刪除，每個索引塊大塊儲存200條記錄

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 操作
4.2.1、一般更新

USED_UREC=更新表記錄數+更新索引記錄變更數*2(每行索引變更有2個記錄，一個是記錄原索引指標，另外是記錄新索引指標)

假設表有2個索引，更新10條記錄的2個欄位，其中要更新1個是普通欄位，1個是索引欄位

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

 註：在語句中，普通欄位不管更新前與更新後是否發生變化，都會產生UNDO記錄，但是索引欄位只有發生了變化才會產生UNDO記錄，如下測試，object_name做了更新操作，但是沒有發生變化，所以索引記錄不會發生變更。

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、通過索引範圍條件更新該索引欄位

USED_UREC=更新表記錄數+更新索引塊數*2

假設表有1個單欄位普通索引，通過索引範圍查詢10000條記錄並更新對應的索引欄位，每個索引塊大塊儲存200條記錄

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

註：可以看出這種通過索引範圍訪問並更新該索引欄位的情況非常少，要求也非常特殊。假設剛才的語句做一點小變化都不滿足要求，如下加了一個rownum條件，實際更新的記錄數都是一樣的，但是使用的UNDO記錄數只能按一般更新計算。

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 操作
4.3.1、單條insert (insert into t1 values ...)
USED_UREC=新增記錄數+表索引個數*新增記錄數
假設表有2個索引，新增3條記錄
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、批量insert(insert into t1 select ...)
這個非常難準確計算，因為新增記錄會利用以前閒置資料區塊，只有資料區塊有記錄變化都需要儲存資料區塊對應的復原記錄，同時也儲存索引的復原記錄，所以
USED_UREC≈新增記錄變更表資料區塊數+∑每個變更表資料區塊對應變更的索引塊數
假設表有2個索引，新增1000條記錄，每個資料區塊大約可儲存600條記錄，新增第一個資料區塊儲存了600條記錄，同時變更了第1個索引30個索引塊，第2個索引40個資料區塊，新增第2個資料區塊儲存了400條記錄，同時變更了第1個索引20個索引塊，第2個索引60個資料區塊
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

五、總結
以上指令碼是在Oracle9.2上測試，Oracle對UNDO的處理非常複雜，這裡介紹只是常用的一些DML產生UNDO估算方法，從估算公式可以看出，索引對DML操作的影響非常大，當一個表有索引比沒索引時做DML操作花費的UNDO開銷非常具大， 因為資料庫要保證交易回復的可行性，需要對索引做許多額外的事情，更新索引欄位及批量INSERT操作尤其明顯。在有索引和沒索引的表上做批量資料匯入，效能有可能相關好幾倍。通過估算Oracle的DML操作需要的UNDO記錄數，也可以間接估算一個DML還需要的時間。工作中有時會遇到一個DML操作時間非常長，如果v$session_longops視圖也沒有可以跟蹤的資訊，這時就可以通過v$transaction的USED_UREC資訊估算SQL的進度。DML在操作時，USED_UREC是一直在增加的，當事務開始復原時USED_UREC會開始下降，直到等於0，則復原完成，因此我們也可以根據這個欄位的變化判斷復原進度。