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
  • 下一页

相关内容