Top 60 Oracle Blogs

Recent comments


Distributed Sets

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.

Here’s a little demonstration, cut-n-pasted with a little cosmetic editing from an 11gR1 SQL*Plus session:

Distributed Queries – 3

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.

I’ll start with, with some fixed system stats (as they could make a difference to repeatability), and the following data set.

Distributed Queries – 2

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.

Here are two sections extracted from a 10053 trace file running under with CPU costing (system statistics) enabled:

  Table: T1  Alias: AWAY
    Card: Original: 3240  Rounded: 41  Computed: 40.50  Non Adjusted: 40.50
  Access Path: TableScan
    Cost:  53.22  Resp: 53.22  Degree: 0
      Cost_io: 53.00  Cost_cpu: 2073815
      Resp_io: 53.00  Resp_cpu: 2073815
  Access Path: index (AllEqRange)
    Index: 0
    resc_io: 4.00  resc_cpu: 29536
    ix_sel: 0.0125  ix_sel_with_filters: 0.0125
    Cost: 4.00  Resp: 4.00  Degree: 1
  Remote table cost added, new values: cost 4.00  resc 4.00  resp .2f  Best:: AccessPath: IndexRange  Index: 0        <<===
         Cost: 4.00  Degree: 1  Resp: 4.00  Card: 40.50  Bytes: 0


  HA cost: 50.54
     resc: 50.54 resc_io: 50.00 resc_cpu: 5187262
     resp: 50.54 resp_io: 50.00 resp_cpu: 5187262
  Cost adjustment for NL join with remote table: 0.72             <<===
Join order aborted: cost > best plan cost

Note the two lines with the reference to “remote” (I’d highlight them properly, but you can’t do highlighing and code in the same text). Notice, also that one of the programmers made a bit of a mistake with their printf() call in the first of the lines – a bug that is still there in

Clearly Oracle is doing some arithmetic relating to the costs of accessing distributed data from at least (there was nothing similar in the equivalent trace file for, and I don’t have a 10.1 available for testing). Unfortunately I have yet to see a single distributed execution plan where it does the right thing – but that might be a problem related to histograms (and the failure to use them) rather than a defect in the algorithms for distributed cost.

I’ll have to spend some time looking at what it does before I can write any more about it – but given the number of times I’ve said the optimizer doesn’t do any arithmetic I thought it was important to point out that I was wrong as soon as I discovered the change.

Footnote: I have added a category “distributed” to my list of categories – and added a link to it at the bottom of every article I’ve written about distributed SQL. That’s a pattern that I may copy across other articles in the future – especially if I can find out how to order the articles by date (ascending).

[Further reading on distributed databases]