Oracle教程：select 操作產(chǎn)生的 redo

數(shù)據(jù)庫版本：
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0

創(chuàng)建測試表：
SQL> create table a  as select * from all_objects  ;
Table created.
-
 

SQL> set autotrace on statistics ;

插入數(shù)據(jù)（ｈｉｎｔ　ａｐｐｅｎｄ）：

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.

第一次查詢數(shù)據(jù)：
SQL> select count(*) from a ;
  COUNT(*)
----------
     19782

Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
        255  consistent gets
        248  physical reads
  168  redo size　　　　--------------------------------->　？？？產(chǎn)生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

=================================================
如上所示，為什么在查詢的時候會產(chǎn)生 redo ？ 產(chǎn)生的redo 到底是做什么的？
=================================================
----

取消 hint append 插入數(shù)據(jù)，第一次查詢不會產(chǎn)生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 操作后，第一次查詢也出現(xiàn) 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>

----------------
簡單的說，在Oracle的block上都有活動事務(wù)的標(biāo)志的，如果一個事務(wù)commit后，由于某些block在commit之前已經(jīng)寫回datafile, 或者事務(wù)影響到的block數(shù)過多，則commi的時候只會清理undo segment header中的事務(wù)表信息，data block上的事務(wù)標(biāo)志不會清除，否則代價過高。那么在一些讀取這些block時，需要將這些事務(wù)標(biāo)志進(jìn)行清除，就是延遲塊清除
-------------------------
這個在用append引語的時候才會產(chǎn)生select的redo日志，說明在提交前已經(jīng)把數(shù)據(jù)塊給寫了，也進(jìn)一步說明了直插的模式，就是不走緩存，直接寫數(shù)據(jù)塊和回滾快。滿足延遲塊清除的第一個條件，就是還沒提交，數(shù)據(jù)已經(jīng)寫了。
---------------------------
====================================
在做個測試如下：
====================================

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產(chǎn)生 （延遲塊清除）
        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>

-------------------
說白了就是數(shù)據(jù)塊上的信息在前面還沒來得及清理，select來幫它清理一下，既然select對數(shù)據(jù)塊做了操作了，自然就要寫redo了。
 

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]