row-based load and bulk bind load

本文测试一下传统的row-based insert和使用bulk bind insert的性能,对比一下数据库中的stats信息,并进一步的比较一下使用bulk bind insert对pga的内存使用情况。
实验环境11.2.0.4.7 RAC+ ASM EMC存储 PC服务器。

脚本

set serveroutput on
drop table blue purge;
create table blue(
id number(10) primary key,
n1  varchar2(10),
small_vc varchar2(100));

alter table blue add constraint uk_blue unique(n1);


declare
  type t_blue_tab IS TABLE OF blue%ROWTYPE;  --nested table

  l_tab  t_blue_tab := t_blue_tab(); --initialized to empty 
  l_start_time number;
  row_size number  := 10000000;


begin
  --创建collection
  for i in 1 .. row_size loop
    l_tab.extend;
  
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).n1 := to_char(i);
    l_tab(l_tab.last).small_vc := 'blue_: ' || mod(i,2);

  end loop;

  --清理数据
  execute immediate 'truncate table blue';

  --normal row-based insert
  l_start_time := dbms_utility.get_time;

  for i in l_tab.first .. l_tab.last loop
    insert into blue values(l_tab(i).id,l_tab(i).n1,l_tab(i).small_vc);
  end loop;

  dbms_output.put_line('normal inserts: ' || (dbms_utility.get_time - l_start_time));
  --清理数据
  execute immediate 'truncate table blue';
 
  --bulk insert

  l_start_time := dbms_utility.get_time;
  
  forall i in l_tab.first .. l_tab.last
    insert into blue values l_tab(i);
  
  dbms_output.put_line('bulk inserts: ' || (dbms_utility.get_time - l_start_time));
  commit;
end;
/

DEMO:
接下来比较insert 100w条记录的性能。
row-based insert

SQL> declare
  2    type t_blue_tab IS TABLE OF blue%ROWTYPE;  --nested table
  3
  4    l_tab  t_blue_tab := t_blue_tab(); --initialized to empty
  5    l_start_time number;
  6    row_size number  := 1000000;
  7
  8  begin
  9    --创建collection
 10    for i in 1 .. row_size loop
 11      l_tab.extend;
 12
 13      l_tab(l_tab.last).id := i;
 14      l_tab(l_tab.last).n1 := to_char(i);
 15      l_tab(l_tab.last).small_vc := 'blue_: ' || mod(i,2);
 16
 17    end loop;
 18
 19    --清理数据
 20    execute immediate 'truncate table blue';
 21
 22    l_start_time := dbms_utility.get_time;
 23
 24    --normal row-based insert
 25    l_start_time := dbms_utility.get_time;
 26
 27    for i in l_tab.first .. l_tab.last loop
 28      insert into blue values(l_tab(i).id,l_tab(i).n1,l_tab(i).small_vc);
 29    end loop;
 30
 31    dbms_output.put_line('bulk inserts: ' || (dbms_utility.get_time - l_start_time));
 32    commit;
 33  end;
 34  /
bulk inserts: 6583

PL/SQL procedure successfully completed.

Elapsed: 00:01:08.08

bulk bind insert

SQL> declare
  2    type t_blue_tab IS TABLE OF blue%ROWTYPE;  --nested table
  3
  4    l_tab  t_blue_tab := t_blue_tab(); --initialized to empty
  5    l_start_time number;
  6    row_size number  := 1000000;
  7
  8  begin
  9    --创建collection
 10    for i in 1 .. row_size loop
 11      l_tab.extend;
 12
 13      l_tab(l_tab.last).id := i;
 14      l_tab(l_tab.last).n1 := to_char(i);
 15      l_tab(l_tab.last).small_vc := 'blue_: ' || mod(i,2);
 16
 17    end loop;
 18
 19    --清理数据
 20    execute immediate 'truncate table blue';
 21
 22    --bulk insert
 23
 24    l_start_time := dbms_utility.get_time;
 25
 26    forall i in l_tab.first .. l_tab.last
 27      insert into blue values l_tab(i);
 28
 29    dbms_output.put_line('bulk inserts: ' || (dbms_utility.get_time - l_start_time));
 30    commit;
 31  end;
 32  /
bulk inserts: 695

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.33

时间上差距比较明显,row-based模式下使用了68秒,而使用bulk bind使用了9秒多一些。
然后看下数据库在这两种方式下的stats情况。
左侧为row-based 右侧为bulk bind(set-based)

