nologging与append测试
来源：互联网 发布：mac转码软件 编辑：程序博客网 时间：2024/06/14 00:03
nologging与append测试
早前解决提升批量插入性能时的做的测试，翻出来，贴一下。
在对数据进行批量insert时，正确使用nologging和append，不仅能够极大的操作提高效率同时也能免去不必要的redo日志。通过以下4个实验看他们的正确使用。
环境：
OS：WindowsXP
DB：Oracle9.2.0.1
1，实验一：归档模式下，创建表不使用nologging
代码SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination E:\oracle\ora92\RDBMSOldest online log sequence 12Next log sequence to archive 14Current log sequence 14SQL> create table ttt as select object_id,object_name from dba_objects where 1=0;Table created.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 1469088 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 1469088 SQL> insert into ttt select object_id,object_name from dba_objects;30899 rows created.SQL> commit;Commit complete.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 2775240 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 2775240 SQL> insert /*+APPEND*/into ttt select object_id,object_name from dba_objects;30899 rows created.SQL> commit;Commit complete.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 4019400 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 4019400 SQL> select (2775240-1469088) noappend,(4019400-2775240) append from dual; NOAPPEND APPEND ---------- ---------- 1306152 1244160
总结：在归档模式下，创建表时不使用nologging参数，对日后的表操作没有实质性影响，加与不加apped参数生成的redo日志基本相同，效率得不到提升。
2，实验二：归档模式下，创建表使用nologging
代码SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination E:\oracle\ora92\RDBMSOldest online log sequence 12Next log sequence to archive 14Current log sequence 14SQL> create table tttt nologging as select object_id,object_name from dba_objects;Table created.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 7066492 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 7066492 SQL> insert into tttt select object_id,object_name from dba_objects;30900 rows created.SQL> commit;Commit complete.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 8345292 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 8345292 SQL> insert /*+APPEND*/into tttt select object_id,object_name from dba_objects;30900 rows created.SQL> commit;Commit complete.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 8399484 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 8399484 SQL> select (8399484-8345292) append,(8345292-7066492) noappend from dual; APPEND NOAPPEND ---------- ---------- 54192 1278800
3，实验三：非归档模式下，创建表不使用nologging
代码SQL> archive log listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination E:\oracle\ora92\RDBMSOldest online log sequence 12Current log sequence 14SQL> create table tt as select object_id,object_name from dba_objects where 1=0;Table created.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 3416124 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 3442132 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 3442132 SQL> insert /*+APPEND*/into tt 2 select object_id,object_name from dba_objects;30898 rows created.SQL> commit;Commit complete.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 3467448 SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 3519424 SQL> insert into tt select object_id,object_name from dba_objects;30898 rows created.SQL> commit;Commit complete.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 4798068 SQL> select (3467448-3442132) append,(4798068-3519424) noappend from dual; APPEND NOAPPEND ---------- ---------- 25316 1278644
4，实验四：非归档模式下，创建表使用nologging
代码SQL> archive log listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination E:\oracle\ora92\RDBMSOldest online log sequence 12Current log sequence 14SQL> create table tt nologging as select object_id,object_name from dba_objects where 1=0;Table created.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 246108 SQL> insert into tt select object_id,object_name from dba_objects;30900 rows created.SQL> commit;Commit complete.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 1552440 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 1552440 SQL> insert /*+APPEND*/into tt select object_id,object_name from dba_objects;30900 rows created.SQL> commit;Commit complete.SQL> column sid format 9999SQL> column name format a50SQL> column value format 999999999SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b 2 where a.statistic#=b.statistic# and a.name like 'redo size%'; SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 1580736 SQL> / SID NAME VALUE ----- -------------------------------------------------- ---------- 9 redo size 1580736 SQL> select (1580736-1552440) append,(1552440-246108) noappend from dual; APPEND NOAPPEND ---------- ---------- 28296 1306332
小结：
非归档模式下,不管创建表时nologging参数是否存在,只有使用append参数才能提高效率,减少redo生成量；
归档模式下,nologging和append参数同时使用，才能够提升效率，减少redo生成量。
原帖地址：http://www.cnblogs.com/daduxiong/archive/2010/08/26/1809198.html
0 0
- nologging与append测试
- append与nologging的使用
- Oracle中append与Nologging
- append与nologging的使用
- append与nologging的使用
- oracle nologging append 注意
- append+nologging方式
- Oracle Nologging And Append
- oracle 的nologging和append
- insert /*+append*/ 和nologging在归档和非归档模式下redo测试
- 表在nologging与logging模式下执行append插入，redo大小差别
- 快速向表中插入大量数据Oracle中append与Nologging
- 快速向表中插入大量数据Oracle中append与Nologging
- 快速向表中插入大量数据Oracle中append与Nologging
- 快速向表中插入大量数据Oracle中append与Nologging
- Oracle Nologging and Append 经典总结
- Oracle Nologging and Append 及锁介绍
- Nologging到底何时才能生效 append
- JS中如何定义全局变量
- 三星7420 cpu 做的手机源码你们去编译过吗？
- OpenCV 图像数据结构
- 欢迎使用CSDN-markdown编辑器
- SVG动态修改Text的文本
- nologging与append测试
- chromeOS上基于chroot的ubuntu配置笔记(1)
- 数据库的隔离级别
- Handler基本使用（三） Handler机制的原理和源码讲解
- js控制的Flv视频播放器源码下载
- 基于VM虚拟机搭建ubuntu
- 配置环境教程
- ThinkPHP数据回滚的使用简介
- 着色状态栏
