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?

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.)

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".