SQL*Net message from dblink wait event in Oracle

Question

I have an INSERT query in Oracle 10g that is getting stuck on a "SQL*Net message from dblink" event. It looks like:

INSERT INTO my_table (A, B, C, ...) 
  SELECT A, B, C, ... FROM link_table@other_system;

I do not see any locks on my_table besides the one from the INSERT I'm trying to do. The SELECT query on link_table@other_system completes without any trouble when run on its own. I only get this issue when I try to do the INSERT.

Does anyone know what could be going on here?

UPDATE The SELECT returns 4857 rows in ~1.5 mins when run alone. The INSERT was running over an hour with this wait message before I decided to kill it.

UPDATE I found an error in my methods. I was using a date range to limit the results. The date range I used when testing the SELECT only was before the last OraStats run on the link_table, but the date range that I used when testing the INSERT was after the last OraStats run on the link_table. So, that mislead me to believe there was a problem with the INSERT. Not very scientific of me to do this; my mistake.

Solution 1

Are you using a /*+ driving_site(link_table) */ hint to make Oracle perform the joins on the remote server?

If so, that hint will not work with DML, as explained by Jonathan Lewis on this page.

This may be a rare case where running the query just as a SELECT uses a very different plan than running the query as part of an INSERT. (You will definitely want to learn how to generate explain plans in your environment. Most tools have a button to do this.)

As Andras Gabor recommended in the link, you may want to use PL/SQL BULK COLLECT to improve performance. This may be a rare case where PL/SQL will work faster than SQL.

Solution 2

SQL*Net message from dblink generally means that your local system is waiting on the network to transfer the data across the network. It's a very normal wait event for this sort of query.

How many rows does the SELECT statement return? How much data (in MB/ GB) does that represent?

When you say that it "completes without any trouble on its own", are you actually fetching all the data? If you're using something like TOAD or SQL Developer, the GUI will generally fetch the first N rows and return to you. That can be very quick but it doesn't imply that the database is done executing the query-- it may take much more time to finish producing all the rows your query is going to return. It's pretty common for people to measure the time required to fetch the first N rows rather than the time to fetch the last row-- your INSERT statement, obviously, can't return until all the rows have been fetched from the remote table.

Comments

[Justin Cave]

Is the SELECT really hitting just one table with no predicate? Or is it doing joins and filters? Is the query plan the same for the standalone SELECT and the INSERT ... SELECT?

[Paul]

You caught me. There are a few joins, but all on the same dblink. How do I see the query plan?

[Paul]

See update in question. It's pretty small and shouldn't take long.

[Paul]

Adding the driving_site hint fixed the issue on the SELECT in light of my second update.