--insert操作 undo记录什么
--update操作 undo记录什么
--delete操作 undo记录什么
//index是否写undo？
结论是写。可通过对比加index之前和加index之后的undo生成量进行对比得出结论。
//undo数据产生量
redo中只会记录少量信息,这些信息足以重演事务;
undo中也只记录精简信息,这些信息足以撤消事务。
insert 产生undo最少,只记录插入记录的rowid;
update 中间,回滚段只需要记录被更新字段的旧值即可（前镜像）,回退时通过旧值覆盖新值即可完成回退;
delete 最多,Oracle则必须记录整行的数据,在回退时,Oracle通过一个反向操作恢复删除的数据。
undo数据量查看：v$transaction.used_ublk
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使用空间
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='XXXXXXX';
--由于测试环境就一个人使用,不存在并发,为简化操作,忽略会活参数,简化的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内容。
三.--测试准备
--创建表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
--object_id创建索引
SQL> create index idx_t1_object_id on t1(object_id);
Index created
--object_name创建索引
SQL> create index idx_t1_object_name on t1(object_name);
Index created
四.--计算方法及测试脚本:USED_UREC
下面介绍事务中各种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 deleted
SQL> select USED_UREC from v$transaction;
USED_UREC
----------
30
SQL> 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
----------
20242
SQL> 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
----------
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条记录
--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
五.--总结
1.以上脚本是在Oracle9.2上测试,Oracle对undo的处理非常复杂,这里介绍只是常用的一些dml产生undo估算方法;
2.从估算公式可以看出,索引对dml操作的影响非常大,当一个表有索引比没索引时做dml操作花费的undo开销非常具大,因为数据库要保证事务回滚的可行性,需要对索引做许多额外的事情,更新索引字段及批量insert操作尤其明显.
3.在有索引和没索引的表上做批量数据导入,性能有可能相关好几倍.通过估算oracle的dml操作需要的undo记录数,也可以间接估算一个dml还需要的时间.
4.工作中有时会遇到一个dml操作时间非常长,如果 v$session_longops 视图也没有可以跟踪的信息,这时就可以通过v$transaction的'used_urec'信息估算sql的进度.文章来源：https://www.toymoban.com/news/detail-819476.html
dml在操作时,used_urec 是一直在增加的,当事务开始回滚时used_urec会开始下降,直到等于0,则回滚完成,因此也可以根据这个字段的变化判断回滚进度.文章来源地址https://www.toymoban.com/news/detail-819476.html
到了这里，关于增删改undo生成量？？index是否写undo？Oracle DML语句(insert,update,delete) ‘回滚开销估算‘的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
