direct insert下不会产生数据的 UNDO

原创 2004年07月04日 15:30:00

    从理论上来说,对于 direct insert 的 undo (rowid)实在没有存在的必要,因为HWM 在移动的过程中,这些block是不能被其他process使用的,那么,意味着,只要记录下该次direct insert所涉及到的 空间的redo 和 undo ,在失败回滚的时候,只需要把这些空间修改为原来的状态就可以而而不用逐个记录去delete。也就是说不管表是否处于nologging下,direct insert都不会产生数据的undo。



为此我将在这里做几个组合实验





SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Linux: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL>
SQL> drop table t;

Table dropped.

SQL> create table t as select * from dba_objects ;

Table created.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 253952 11168864
2 2220032 11335220
3 2220032 9668346
4 1171456 8887572
5 122880 84154
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> insert into t select * from dba_objects;

13197 rows created.

SQL> commit;

Commit complete.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 253952 11168864
2 2220032 11336024
3 2220032 9668346
4 2220032 9810066
5 122880 85452
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select 9810066 - 8887572 from dual;

9810066-8887572
---------------
922494 正常情况下产生的回滚段信息量
SQL> select 85452 - 84152 from dual;

85452-84152
-----------
1300 undo中这个细微变化目前不详,估计可能是空间的变化导致的


SQL> alter table t nologging;设置表为nologging

Table altered.

SQL> insert /*+ append */ into t select * from dba_objects; direct insert

13197 rows created.

SQL> commit;

Commit complete.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 253952 11169498
2 2220032 11339548
3 2220032 9670376
4 2220032 9813076
5 122880 85452
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select 9670376 - 9668346 from dual;

9670376-9668346
---------------
2030 发现回滚段信息极少

SQL> select 9813076-9810066 from dual;

9813076-9810066
---------------
3010

SQL>






SQL> alter table t logging; 把表置回logging状态

Table altered.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 253952 11173844
2 2220032 11343052
3 2220032 9676700
4 2220032 9816500
5 122880 89668
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> insert into t select * from dba_objects; 正常插入数据

13197 rows created.

SQL> commit;

Commit complete.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 253952 11173844
2 2220032 12266342
3 2220032 9676918
4 2220032 9817356
5 122880 90478
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select 12266342 - 11343052 from dual;

12266342-11343052
-----------------
923290 产生的回滚段信息

SQL> insert /*+ append */ into t select * from dba_objects; direct insert

13197 rows created.

SQL> commit;

Commit complete.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 253952 11174528
2 2220032 12266768
3 2220032 9678420
4 2220032 9817356
5 122880 92562
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL>

SQL> select 11174528 - 11173844 from dual;

11174528-11173844
-----------------
684

SQL> select 92562 - 90478 from dual;

92562-90478
-----------
2084

SQL>
在这里我们核对任意一个回滚段的生成量,发现几乎很少

由此我们几乎可以下个结论,不管表是否在nologging 下,只要是 direct insert,就不会对数据内容生成undo,也就是不会为insert而记录 rowid






接下来我们进一步实验,在表同样处于 logging 状态下测试

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 133173584 1236385760

SQL> insert into t select * from dba_objects;

13197 rows created.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 137974492 1236385760

SQL> select 137974492 - 133173584 from dual;

137974492-133173584
-------------------
4800908 正常插入产生的日志

SQL> roll;
Rollback complete.
SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 140087680 1236385760

SQL> select 140087680 - 137974492 from dual;

140087680-137974492
-------------------
2113188 正常插入后回滚所产生的日志

SQL> insert /*+ append */ into t select * from dba_objects;

13197 rows created.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 141531644 1236385760

SQL> select 141531644 - 140087680 from dual;

141531644-140087680
-------------------
1443964 direct 插入产生的日志

SQL> roll;
Rollback complete.
SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 141534344 1236385760

SQL> select 141534344 - 141531644 from dual;

141534344-141531644
-------------------
2700 direct插入后回滚产生的日志

SQL>

从这里的实验可以看出,在 direct insert 后回滚数据,实际上并没有进行数据的 删除操作,而是仅仅对空间进行了回收。若是删除,不可能只产生这么少的 redo,这里从另一个侧面证明,即使 logging 下的 direct insert 对于回滚信息,也是不会对数据产生 undo 而仅仅产生空间变化的 undo

 

 

带索引表,表和索引均是logging状态,测试结果及过程如下

----------------------------常规插入-------direct插入

插入日志生成量----------------8350864--------2364484

插入回滚段生成量--------------2343894--------426838

回滚日志生成量----------------4018204--------76032

回滚本身不存在产生回滚-------------------------------------


结论是很显然的,也许这里有人要问,既然direct有这么多好处,那为什么还用常规?
因为920前的版本sqlldr direct导致trigger无用、函数无用
direct直接在hwm上移动而不使用delete删除释放的空间可能导致空间浪费
direct的时候,据说同一个extent只能由一个进程使用(未测试,目前无LMT表空间环境)

direct + nologging 由于不产生数据日志导致恢复会出现问题

SQL> truncate table t;

Table truncated.

SQL> create index t_index on t(object_id);

Index created.

SQL> col name format a20
SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 516096 14166140
2 1171456 17003930
3 2220032 13918700
4 1171456 13550540
5 122880 756246
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 181757168 1236385760

