Oracle DML语句回滚开销估算

日期: 2011-09-26 作者:MKing 来源:TechTarget中国 英文

  一、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> –创建表t1
SQL> 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条记录

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

  4.1.2、通过索引范围条件删除记录

  USED_UREC=删除表记录数+更新索引块数

  假设表有1个单字段普通索引,通过索引范围查询10000条记录并删除,每个索引块大块保存200条记录

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

  4.2、update 操作

  4.2.1、一般更新

  USED_UREC=更新表记录数+更新索引记录变更数*2(每行索引变更有2个记录,一个是记录原索引指针,另外是记录新索引指针)

  假设表有2个索引,更新10条记录的2个字段,其中要更新1个是普通字段,1个是索引字段

SQL> update  t1 set  object_name=’test’,owner=’MK’ where rownum<=10;
 
10 rows updated
 
SQL> select USED_UREC  from v$transaction;
 
 USED_UREC
———-
        30
SQL> 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
———-
        10
SQL> commit;
 
Commit complete

  4.2.2、通过索引范围条件更新该索引字段

  USED_UREC=更新表记录数+更新索引块数*2

  假设表有1个单字段普通索引,通过索引范围查询10000条记录并更新对应的索引字段,每个索引块大块保存200条记录

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条记录

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个数据块

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,则回滚完成,因此我们也可以根据这个字段的变化判断回滚进度。

我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。

我原创,你原创,我们的内容世界才会更加精彩!

【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论,本站保留删除与本文无关和不雅评论的权力。

作者

MKing
MKing

相关推荐