遊標資料不同方式讀取、提交效能對比分析

pwz1688發表於2014-03-06
在itpub論壇中看到有人歸納整理遊標資料幾種提取方式及提交方式的效能分析，收集的很全面，於是在本機也測試了一把，附上我的測試程式碼及測試資料。
先看遊標資料讀取提交的五種方式，如下：
1. 使用遊標 逐條提取、逐條處理、逐條插入、逐條提交。
2. 使用遊標 逐條提取、逐條處理、逐條插入、利用自定義計數變數分批提交 。
3. 同方案2,但是使用了ROWCOUNT變數分批提交。
4. 使用bulk collect limit方式提取資料,逐條處理,逐條插入,然後提交本次資料 。
5. 使用bulk collect limit方式提取資料,逐條處理,然後使用forall提交本次資料。
測試結果如下：
COMMIT_COUNT bulk_forall bulk_for cursor_rowconut cursor_vcount cursor_every_record
500 2.000 3.000 7.000 6.000 9.000
1000 1.000 7.000 7.000 4.000 9.000
1500 3.000 4.000 6.000 5.000 10.000
2000 1.000 5.000 5.000 5.000 10.000
2500 1.000 5.000 6.000 5.000 10.000
3000 1.000 7.000 5.000 8.000 10.000
3500 2.000 3.000 6.000 5.000 11.000
4000 2.000 4.000 6.000 5.000 11.000
4500 2.000 4.000 5.000 5.000 12.000
5000 2.000 3.000 6.000 6.000 12.000
由此可知：根據速度從大到小排序為5>4>2>3>1。
附測試程式碼如下：
1、表初始化程式碼：
SQL> create table BULK_LOG_BY_PENGWZH
  2 (
  3 id NUMBER(12),
  4 type VARCHAR2(200),
  5 start_time DATE,
  6 end_time DATE,
  7 used_time_in_seconds NUMBER(12,3)
  8 );
表已建立。
SQL> create table BULK_RESULT_BY_PENGWZH
  2 (
  3 id NUMBER(12),
  4 type VARCHAR2(200),
  5 nresult NUMBER(12),
  6 vresult VARCHAR2(600)
  7 );
表已建立。
SQL> create table BULK_TEST_BY_PENGWZH
  2 (
  3 id NUMBER(12),
  4 n1 NUMBER(12),
  5 n2 NUMBER(12),
  6 n3 NUMBER(12),
  7 v1 VARCHAR2(200),
  8 v2 VARCHAR2(200),
  9 v3 VARCHAR2(200)
 10 );
表已建立。
2、測試包及包體程式碼如下：
----------------------------------------------------
-- Export file for user TEST --
-- Created by Administrator on 2014/3/6, 15:56:01 --
----------------------------------------------------
prompt
prompt Creating view QUERY_RESULT_BY_PENGWZH
prompt =====================================
prompt
create or replace view test.query_result_by_pengwzh as
select id,
       regexp_substr(type, '[^0-9]+') type,
       to_number(regexp_substr(type, '[0-9]+')) commit_count,
       t.used_time_in_seconds
  from BULK_LOG_BY_PENGWZH t
 order by 2, 3, 4;
prompt
prompt Creating package P_BULK_TEST
prompt ============================
prompt
create or replace package test.p_bulk_test is
  -- Author : ADMINISTRATOR
  -- Created : 2014/3/6 13:54:34
  -- Purpose :
  procedure p_bulk_test_1_BY_PENGWZH(v_count number);
  procedure p_bulk_test_2_BY_PENGWZH(v_count number);
  procedure p_cursor_test_1_BY_PENGWZH;
  procedure p_cursor_test_2_BY_PENGWZH(v_commit number);
  procedure p_cursor_test_3_BY_PENGWZH(v_commit number);
  procedure p_init_data_BY_PENGWZH(v_count number);
  procedure p_test_BY_PENGWZH;