SQL> insert into t select * from dba_objects;

13198 rows created.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 516096 14166140
2 4317184 19347824
3 2220032 13918700
4 1171456 13551396
5 122880 756246
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 190108032 1236385760

SQL> select 19347824 - 17003930 from dual;

19347824-17003930
-----------------
2343894 存在索引,常规插入方式下产生的回滚量

SQL> select 190108032 - 181757168 from dual;

190108032-181757168
-------------------
8350864 存在索引,常规插入方式下产生的日志量

SQL> roll;
Rollback complete.
SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 516096 14166140
2 4317184 19347824
3 2220032 13918700
4 1171456 13551396
5 122880 757102
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 194126236 1236385760

SQL> select 194126236 - 190108032 from dual;

194126236-190108032
-------------------
4018204 存在索引,常规插入方式下然后回滚所产生的日志量

SQL> truncate table t;

Table truncated.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 581632 14185742
2 4317184 19356862
3 2220032 13936438
4 1171456 13566936
5 122880 757102
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 194287336 1236385760

SQL> insert /*+ append */ into t select * from dba_objects;

13198 rows created.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 2088960 14612580
2 4317184 19356862
3 2220032 13936438
4 1171456 13569090
5 122880 757102
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 196651820 1236385760

SQL> select 14612580 - 14185742 from dual;

14612580-14185742
-----------------
426838 存在索引,direct插入方式下产生的回滚量

SQL> select 196651820 - 194287336 from dual;

196651820-194287336
-------------------
2364484 存在索引,direct插入方式下产生的日志量

SQL> roll;
Rollback complete.
SQL> select usn,RSSIZE ,WRITES from v$rollstat;

USN RSSIZE WRITES
---------- ---------- ----------
0 385024 7620
1 2088960 14612580
2 4317184 19360722
3 2220032 13936438
4 1171456 13569946
5 122880 757908
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
133 redo size 2 196727852 1236385760

SQL> select 196727852 - 196651820 from dual;

196727852-196651820
-------------------
76032 存在索引,direct插入方式下回滚产生的日志量

SQL>

 

 

 

oracle insert时undo的记录

oracle在没有提交的时候就可能将数据写入到文件中,此时undo怎样记录的? SQL> create table test(id int,name varchar2(10)); Table cr...
  • xionglang7
  • xionglang7
  • 2012年09月04日 20:25
  • 1875

InnoDB undo log解析(一)

在InnoDB存储引擎中,undo log用来完成事务的回滚以及MVCC的功能。但是InnoDB本身提供对于undo log的分析或者查询功能非常有限。用户仅能通过SHOW ENGINE INNODB...
  • skiof007
  • skiof007
  • 2016年11月28日 14:38
  • 454

对比DML操作产生的REDO及UNDO大小

结论:相同数据量的数据操作: INSERT产生最少REDO;UPDATA首先的REDO居中;DELETE产生最多REDO。 DELETE产生UNDO最多,UPDATE次之,INSERT产生UN...
  • q947817003
  • q947817003
  • 2013年09月07日 20:19
  • 1605

第二十四讲--Undo段及区的状态和使用

Undo的作用:oracle开始一个事务的时候使用undo表空间。 假如执行一个delete语句,oracle会把删除前的数据放到undo表空间的undo段里面。一个事务修改的block越多则占用的...
  • chengonghao
  • chengonghao
  • 2016年03月22日 12:26
  • 561

【优化批量插入】nologging和append使用解析

Nologging 更改表的日志记录方式 Alter table table_name nologging; Alter table table_name logging; 数据库和表空间也可...
  • wang1016612067
  • wang1016612067
  • 2016年10月08日 22:18
  • 521

用直接路径(direct-path)insert提升性能的两种方法

Direct-path insert 方式的优点 (1)可以将insert数据跳过buffer_cahce,省掉了buffer block的格式化与DBWR操作,直接从PGA写入磁盘 (2)不检查表中...
  • ljunjie82
  • ljunjie82
  • 2015年01月11日 20:52
  • 1376

oracle笔记整理2——redo与undo

1. redo和undo 1) redo a) 也就是重新做的意思,当系统发生故障时重新做。 b) oracle日志文件分为重做日志文件(Redo Log File)和归档日志文件。 c) ...
  • thinkpadshi
  • thinkpadshi
  • 2015年12月29日 16:19
  • 1025

在线扩大数据库UNDO表空间

用oracle账号登陆ORACLE数据库服务器 方法一: 查看表空间的名字及文件所在位置: select tablespace_name, file_id, file_name,round...
  • Jerry_1126
  • Jerry_1126
  • 2014年07月16日 19:08
  • 2261

oracle undo表空间的清理

找出UNDO表空间的路径及大小 SQL>  select file_name,bytes/1024/1024 from dba_data_files where tablespace_name li...
  • barsala
  • barsala
  • 2015年01月09日 23:53
  • 3260

Oracle切换undo表空间操作步骤

操作系统版本及数据库版本如下: SQL> !cat /etc/redhat-release Red Hat Enterprise Linux Server release 6.5 (Santiago)...
  • lk_db
  • lk_db
  • 2016年04月22日 13:17
  • 2349
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:direct insert下不会产生数据的 UNDO
举报原因:
原因补充:

(最多只允许输入30个字)