insert /*+append*/ into产生日志的疑问

蓝衣子龙

数据库版本10.2.0.2
书上说使用直接路径插入在标记为nologging的表上执行操作是才能绕过重做日志的生成
故测试如下；
1.创建表
  create table rainbow_objects as select * from dba_objects where 1=2;
2.检查该表属性
  SQL> select LOGGING from dba_tables where table_name='RAINBOW_OBJECTS';

   LOGGING
  ---------
  YES
3.set autotrace traceonly statistics

4.测试插入产生的redo
   方法1 正常插入
   SQL> insert into rainbow_objects select * from dba_objects;

  40352 rows created.


  Statistics
  ----------------------------------------------------------
       1419  recursive calls
       5650  db block gets
       4104  consistent gets
          4  physical reads
   4597764  redo size
        840  bytes sent via SQL*Net to client
        749  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
      40352  rows processed

  方法2 直接路径插入
  

SQL> insert /*+append*/ into rainbow_objects select * from dba_objects;

40352 rows created.


Statistics
----------------------------------------------------------
        184  recursive calls
        699  db block gets
       2750  consistent gets
          0  physical reads
      13692  redo size
        824  bytes sent via SQL*Net to client
        764  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      40352  rows processed

结果发现redo size 得到了明显的减少，这是为什么，我的表还没设置为nologging啊

owen_fei

很奇怪哦，而且direct insert好像是工作于Database Noarchivelog + Table Nologging mode,大家给点意见呢

蓝衣子龙

我加了nolgging后反而生成的日志更多了，我日
SQL> alter table rainbow_objects nologging;

Table altered.

SQL> insert /*+append*/ into rainbow_objects select * from dba_objects;

40352 rows created.


Statistics
----------------------------------------------------------
        452  recursive calls
        722  db block gets
       2816  consistent gets
          0  physical reads
      16768  redo size
        824  bytes sent via SQL*Net to client
        764  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      40352  rows processed

     16768  redo size

owen_fei

把database 换成Noarchivelog mode 试试看

rollingpig

看看这个
http://www.itpub.net/242761.html

jolly10

原帖由 蓝衣子龙 于 2008-4-28 16:31 发表
数据库版本10.2.0.2
书上说使用直接路径插入在标记为nologging的表上执行操作是才能绕过重做日志的生成
故测试如下；
1.创建表
  create table rainbow_objects as select * from dba_objects where 1=2;
2.检查该表属性
  SQL> select LOGGING from dba_tables where table_name='RAINBOW_OBJECTS';

   LOGGING
  ---------
  YES
3.set autotrace traceonly statistics

4.测试插入产生的redo
   方法1 正常插入
   SQL> insert into rainbow_objects select * from dba_objects;

  40352 rows created.


  Statistics
  ----------------------------------------------------------
       1419  recursive calls
       5650  db block gets
       4104  consistent gets
          4  physical reads
    4597764  redo size
        840  bytes sent via SQL*Net to client
        749  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
      40352  rows processed

  方法2 直接路径插入
  

SQL> insert /*+append*/ into rainbow_objects select * from dba_objects;

40352 rows created.


Statistics
----------------------------------------------------------
        184  recursive calls
        699  db block gets
       2750  consistent gets
          0  physical reads
      13692  redo size
        824  bytes sent via SQL*Net to client
        764  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      40352  rows processed

结果发现redo size 得到了明显的减少，这是为什么，我的表还没设置为nologging啊

你的database是noarchivelog模式吧,在noarchivelog模式下是这样的,和表是否nologging没有关系,只要直接加载产短的log就会少.

蓝衣子龙

原帖由 jolly10 于 2008-4-28 16:59 发表


你的database是noarchivelog模式吧,在noarchivelog模式下是这样的,和表是否nologging没有关系,只要直接加载产短的log就会少.

楼上说的是正解，忘了我的数据库是noarchive的

owen_fei

学习了

myfriend2010

在归档模式下,表nologging+ append 不写redo!
在非归档模式下,append就直接不写redo!

蓝衣子龙

谢谢大家的帮助，现在给出在archive log 下的测试结果
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archivelog
Oldest online log sequence     70
Next log sequence to archive   72
Current log sequence           72

SQL> alter table rainbow_objects logging;

Table altered.

Elapsed: 00:00:00.04
SQL>  set autotrace traceonly statistics;
SQL> insert into rainbow_objects select * from object;

3712476 rows created.

Elapsed: 00:01:04.59

Statistics
----------------------------------------------------------
       9706  recursive calls
    2626845  db block gets
     194131  consistent gets
         11  physical reads
  853455224  redo size
        837  bytes sent via SQL*Net to client
        744  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
    3712476  rows processed

SQL> insert /*+append*/ into rainbow_objects select * from object;

3712476 rows created.

Elapsed: 00:01:34.46

Statistics
----------------------------------------------------------
       3125  recursive calls
     364638  db block gets
      89578  consistent gets
      28409  physical reads
  749863644  redo size
        825  bytes sent via SQL*Net to client
        756  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
    3712476  rows processed

SQL> alter table rainbow_objects nologging;

Table altered.

Elapsed: 00:00:00.15
SQL> insert /*+append*/ into rainbow_objects select * from object;

3712476 rows created.

Elapsed: 00:01:14.54

Statistics
----------------------------------------------------------
       2300  recursive calls
     352082  db block gets
      91891  consistent gets
      28409  physical reads
  285545596  redo size
        826  bytes sent via SQL*Net to client
        756  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          7  sorts (memory)
          1  sorts (disk)
    3712476  rows processed

SQL> insert into rainbow_objects select * from object；

3712476 rows created.

Elapsed: 00:04:50.75

Statistics
----------------------------------------------------------
        306  recursive calls
    9149717  db block gets
     152892  consistent gets
          0  physical reads
1100134184  redo size
        843  bytes sent via SQL*Net to client
        744  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    3712476  rows processed



确实在archive 下只有nologging和append 一起使用时才能大幅度减少redo的产生

[ 本帖最后由 蓝衣子龙 于 2008-4-28 17:46 编辑 ]