end p_bulk_test;
/
prompt
prompt Creating package body P_BULK_TEST
prompt =================================
prompt
create or replace package body test.p_bulk_test is
  procedure p_bulk_test_1_BY_PENGWZH(v_count number) as
    /*
    *bulk collect
    *每次從遊標中提取 v_count 條資料
    *for 迴圈逐條處理insert
    *for 迴圈之後提交當前資料
    *此模式下 exit 不能緊跟fetch,否則會缺失資料
    */
    v_id number(12);
    v_name varchar2(200) := 'BULK_TEST_COMMIT_BY_AFTER_FOR_EVERY_' ||
                            to_char(v_count) || '_RECORD';
    cursor mycur is
      select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
        from bulk_test_BY_PENGWZH s;
 
    type v_line_table is table of bulk_test_BY_PENGWZH%rowtype index by binary_integer;
    v_lines v_line_table;
    v_line bulk_test_BY_PENGWZH%rowtype;
 
    v_n_result number(12);
    v_v_result varchar2(600);
  begin
    execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不計入時間
    --取下一日誌編號
    select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
    --記錄日誌
    insert into bulk_log_BY_PENGWZH
      (id, type, start_time)
    values
      (v_id, v_name, sysdate);
    commit;
    /*****************************************/
    open mycur;
    loop
      fetch mycur bulk collect
        into v_lines limit v_count;
      for i in 1 .. v_lines.count loop
        v_line := v_lines(i);
        v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
        v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
        insert into bulk_result_BY_PENGWZH
          (id, nresult, vresult)
        values
          (v_line.id, v_n_result, v_v_result);
      end loop;
      commit;
      exit when mycur%notfound;
    end loop;
    /*****************************************/
    --更新日誌
    update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
    update bulk_log_BY_PENGWZH
       set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
                                        3)
     where id = v_id;
    commit;
  end;
  procedure p_bulk_test_2_BY_PENGWZH(v_count number) as
    /*
    *bulk collect
    *每次從遊標中提取 v_count 條資料
    *for 迴圈逐條處理
    *for 迴圈之後使用forall提交當前資料
    *此模式下 exit 不能緊跟fetch,否則會缺失資料
    */
    v_id number(12);
    v_name varchar2(200) := 'BULK_TEST_COMMIT_BY_AFTER_FORALL_EVERY_' ||
                            to_char(v_count) || '_RECORD';
    cursor mycur is
      select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
        from bulk_test_BY_PENGWZH s;
 
    type v_line_table is table of bulk_test_BY_PENGWZH%rowtype index by binary_integer;
    v_lines v_line_table;
    v_line bulk_test_BY_PENGWZH%rowtype;
 
    v_n_result number(12);
    v_v_result varchar2(600);
 
    type v_id_table is table of number(12) index by binary_integer;
    type v_n_table is table of number(12) index by binary_integer;
    type v_r_table is table of varchar2(600) index by binary_integer;
 
    v_ids v_id_table;
    v_ns v_n_table;
    v_vs v_r_table;
  begin
    execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不計入時間
    --取下一日誌編號
    select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
    --記錄日誌
    insert into bulk_log_BY_PENGWZH
      (id, type, start_time)
    values
      (v_id, v_name, sysdate);
    commit;
    /*****************************************/
    open mycur;
    loop
      fetch mycur bulk collect
        into v_lines limit v_count;
      for i in 1 .. v_lines.count loop
        v_line := v_lines(i);
        v_ids(i) := v_line.id;
        v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
        v_ns(i) := v_n_result;
        v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
        v_vs(i) := v_v_result;
      end loop;
      forall i in 1 .. v_lines.count
        insert into bulk_result_BY_PENGWZH
          (id, nresult, vresult)
        values
          (v_ids(i), v_ns(i), v_vs(i));
      commit;
      exit when mycur%notfound;
    end loop;
    /*****************************************/
    --更新日誌
    update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
    update bulk_log_BY_PENGWZH
       set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
                                        3)
     where id = v_id;
    commit;
  end;
  procedure p_cursor_test_1_BY_PENGWZH as
    /*
    *每條提交
    *逐條fetch的遊標在fetch後必須緊跟exit
    */
    v_id number(12);
    v_name varchar2(200) := 'CURSOR_TEST_COMMIT_EVERY_RECORD';
    cursor mycur is
      select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
        from bulk_test_BY_PENGWZH s;
 
    v_line bulk_test_BY_PENGWZH%rowtype;
 
    v_n_result number(12);
    v_v_result varchar2(600);
  begin
    execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不計入時間
    --取下一日誌編號
    select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
    --記錄日誌
    insert into bulk_log_BY_PENGWZH
      (id, type, start_time)
    values
      (v_id, v_name, sysdate);
    commit;
    /*****************************************/
    open mycur;
    loop
      fetch mycur
        into v_line;
      exit when mycur%notfound;
      v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
      v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
      insert into bulk_result_BY_PENGWZH
        (id, nresult, vresult)
      values
        (v_line.id, v_n_result, v_v_result);
      commit;
    end loop;
    close mycur;
    /*****************************************/
    --更新日誌
    update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
    update bulk_log_BY_PENGWZH
       set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
                                        3)
     where id = v_id;
    commit;
  end;
  procedure p_cursor_test_2_BY_PENGWZH(v_commit number) as
    /*
    *每N條提交
    *利用 mycur%rowcount 進行每 v_commit 條進行提交
    *逐條fetch的遊標在fetch後必須緊跟exit
    */
    v_id number(12);
    v_name varchar2(200) := 'CURSOR_TEST_COMMIT_BY_ROWCOUNT_EVERY_' ||
                            to_char(v_commit) || '_RECORD';
    cursor mycur is
      select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
        from bulk_test_BY_PENGWZH s;
 
    v_line bulk_test_BY_PENGWZH%rowtype;
 
    v_n_result number(12);
    v_v_result varchar2(600);
  begin
    execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不計入時間
    --取下一日誌編號
    select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
    --記錄日誌
    insert into bulk_log_BY_PENGWZH
      (id, type, start_time)
    values
      (v_id, v_name, sysdate);
    commit;
    /*****************************************/
    open mycur;
    loop
      fetch mycur
        into v_line;
      exit when mycur%notfound;
      v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
      v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
      insert into bulk_result_BY_PENGWZH
        (id, nresult, vresult)
      values
        (v_line.id, v_n_result, v_v_result);
      if mod(mycur%rowcount, v_commit) = 0 then
        commit;
      end if;
    end loop;
    commit;
    close mycur;
    /*****************************************/
    --更新日誌
    update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
    update bulk_log_BY_PENGWZH
       set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
                                        3)
     where id = v_id;
    commit;
  end;
  procedure p_cursor_test_3_BY_PENGWZH(v_commit number) as
    /*
    *每N條提交
    *利用 計數變數 進行每 v_commit 條進行提交
    *逐條fetch的遊標在fetch後必須緊跟exit
    */
    v_count number(12) := 0;
    v_id number(12);
    v_name varchar2(200) := 'CURSOR_TEST_COMMIT_BY_VCOUNT_EVERY_' ||
                             to_char(v_commit) || '_RECORD';
    cursor mycur is
      select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
        from bulk_test_BY_PENGWZH s;
 
    v_line bulk_test_BY_PENGWZH%rowtype;
 
    v_n_result number(12);
    v_v_result varchar2(600);
  begin
    execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不計入時間
    --取下一日誌編號
    select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
    --記錄日誌
    insert into bulk_log_BY_PENGWZH
      (id, type, start_time)
    values
      (v_id, v_name, sysdate);
    commit;
    /*****************************************/
    open mycur;
    loop
      fetch mycur
        into v_line;
      exit when mycur%notfound;
      v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
      v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
      insert into bulk_result_BY_PENGWZH
        (id, nresult, vresult)
      values
        (v_line.id, v_n_result, v_v_result);
   
      v_count := v_count + 1;
      if mod(v_count, v_commit) = 0 then
        commit;
      end if;
    end loop;
    commit;
    close mycur;
    /*****************************************/
    --更新日誌
    update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
    update bulk_log_BY_PENGWZH
       set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
                                        3)
     where id = v_id;
    commit;
  end;
  procedure p_init_data_BY_PENGWZH(v_count number) as
    v_id number(12);
  begin
    --清空表
    execute immediate 'truncate table bulk_test_BY_PENGWZH';
    --取下一日誌編號
    select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
    --記錄日誌
    insert into bulk_log_BY_PENGWZH
      (id, type, start_time)
    values
      (v_id, 'init_data_' || to_char(v_count), sysdate);
 
    --插入資料
    for i in 1 .. v_count loop
      insert into bulk_test_BY_PENGWZH
        (id, n1, n2, n3, v1, v2, v3)
      values
        (i,
         round(dbms_random.value * 1000000),
         round(dbms_random.value * 1000000),
         round(dbms_random.value * 1000000),
         dbms_random.string('a', round(dbms_random.value * 200)),
         dbms_random.string('a', round(dbms_random.value * 200)),
         dbms_random.string('a', round(dbms_random.value * 200)));
    end loop;
    --提交
    commit;
    --更新日誌
    update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
    update bulk_log_BY_PENGWZH
       set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
                                        3)
     where id = v_id;
    commit;
  end;
  procedure p_test_BY_PENGWZH as
    v_init_count number(12) := 100000; --測試資料量
    v_commit_count number(12);
    v_start_size number(12) := 500; --初始值
    v_test_count number(12) := 10; --測試次數
    v_step_size number(12) := 500; --步進
  begin
    execute immediate 'truncate table bulk_log_BY_PENGWZH';
 
    --初始化測試資料
    p_init_data_BY_PENGWZH(v_init_count);
 
    for i in 1 .. v_test_count loop
      --500, 1000, 1500 ... 10000
      v_commit_count := v_start_size + v_step_size * (i - 1);
      p_cursor_test_1_BY_PENGWZH;
      p_cursor_test_2_BY_PENGWZH(v_commit_count);
      p_cursor_test_3_BY_PENGWZH(v_commit_count);
      p_bulk_test_1_BY_PENGWZH(v_commit_count);
      p_bulk_test_2_BY_PENGWZH(v_commit_count);
    end loop;
  end;
end p_bulk_test;
/

3、測試方法說明
執行exec p_bulk_test.p_test_BY_PENGWZH;
檢視結果可直接查詢檢視QUERY_RESULT_BY_PENGWZH即可。
 

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

相關文章