ORACLE 10g append跟nologging插入对REDO的影响
ORACLE 10g append跟nologging插入对REDO的影响
/*+append*/
1. append 属于direct insert,归档模式下append+table nologging会大量减少日志,
非归档模式append会大量减少日志,append方式插入只会产生很少的undo
2.
综合一下吧：一是减少对空间的搜索；二是有可能减少redolog的产生。所以append方式会快很多，一般用于大数据量的处理 3. 建议不要经常使用append，这样表空间会一直在高水位上，除非你这个表只插不删 4.----------------------------------------------------------------------------------------------------------------------------------------------------
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
