oracle 的nologging和append

最新推荐文章于 2021-04-14 13:20:13 发布

北京-agan

最新推荐文章于 2021-04-14 13:20:13 发布

阅读量1.2k

点赞数

文章标签： nologging append

本文链接：https://blog.csdn.net/aizaikuerle/article/details/10401693

版权

有时候我们为了提高DML的速度，我们会采取不记录日志的方式，下面我们就来做个实验，看下NOLOGGING和append的作用。

创建实验用表

SQL> create table test11 as select * from dba_onjects where 1=2;

查看数据库的force_logging模式状态

SQL> select force_logging from v$database;

FOR
---
NO

数据库为非强制日志状态

查看表的日志状态

SQL> select table_name,logging from dba_tables where table_name = 'TEST11';

TABLE_NAME		       LOG
------------------------------ ---
TEST11			       YES

表为记录日志状态

一，下面是四种数据插入语句后的redo size （1.普通 2.使用nologging 3.使用append hint 4. 使用append hint和nologging ）

1.普通

SQL> insert into test11 select * from dba_objects;

Statistics
----------------------------------------------------------
       2061  recursive calls
       6972  db block gets
       7136  consistent gets
	  2  physical reads
    5730388  redo size
	673  bytes sent via SQL*Net to client
	580  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	 21  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

2.使用nologging

SQL> insert into test11 select * from dba_objects nologging;

Statistics
----------------------------------------------------------
	266  recursive calls
       6320  db block gets
       6530  consistent gets
	  0  physical reads
    5693384  redo size
	676  bytes sent via SQL*Net to client
	590  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

3.使用append

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

Statistics
----------------------------------------------------------
	272  recursive calls
	952  db block gets
       5625  consistent gets
	  0  physical reads
    5739768  redo size
	661  bytes sent via SQL*Net to client
	592  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

4.使用append + nologing

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

Statistics
----------------------------------------------------------
	220  recursive calls
	863  db block gets
       5608  consistent gets
	  0  physical reads
    5734160  redo size
	663  bytes sent via SQL*Net to client
	605  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

二，下面修改表的logging模式

SQL> alter table test11 nologging;

再看四种数据插入语句后的redo size （1.普通语句 2.使用nologging 3.使用append hint 4. 使用append hint和nologging ）

1.普通

SQL> insert  into test11 select * from dba_objects ;

Statistics
----------------------------------------------------------
       1989  recursive calls
       6985  db block gets
       7068  consistent gets
	  2  physical reads
    5730840  redo size
	679  bytes sent via SQL*Net to client
	582  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	 21  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

2.nologging
SQL> insert into test11 select * from dba_objects nologging;

Statistics
----------------------------------------------------------
	258  recursive calls
       6319  db block gets
       6466  consistent gets
	  0  physical reads
    5692036  redo size
	679  bytes sent via SQL*Net to client
	591  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

2.append hint

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

Statistics
----------------------------------------------------------
	888  recursive calls
       1269  db block gets
       5820  consistent gets
	  2  physical reads
      57180  redo size
	663  bytes sent via SQL*Net to client
	593  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

3.append + nologging

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

Statistics
----------------------------------------------------------
	888  recursive calls
       1269  db block gets
       5820  consistent gets
	  2  physical reads
      57240  redo size
	663  bytes sent via SQL*Net to client
	606  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

三，现在将数据模式修改为强制日志（force_logging）

SQL> alter database force logging;

然后再看append 和 append+logging

1.append hint

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

Statistics
----------------------------------------------------------
	888  recursive calls
       1269  db block gets
       5820  consistent gets
	  2  physical reads
    5790452  redo size
	663  bytes sent via SQL*Net to client
	593  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

2.append + nologging

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

Statistics
----------------------------------------------------------
	888  recursive calls
       1271  db block gets
       5820  consistent gets
	  2  physical reads
    5782404  redo size
	663  bytes sent via SQL*Net to client
	606  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
      50604  rows processed

通过前面三组实验，我们可以得到下面的结论

1.数据库database如果是force_logging模式，那么数据库会忽略nologging标记，而记录任何操作的日志。

2.nologging关键词必须是在create table 和 alter table 中使用才有效。

3.在DML语句中使用NOLOGGING其实是起不到作用的。

4.使用直接路径加载append可以发挥计入最小日志(nologging)的作用。

另外，我们既然设置了nologging，就应该不产生日志才对啊，为什么还会产生少量的日志呢？

这是因为其实所有的操作都会记录redo-----不论日志模式是什么，所有数据字典操作都会计入日志，只不过使用NOLOGGING之后，生成的redo量可能会显著减少。