oracle dblinks – performance considerations

Let’s imagine the following situation – you have a table called EMPLOYEES_TMP which is a global temporary table with a variable number of rows – totally nondeterministic. And there is a query which runs very often to compare primary key values between this table and a remote one (which is basically the same but not temporary).

And the query sometimes run very fast and other times – very very slow. I had a similar situation last week with one of my customers.

Well, actually the case is quite simple here – let’s see the execution plan:

OK so we can see that for each row in the local temporary table (EMPLOYEES_TMP – E1@SEL$1) the subquery will be executed (SEL$2).

We can actually check the number of socket reads and writes by this process server, while executing this query for different number of rows in the temporary table.

The process has opened a socket for dblink communication:

File descriptor for the socket is 8 – we will use it with strace.

• 0 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g

• 100 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g

• 201 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g

• 500 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g

• 14621 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g

So as you can see – it doesn’t scale good. But this should be obvious just by looking on the explain plan – strace here is just for fun 😉

How can we change the query? First of all we have to be sure, that there will be no nested loops in the explain plan because that changes nothing.

SQL Plan with nested loops:

For this plan, strace shows:

So its even worse! Let’s try to force unnesting but with hash join – the query would look like this:

Explain plan:

In the STRACE we can see huge improvement!

Reducing the number of SQL*Net socket reads and writes was the solution. But what to do if we can’t change the SQL text? There are a few methods – for example SQLPatch:

Now we can run our SQL even in changed form 🙂