[Oracle] Insert into速度測試nologging+parallel

C:\Users\Admin>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 10月 4 13:24:02 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list;
資料庫日誌模式            存檔模式
自動存檔             啟用
存檔終點            C:\oracle\arch
最早的聯機日誌序列     1651
下一個存檔日誌序列   1653
當前日誌序列           1653

SQL> create table bruce5 as select * from bruce where 1=2;

表已建立。

SQL> insert into bruce5 select * from bruce;

已建立83324928行。

已用時間:  00: 09: 06.82

SQL> commit;

提交完成。

SQL>
SQL> select segment_name,bytes/1024/1024/1024 from user_segments;

SEGMENT_NAME         BYTES/1024/1024/1024
-------------------- --------------------
BRUCE                               8.875
BRUCE1                             8.9375
BRUCE2                         8.88769531
BRUCE3                         8.88317871
BRUCE4                         8.88317871
BRUCE5                              8.875


SQL> create table bruce6 as select * from bruce where 1=2;

表已建立。

SQL> insert /*+nologging*/ into bruce6 select * from bruce;

已建立83324928行。

已用時間:  00: 10: 17.63
SQL> commit;

提交完成。

SQL> truncate table bruce6;

表被截斷。

已用時間:  00: 00: 00.64
SQL> insert /*+append nologging*/ into bruce6 select * from bruce;

已建立83324928行。

已用時間:  00: 08: 07.45
SQL> commit;

提交完成。

SQL> alter table bruce6 nologging;

表已更改。

已用時間:  00: 00: 00.03
SQL> select table_name,logging from user_tables where table_name='BRUCE6';

TABLE_NAME                                                   LOGGIN
------------------------------------------------------------ ------
BRUCE6                                                       NO


SQL> insert /*+ append */ into bruce6 select * from bruce;

已建立83324928行。

已用時間:  00: 02: 46.54
SQL> commit;

提交完成。

已用時間:  00: 00: 00.06

SQL> truncate table bruce6;

表被截斷。

已用時間:  00: 00: 00.29
SQL> insert into bruce6 select * from bruce;

已建立83324928行。

已用時間:  00: 09: 47.17
SQL> commit;

提交完成。

已用時間:  00: 00: 00.00


SQL> truncate table bruce6;

表被截斷。

已用時間:  00: 00: 00.53
SQL> select logging from user_tables where table_name='BRUCE6';

LOGGIN
------
NO

已用時間:  00: 00: 00.03
SQL> insert /*+ append parallel(bruce6 2) */ into bruce6 select * from bruce;

已建立83324928行。

已用時間:  00: 02: 49.58
SQL> commit;

提交完成。

已用時間:  00: 00: 00.06
SQL> truncate table bruce6;

表被截斷。

已用時間:  00: 00: 00.26


已用時間:  00: 00: 00.00
SQL> alter session enable parallel dml;

會話已更改。

已用時間:  00: 00: 00.01

SQL> insert /*+ append parallel(bruce6 2) */ into bruce6 select * from bruce;

已建立83324928行。

已用時間:  00: 05: 03.96

SQL> commit;

提交完成。

已用時間:  00: 00: 00.03


SQL> alter table bruce6 parallel 2;

表已更改。

已用時間:  00: 00: 00.00
SQL>
SQL> select logging,degree from user_tables where table_name='BRUCE6';

LOGGIN DEGREE
------ --------------------------------------------------------------------------------
NO              2

已用時間:  00: 00: 00.01
SQL> ALTER SESSION ENABLE PARALLEL DML;

會話已更改。

已用時間:  00: 00: 00.00
SQL> insert /*+ append */ into bruce6 select * from bruce;

已建立83324928行。

已用時間:  00: 02: 46.48
SQL> COMmit;

提交完成。

已用時間:  00: 00: 00.01



---------------------------------------------------------------------
table是logging     |  普通情況                 |   09: 06.82
table是logging     |  /*+append nologging*/    |   08: 07.45
table是nologging   |  /*+append*/              |   02: 46.54
table是nologging   |  無hint                   |   09: 47.17
table是nologging + parallel  |  /*+ append parallel(bruce6 2) */   |  05: 03.96            時間更長，會使用temp表空間的資料
table是nologging + parallel  |  /*+ append */                      |  02: 46.48


(1) alter table nologging + append(direct insert) 可以大幅提高insert資料
(2) alter table nologging + append(direct insert) + parallel 沒有 (1)的情況好，但是比普通insert的資料塊


SQL> select logging,degree from user_tables where table_name='BRUCE6';

LOGGIN      DEGREE
--------------------------------------------------------------------------------
NO          2

SQL> alter session enable parallel dml;

會話已更改。

已用時間:  00: 00: 00.00
SQL> insert /*+ append */ into bruce6 select /*+parallel(bruce 2)*/ * from bruce
  2  ;

已建立83324928行。

已用時間:  00: 00: 58.06


SQL> select logging,degree from user_tables where table_name='BRUCE7';

LOGGIN DEGREE
------ --------------------------------------------------------------------------------
YES             1

SQL> select logging,degree from user_tables where table_name='BRUCE7';

LOGGIN DEGREE
------ --------------------------------------------------------------------------------
NO              1

已用時間:  00: 00: 00.01
SQL> insert /*+ append */ into bruce7 select /*+parallel(bruce 2)*/ * from bruce;

已建立83324928行。

已用時間:  00: 01: 02.75
SQL> commit;

提交完成。

已用時間:  00: 00: 00.07

在後面的select中加上/*+parallel(bruce 2)*/ 還是可以加快速度的.
透過上面的實驗 insert 大量的資料的時候，透過以下幾個方面可以加快資料
(1) alter table xxxx nologging
(2) alter table xxxx parallel n;
(3) insert後面的select 中加上/*+parallel (xxxx n)*/ 的hint可以加快速度
(4) 執行insert之前記得  alter session enable parallel dml;
(5) 執行完成之後，切記將 table的logging和degree屬性改回來