select語句寫redolog的條件

通過測試,我們發現select 語句在執行for update的時候會寫REDOLOG,以下是測試結果:

SQL> desc test
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 NAME                                                                       VARCHAR2(20)
 CHENFENG                                                                   DATE

SQL> select name,count(*) from test group by name;

NAME                   COUNT(*)
-------------------- ----------
msptest                       1
tsptest                    1034

SQL> select * from test where name='msptest';

NAME                 CHENFENG
-------------------- -------------------
msptest              2004-05-10:04:00:43

SQL> select name,value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name like '%redo%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                         1
redo synch time                                                           1
redo entries                                                              3
redo size                                                               532
redo buffer allocation retries                                            0
redo wastage                                                              0
redo writer latching time                                                 0
redo writes                                                               0
redo blocks written                                                       0
redo write time                                                           0
redo log space requests                                                   0
redo log space wait time                                                  0
redo log switch interrupts                                                0
redo ordering marks                                                       0

14 rows selected.

不加for update的時候,得到當前select語句的redo產生量為532.

執行select ... for update後,看看redo的產生量:

SQL> select * from test where name='msptest' for update;

NAME                 CHENFENG
-------------------- -------------------
msptest              2004-05-10:04:00:43

SQL> select name,value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name like '%redo%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                         1
redo synch time                                                           1
redo entries                                                              4
redo size                                                               836
redo buffer allocation retries                                            0
redo wastage                                                              0
redo writer latching time                                                 0
redo writes                                                               0
redo blocks written                                                       0
redo write time                                                           0
redo log space requests                                                   0
redo log space wait time                                                  0
redo log switch interrupts                                                0
redo ordering marks                                                       0

14 rows selected.

產生量增加到了836,這說明select ... for update操作會寫一定量的redo.

我們執行rollback後,看一下redo的產生量:

SQL> rollback;

Rollback complete.

SQL> select name,value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name like '%redo%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                         2
redo synch time                                                           2
redo entries                                                              6
redo size                                                              1088
redo buffer allocation retries                                            0
redo wastage                                                              0
redo writer latching time                                                 0
redo writes                                                               0
redo blocks written                                                       0
redo write time                                                           0
redo log space requests                                                   0
redo log space wait time                                                  0
redo log switch interrupts                                                0
redo ordering marks                                                       0

14 rows selected.

SQL>  select name,value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name like '%redo%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                         2
redo synch time                                                           2
redo entries                                                              6
redo size                                                              1088
redo buffer allocation retries                                            0
redo wastage                                                              0
redo writer latching time                                                 0
redo writes                                                               0
redo blocks written                                                       0
redo write time                                                           0
redo log space requests                                                   0
redo log space wait time                                                  0
redo log switch interrupts                                                0
redo ordering marks                                                       0

14 rows selected. 

結果表明rollback或commit操作也會寫一定量的redo.