Oracle Undo的问题

Yong Huang

原帖由 sqysl 于 2008-10-23 20:26 发表
1、其实，想想APPEND和DIRECT的原理就知道，它们不产生UNDO的原理是不同的。
2、还有，楼主提出的疑问和APPEND不产生UNDO没关系啊，它不产生，并不代表后面的操作不产生，你后来用FLASHBACK查到的APPEND插入的数据是后续的操作产生的前影像啊，比如：你APPEND INSERT后，但你后面的操作会产生前影像，那么APPEND INSERT的数据就成了后续操作的前影，所以能查到。

Can you elaborate your first point? What's the difference?

I'm not sure what you refer to by "后续的操作" after insert /*+ append */. For example,

[table t is empty]
select dbms_flashback.get_system_change_number from dual;
[returns 14745900596]
insert /*+ append */ into t select rownum from dba_users;
commit;
select count(*) from t as of scn 14745900596;
[returns 0 rows]

There's no operation following the insert, except commit and the flashback query.

Yong Huang

sqysl

TO YONGHUANG:抱歉，关于这个帖子1、2两点，我回答的欠妥。
我认真看了一下资料，关于带APPEND的INSERT语句的redo和undo可以选择产生和不产生，楼主测试库的设置是否是产生UNDO的呢？
下面我贴出部分资料内容，大家一起参考：
Specifying the Logging Mode for Direct-Path INSERT
Direct-path INSERT lets you choose whether to log redo and undo information during the insert operation.

1、You can specify logging mode for a table, partition, index, or LOB storage at create time (in a CREATE statement) or subsequently (in an ALTER statement).

2、If you do not specify either LOGGING or NOLOGGING at these times:

     （1）The logging attribute of a partition defaults to the logging attribute of its table.

     （2）The logging attribute of a table or index defaults to the logging attribute of the tablespace in which it resides.

     （3）The logging attribute of LOB storage defaults to LOGGING if you specify CACHE for LOB storage. If you do not specify CACHE, then the logging attributes defaults to that of the tablespace in which the LOB values resides.

3、You set the logging attribute of a tablespace in a CREATE TABLESPACE or ALTER TABLESPACE statements.

Note:

If the database or tablespace is in FORCE LOGGING mode, then direct path INSERT always logs, regardless of the logging setting.

4、Direct-Path INSERT with Logging
In this mode, Oracle Database performs full redo logging for instance and media recovery. If the database is in ARCHIVELOG mode, then you can archive redo logs to tape. If the database is in NOARCHIVELOG mode, then you can recover instance crashes but not disk failures.

5、Direct-Path INSERT without Logging
In this mode, Oracle Database inserts data without redo or undo logging. (Some minimal logging is done to mark new extents invalid, and data dictionary changes are always logged.) This mode improves performance. However, if you subsequently must perform media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because no redo data was logged for them. Therefore, it is important that you back up the data after such an insert operation.

joe_xuqj

牛，学习一下

Yong Huang

原帖由 sqysl 于 2008-10-24 21:15 发表
TO YONGHUANG:抱歉，关于这个帖子1、2两点，我回答的欠妥。
我认真看了一下资料，关于带APPEND的INSERT语句的redo和undo可以选择产生和不产生，楼主测试库的设置是否是产生UNDO的呢？
下面我贴出部分资料内容，大家一起参考：
Specifying the Logging Mode for Direct-Path INSERT
Direct-path INSERT lets you choose whether to log redo and undo information during the insert operation.
...

We're all clear about the logging part. But whether there's undo is not clearly explained. I think the documentation is misleading, or simply wrong, in saying "Direct-path INSERT lets you choose whether to log redo and undo." Redo can be chosen. But undo for the table is always bypassed in direct path insert; it's not possible to generate undo for the table in direct path insert.

Yong Huang

sqysl

YONGHUANG版主，谢谢你的回复，你在帖子里说“But undo for the table is always bypassed in direct path insert; it's not possible to generate undo for the table in direct path insert.”
为什么这么说呢？有确凿的证据吗？比如试验或资料，因为，我觉得既然能产生REDO，那么产生UNDO也不是没可能的，DIRECT PATH只是绕过了BUFFERS，而直接将数据写在了数据文件里，既然可以直接往数据文件里写，为什么不可以往UNDO数据文件里直接写呢？还有，通常情况下，LOG不仅包含了数据文件操作的REDO，而且还包含了UNDO空间的REDO，如果设置DIRECT PATH产生REDO，那恐怕不能只产生数据文件的REDO而忽略UNDO空间的REDO吧？如果这样，在INSTANCE CRASH RECOVERY的过程中，数据文件的REDO还是没意义的，不过，我们有时间可以设计一个试验来测试一下。

