Oracle's Redo and undo__oracle

Turn from: http://blog.csdn.net/inthirties/article/details/4843831

Here we will describe how Undo,redo is produced, the impact on transactions, and how they work together.

What is Redo

Redo records transaction logs, divided into online and archived. For the purpose of recovery.
For example, if the machine is out of power, then you need to go online redo logs to restore the system to the failure point after the reboot.
For example, the disk is out of date and needs to be recovered using the archived redo logs and online redo logs area.
For example, truncate a table or other operation, want to revert to the previous state, also need.

What is Undo

Redo is to recreate your operation, and Undo is to undo what you do, for example, if you have a transaction failure or you regret it, you need to use the rollback command to fall back to the operation. Rollback is implemented at the logical level rather than at the physical level, because in a multi-user system, data structures, blocks, and so on are constantly changing, such as we insert a data, the table is not enough space, extended a new extent, our data is stored in this new extent, Other users subsequently inserted the data in the extent, and I wanted to rollback it, so it was obviously physically impossible to undo this extent, because doing so would affect other users ' actions. So, rollback is the logical rollback, for example, for inserts, then rollback is the delete.

Prior to the commit, it was often assumed that a commit of a large transaction (such as a large number of inserts) would take longer than a short transaction. And the fact that there is no difference,
Because Oracle has written what should be written to disk before a commit,
We commit just
1, the creation of a SCN gives us a transaction,scn simple understanding is to queue for transaction in order to restore and maintain consistency.
2,redo Write REDO to disk (LGWR, this is log file sync), record SCN in online REDO log, when this step occurs, we can say that the fact has been submitted, the transaction has ended (in v$ Disappeared in the transaction)
The LOCK (V$lock) owned by 3,session is released.
4,block cleanout (This problem is the root cause of ora-01555:snapshot too old) ROLLBACK ROLLBACK and commit just the opposite, The rollback time is directly related to the size of the transaction. Because rollback must physically recover the data. A commit is quick because Oracle has done a lot of work before a commit (creating undo, modifying Block,redo,latch allocations),

Rollback slow is also based on the same reason.
Rollback will
1, restore the data, delete the Insert,insert again delete,update again update.
2,release LOCK rollback consumes more resources than commits, because Oracle believes that once you do data updates, that means you commit (other databases are not entirely the design concept, such as DB2), So you do a lot of work when you update your data, and you can understand why you don't recommend using table as a temporary table. (TEMP table consumes a lot less redo than a fixed list when you insert it, which is almost 1/2 when you update it.
But delete is just about the same. The more REDO you REDO, the slower your system will affect your own session and other SESSION,LGWR management REDO, and the end of transaction.

The first thing to know is how to monitor redo, of course, SET autotrace on, but only the DML statements can be monitored, and like procedure cannot be monitored. Then we'll need to look at the dictionary, V$mystat, V$statname,


There are two scripts in front, MYSTAT,MYSTAT2

sql> @mystat "Redo Size"

NAME VALUE
Redo Size 1016784

sql> INSERT INTO T-select * from big_table; 46990 lines have been created.

Sql> @mystat2
NAME VALUE DIFF
Redo Size 6604308 5,587,524
See the redo that produced 5,587,524, and then in contrast, insert with Nolog.

sql> @mystat "Redo Size"
NAME VALUE
Redo Size 6604308

sql> Insert/*+ APPEND/into t select * from big_table;
46990 lines have been created.

Sql> @mystat2
NAME VALUE DIFF
Redo Size 6616220 11,912

See Append inserts with a 11,912-byte redo, much less than a general insert. Or use this procedure to observe the redo of SQL consumption.Reference:Sql>create or Replace procedure Do_sql (P_sql in VARCHAR2)
2 AS
3 L_start_redo number;
4 L_redo number;
5 begin
6 Select V$mystat.value
7 into L_start_redo
8 from V$mystat, V$statname
9 Where v$mystat.statistic# = v$statname.statistic#
V$statname.name = ' redo size ';
11
Execute immediate p_sql;
commit;
14
Select V$mystat.value-l_start_redo
Into L_redo
From V$mystat, V$statname
where v$mystat.statistic# = v$statname.statistic#
V$statname.name = ' redo size ';
20
Dbms_output.put_line
To_char (L_redo, ' 9,999,999 ') | | ' bytes of redo generated for ' | |
SUBSTR (replace (P_sql, Chr (10), "), 1, 25) | | '"...' );
End;
25/

The usage is not much said. Reduce redo since redo consumes so much resources, can we shield redo? Obviously not, can we reduce the redo? This is possible (note that after 9.2, you can use the FORCE LOGGING switch to control whether or not to force the redo, if yes, nologging or append will not have any effect, you can select Force_logging from v$ Database to see if force. Also need to understand, there is no way to completely do not record redo, can only be reduced redo. Because no matter what, the data dictionary always has to produce some redo. CREATE table nologging as Select xxx The newly created table has no index and default value for the original table, and only non-null (NOT NULL) constraint element conditions can be inherited. INSERT/*+ APPEND/into Target_tablename SELECT If you run this command, a DML operation against Target_tablename will queue up behind it, which is not appropriate for the table operations used by the OLTP system. You can quickly insert data to specify append prompts, but note that Noarchivelog mode is used by default append is the nologging mode. Under Archivelog, you need to set the table to nologging mode. You can set the No FORCE LOGGING by using the following statement. Alter database no force logging; These two methods of transferring data are not used in the SGA data buffer and things to deal with the rollback segment, and do not write online things log, just as the database load tool sqlload directly to the data to the physical file. The problem of Redo
Sometimes, you'll find it in alert.
Thread 1 cannot allocate new log, sequence 1466 Checkpoint not complete current log# 3 seq# 1465 mem# 0:/home/ora10g/orad Ata/ora10g/redo03.log
This problem occurs when the system tries to reuse online redo log file but is not available. May be due to DBWR not completed (Checkpoint not complete) or arch not completed.
1,DBWR, using more DBWR process, reasonable distribution of data,
2, add redo LOG FILE
3, enlarge the size of the redo
4, let CHECKPOINT occur more frequently, can reduce block buffer cache,fast_start_mttr_target, log_checkpoint_interval,log_checkpoint_timeout.