Direct Path Insert Considerations for Distributed Transactions
Posted by decipherinfosys on February 11, 2009
We have discussed about direct path inserts before in our posts – one of them you can access here where we had discussed an alternative approach to updating millions of records in a table. There is one thing to remember though when running bulk load scripts (using direct path inserts) with distributed transactions.
A lot of times we get bulk load scripts that have an APPEND hint in the insert. We were trouble shooting an issue for a data migration to identify distributed transaction lock errors. The scripts we have kick of parallel sessions to pull data from multiple servers. Each session performs an insert on the same table using an APPEND hint. Using the APPEND hint makes sense when doing bulk loading of data, however, it causes issues with the parallel method we were using (i.e. parallel sessions). This is because using a direct path insert obtains an exclusive lock on the table, which prevents any other sessions from performing DML on the table. If one parallel session gets to the table first, the others must wait or eventually time out waiting.
In the future if you are running a script that spawns parallel sessions and you start getting distributed transaction lock errors, you can dig into the scripts to see if there is an insert APPEND causing the issue. Below is the official documentation from oracle regarding this: