不管表是否处于nologging下，direct insert 都不会对数据产生 UNDO [转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 4019 | 回复: 0   主题： 不管表是否处于nologging下，direct insert 都不会对数据产生 UNDO [转帖]        下一篇 
Gavin
注册用户
等级：少校
经验：878
发帖：130
精华：1
注册：2011-7-21
状态：离线
发送短消息息给Gavin 加好友    发送短消息息给Gavin 发消息
发表于： IP：您无权察看 2011-8-2 9:03:26 | [全部帖] [楼主帖] 楼主

转自：http://www.itpub.net/viewthread.php?tid=217094&extra=page%3D5%26amp%3Bfilter%3Ddigest

不管表是否处于nologging下，direct insert 都不会对数据产生 UNDO

从理论上来说，对于 direct insert 的 undo （rowid）实在没有存在的必要

因为HWM 在移动的过程中，这些block是不能被其他process使用的，那么，意味着，只要记录下该次direct insert所涉及到的 空间的redo 和 undo ，在失败回滚的时候，只需要把这些空间修改为原来的状态就可以而而不用逐个记录去delete。

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

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 insert 未提交会阻塞其他dml，也会阻止进程自己对该表的查询。

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>


我测试的数据

PHP code:



SQL> show user

USER is "SCOTT"

SQL> create table t as select * from emp;

Table created.

SQL> @roll.sql

NAME USN RSSIZE WRITES

------------------------------ ---------- ---------- ----------

SYSTEM 0 385024 7020

_SYSSMU1$ 1 1171456 956160

_SYSSMU2$ 2 188416 943404

_SYSSMU3$ 3 319488 1171200

_SYSSMU4$ 4 1171456 998598

_SYSSMU5$ 5 1171456 970452

_SYSSMU6$ 6 1171456 967354

_SYSSMU7$ 7 1171456 948962

_SYSSMU8$ 8 2220032 968382

_SYSSMU9$ 9 1171456 1101832

_SYSSMU10$ 10 3268608 1162138

11 rows selected.

SQL> insert into t select * from emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> @roll.sql

NAME USN RSSIZE WRITES

------------------------------ ---------- ---------- ----------

SYSTEM 0 385024 7020

_SYSSMU1$ 1 1171456 956386

_SYSSMU2$ 2 188416 943458

_SYSSMU3$ 3 319488 1171318

_SYSSMU4$ 4 1171456 998794

_SYSSMU5$ 5 1171456 970562

_SYSSMU6$ 6 1171456 967580

_SYSSMU7$ 7 1171456 949072

_SYSSMU8$ 8 2220032 968608

_SYSSMU9$ 9 1171456 1101942

_SYSSMU10$ 10 3268608 1162248

11 rows selected.

SQL> alter table t nologging;

Table altered.

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

14 rows created.

SQL> commit;

Commit complete.

SQL> @roll.sql

NAME USN RSSIZE WRITES

------------------------------ ---------- ---------- ----------

SYSTEM 0 385024 7020

_SYSSMU1$ 1 1171456 957058

_SYSSMU2$ 2 188416 944334

_SYSSMU3$ 3 319488 1172750

_SYSSMU4$ 4 1171456 999406

_SYSSMU5$ 5 1171456 971234

_SYSSMU6$ 6 1171456 968252

_SYSSMU7$ 7 1171456 949744

_SYSSMU8$ 8 2220032 969280

_SYSSMU9$ 9 1171456 1102270

_SYSSMU10$ 10 3268608 1162920

11 rows selected.

SQL>

SQL> alter table t logging;

Table altered.

SQL> @roll.sql

NAME USN RSSIZE WRITES

------------------------------ ---------- ---------- ----------

SYSTEM 0 385024 7020

_SYSSMU1$ 1 1171456 957644

_SYSSMU2$ 2 188416 944920

_SYSSMU3$ 3 319488 1173392

_SYSSMU4$ 4 1171456 1000274

_SYSSMU5$ 5 1171456 972016

_SYSSMU6$ 6 1171456 969150

_SYSSMU7$ 7 1171456 950526

_SYSSMU8$ 8 2220032 970006

_SYSSMU9$ 9 1171456 1102740

_SYSSMU10$ 10 3268608 1164018

11 rows selected.

SQL> insert into t select * from emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> @roll.sql

NAME USN RSSIZE WRITES

------------------------------ ---------- ---------- ----------

SYSTEM 0 385024 7020

_SYSSMU1$ 1 1171456 957840

_SYSSMU2$ 2 188416 945146

_SYSSMU3$ 3 319488 1173502

_SYSSMU4$ 4 1171456 1000500

_SYSSMU5$ 5 1171456 972126

_SYSSMU6$ 6 1171456 969376

_SYSSMU7$ 7 1171456 950636

_SYSSMU8$ 8 2220032 970060

_SYSSMU9$ 9 1171456 1102850

_SYSSMU10$ 10 3268608 1164136

11 rows selected.

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

2 emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> @roll

NAME USN RSSIZE WRITES

------------------------------ ---------- ---------- ----------

SYSTEM 0 385024 7020

_SYSSMU1$ 1 1171456 958036

_SYSSMU2$ 2 188416 945372

_SYSSMU3$ 3 319488 1173612

_SYSSMU4$ 4 1171456 1000726

_SYSSMU5$ 5 1171456 972236

_SYSSMU6$ 6 1171456 969602

_SYSSMU7$ 7 1171456 950746

_SYSSMU8$ 8 2220032 970114

_SYSSMU9$ 9 1171456 1102960

_SYSSMU10$ 10 3268608 1164528

11 rows selected.

SQL>


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com