構建百萬千萬級表的多種方法及其效能對比分析

前兩個實驗是前一段自己做的。後面的實驗是一本書上的。
實驗環境：虛擬機器，LINUX+ORACLE 11G
說明：每個實驗完成後，需要及時刪除表，以便進行下一個實驗。 我這裡節約篇幅，省略了。
刪除重新開始下一個實驗
drop table test1 purge;
drop table test2 purge;
truncate table test3;

drop table test3 purge;

清除緩衝區和共享池

alter system flush buffer_cache;
alter system flush shared_pool; --這裡只清共享池就可以。

關於硬解析與軟解析-來自網路：

一次硬解析，多次軟解析，Session_cache_cursor設定為0，最容易發生，軟解析的代價是，每次要在library cache中定位遊標。

一次硬解析，多次軟解析，Session_cache_cursor設定為非空值，PGA中保留了指向library cache的指標，直接定位子游標。

一次解析，多次執行，保持遊標開啟（pin住記憶體堆），沒有定位自由表的過程，容易造成開啟遊標數過多，要記得程式碼的最後關閉遊標。

 select t.sql_text, t.sql_id,t.PARSE_CALLS, t.EXECUTIONS  from v$sql t  where sql_text like '%insert into test1 %' and rownum <10;

方法一：使用笛卡爾積，速度很快。

BYS@ bys001>create table test1 as select rownum a from dual connect by rownum<1001;
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>create table test3 as select a.* from test1 a,test1 b;
Table created.

Elapsed: 00:00:01.63
BYS@ bys001>select count(*) from test3;
  COUNT(*)
----------
   1000000
##########################################################################

方法二：利用自查詢插入，速度比較慢。

BYS@ bys001>create table test1 as select rownum a from dual connect by rownum<10001;
Table created.
Elapsed: 00:00:00.25
begin
for i in 1 .. 10 loop
insert into test1 select * from test1;
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:43.62
BYS@ bys001>select count(*) from test1;
  COUNT(*)
----------
   1024000
Elapsed: 00:00:00.02

插入速度是每秒兩萬多條
BYS@ bys001>select 1024000/43 from dual;
1024000/43
----------
23813.9535
19:34:16 SQL>  select t.sql_text, t.sql_id,t.PARSE_CALLS, t.EXECUTIONS
           2    from v$sql t
           3   where sql_text like '%INSERT INTO TEST1%' and rownum <10;          

SQL_TEXT                       SQL_ID        PARSE_CALLS EXECUTIONS
------------------------------ ------------- ----------- ----------

INSERT INTO TEST1 SELECT * FRO cwjfx8x2zfvq1           1         10

批量提交節約了每次提交時 commit耗費的時間。
但是在大DML事務時，要注意延遲塊清除可能引起的ORA-01555錯誤。在此不多說這個問題。
########################################################

方法三：建立儲存過程，未使用繫結變數。速度很慢

這裡只插入100萬條資料來測試，因為插入1000萬條資料需要時間太長（我虛擬機器是一個小時左右）。

create or replace procedure proc_test1
as
begin
for i in 1 .. 1000000
loop
execute immediate
'insert into test1 values ( '||i||')';
commit;
end loop;
end;
/

Procedure created.
BYS@ bys001>create table test1(a number);
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>exec proc_test1;

PL/SQL procedure successfully completed.

Elapsed: 00:13:03.43
BYS@ bys001>select count(*) from test1;
  COUNT(*)
----------
   1000000

每秒插入一千多條資料，資料條數除所用時間
BYS@ bys001>select 1000000/13/60 from dual;
1000000/13/60
-------------
   1282.05128

查詢共享池中有快取，可以看到每個語句都是隻解析一次，執行一次。
整個儲存過程解析了100萬次，所以耗時很長。
SQL>  select t.sql_text, t.sql_id,t.PARSE_CALLS, t.EXECUTIONS
  2    from v$sql t
  3   where sql_text like '%insert into test1 %' and rownum <10;

SQL_TEXT                                          SQL_ID        PARSE_CALLS EXECUTIONS
------------------------------------------------ ------------- ----------- ----------
insert into test1 values ( 991386)                4d4ywgu81n009           1          1
insert into test1 values ( 997580)                 0mg9u6mx6s00j           1          1
insert into test1 values ( 997063)                 gfkpbx0av000w           1          1
insert into test1 values ( 992660)                 3pruwwdb00026           1          1
insert into test1 values ( 997621)                27acn7hja802u           1          1
###########################################################################

方法四：使用繫結變數，一次解析，多次執行。

create or replace procedure proc_test1
as
begin
for i in 1 .. 1000000
loop
execute immediate
'insert into test1 values (:aaa)' using i;
commit;
end loop;
10  end;
11  /

Procedure created.

Elapsed: 00:00:02.02
BYS@ bys001>create table test1(a number);
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>exec proc_test1;
PL/SQL procedure successfully completed.
Elapsed: 00:04:20.11

查詢共享池中有快取，可以看到每個語句都是隻解析一次，執行一百萬次。
SQL> select t.sql_text, t.sql_id,t.PARSE_CALLS, t.EXECUTIONS
  2    from v$sql t
  3   where sql_text like '%insert into test1 %' and rownum <10;

