溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

ORACLE 10g append跟nologging插入對REDO的影響

發(fā)布時間：2020-08-08 19:50:24 來源：ITPUB博客 閱讀：105 作者：dongxinhjj 欄目：關(guān)系型數(shù)據(jù)庫

ORACLE 10g append跟nologging插入對REDO的影響

/*+append*/ 

1.  append 屬于direct insert,歸檔模式下append+table nologging會大量減少日志,

    非歸檔模式append會大量減少日志,append方式插入只會產(chǎn)生很少的undo

2.

綜合一下吧：一是減少對空間的搜索；二是有可能減少redolog的產(chǎn)生。所以append方式會快很多，一般用于大數(shù)據(jù)量的處理
3. 建議不要經(jīng)常使用append，這樣表空間會一直在高水位上，除非你這個表只插不刪
4.

 

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

 

oracle append有什么作用？

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

在使用了append選項以后，insert數(shù)據(jù)會直接加到表的最后面，而不會在表的空閑塊中插入數(shù)據(jù)。
使用append會增加數(shù)據(jù)插入的速度。
/*+APPEND*/的作用是在表的高水位上分配空間,不再使用表的extent中的空余空間
append 屬于direct insert,歸檔模式下append+table nologging會大量減少日志,非歸檔模式append會大量減少日志,append方式插入只會產(chǎn)生很少的undo
不去尋找 freelist 中的free block , 直接在table HWM 上面加入數(shù)據(jù)。

 

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

 

 

 

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的對比


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的對比

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


總結(jié):

 

 normal
 append
 nologging
 Append+nologging
 
 
Noarchive
 1162976
 94168
 1218368
 6976
 
 
Archive
 1160796
 1145456
 1162536
 2568
 
 

 

可以看出

1. 不管哪種模式下append要與nologging方式聯(lián)用才能達到很好的效果。

2. 非歸檔與歸檔方式，只用NOLOGGING是不起效果的。

3. 非歸檔下append已達到不錯的效果，但不及與nologging的聯(lián)用方式。

4. 歸檔下單append起不到效果。


NOLOGGING插完后最好做個備份。


另外，如果庫處在FORCELOGGING模式下，此時的nologging方式是無效的，這個我也測試過。


ITPUB上也有關(guān)于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*/系統(tǒng)到底會不會寫日志，
很多人說不寫日志，但怎么還有另外一個參數(shù)nologing,
如果真的沒有寫日志，也是很麻煩的

寫的日志量不同^_^.
匯總一下下面的例子中關(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

向AI問一下細節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手