数据库版本：
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0


创建测试表：
SQL> create table a  as select * from all_objects  ;
Table created.

SQL> set autotrace on statistics ;

插入数据（ｈｉｎｔ　ａｐｐｅｎｄ）：

SQL> insert /*+ append */ into a select * from all_objects ;
9891 rows created.

Statistics
----------------------------------------------------------
        302  recursive calls
        137  db block gets
       6040  consistent gets
          0  physical reads
    1055332  redo size
        627  bytes sent via SQL*Net to client
        558  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       9891  rows processed
SQL> commit ;
Commit complete.

第一次查询数据：
SQL> select count(*) from a ;
  COUNT(*)
----------
     19782

Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
        255  consistent gets
        248  physical reads
  168  redo size　　　　--------------------------------->　？？？产生redo？？？
        395  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


第二次查询：

SQL> select count(*) from a ;
  COUNT(*)
----------
     19782

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        252  consistent gets
          1  physical reads
    0 redo size　　　　　　
        395  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


=================================================
如上所示，为什么在查询的时候会产生 redo ？ 产生的redo 到底是做什么的？
=================================================
----

取消 hint append 插入数据，第一次查询不会产生redo


SQL> insert into a select * from a ;

19782 rows created.


Statistics
----------------------------------------------------------
        112  recursive calls
      21100  db block gets
        699  consistent gets
          0  physical reads
    7149196  redo size
        642  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      19782  rows processed

SQL>
SQL>
SQL> select count(*) from a ;

  COUNT(*)
----------
     39564


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        502  consistent gets
          0  physical reads
          0  redo size
        395  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
---------------------------------------------------

对表做了truncate 操作后，第一次查询也出现 redo

SQL> truncate table a ;

Table truncated.

SQL>
SQL> select count(*) from a;

  COUNT(*)
----------
         0


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          6  consistent gets
          0  physical reads
       96  redo size
        392  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

----------------

====================================
在做个测试如下：
====================================

SQL> insert into a
  2  select * from a ;

129103 rows created.


Statistics
----------------------------------------------------------
        489  recursive calls
     137442  db block gets
       4058  consistent gets
       1516  physical reads
   46645744  redo size
        643  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     129103  rows processed

SQL> alter system checkpoint ;

System altered.

SQL>
SQL> select count(*) from a ;

  COUNT(*)
----------
    258206


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3241  consistent gets
       2790  physical reads
          0  redo size
        395  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit ;

Commit complete.

SQL> select count(*) from a ;

  COUNT(*)
----------
    258206


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4857  consistent gets
       2796  physical reads
      116484  redo size ------------------------------------> 第一次查询redo产生 （延迟块清除）
        395  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from a ;

  COUNT(*)
----------
    258206


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3241  consistent gets
       2746  physical reads
          0  redo size        
       395  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

-------------------

说白了就是数据块上的信息在前面还没来得及清理，select来帮它清理一下，既然select对数据块做了操作了，自然就要写redo了。

您可能感兴趣的文章: