REDO日志的基本结构

liyongdong

相较于执行UPDATE、INSERT、DELETE处理，在执行CREATE、DROP处理时会对REDO执行大量写入的现象。

liyongdong

这是因为CREATE（DROP）表的时候，会对多个数据字典进行变更。如果只是1个INSERT（DELETE）处理，那么只会产生一次REDO entry (record)，但是执行CREATE处理的时候会发生69次(DROP的情况下会发生51次) entry。实际调查REDO．DUMP的内容就会发现69次entry中绝大部分都是针对数据字典产生的。
当ORACLE运行在NOLOGGING模式时，因为不会把 dictionary变更以外的部分纪录到日志中去，所以可以大幅减少对REDO日志进行写入的次数，从而提升REDO的

liyongdong

性能。

Yong Huang

I'm afraid I have to clarify something you wrote. A server process *is* what Oracle articles sometimes call foreground process, as distinct from a background process (v$bgprocess). But you may already agree with this.

You seem to keep saying before image of a data block is written to log buffer (and eventually redo logfiles). That's not true. As I said, redo records only record changes, no before image, which is what rollback or undo does.

"在频繁执行Update操作的情况下，LGWR会满负荷运行" should be qualified to be "在频繁执行DML操作的情况下 *and* with frequent commits,..." Without commits, LGWR doesn't work that hard. It's true that without commits, server processes still write redo records into log buffer. But LGWR only works when log buffer is 1/3 full (the 3 second rule still applies but it's unrelated to frequent DMLs).

A change vector does not contain before image. It does have block DBA, as well as block version number and op code.

By "在编写程序的时候要集中transaction再进行处理", you mean commit only if absolutely necessary? That's good advice because you want to avoid too many log file sync waits, one common problem in OLTP databases. However, regardless how frequently you commit, the amount of redo is about the same and is dependent on your business logic.

Generally, update generates the most *undo*, insert the least, delete in the middle unless it's a long row. (But you can create cases this general rule doesn't apply.) It's an interesting question which one of the three generates the most or least *redo*. I think since the accompanied undo for these DMLs also generates redo, the total amount of redo follows the same order (i.e. update > delete > insert). Anyway, I don't follow your logic that "UPDATE、INSERT、DELETE对REDO日志文件写入的信息量并没有太大的差异" based on logfile block size being 512.

Yong Huang

liyongdong

最初由 Yong Huang 发布
[B]I'm afraid I have to clarify something you wrote. A server process *is* what Oracle articles sometimes call foreground process, as distinct from a background process (v$bgprocess). But you may already agree with this.

You seem to keep saying before image of a data block is written to log buffer (and eventually redo logfiles). That's not true. As I said, redo records only record changes, no before image, which is what rollback or undo does.

"在频繁执行Update操作的情况下，LGWR会满负荷运行" should be qualified to be "在频繁执行DML操作的情况下 *and* with frequent commits,..." Without commits, LGWR doesn't work that hard. It's true that without commits, server processes still write redo records into log buffer. But LGWR only works when log buffer is 1/3 full (the 3 second rule still applies but it's unrelated to frequent DMLs).

A change vector does not contain before image. It does have block DBA, as well as block version number and op code.

By "在编写程序的时候要集中transaction再进行处理", you mean commit only if absolutely necessary? That's good advice because you want to avoid too many log file sync waits, one common problem in OLTP databases. However, regardless how frequently you commit, the amount of redo is about the same and is dependent on your business logic.

Generally, update generates the most *undo*, insert the least, delete in the middle unless it's a long row. (But you can create cases this general rule doesn't apply.) It's an interesting question which one of the three generates the most or least *redo*. I think since the accompanied undo for these DMLs also generates redo, the total amount of redo follows the same order (i.e. update > delete > insert). Anyway, I don't follow your logic that "UPDATE、INSERT、DELETE对REDO日志文件写入的信息量并没有太大的差异" based on logfile block size being 512.

Yong Huang [/B]

谢谢  Yong Huang   给我提出的问题与补充。
redo records only record changes, no before image,。是对的。我的错了。
对于UPDATE、INSERT、DELETE对REDO日志文件写入的信息量并没有太大的差异，是在通常的情况下。
如果是处理任务关联非常多的表，情况就会不同。

水生木

学到很多知识，谢谢楼主讲解

liyongdong

谢谢!,以后我还会补充。

liyongdong

这次要说明和REDO有关的两个latch（latch是一种Oracle低级结构,用于保护内存资源，是一种内部生命周期很短的lock）：REDO allocation latch和REDO copy latch的功能。

liyongdong

把REDO信息写入到REDO日志缓冲时，Oracle必须取得REDO allocation latch和REDO copy latch这两个latch。
            REDO allocation(分配) latch：在REDO日志缓冲内，分配空间给要存入的REDO entry时所需的latch。
            REDO copy latch：当REDO entry太大，无法以REDO allocation latch执行copy时，REDO copy latch会代替REDO allocation latch执行copy。

liyongdong

这两个latch的功能和作用请参考下图的说明。图中提到REDO entry的大小，主要取决于初始设定参数LOG_SMALL_ENTRY_MAX_SIZE。