Oracle multi insert statement

Question

In my application, I need to add many entries. I use the following construction:

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 also use APPEND and PARALLEL hints. Please note that I am inserting data into two different tables. It seems that the parallel is being ignored (I was told by the DBA). So how can I know if it is used or not? Is it possible to use a PARALLEL hint of such a design? Is it effective?

Answer 1

This will probably be enough to make it work:

 alter session enable parallel dml; 

You can check the actual degree of parallelism with the following query:

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

If you still do not get parallelism, there are many possible reasons. First, consider the following options:

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

But you probably don't want parallelism for your insert statement. parallelism has a lot of overhead and is usually useful if you are dealing with many thousands or millions of records.

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

Answer 2

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

Answer 3

There are times when parallelism is disabled. Including, from Oracle Documentation :

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

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

Answer 4

Enabling parallelism for 30 entries would be a waste of resources. parallelism involves costly overhead (splitting, assigning processes, synchronizing results ...) that would not be worth the cost of such a small operation.

I assume that you execute this expression millions of times if you want to optimize it so badly. In this case, it would probably be more efficient to find a way to turn these millions of applications into large multitude operations that could benefit from parallelism.

---

Refresh . Another idea is to run statements with multiple sessions, effectively implementing DIY parallelism. Can you design your own process so that multiple sessions read the input and insert it at the same time?