SQL_TEXT                       SQL_ID        PARSE_CALLS EXECUTIONS
------------------------------ ------------- ----------- ----------
insert into test1 values (:aaa 7mj20sj5apmsf）           0    1000000                                          
每秒可以插入將近4千條資料，速度是未使用繫結變數時的3倍多點。
BYS@ bys001>select 1000000/260 from dual;
1000000/260
-----------
3846.15385
###########################################################

方法五：使用靜態SQL，速度和上一個實驗動態SQL相比略有提升。

create or replace procedure proc_test1
as
begin
for i in 1 .. 1000000
loop
insert into test1 values (i) ;
commit;
end loop;
end;
/

Warning: Procedure created with compilation errors.
Elapsed: 00:00:00.84
BYS@ bys001>show error
Errors for PROCEDURE PROC_TEST1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1      PL/SQL: SQL Statement ignored
6/13     PL/SQL: ORA-00942: table or view does not exist
BYS@ bys001>create table test1(a number);
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>exec proc_test1;
PL/SQL procedure successfully completed.
Elapsed: 00:04:13.53

這次沒有使用execute immediate
execute immediate是動態SQL寫法，常用於表名欄位名是變數等情況。
動態SQL特點是執行過程中解析，靜態SQL是編譯過程就解析，
所以速度又有一點提升。
同樣也用到了繫結變數，一次解析，多次執行。
19:04:33 SQL> select t.sql_text, t.sql_id,t.PARSE_CALLS, t.EXECUTIONS
           2    from v$sql t
           3   where sql_text like '%INSERT INTO TEST1%' and rownum <10;
SQL_TEXT                       SQL_ID        PARSE_CALLS EXECUTIONS
------------------------------ ------------- ----------- ----------
INSERT INTO TEST1 VALUES (:B1  b9vjmmkg8ffhg           0    1000000)                                                  
BYS@ bys001>select 1000000/253 from dual;
1000000/253
-----------

3952.56917

##############################################################

方法六：使用批量插入後再使用一個commit的方法, 極大提高了插入資料的效能。

因為前面的實驗時每次插入都要做一個commit,整個儲存過程就需要一百萬次commit,
即使每次commit所需時間很短，提交一百萬次時間也比較可觀了。
create or replace procedure proc_test1
as
begin
for i in 1 .. 1000000
loop
insert into test1 values (i) ;
end loop;
commit;
end;
/

Warning: Procedure created with compilation errors.
Elapsed: 00:00:00.14
這是因為先建立過程還沒建立表，不用管就可以。
BYS@ bys001>show error
Errors for PROCEDURE PROC_TEST1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1      PL/SQL: SQL Statement ignored
6/13     PL/SQL: ORA-00942: table or view does not exist
BYS@ bys001>create table test1(a number);
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>exec proc_test1;
PL/SQL procedure successfully completed.
Elapsed: 00:01:21.39

19:12:38 SQL>  select t.sql_text, t.sql_id,t.PARSE_CALLS, t.EXECUTIONS
           2    from v$sql t
           3   where sql_text like '%INSERT INTO TEST1%' and rownum <10;

SQL_TEXT                       SQL_ID        PARSE_CALLS EXECUTIONS
------------------------------ ------------- ----------- ----------                 
INSERT INTO TEST1 VALUES (:B1  b9vjmmkg8ffhg           0    1000000                                                     
BYS@ bys001>select 1000000/81 from dual;
1000000/81
----------
12345.679
##########################################

方法七：這種方法是把原來的過程變成了單條SQL，把一條一條插入的語句變成一個集合，

成批次的寫入data buffer區，再次提高了速度。

BYS@ bys001>create table test1(a number);
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>insert into test1 select rownum from dual connect by rownum<1000001;
1000000 rows created.
Elapsed: 00:00:04.46
BYS@ bys001>commit;
BYS@ bys001>select count(*) from test1;
  COUNT(*)
----------
   1000000
#############################################

方法八：使用CREATE TABLE as select的方法來建立表更快速。

原因是insert into test1 select的方式需要先將資料寫入data buffer區，再寫入磁碟
create table  test1  as select的方式跳過data buffer區直接寫入磁碟。

BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>create table  test1  as select rownum a from dual connect by rownum<1000001;
BYS@ bys001>alter system flush shared_pool;
BYS@ bys001>create table  test1  as select rownum a from dual connect by rownum<1000001;
Table created.
Elapsed: 00:00:03.10
############################

方法九 ：設定並行度為2，再次提升速度。

修改虛擬機器配置為使用兩個核心。檢視：
[oracle@oel-01 ~]$ grep 'physical id' /proc/cpuinfo | so
physical id     : 0
[oracle@oel-01 ~]$ grep 'core id' /proc/cpuinfo | sort -u | wc -l
2
比如我這裡就是單CPU雙核心，設定並行度為2
BYS@ bys001>alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.35
BYS@ bys001>create table  test1  parallel 2 as select rownum a from dual connect by rownum<1000001;
Table created.
Elapsed: 00:00:02.20