關於加快INSERT語句執行速度和 HINT /*+ append*/及nologging的使用

liglewang發表於2011-09-01

(非歸檔模式下)建立表T01

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

Table created.

(非歸檔模式下)檢視當前redo大小:

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

49784

(非歸檔模式下)普通INSERT語句插入:

SQL> insert into t01 select * from dba_objects;

11698 rows created.

(非歸檔模式下)檢視普通INSERT語句執行後,當前redo大小:

SQL> select value

from v$mystat,v$statname

2 3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size';

VALUE

----------

1305724

(非歸檔模式)採用HINT /*+ append*/執行INSERT語句:

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

11698 rows created.

(非歸檔模式)查詢帶/*+ append*/INSERT執行後,當前redo大小:

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size';

VALUE

----------

1308140

(非歸檔模式)計算使用HINT(/*+ append*/)和普通INSERT語句分別產生的redo大小:

SQL> select (1308140-1305724) redo_append,(1305724-49784) redo from dual;

REDO_APPEND REDO

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

2416 1255940

可見,在非歸檔模式下,INSERT語句採用HINT /*+ APPEND*/的方式確實比普通INSERT語句產生的redo要少很多。

=========================================

下面測試歸檔模式下

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch

Oldest online log sequence 109

Next log sequence to archive 111

Current log sequence 111

(歸檔模式)檢視當前redo大小:

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

19540

(歸檔模式)執行普通INSERT語句:

SQL> insert into t01 select * from dba_objects;

11698 rows created.

(歸檔模式)普通INSERT方式產生的redo大小:

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

1218884

(歸檔模式)採用HINT /*+ append*/執行INSERT語句:

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

11698 rows created.

(歸檔模式)查詢帶/*+ append*/INSERT執行後,當前redo大小:

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size';

VALUE

----------

2451664

(歸檔模式)計算使用HINT(/*+ append*/)和普通INSERT語句分別產生的redo大小:

SQL> select (2451664-1218884) redo_append,(1218884-19540) from dual;

REDO_APPEND (1218884-19540)

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

1232780 1199344

可見,在歸檔模式下,採用HINT與普通INSERT方式產生的redo量是相當的,且略大於普通INSERT語句產生的redo量。

=========================================

再看一種情況,歸檔模式,T01也是LOGGING模式

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

2541780

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

或者執行:

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

也將產生redo的量為:1233040

=========================================

最後一種情況:

(歸檔模式)修改T01的屬性為NOLOGGING

SQL> alter table t01 nologging;

Table altered.

SQL> select table_name,logging from dba_tables where table_name='T01';

TABLE_NAME LOG

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

T01 NO

(歸檔模式)檢視當前redo值:

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

5010600

(歸檔模式)執行普通INSERT語句:

SQL> insert into t01 select * from dba_objects;

11698 rows created.

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

6209848

(歸檔模式)採用HINT /*+ append*/執行INSERT語句:

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

11698 rows created.

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

6210536

(歸檔模式)計算兩者產生的redo差值:

SQL> select (6210536-6209848) redo_append,(6209848-5010600) redo from dual;

REDO_APPEND REDO

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

688 1199248

可見,在歸檔模式下,將表調整為NOLOGGING,產生的redo量是最少的!

對比一下:

非歸檔,T01(LOGGING),普通INSERT語句產生的redo值:1255940

非歸檔,T01(LOGGING)HINT /*+append*/,產生的redo值:2416

歸檔,T01(LOGGING),普通INSERT語句產生的redo值:1199344

歸檔,T01(LOGGING) HINT /*+ append*/,產生的redo值:1232780

歸檔,T01(LOGGING)/*+append*/ + nologging,產生的redo值:1233040

歸檔,T01(NOLOGGING),普通INSERT語句產生的redo值:688

歸檔,T01(NOLOGGING)HINT /*+append*/,產生的redo值:1199248

綜合以上的資料,可以明白,如果想INSERT語句執行的更快,產生更少的redo,分兩種情況:

非歸檔模式:在INSERT語句中使用HINT /*+ append */

歸檔模式:將目標表修改為NOLOGGINGalter table xxx nologging),然後在INSERT語句中使用HINT /*+ append */.

[@more@]

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25834554/viewspace-1054672/,如需轉載,請註明出處,否則將追究法律責任。

相關文章