Distributed Queries

Some time ago I wrote a note about distributed DML, pointing out that the driving_site() hint works with distributed queries but not with DML based on distributed queries; so insert as select, or create as select and so on will “ignore” the hint.

This is just a little follow-up to give you an  idea of what execution plans for distributed queries look like so that you can tell whether your query is going to work locally or remotely.

I have created a loopback database link (d10g@loopback) with a connection qualifier – another term for the glossary eventually – so the example runs on a single database, but the optimizer believes it is running on a distributed pair; and I’ve defined a couple of small tables to join.

Here’s the query with its execution plan when I run the join at the local database. I’ve used the driving_site() hint for textual clarity to identify the “home” database as the place to run the query, but in the absence of a hint I believe the query will always run at the local database – the optimizer appears to have no mechanism for evaluating the network cost of changing the location where the query runs. (I have seen a few books and a metalink note claiming that there is a network cost built into the cost arithmetic – but I’ve never seen anything in the 10053 trace that supports the claim. [Update Dec 2014: see footnote 2])

SQL  select
  2  	/*+ driving_site (dh) */
  3  	dh.small_vc,
  4  	da.large_vc
  5  from
  6  	dist_home		dh,
  7  	dist_away@d10g@loopback	da
  8  where
  9  	dh.small_vc like '12%'
 10  and	da.id = dh.id
 11  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1261259267

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     2 |   260 |    21   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS      |           |     2 |   260 |    21   (0)| 00:00:01 |        |      |
|*  2 |   TABLE ACCESS FULL| DIST_HOME |     2 |    30 |    19   (0)| 00:00:01 |        |      |
|   3 |   REMOTE           | DIST_AWAY |     1 |   115 |     1   (0)| 00:00:01 |   D10G | R->S |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DH"."SMALL_VC" LIKE '12%')

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "ID","LARGE_VC" FROM "DIST_AWAY" "DA" WHERE "ID"=:1 (accessing
       'D10G.JLCOMP.CO.UK@LOOPBACK' )

Note, in particular, that the operation in line 3 is “REMOTE” – and the name of the remote object is dist_away. This should be enough to tell us (at least in this case) where the query is actually executing and which database is the remote one.  The “Remote SQL Information” makes things even more clear: it tells us the SQL that is being sent to “the other” database, and tells us where that database is. This query is running at the database that holds table dist_home and, for each row selected from dist_home, is sending a query to d10g@jlcomp.demon.co.uk@loopback.

Now we repeat the test, but put into the driving_site() hint the alias of the table that is located at the remote database:

SQL  select
  2  	/*+ driving_site (da) */
  3  	dh.small_vc,
  4  	da.large_vc
  5  from
  6  	dist_home		dh,
  7  	dist_away@d10g@loopback	da
  8  where
  9  	dh.small_vc like '12%'
 10  and	da.id = dh.id
 11  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 4154226149

---------------------------------------------------------------------------------------------------------
| Id | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT REMOTE      |           |     2 |   450 |    15   (0)| 00:00:01 |        |      |
|  1 |  NESTED LOOPS                |           |     2 |   450 |    15   (0)| 00:00:01 |        |      |
|  2 |   REMOTE                     | DIST_HOME |     2 |    40 |    13   (0)| 00:00:01 |      ! | R->S |
|  3 |   TABLE ACCESS BY INDEX ROWID| DIST_AWAY |     1 |   205 |     1   (0)| 00:00:01 |   D10G |      |
|* 4 |    INDEX UNIQUE SCAN         | DA_PK     |     1 |       |     0   (0)| 00:00:01 |   D10G |      |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A1"."ID"="A2"."ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------
   2 - SELECT "ID","SMALL_VC" FROM "DIST_HOME" "A2" WHERE "SMALL_VC" LIKE '12%' (accessing '!' )

Note
-----
   - fully remote statement

The “Note” section is a bit of a clue here – the query is running at the remote site. We also see at line 2 that the “REMOTE” operation is against a table called dist_home – which isn’t remote as far as we’re concerned, it’s in our local database! But we’re seeing the execution plan from the perspective of “the other” database – and to the other database we are remote. This is confirmed by the instance information and the “Remote SQL Information” where we see “!” as the location of dist_home. The “!” is my database’s name for itself: the remote database is running the query, and sending a select statement to me for each row it selects from dist_away.

Footnote: If you’ve ever seen sysdate, for example, turning into sysdate@! in the predicate section of an execution plan, you now know why. “!” is the database’s name for itself and the database is making it very clear whose value of sysdate it is using.

Footnote 2: Alerted to this blog note by a ping-back, I need to correct a significant error.  I have now seen in recent versions of Oracle some information showing that the optimizer has considered the cost of remote vs. local access to data. I haven’t yet examined how far back this feature goes, or tried to work out the details of how the calculation is done, but will note that the presence of the calculation rarely, if ever, seems (based on a couple of examples I created) to change the access path. It’s possible that the calculation simple modifies the join method (HJ vs NLJ) without considering the choice of driving site.