好贷网好贷款

oracle 的nologging和append

发布时间:2016-12-4 16:29:08 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"oracle 的nologging和append",主要涉及到oracle 的nologging和append方面的内容,对于oracle 的nologging和append感兴趣的同学可以参考一下。

有时候我们为了提高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 processed2.使用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 processed3.使用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 processed4.使用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 processed2.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 processed2.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 processed3.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量可能会显著减少。

上一篇:chromium UI库简介
下一篇:java中的char占几个字节

相关文章

相关评论