NAME                                             VALUE   NAME                                                    VALUE
---------------------------------------------------------   ----------------------------------------------- ----------
opened cursors cumulative                         1000313   opened cursors cumulative                              310
recursive calls                                   1002654   recursive calls                                       2570
recursive cpu usage                                  5794   recursive cpu usage                                    622
session logical reads                             6607593   session logical reads                               188837
CPU used when call started                           6740   CPU used when call started                             831
CPU used by this session                             6740   CPU used by this session                               831
DB time                                              6866   DB time                                                908
session uga memory                                3441104   session uga memory                                 7632320
session uga memory max                            3441104   session uga memory max                             7632320
session pga memory                                9187408   session pga memory                                13430192
session pga memory max                          320417872   session pga memory max                           324415568
physical read total IO requests                        10   physical read total IO requests                          3
physical read total bytes                           81920   physical read total bytes                            24576
cell physical IO interconnect bytes                 81920   cell physical IO interconnect bytes                  24576
gcs messages sent                                    4701   gcs messages sent                                     4686
ges messages sent                                     113   ges messages sent                                       97
db block gets                                     6587158   db block gets                                       164951
db block gets from cache                          6587158   db block gets from cache                            164951
db block gets from cache (fastpath)               2001286   db block gets from cache (fastpath)                  20144
consistent gets                                     20435   consistent gets                                      23886
consistent gets from cache                          20435   consistent gets from cache                           23886
consistent gets from cache (fastpath)                6176   consistent gets from cache (fastpath)                 9648
consistent gets - examination                       14185   consistent gets - examination                        14166
logical read bytes from cache                  5.4129E+10   logical read bytes from cache                   1546952704
physical reads                                         10   physical reads                                           3
physical reads cache                                   10   physical reads cache                                     3
physical read IO requests                              10   physical read IO requests                                3
physical read bytes                                 81920   physical read bytes                                  24576
db block changes                                  6174585   db block changes                                    199700
change write time                                    1245   change write time                                       92
free buffer requested                               49859   free buffer requested                                23625
switch current to new buffer                           26   switch current to new buffer                            26
shared hash latch upgrades - no wait                   48   shared hash latch upgrades - no wait                    47
calls to kcmgcs                                     19681   calls to kcmgcs                                      22443
calls to kcmgas                                     48515   calls to kcmgas                                      22173
calls to get snapshot scn: kcmgss                    6206   calls to get snapshot scn: kcmgss                     6187
redo entries                                      3082590   redo entries                                        107471
redo size                                       819710516   redo size                                        189726100
redo ordering marks                                 42483   redo ordering marks                                  16231
redo subscn max counts                                 22   redo subscn max counts                                  22
redo synch time                                         1   redo synch time                                          1
redo synch time (usec)                               8607   redo synch time (usec)                                   7     
file io wait time                                   38189   file io wait time                                     6674   
gc current blocks received                              1   gc current blocks received                               
gc local grants                                     45143   gc local grants                                      18912      
gc remote grants                                     4700   gc remote grants                                      4686      
undo change vector size                         277531708   undo change vector size                           69754728      
no work - consistent read gets                        266   no work - consistent read gets                         261      
rollback changes - undo records applied                27   rollback changes - undo records applied                 15      
transaction rollbacks                                  12   transaction rollbacks                                    6      
immediate (CURRENT) block cleanout applications     15912   immediate (CURRENT) block cleanout applications      15912      
deferred (CURRENT) block cleanout applications         43   deferred (CURRENT) block cleanout applications          45      
commit txn count during cleanout                     5795   commit txn count during cleanout                      5795      
active txn count during cleanout                    13654   active txn count during cleanout                     13652      
cleanout - number of ktugct calls                   13664   cleanout - number of ktugct calls                    13662      
Commit SCN cached                                      30   Commit SCN cached                                            
commit batch/immediate requested                       12   commit batch/immediate requested                         6      
commit immediate requested                             12   commit immediate requested                               6      
commit batch/immediate performed                       12   commit batch/immediate performed                         6      
commit immediate performed                             12   commit immediate performed                               6      
table scans (short tables)                             20   table scans (short tables)                              20      
table scan rows gotten                                296   table scan rows gotten                                 296      
table scan blocks gotten                               16   table scan blocks gotten                                16      
table fetch by rowid                                  133   table fetch by rowid                                   132      
cluster key scans                                      55   cluster key scans                                       52      
cluster key scan block gets                            55   cluster key scan block gets                             52      
rows fetched via callback                              75   rows fetched via callback                               75      
leaf node splits                                     5781   leaf node splits                                      5781      
leaf node 90-10 splits                               1874   leaf node 90-10 splits                                1874      
branch node splits                                     12   branch node splits                                      12      
root node splits                                        2   root node splits                                         2      
index fetch by key                                    180   index fetch by key                                     177      
index scans kdiixs1                                   165   index scans kdiixs1                                    164      
HSC Heap Segment Block Changes                    1000078   HSC Heap Segment Block Changes                        7561      
Segment Array Inserts                                       Segment Array Inserts                                 7483      
Heap Segment Array Updates                             12   Heap Segment Array Updates                              12      
sql area purged                                         4   sql area purged                                          4      
sql area evicted                                        5   sql area evicted                                         5      
session cursor cache hits                           17107   session cursor cache hits                              145      
session cursor cache count                             50   session cursor cache count                              50
cursor authentications                                  3   cursor authentications                                   1      
buffer is pinned count                                 38   buffer is pinned count                                  38      
buffer is not pinned count                            635   buffer is not pinned count                             626  
workarea executions - optimal                          29   workarea executions - optimal                           29      
parse time cpu                                          5   parse time cpu                                           6      
parse time elapsed                                      6   parse time elapsed                                       8      
parse count (total)                                   263   parse count (total)                                    262      
parse count (hard)                                     14   parse count (hard)                                      14      
parse count (failures)                                  1   parse count (failures)                                   1      
execute count                                     1000351   execute count                                          348      

从上面的数据对比可以看到,bulk bind insert对pga的消耗相对较大,recursive calls的次数bulk bind模式下对传统方式小了很多、进行的逻辑读也是递减的、redo的量在bulk模式下生成较少,执行的次数也是天壤之别。

接下来比较一下bulk bind insert的对pga的消耗,关注session pga memory max参数。

SQL> set serveroutput on
SQL>
SQL>
SQL>
SQL>
SQL> declare
  2    type t_blue_tab is table of blue.small_vc%type;
  3    l_tab t_blue_tab;
  4
  5    l_start_time timestamp;
  6
  7    l_pga_size number(10);
  8
  9    type t_bulk_sizes is table of pls_integer;
 10    l_bulk_sizes t_bulk_sizes := t_bulk_sizes(10,
 11                                              100,
 12                                              1000,
 13                                              10000,
 14                                              100000,
 15                                              1000000);
 16
 17    total_rows pls_integer := 10000000;
 18
 19  begin
 20    select value
 21      into l_pga_size
 22      from v$statname sn, v$mystat ms
 23     where ms.value != 0
 24       and sn.statistic# = ms.statistic#
 25       and sn.name = 'session pga memory max';
 26
 27    dbms_output.put_line('Initial session pga memory max: ' || l_pga_size);
 28
 29    for i in 1 .. l_bulk_sizes.count loop
 30
 31      execute immediate 'truncate table blue';
 32
 33      l_start_time := systimestamp;
 34
 35      l_tab := t_blue_tab();
 36      l_tab.extend(l_bulk_sizes(i));
 37      for j in 1 .. l_bulk_sizes(i) loop
 38        l_tab(j) := lpad('a', 20);
 39      end loop;
 40
 41      for iter in 1 .. total_rows / l_bulk_sizes(i) loop
 42        forall j in 1 .. l_bulk_sizes(i)
 43          insert into blue (small_vc) values (l_tab(j));
 44      end loop;
 45
 46      select value
 47        into l_pga_size
 48        from v$statname sn, v$mystat ms
 49       where ms.value != 0
 50         and sn.statistic# = ms.statistic#
 51         and sn.name = 'session pga memory max';
 52
 53      dbms_output.put_line('Bulk size: ' || l_bulk_sizes(i));
 54      dbms_output.put_line('Elapsed Time: ' || (systimestamp - l_start_time));
 55      dbms_output.put_line('session pga memory max: ' || l_pga_size);
 56
 57    end loop;
 58
 59  end;
 60  /
Initial session pga memory max: 3616848
Bulk size: 10
Elapsed Time: +000000000 00:00:57.195408000
session pga memory max: 5189712
Bulk size: 100
Elapsed Time: +000000000 00:00:16.502316000
session pga memory max: 5255248
Bulk size: 1000
Elapsed Time: +000000000 00:00:11.627331000
session pga memory max: 5255248
Bulk size: 10000
Elapsed Time: +000000000 00:00:10.496830000
session pga memory max: 6041680
Bulk size: 100000
Elapsed Time: +000000000 00:00:10.914825000
session pga memory max: 13906000
Bulk size: 1000000
Elapsed Time: +000000000 00:00:11.165778000
session pga memory max: 92287056

PL/SQL procedure successfully completed.

随着bulk size的逐渐增大,session pga memory max的消耗越来越大,性能越来越差。

此条目发表在PLSQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s