Oracle multi insert statement

Question

In my application I have to add many records. I am using the following construct:

   INSERT /*+ append parallel(t1, 4) parallel(t2, 4) */ ALL
   INTO t1 (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t2 (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t2 (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

I am also using APPEND and PARALLEL hints. Notice that I am inserting data in two different tables. It seems that parallel is being ignored (the DBA told me). So how I can know if it is being used or not? Is it possible to use PARALLEL hint in such construct? Is it effective?

Kenn

Where are the hints in your statement? Is this actually the statement you are using? What, exactly are you trying to do in parallel? Do you have an actual query as part of the multi table insert or is the use of DUAL just a gross simplification of what you are doing? Are you using PARALLEL DML? – Frederickson

I added the hints. I am trying to add many records with a single command using Direct Path and making use of parallelism. The problem is that the insert is not making use of parallelism. I would like to know why and how I can check if it is being used or not. – Kenn

How many is "many records", a few dozen or a few million? – Sherleysherline

Around 30 records per statement. – Kenn

All answers are very helpful. Unfortunately I cannot select all. Thank you very much. – Kenn

Answer 1

This will probably be enough to get it to work:

alter session enable parallel dml;

You can check the actual degree of parallelism with a query like this:

select px_servers_executions, v$sql.*
from v$sql where lower(sql_text) like '%insert%parallel%' order by last_load_time desc;

If you're still not getting parallelism there are many possible reasons. To start, look at these parameters:

select * from v$parameter where name like 'parallel%'

But you probably don't want parallelism for your insert statement. Parallelism has a a large amount of overhead, and generally is only useful if you're dealing with many thousands or millions of records.

I'm guessing your real problem is the time to parse the large SQL statement. Multi-table inserts are especially bad. If you try to insert more than a few hundred rows your query will take many seconds for parsing. And depending on your version of Oracle, it will just hang forever if you try to use 501 tables. It's much faster to run several smaller queries instead of one large query. For example, 5 inserts of 100 rows will run much faster than one insert of 500 rows. (In general this is the exact opposite of how to performance tune for Oracle. This is a special case because of the bugs related to parsing large SQL statements.)

Callender

Answer 2

The APPEND hint is only supported with the subquery syntax of the INSERT statement, not the VALUES clause. If you specify the APPEND hint with the VALUES clause, it is ignored and conventional insert will be used. To use direct-path INSERT with the VALUES clause, refer to "APPEND_VALUES Hint".

Mooneyham

Answer 3

There are cases where parallelism is disabled. Including, from the Oracle documentation:

Parallelism is disabled for DML operations on tables on which you have defined a trigger or referential integrity constraint.

Which seems like a pretty big restriction to me. Do you have a trigger or foreign key on your tables?

Sherleysherline

Answer 4

Enabling parallelism for 30 records would be a waste of resource. Parallelism involves an expensive overhead (splitting work, assigning processes, synchronizing results...) that would not be worth the cost for such a small operation.

I suppose you are executing this statement millions of times if you want to optimize it that bad. In that case it would probably more efficient to find a way to turn these million statements into big set operations -- that could nicely profit from parallelism.

---

Update: Another idea might be to run the statements with more than one session, effectively implementing DIY parallelism. Can you design your process so that more than one session read the input data and insert at the same time?

Deil

Hi Vicent. That is not possible. My app is reading millions of records, processing them and writing the results (consolidated). I tried several solutions, like a package for collecting records in memory and writing them using a bulk operation, but it decreased the performance (overhead of calling a stored procedure for each record). – Kenn

@Eduardo: can you divide work so that more than one process can process the inserts at the same time? – Deil