Oracle教程：select 操作产生的 redo

文章由LinuxBoy分享于2019-03-30 05:03:37热评（289）

Oracle教程：select 操作产生的 redo

数据库版本：
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>

---------------- 简单的说，在oracle的block上都有活动事务的标志的，如果一个事务commit后，由于某些block在commit之前已经写回datafile, 或者事务影响到的block数过多，则commi的时候只会清理undo segment header中的事务表信息，data block上的事务标志不会清除，否则代价过高。那么在一些读取这些block时，需要将这些事务标志进行清除，就是延迟块清除
------------------------- 这个在用append引语的时候才会产生select的redo日志，说明在提交前已经把数据块给写了，也进一步说明了直插的模式，就是不走缓存，直接写数据块和回滚快。满足延迟块清除的第一个条件，就是还没提交，数据已经写了。 --------------------------- ====================================
在做个测试如下：
====================================

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了。

推荐文章：

Oracle教程：select 操作产生的 redo