Monthly Archives: December 2009

Notes on ‘Expert Oracle’ — No.9.6: Redo&Undo — Undo Log

1. Amount of Undo Log

    Delete > Update > Insert

    Operations on Indexed Columns > Operations on Non-indexed

2. What can cause "Snap Too Old"  exception?  => The undo segment is too small

3. How to make the undo segment not so small?

   a. Enlarge it, of course

      i.Enlarge UNDO_RETENTION, which should be larger than longest transaction

     ii.Set it by DBA manually 

   b. Query Tuning

   c. Do the block-cleanout by your self



Notes on ‘Expert Oracle’ — No.9.5: Redo&Undo — Block Cleanout

a.When data blocks are involved in a transaction, there are locking info created in the block.

b.This info should be cleaned out when the transaction ends.

c.They can be cleaned out immediately when the transaction ends, but not definitely.

   —- There are cleaned out on commitment, only if the blocks needed to be cleaned account for less than the 10% of the all the blocks in the cache.

d.So, if Oracle doesn’t do the cleansing while commitement, the blocks will be cleaned on the next operation, "select" for example.

e.Cleaning out the blocks in "select" also creates Redo Log.

   —So, the first select after a large-scale DML may be very time-consuming.

Notes on ‘Expert Oracle’ — No.9.4: Redo&Undo — More about Redo Log

1. The large your Redo log is, the more time-consuming your SQL operation is.

2. Can we turn off Redo Log?

   a. Not Recommeded

   b. You can use "NOLOGGING" in SQLs to reduce much of the log.

   c. You can also use "NOLOGGING" for INDEX. This is actually acceptable.

3. How to avoid Log Contention?


   a. Redo Log的存储设备应该支持顺序写,而不是随机写

   b. 准备两组文件组,当一组用于LGWR写在线日志时,另一组则用于ARCH进行日志归档。这样就不会有写竞争。

4. Temporary Table

   There’s no redo log for temp table’s data blocks.  But there will be redo log for undo.

Notes on ‘Expert Oracle’ — No.9.3: Redo&Undo — Commit & Rollback

1. What happens if a transaction is commited?

  a. The Redo Log is flushed

  b. Is the data blocks in SGA flushed also?  No.  We don’t need to do that with Redo Log.


2. Is the time of commitment a function of the volumn of manipulated data?

  No. There is not a lot to do when the transactions reach the point of commit.

    a. Flush the Redo log. The log is not so much, since the LGWR has been flushing logs when the transaction starts.

    b. Put the SCN in the Redo Log. (SCN = Indication of a transaction)

3. So, don’t commit frequently. Commit a long transaction.

    a. You don’t have to commit frequently. The time of a single commitment is not getting larger just because your transaction is long.

    b. Everytime you commit, you have to wait for the LGRW to flush the log file. The more you committed, the longer you have to wait. That’s time consuming.

4. The time of Rollback is a function of the data manipulated.

    a. It is actually running the opposite SQLs.

    b. So,  avoid large-scale Rollbacks.

Notes on ‘Expert Oracle’ — No.9.2: Redo&Undo — How Redo works?

1. Redo log is made for

   a. Data Blocks

   b. Indexes

   c. Undo (That’s intersting)

2. The Redo info is first made in Cache, and it will be flushed into disk when

   a. 3 seconds passed

   b. The Redo Cache is 1/3 full or is over 1MB

   c. Commit happens

3. How Redo log helps with restoring?

  a. We use redo log to redo all commited transactions (Roll-Forward)

  b. After doing last step, we’ve already got the undo info for uncommited transactions, since it is also recorded in Redo Log

  c. The we do "Undo", to rollback uncommited transactions.


4.Again, Redo log is only for restore.

  a. The log is never read online.

  b. When online, the log is only being written, not being read.