Oracle Scratchpad

September 18, 2013

Distributed Sets

Filed under: distributed,Oracle,Performance,sorting — Jonathan Lewis @ 6:14 pm BST Sep 18,2013

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.


August 19, 2013

Distributed Queries – 3

Filed under: distributed,Histograms,Oracle — Jonathan Lewis @ 7:25 am BST Aug 19,2013

A comment I’ve made many times in the past about distributed queries is that Oracle doesn’t try to retrieve histogram information from remote databases when optimizing a query. Checking back through previous posts, though, I don’t think I’ve ever mentioned it on the blog – so here’s an example demonstrating the point.


November 24, 2010

Distributed Queries – 2

Filed under: CBO,distributed — Jonathan Lewis @ 9:46 am BST Nov 24,2010

I have often said that the optimizer “forgets” that it is dealing with a distributed query once it has collected the stats that it can about the objects in the query, and that as a consequence the driving site for a distributed query will be the local database unless you use the /*+ driving_site */ hint to change it.

While investigating an oddity with a distributed query between two databases a few days, I noticed something in the 10053 trace file that made me change my mind, and go back to look at earlier versions of Oracle.

October 11, 2010

Distributed Objects

Filed under: distributed,Performance,Troubleshooting — Jonathan Lewis @ 7:12 pm BST Oct 11,2010

I recently came across a tidy solution to a common problem – how to minimise code maintenance in a procedure while maximising flexibility of the procedure. The task was fairly simple – create a ref cursor for a calling program to return data that (a) followed complex selection rules and (b) allowed the user to specify numerous types of input.

The principle was simple – the final ref cursor was driven by a list of (say) order ids – and the details to be returned about those orders required some fairly complex SQL to execute. To separate the complexity of constructing the list of columns from the complexity of identifying the required rows the developers had split the procedure into two stages. First, select the list of relevant order ids using one of several possible statements – the appropriate statement being derived from analysis of the inputs to the procedure; secondly open a ref cursor using that list of order ids. In this way if a new set of rules for selection appeared the only new code needed was a new query to select the ids – the main body of code didn’t need to be modified and re-optimised.

October 7, 2010

Distributed Pipelines

Filed under: distributed,Performance — Jonathan Lewis @ 6:06 pm BST Oct 7,2010

In 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 select” (CTAS) and “insert as select”. One of the people commenting on the note mentioned pipelined function as a workaround to this limitation – and I’ve finally got around to writing a note about the method.

The idea is simple. If you can write a distributed select statement that takes advantage of the /*+ driving_site */ hint to work efficiently, you can wrap the statement in a pl/sql cursor loop and stick that loop into a pipelined function to maximise the efficiency of create or insert as select. Here’s some sample code (tested on to demonstrate the principle:

May 19, 2010

Ignoring Hints

Filed under: distributed,Hints,Ignoring Hints,Troubleshooting,Tuning — Jonathan Lewis @ 9:04 pm BST May 19,2010

I’ve previously published a couple of notes (hereand here) about the use of the driving_site() hint with distributed queries. The first note pointed out that the hint was deliberately ignored if you write a local CTAS or INSERT that did a distributed query. I’ve just found another case where the hint is ignored – this time in a simple SELECT statement.

Try running an ordinary distributed query from the SYS account, and then try using the driving_site() hint to make it run at the remote site. When I tried this a few days ago I ended up wasting half an hour translating some SQL from ANSI to Oracle dialect because I thought that the ANSI was making Oracle transform the query in a way that lost the hint – then I discovered that both versions of the code worked correctly if I logged in as a different user.

I was running my queries between two databases using – I won’t guarantee you get the same results on other versions, but it looks like SYS doesn’t honour the driving_site() hint. I can’t think of a robust argument why this should be the case, but if I were forced to do some vague hand-waving I’d probably mumble something about potential security loopholes.

Footnote: I should, of course, have mentioned that there are all sorts of things that behave in unexpected ways if you are logged on as SYS, and that you shouldn’t be logged on as SYS – especially in a production system.

[Further reading on “ignoring hints”]

July 1, 2009

Distributed Queries

Filed under: distributed,Execution plans,Hints — Jonathan Lewis @ 7:20 am BST 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 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.


December 5, 2008

Distributed DML

Filed under: distributed,Hints,Infrastructure,Performance,Tuning — Jonathan Lewis @ 9:19 pm BST 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:

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


November 13, 2006

Distributed Oddity

Filed under: distributed,Infrastructure,Troubleshooting — Jonathan Lewis @ 9:55 pm BST Nov 13,2006

An interesting side-effect of blogging (rather than writing up a more formal document for my web site) is that it feels perfectly reasonable to throw out some odd observations without necessarily having to supply an explanation or solution.

So here’s the bare bones of an oddity relating to distributed queries that someone emailed me a couple of days ago:

INST1 -  Insert data into table X and commit 
INST2 -  select from X@DB1,dual;     


The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 6,323 other followers