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
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.
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的存储设备应该支持顺序写，而不是随机写
4. Temporary Table
There’s no redo log for temp table’s data blocks. But there will be redo log for undo.
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.
1. Redo log is made for
a. Data Blocks
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.
a. Undo is recorded in the Undo Segment
b. Used for undo uncommited operations.
c. When we do undo, we actually run the opposite SQLs.
a. Redo = Redo Log
i. Log as Cache in SGA
ii. Log flused to Disk
b. Used only for restoring after failure