We are running Oracle 11.2.0.3. We have a query that selects ~300,000 records over a DB Link. Just running this query takes about 50 seconds. But when we take the same query and use it in an insert statement, it takes nearly 3 minutes.
An example:
SELECT columns
FROM table@dblink
WHERE .....
INSERT INTO local_table
SELECT columns
FROM table@dblink
WHERE .....
We are using the DRIVING_SITE hint in the query and the APPEND hint in the insert statement, and there are no indexes on the table (it was even slower with indexes present).
We've also tried writing a PL block that selects the records into a cursor using BULK COLLECT, then looping through the dataset and inserting into the table, but that took roughly the same amount of time as the regular INSERT statement.
Can anyone give me any hints where we can look to troubleshoot this slowdown? Our DB instance is running on Windows 2003 R2 x64 in a VMWare virtual machine, and storage is on SAN.