/*+append*/
成都創(chuàng)新互聯(lián)公司服務(wù)項(xiàng)目包括秦淮網(wǎng)站建設(shè)、秦淮網(wǎng)站制作、秦淮網(wǎng)頁(yè)制作以及秦淮網(wǎng)絡(luò)營(yíng)銷策劃等。多年來(lái)，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，秦淮網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到秦淮省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
1. append 屬于direct insert,歸檔模式下append+table nologging會(huì)大量減少日志,
非歸檔模式append會(huì)大量減少日志,append方式插入只會(huì)產(chǎn)生很少的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 三種情況數(shù)據(jù)產(chǎn)生REDO的對(duì)比
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 三種情況數(shù)據(jù)產(chǎn)生REDO的對(duì)比
PHP語(yǔ)言: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
總結(jié):
normal
append
nologging
Append+nologging
Noarchive
1162976
94168
1218368
6976
Archive
1160796
1145456
1162536
2568
可以看出
1. 不管哪種模式下append要與nologging方式聯(lián)用才能達(dá)到很好的效果。
2. 非歸檔與歸檔方式，只用NOLOGGING是不起效果的。
3. 非歸檔下append已達(dá)到不錯(cuò)的效果，但不及與nologging的聯(lián)用方式。
4. 歸檔下單append起不到效果。
NOLOGGING插完后最好做個(gè)備份。
另外，如果庫(kù)處在FORCELOGGING模式下，此時(shí)的nologging方式是無(wú)效的，這個(gè)我也測(cè)試過(guò)。
ITPUB上也有關(guān)于NOLOGGING何時(shí)生效的討論
http://www.itpub.net/showthread.php?threadid=239905
eygle也做過(guò)這個(gè)實(shí)驗(yàn)
http://www.eygle.com/faq/Nologging&append.htm
Originally posted by jwzl at 2004-12-6 09:02:
我想確認(rèn)一下/*+append*/系統(tǒng)到底會(huì)不會(huì)寫(xiě)日志，
很多人說(shuō)不寫(xiě)日志，但怎么還有另外一個(gè)參數(shù)nologing,
如果真的沒(méi)有寫(xiě)日志，也是很麻煩的
寫(xiě)的日志量不同^_^.
匯總一下下面的例子中關(guān)于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
重慶分公司
重慶分公司
