Oracle Scratchpad

December 5, 2008

Distributed DML

Filed under: distributed,Hints,Infrastructure,Performance,Tuning — Jonathan Lewis @ 9:19 pm GMT Dec 5,2008

Someone recently sent me a request about a piece of SQL they could not optimise. I don’t usually respond to private requests – it’s not an effective use of my time – but their example was something that pops up relatively frequently as a “bug” – so I thought I’d mention it here.

The SQL looked like this:

rem     Script:         distributed_dml.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2008
rem     Last tested 

insert into tab3
select 				-- small result set
	tab1@dblink	t1	-- large data set
	tab1.col1 in (
			tab2	-- small data set

The “obvious” execution plan is to send the small amount of data from tab2 to the remote site, perform a join at the remote site, then pull the small result set back to the local site for the insert.

If you eliminate the “insert into” bit of the code, a simple /*+ driving_site(t1) */ hint would do exactly that. But when the “insert into” is included, the join doesn’t obey the hint. The execution path for the “insert as select” pulls every row from tab1 (but only the columns it needs, of course) across the network and does the join locally, discarding most of the data as it does so.

So why is Oracle “ignoring” the hint ? Because it’s supposed to. The SQL has to operate at the site which holds the target of the DML statement. It’s not a bug, it’s defined behaviour.

I mentioned this to Joel Goodman (Oak Table member and instructor at Oracle University) recently, and he promptly dug up a bug number on Metalink for me – 5517609 – which confirms that this is expected behaviour.

This is not a bug. A distributed DML statement must execute on the database where the DML target resides. The DRIVING_SITE hint cannot override this.

Footnote: If you want to make this example join remotely, you’ll have to create a join view at the remote site, and query the view.

Update (Mar 2019)

The statement about the DML executing at the database that is the target of the DML is re-inforced by Doc ID 825677.1: Limitations of DRIVING_SITE Hint For DMLS and DDLS which repeats the above and then points out that the hint always applies to the whole statement, not to a subqury.

More interestingly it also introduces an important point about distributed queries and databases that have been opened read-only. If the read-only database becomes the driving site of the query its SCN is fixed at the moment it opened – so your entire query will run as at the moment the read-only database opened.


[Further reading on distributed databases]


  1. Thank you for a great article.
    Correct me, if I’m wrong. I think that Oracle will not pull whole table tab1 across network, for example, for nested loop join with access by index on tab1.col1 column.

    Comment by Vyacheslav Rasskazov — December 6, 2008 @ 1:12 am GMT Dec 6,2008 | Reply

  2. Vyacheslav,

    Thanks for the comment. You are correct – there are alternative execution plans (depending on Oracle version, volume of data, and so on) that could use a nested loop approach.

    In the example, I could hint, or manually rewrite, the query to create an inline aggregate view of t2 and force a nested loop into t1 – and that would only pull the minimum volume of data across the network. (The efficiency would then depend on a suitable index being in place, of course, and the number of trips across the network – and in 9i that particular rewrite might have happened automatically).

    Comment by Jonathan Lewis — December 6, 2008 @ 10:19 am GMT Dec 6,2008 | Reply

  3. We used to face this problem often and therefore had to come up with a permanent solution for this.
    Most of the time the context is such for us that PL/SQL is an alternative.
    So we do BULK COLLECT into collections and INSERT … SELECT from TABLE(v_collection).
    As for the bulk collect we normally use an explicit cursor with FETCH … BULK COLLECT … LIMIT to prepare for forseeable/unforseeable increase in the amount of the results to be pulled.
    Note: FORALL INSERT could be used sometimes, but then it would end up being several small inserts and not just a few large ones. In cases where we use APPEND hint this would be a problem, since the single row insert cannot take advantage of the driect load.

    Comment by Andras Gabor — December 6, 2008 @ 4:31 pm GMT Dec 6,2008 | Reply

  4. Andras,

    Thanks for the comment – that’s an excellent suggestion.

    Comment by Jonathan Lewis — December 6, 2008 @ 7:47 pm GMT Dec 6,2008 | Reply

  5. Hi Jonathan

    FWIW, back in 2006 I sent an enhancement request directly to Mohamed about that problem. His reply was the following: “Your enhancement request makes sense. I will add an item to track it.”
    Apparently, it is either not so easy to implement such a change or the required change has a very low priority…

    IMHO the explanation given in Metalink does not make really sense… nobody is asking to run the DML remotely! Fact is that in such execution plans part of the query is executed remotely and, therefore, a “R->S” operation is needed. Now, it is for sure technically possible to do more processing remotely and to send back the data just before inserting it.


    Comment by Christian Antognini — December 7, 2008 @ 9:27 am GMT Dec 7,2008 | Reply

  6. “IMHO the explanation given in Metalink does not make really sense… nobody is asking to run the DML remotely!”
    I think they mean that the db where the data is being changed must be the “master”/”driving site”.
    But there’s no problem (in XE) with :
    insert into x@db1 (dummy) select substr(username,1,1) from all_users@db2;
    which shows up with an operation of “INSERT STATEMENT REMOTE”
    Similarly an update involving two separate remote dbs comes up as “UPDATE STATEMENT REMOTE”
    So the ‘data change’ component CAN be done on a non-local db.

    Comment by Gary — December 7, 2008 @ 10:15 am GMT Dec 7,2008 | Reply

  7. Hi Gary

    > I think they mean that the db where the data is being changed
    > must be the “master”/”driving site”.

    Yes, this is my understanding as well. But, as I wrote, it does not make sense… or, at least, it could be different ;-)

    In the documentation is written “The DRIVING_SITE hint instructs the optimizer to execute the query at a different site than that selected by the database. This hint is useful if you are using distributed query optimization.”

    My point is that the DRIVING_SITE hint should not apply to the DML part of a statement but only (as the documentation says) to the query. In a CTAS or INSERT SELECT statement that would mean that the driving site might be on a different databases than the target database (where data is inserted/updated/deleted).

    Honestly, I do not see a technical reason why that should not be possible. Take, for example, a plain query executed remotely for which the remote database is also the driving site. Where is sent the output data? Locally. So, why it should not be possible to write that data into a local table instead than sending it back to a client? Once again, I do not see why that should not be possible. Hence, this is an implementation restriction…


    Comment by Christian Antognini — December 7, 2008 @ 10:41 pm GMT Dec 7,2008 | Reply

  8. Hi Chris,
    I agree that it is an implementation restriction. I don’t think there’s any theoretical reason why it can’t be done. But equally I can see that some-one may look at the existing code and try to work out what needs to be done in terms of read and write consistency, locking, mutating tables, deferrable constraints, multi-table inserts, logging DML exceptions, backwards compatibility, trace, audit etc, and decide its not worth the trouble.

    Jonathan’s footnote “you’ll have to create a join view at the remote site” needs careful weighing up.
    You create db_link “ab” to go from “a” to “b” and “ba” to go from “b” back to “a”.
    In “a” your “session 1” does a “select * from x@ab”. In “b”, “x” is a synonym for “y@ba”. The actual result from object “y” will be from a separate database session than your original “session 1”. It won’t see any of session 1’s uncommitted changes to the table and may have a different isolation level, sys_context values (VPD), roles enabled, package state…

    Comment by gary — December 8, 2008 @ 4:01 am GMT Dec 8,2008 | Reply

  9. Gary,

    I think your final comment about “careful weighing up” applies to all cases of distributed queries and transactions.

    I recall cases in earlier versions of Oracle where I could run a (long) distributed nested loop join, and get inconsistent results by changing the data at the remote table as the nested loop was running.

    I’m not sure that your example would be an more problematic than not creating the view, by the way – but I’d want to check it before I committed myself.

    If you have links that double back like this, I think that (recent versions of) Oracle may detect what’s going on and make the queries tightly-coupled branches of the same transaction. (This wouldn’t handle the VPD / sys_context features necessarily, but they’re likely to behave in unexpected ways anyway)

    Comment by Jonathan Lewis — December 8, 2008 @ 9:11 pm GMT Dec 8,2008 | Reply

  10. Very timely for me – I am reviewing a data warehouse where a lot of the ETL is built around joins running on remote (source) machines. Coupled with the explain plans I captured and this blog piece I have convinced the customer that they need to rethink how they do this part of their batch process.

    I suspect that they (in the long distant past) gave these these troublesome distributed queries to a DBA to tune, and then wrapped the tuned select statements with the insert into and did not notice the change in plan.

    Comment by Pete Scott — December 10, 2008 @ 5:31 pm GMT Dec 10,2008 | Reply

  11. just to add to Andras … yeah I use the same approach BULK COLLECT, I use FORALL and commit after X number of rows (one large insert can eat a lot of memory – even 1GB+ !!) – but of course cannot be simply rollbacked. I have whole thing is as EXECUTE IMMEDIATE (which allows to adjust hints on a fly) in one stored procedure with detail logging about number of rows inserted… works like a charm even for 100mm+ inserts.


    I tried to use pipelined functions but although it can speed up a lot the whole insert, pipeline functions do not like (if I can use that word) db-limk in parallel. When I ran the inserts in single server, I noticed good improvement, but it went all away with db-link iserts (the main point of this).

    Comment by jiri — January 13, 2009 @ 11:40 pm GMT Jan 13,2009 | Reply

  12. […] Execution plans, Hints — Jonathan Lewis @ 7:20 am UTC Jul 1,2009 Some time ago I wrote a note about distributed DML, pointing out that the driving_site() hint works with distributed queries but not with distributed […]

    Pingback by Distributed Queries « Oracle Scratchpad — July 5, 2009 @ 6:11 am BST Jul 5,2009 | Reply

  13. […] Jonathan Lewis @ 9:04 pm UTC May 19,2010 I’ve previously published a couple of notes (here and here) about the driving_site() hint. The first note pointed out that the hint was deliberately […]

    Pingback by Ignoring Hints « Oracle Scratchpad — May 19, 2010 @ 9:05 pm BST May 19,2010 | Reply

  14. […] an article that I wrote about the /*+ driving_site */ hint a few months ago I pointed out that the hint was not supposed to work with “create table as […]

    Pingback by Distributed Pipelines « Oracle Scratchpad — October 7, 2010 @ 6:08 pm BST Oct 7,2010 | Reply

  15. […] consequence the driving site for a distributed query will be the local database unless you use the /*+ driving_site */ hint to change […]

    Pingback by Distributed Queries – 2 « Oracle Scratchpad — November 24, 2010 @ 10:12 am GMT Nov 24,2010 | Reply

  16. Andras Gabor …Thank you for the wonderful comment . By bulk collecting the select into an array and then inserting forall works perfectly .

    Comment by Hass Khan — September 5, 2012 @ 5:19 am BST Sep 5,2012 | Reply

  17. […] In an earlier post I’ve described how a distributed query can operate at a remote site if it’s a simple select but has to operate at the local site if it’s a CTAS (create as select) or insert as select. There’s (at least) one special case where this turns out to be untrue … provided you write the query in the correct fashion. I discovered this only as a result of doing a few experiments in response to a question on the OTN database forum. […]

    Pingback by Distributed Sets | Oracle Scratchpad — September 18, 2013 @ 6:14 pm BST Sep 18,2013 | Reply

  18. […] cases this simply means that the query was a distributed query and the plan changed because the driving site changed from the remote to the local server. There are a couple of other reasons, but distributed […]

    Pingback by Scalar Subq Bug | Oracle Scratchpad — December 30, 2019 @ 9:30 am GMT Dec 30,2019 | Reply

  19. […] distributed instead of fully remote, and the query for a CTAS or “insert/select” has to be driven from the local site if it’s a distributed […]

    Pingback by Distributed Trap | Oracle Scratchpad — July 24, 2020 @ 10:21 am BST Jul 24,2020 | Reply

  20. […] about the structure of the statement was that it was just a variation of the standard problem of distributed DML. I haven’t written anything previously about how using sequences can introduce the problem so […]

    Pingback by Distributed Sequences | Oracle Scratchpad — March 12, 2021 @ 9:09 am GMT Mar 12,2021 | Reply

  21. […] table the driving site has to become the site that holds the global temporary tables (in fact this isn’t just a feature of GTTs). So there was another thing prompting me to run a test. (And then I suggested using the /*+ […]

    Pingback by Distributed Query | Oracle Scratchpad — August 23, 2021 @ 5:24 pm BST Aug 23,2021 | Reply

  22. […] site but the plan for a create local_table as select (CTAS) or insert into local_table/select is required to use the local site as the driving site. There are workarounds – if you’re allowed to use them – such as creating a view […]

    Pingback by Never … | Oracle Scratchpad — January 11, 2022 @ 3:36 pm GMT Jan 11,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: