Oracle 11g - most efficient way of inserting multiple rows

Question

I have an application which is running slowly over a WAN - we think the cause is multiple inserts into a table. I'm currently looking into more efficient ways to insert multiple rows at the same time.

I found this method:

INSERT ALL
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (100,20)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (21,2)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (321,10)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (22,13)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (14,121)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (11,112)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (112,23)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (132,2323)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (121,34)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (24333,333)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (1232,3434)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (4554,3434)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (3434,211)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (3434,1233)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (12,22)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (356,233)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (9347,23)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (8904,245)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (342,4545)
  INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (453,233)
SELECT 1 FROM DUAL;

What I would like to know is: is the method above actually more efficient than just doing 20 "INSERT INTO MY_TABLE (1,1);"? Are there other methods of doing this?

Solution 1

You can try direct path insert to speed up operation, but for 100 records conventional path insert must be fast enough and it seems that the problem is about table locking while inserting into log from a big number of sources.

To instruct Oracle to use direct path insert you must specifiy either APPEND or APPEND_VALUES hints depending on insert statement syntax. E.g.

insert /*+ APPEND */ 
into multi_insert(val_1, val_2)
select * from (
  select 100,    20 from dual union all
  select 21,      2 from dual union all
  select 321,    10 from dual union all
  select 22,     13 from dual union all
  select 14,    121 from dual union all
  select 11,    112 from dual union all
  select 112,    23 from dual union all
  select 132,  2323 from dual union all
  select 121,    34 from dual union all
  select 24333, 333 from dual union all
  select 1232, 3434 from dual union all
  select 4554, 3434 from dual union all
  select 3434,  211 from dual union all
  select 3434, 1233 from dual union all
  select 12,     22 from dual union all
  select 356,   233 from dual union all
  select 9347,   23 from dual union all
  select 8904,  245 from dual union all
  select 342,  4545 from dual union all
  select 453,   233 from dual
)

If insert statement originated from PL/SQL code then you can use bulk insert with forall statement to improve performance (SQLFiddle) :

declare
  type TRowList is table of multi_insert%rowtype index by binary_integer;

  vRowList TRowList;
  vRow     multi_insert%rowtype;
begin


  vRow.val_1 := 100;
  vRow.val_2 := 20;
  vRowList(0) := vRow;

  vRow.val_1 := 21;
  vRow.val_2 := 2;
  vRowList(1) := vRow;

  vRow.val_1 := 321;
  vRow.val_2 := 10;
  vRowList(2) := vRow;

  -- ...

  forall vIdx in vRowList.first .. vRowList.last
        insert /*+ APPEND_VALUES */  -- direct path insert
        into multi_insert values vRowList(vIdx);

end;

Solution 2

"a client reported that it was working perfectly when the application and Oracle were on the same LAN, but when they moved their Oracle servers abroad they say that the program is performing very slowly"

Okay, so now we're getting somewhere. If you have a set-up in which your hundred statements are individual calls they will probably be sent in separate packets. That would be painful across a WAN compared to a LAN. In that case, it would be worthwhile seeing whether converting statements from RBAR to something Set-based would reduce the number of transmitted packets.

However, I would still advise you to get some hard facts before you roll-out the change. Doesn't your client have a network admin you could talk to? Or at least could you get them to install Wireshark and send you some reports?