重庆分公司,新征程启航
为企业提供网站建设、域名注册、服务器等服务
/*+append*/
成都创新互联公司服务项目包括秦淮网站建设、秦淮网站制作、秦淮网页制作以及秦淮网络营销策划等。多年来,我们专注于互联网行业,利用自身积累的技术优势、行业经验、深度合作伙伴关系等,向广大中小型企业、政府机构等提供互联网行业的解决方案,秦淮网站推广取得了明显的社会效益与经济效益。目前,我们服务的客户以成都为中心已经辐射到秦淮省份的部分城市,未来相信会继续扩大服务区域并继续获得客户的支持与信任!
1. append 属于direct insert,归档模式下append+table nologging会大量减少日志,
非归档模式append会大量减少日志,append方式插入只会产生很少的undo
2.
----------------------------------------------------------------------------------------------------------------------------------------------------
oracle append有什么作用?
----------------------------------------------------------------------------------------------------------------------------------------------------
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