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來查看：

1. select b.sid,--會話編號   
2.        b.SERIAL#,  
3.        b.USERNAME,  
4.        b.MACHINE,  
5.        b.sql_hash_value,  
6.        a.START_TIME,--事務啟動時間   
7.        a.USED_UBLK, --使用的UNDO塊數   
8.        a.USED_UREC, --使用的UNDO記錄條數，是本文接下來的主要估算指標   
9.        a.START_UBAFIL, --使用的UNDO檔案號   
10.        a.START_UBABLK --使用的UNDO起始塊號   
11.   from v$transaction a, v$session b  
12.  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內容，有興趣的同學可以自己測試。

三、測試準備

1. SQL> --建立表t1   
2. SQL> create table t1 as select * from dba_objects;  
3.    
4. Table created  
5.    
6. SQL> select count(*) from t1;  
7.    
8.   COUNT(*)  
9. ----------   
10.      29495  
11.    
12. SQL> desc t1;  
13. Name           Type          Nullable Default Comments   
14. -------------- ------------- -------- ------- --------    
15. OWNER          VARCHAR2(30)  Y                           
16. OBJECT_NAME    VARCHAR2(128) Y                           
17. SUBOBJECT_NAME VARCHAR2(30)  Y                           
18. OBJECT_ID      NUMBER        Y                           
19. DATA_OBJECT_ID NUMBER        Y                           
20. OBJECT_TYPE    VARCHAR2(18)  Y                           
21. CREATED        DATE          Y                           
22. LAST_DDL_TIME  DATE          Y                           
23. TIMESTAMP      VARCHAR2(19)  Y                           
24. STATUS         VARCHAR2(7)   Y                           
25. TEMPORARY      VARCHAR2(1)   Y                           
26. GENERATED      VARCHAR2(1)   Y                           
27. SECONDARY      VARCHAR2(1)   Y                           
28.    
29. SQL> --object_id建立索引    
30. SQL> create index idx_t1_object_id on t1(object_id);  
31.    
32. Index created  
33.    
34. SQL> --object_name建立索引   
35. SQL> create index idx_t1_object_name on t1(object_name);  
36.    
37. Index created  
38.    
39. SQL>   

1 2 下一頁