ORACLE10gappend跟nologging插入对REDO的影响

ORACLE 10g append跟nologging插入对REDO的影响

/*+append*/

成都创新互联公司服务项目包括秦淮网站建设、秦淮网站制作、秦淮网页制作以及秦淮网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，秦淮网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到秦淮省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！

1. append 属于direct insert,归档模式下append+table nologging会大量减少日志,

非归档模式append会大量减少日志,append方式插入只会产生很少的undo

综合一下吧：一是减少对空间的搜索；二是有可能减少redolog的产生。所以append方式会快很多，一般用于大数据量的处理

3. 建议不要经常使用append，这样表空间会一直在高水位上，除非你这个表只插不删

----------------------------------------------------------------------------------------------------------------------------------------------------

oracle append有什么作用？

请教一下，oracle中append是做什么用的。
insert /*+append*/ into table1 select * from table2

在使用了append选项以后，insert数据会直接加到表的最后面，而不会在表的空闲块中插入数据。
使用append会增加数据插入的速度。

/*+APPEND*/的作用是在表的高水位上分配空间,不再使用表的extent中的空余空间
append 属于direct insert,归档模式下append+table nologging会大量减少日志,非归档模式append会大量减少日志,append方式插入只会产生很少的undo

不去寻找 freelist 中的free block , 直接在table HWM 上面加入数据。

----------------------------------------------------------------------------------------------------------------------------------------------------

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

1.非归档模式append,nologging,append+nologging 三种情况数据产生REDO的对比

PHP:

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Current log sequence           15

SQL> create table t as select * from dba_objects where 1=2;

Table created.

SQL> select name,value,class from v$sysstat where name='redo size';

NAME VALUE CLASS
---------- ---------- ----------
redo size 582728 2

SQL> insert into t select * from dba_objects;

10947 rows created.

SQL> select name,value,class from v$sysstat where name='redo size';

NAME VALUE CLASS
---------- ---------- ----------
redo size 1745704 2

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

10947 rows created.

SQL> select name,value,class from v$sysstat where name='redo size';

NAME VALUE CLASS
---------- ---------- ----------
redo size 1839872 2

SQL> select (1745704-582728) redo1,(1839872-1745704) redo2 from dual;

REDO1 REDO2
---------- ----------
1162976 94168

SQL> drop table t;

Table dropped.

SQL> create table t nologging as select * from dba_objects where 1=2;

Table created.

SQL> select name,value,class from v$sysstat where name='redo size';

NAME VALUE CLASS
---------- ---------- ----------
redo size 3441836 2

SQL> insert into t select * from dba_objects;

10947 rows created.

SQL> select name,value,class from v$sysstat where name='redo size';

NAME VALUE CLASS
---------- ---------- ----------
redo size 4660204 2

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

10947 rows created.

SQL> select name,value,class from v$sysstat where name='redo size';

NAME VALUE CLASS
---------- ---------- ----------
redo size 4667180 2

SQL> select (4660204-3441836) redo1,(4667180-4660204) redo2 from dual;

REDO1 REDO2
---------- ----------
1218368 6976

2.归档下:append,nologging,append+nologging 三种情况数据产生REDO的对比

PHP语言:SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> create table t as select * from dba_objects where 1=2;

Table created.

SQL> select name,value from v$sysstat where name='redo size';

NAME VALUE
---------- ----------
redo size 155624

SQL> insert into t select * from dba_objects;

10947 rows created.

SQL> select name,value from v$sysstat where name='redo size';

NAME VALUE
---------- ----------
redo size 1316420

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

10947 rows created.

SQL> select name,value from v$sysstat where name='redo size';

NAME VALUE
---------- ----------
redo size 2461876

SQL> select (1316420-155624) redo1,(2461876-1316420) redo2 from dual;

REDO1 REDO2
---------- ----------
1160796 1145456

SQL> truncate table t;

Table truncated.

SQL> alter table t nologging;

Table altered.

SQL> select name,value from v$sysstat where name='redo size';

NAME VALUE
---------- ----------
redo size 2505320

SQL> insert into t select * from dba_objects;

10947 rows created.

SQL> select name,value from v$sysstat where name='redo size';

NAME VALUE
---------- ----------
redo size 3667856

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

10947 rows created.

SQL> select name,value from v$sysstat where name='redo size';

NAME VALUE
---------- ----------
redo size 3670424

SQL> select (3667856-2505320) redo1,(3670424-3667856) redo2 from dual;

REDO1 REDO2
---------- ----------
1162536 2568

总结:

normal
append
nologging
Append+nologging

Noarchive
1162976
94168
1218368
6976

Archive
1160796
1145456
1162536
2568

可以看出

1. 不管哪种模式下append要与nologging方式联用才能达到很好的效果。

2. 非归档与归档方式，只用NOLOGGING是不起效果的。

3. 非归档下append已达到不错的效果，但不及与nologging的联用方式。

4. 归档下单append起不到效果。

NOLOGGING插完后最好做个备份。

另外，如果库处在FORCELOGGING模式下，此时的nologging方式是无效的，这个我也测试过。

ITPUB上也有关于NOLOGGING何时生效的讨论

http://www.itpub.net/showthread.php?threadid=239905

eygle也做过这个实验

http://www.eygle.com/faq/Nologging&append.htm

Originally posted by jwzl at 2004-12-6 09:02:
我想确认一下/*+append*/系统到底会不会写日志，
很多人说不写日志，但怎么还有另外一个参数nologing,
如果真的没有写日志，也是很麻烦的

写的日志量不同^_^.
汇总一下下面的例子中关于redo log的信息.
pure insert               587624
insert with nologging  585496
insert with append 2240
insert with append & nologging 400

[quote]
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> drop table t;

Table dropped.
SQL> create table t nologging as select * from dba_objects where null = null;

Table created.

SQL> set autot traceonly stat
SQL> insert into t select * from dba_objects;

5888 rows created.

Statistics
----------------------------------------------------------
      271  recursive calls
      773  db block gets
   12653  consistent gets
      0  physical reads
   587624  redo size
      617  bytes sent via SQL*Net to client
      539  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      2  sorts (memory)
      0  sorts (disk)
   5888  rows processed

SQL> insert into t nologging select * from dba_objects;

5888 rows created.

Statistics
----------------------------------------------------------
      28  recursive calls
      721  db block gets
   12654  consistent gets
      0  physical reads
   585496  redo size
      618  bytes sent via SQL*Net to client
      549  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
   5888  rows processed

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

5888 rows created.

Statistics
----------------------------------------------------------
      29  recursive calls
      31  db block gets
   12526  consistent gets
      0  physical reads
   2240  redo size
      603  bytes sent via SQL*Net to client
      551  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
   5888  rows processed

SQL> commit;

Commit complete.

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

5888 rows created.

Statistics
----------------------------------------------------------
      7  recursive calls
      8  db block gets
   12517  consistent gets
      0  physical reads
      400  redo size
      603  bytes sent via SQL*Net to client
      561  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
   5888  rows processed

SQL> commit;

Commit complete.

SQL> exit

当前文章：ORACLE10gappend跟nologging插入对REDO的影响
本文链接：http://kswsj.cn/article/gigeho.html

关于创新互联

ORACLE10gappend跟nologging插入对REDO的影响

ORACLE 10g append跟nologging插入对REDO的影响

其他资讯