INSERT-SELECT (Oracle PL/SQL) - Performance Issue

Question

I have too many SELECT statements toghether with only one INSERT (maybe hundreds of them) And the system is giving a bad performance.

I will explain in general words what is happening and what I'm searching for:

Considering the following two pseudo-codes in Oracle PL/SQL, which of them would give the best performance?

Option A:

INSERT INTO MyTable
  WITH Fields AS (
     SELECT Field1, Field2, ..., FieldN FROM TableA JOIN TableW .... WHERE <condition1>
     UNION ALL
     SELECT Field1, Field2, ..., FieldN FROM TableB JOIN TableX .... WHERE <condition2>
     UNION ALL
     SELECT Field1, Field2, ..., FieldN FROM TableC JOIN TableB .... WHERE <condition3>
     ....
     UNION ALL
     ....
     SELECT Field1, Field2, ..., FieldN FROM TableZZZ JOIN TableB .... WHERE <conditionN>

Option B:

BEGIN
  INSERT INTO MyTable SELECT Field1, Field2, ..., FieldN FROM TableA JOIN TableZ .... WHERE <condition1>
  INSERT INTO MyTable SELECT Field1, Field2, ..., FieldN FROM TableB JOIN TableW .... WHERE <condition2>
  INSERT INTO MyTable SELECT Field1, Field2, ..., FieldN FROM TableC JOIN TableH .... WHERE <condition3>
  ...
  INSERT INTO MyTable SELECT Field1, Field2, ..., FieldN FROM TableZZZZ JOIN TableX .... WHERE <conditionN>
END

I didn't put the real table names, but I would like to know: if I change the current option A to option B, would it present me a better performance? I mean, is it a good idea to replace UNION ALL with many INSERT statements in this case?

@PeterLang, is big and these SELECT statements vary often (I will update the pseudo-code) — Raffael Bechara Rameh, Aug 21 '17 at 11:25
This does not answer your question, but if a redesign is an option, make sure that `TableA`, `TableB` and `TableC` really need to be separate tables. Your current requirement (and maybe many others) could be solved a lot easier if there was a single table with one column describing the type. — Peter Lang, Aug 21 '17 at 11:30

Barbaros Özhan · Answer 1 · 2017-08-21T13:09:23.973

2

Context Switches and Performance

Almost every program PL/SQL developers write includes both PL/SQL and SQL statements. PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine (see Figure 1). This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.

so, use this third way:

create view MyView as select Field1, Field2, ..., FieldN from TableA join TableB .... where <condition1>

declare
  p_array_size pls_integer := 100;
  type         array is table of MyView%rowtype;
  l_data       array;
  cursor c is select * from MyView;    
begin
    open c;
    loop
    fetch c bulk collect into l_data limit p_array_size;

    forall i in 1..l_data.count
    insert into MyTable values l_data(i);

    exit when c%notfound;
    end loop;
    close c;
end;

edited Aug 21 '17 at 13:09

answered Aug 21 '17 at 11:39

Barbaros Özhan

59,113
10
31
55

You should explain what this technique is and why this could lead to better performance. – J. Chomel Aug 21 '17 at 12:23
Your solution is interesting. But do you mean option A is better? still unclear. (+I see no figure 1 in your answer). – J. Chomel Aug 21 '17 at 13:12
@J.Chomel both A and B seem to me from the point of performance. – Barbaros Özhan Aug 21 '17 at 13:47
since solution A is pure SQL, it still looks not so bad. Yours looks ok, but adds the need for PL/SQL where there is none. – J. Chomel Aug 21 '17 at 14:04

score 0 · Answer 2 · answered Aug 21 '17 at 12:38

Except your queries are very peculiar in terms of memory on your database server, option B is not going to increase the performance of the big query.

To ensure the above is true, try ask your DBA to check what happens in your databsae sever SGA at the time you perform the query. If a jam in the memory occurs, then it is worth trying to implement option B.

When I say "memory jams", I mean whole SGA memory is filled, making swap necessary on the server. If you do the inserts in a sequence, then SGA can be reused between the inserts.

INSERT-SELECT (Oracle PL/SQL) - Performance Issue

2 Answers2