[ 本帖最后由 sqysl 于 2008-10-26 09:43 编辑 ]

Yong Huang

原帖由 sqysl 于 2008-10-25 19:35 发表
YONGHUANG版主，谢谢你的回复，你在帖子里说“But undo for the table is always bypassed in direct path insert; it's not possible to generate undo for the table in direct path insert.”
为什么这么说呢？有确凿的证据吗？比如试验或资料，因为，我觉得既然能产生REDO，那么产生UNDO也不是没可能的，DIRECT PATH只是绕过了BUFFERS，而直接将数据写在了数据文件里，既然可以直接往数据文件里写，为什么不可以往UNDO数据文件里直接写呢？

For evidence for lack of undo, refer to the classical "晶晶实验二十二之　直接路径插入篇" at
http://space.itpub.net/?uid-1309 ... space-itemid-225009

In fact, re-reading her article makes me think that we can answer OP (original poster)'s question: How can flashback query, which relies on undo, work, after a direct path insert, which bypasses undo? There won't be any magic here. A flashback query in this case works as if there was a rollback followed by a query, and this rollback is explained in 晶晶's article, section "直接路径插入与回滚".

Yong Huang

sqysl

谢谢YONGHUANG版主的答复，我也大体看了一下晶晶小妹的试验，她确实是个天才，我有时间一定认真仔细的阅读她的试验，但我的心里还是存在疑问，包括和晶晶小妹试验不相关的问题，我有时间验证一下，到时候再贴一下。

sqysl

这几天一直忙着做这个测试，下面是我针对DIRECT PATH INSERT的是否产生UNDO信息的问题做了测试，大体步骤如下：
1、首先，我怀疑DIRECT PATH INSERT 会产生UNDO信息的原因有两个：
（1）我测试了本帖7楼的甲骨虫虫测试，我测试的结果和他完全一致；
（2）另一个原因是：我认为，如果一个事务产生了REDO信息，那么它也会产生UNDO信息，因为在INSTANCE CRASH RECOVERY过程中，如果只有REDO而没有UNDO，系统会遇到麻烦，除非针对某种特殊事务，系统实施了不同的恢复方案，但这对ORACLE来说可能性不大。
2、在测试的过程中，我分别用到了查SESSION的undo change vector size和DBWR undo block writes，每次执行完DIRECT PATH INSERT 后，这两个信息都有变化，具体变化情况在附加文件里，通过它们我只是怀疑有UNDO信息产生，而不确定，此外，我又导出了事务
COMMIT前和COMMIT后的回滚段头信息，发现COMMIT前后的UEL和DBA不一直，我怀疑，在DIRECT PATH提交过程中，事务向UNDO SEGMENT写了UNDO信息，这不难理解，因为在DIRECT PATH INSERT过程中，事务首先要在操作的段里形成一个或几个临时段，在事务提交时，再将临时段合并为一个主段，形成最终结果。然后，我根据事务COMMIT后的事务的DBA信息，导出了对应的回滚段数据块，在数据块文件里，我找到了进行DIRECT PATH INSERT 操作对象的UNDO信息，有UNDO信息也查到，UNDO信息对应的对象就是我们进行DIRECT PATH INSERT的表BB，因此，我得出一个不成熟的结论：append INSERT过程中，是要产生UNDO信息的，只是UNDO比较少，这里之所以说不成熟，是因为我没仔细看找到的UNDO信息的内容，比较耗费精力，有充裕的时间再看看，下面我把测试具体过程和导出文件两个文件附加在这里，方便大家一起阅读，欢迎一起讨论。

***两个文件用写字板打开阅读效果比较好。

Yong Huang

Thanks for the detailed research. I guess we can safely say that there's always a little undo, even in the case of direct path data load (direct path insert or SQL*Loader load). That minimum undo must be due to data dictionary change, i.e. undo for the recursive SQLs. You can imagine the disaster if data dictionary change were to not have any undo. There's no way to avoid recursive DMLs. (Unlike recursive SQLs that do query only, recursive DMLs cannot be eliminated by loading necessary metadata into dictionary cache.)

Yong Huang

sqysl

谢谢YONGHUANG版主的答复，不过，我现在还有两个疑点：
第一，我认为这里产生的UNDO信息，是系统递归DML产生的UNDO的可能性不大，因为UNDO信息里的OBJN对应的是试验表对象；
第二，晶晶小妹的试验我做了几次，她说的都是对的，但她的试验并不能解释DIRECT PATH INSERT操作COMMIT后，还可以进行FLAHSBACK QUERY的问题，也就是七楼甲骨虫虫说的问题。
这仅仅是我的疑问，有时间我会继续再研究一下，到时候，无论我的猜想对错，我都把结果贴出来。