optimization over db link

johnnie d

If I have a distributed transaction between a source and target database, such as :-

'INSERT INTO [email protected]

SELECT * FROM [email protected]

MINUS

SELECT * FROM [email protected] AS AT :T'

*(where T is an arbitrary timestamp)

How do I force the SELECT and MINUS operations to take place on the SOURCE database only ?

At the moment the operations seem to always take place on the target database - that is, the legs of subquery are downloaded over SQL*Net and

the MINUS performed on the target DB. This is hideously inefficient.

Is there a parameter I need to set or a hint I need to run ?

Regs

JOhnnie

Jonathan Lewis

Johnnied,

I've done a little testing  - it gets awkward because of the need to waste some time and resources to avoid the error ORA-01466: unable to read data - table definition has changed

Here's a sample:

SQL> l
  1  insert into t3
  2  select  *
  3  from    (
  4          select * from [email protected]&m_remote
  5          minus
  6          select * from [email protected]&m_remote as of timestamp (timestamp ' 2013-07-09 15:00:36')
  7*         )
SQL> /

100 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 1788691278

----------------------------------------------------------------------
| Id  | Operation                | Name | Cost (%CPU)| Inst   |IN-OUT|
----------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     0   (0)|        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |            |        |      |
|   2 |   REMOTE                 |      |            | [email protected]~ | R->S |
----------------------------------------------------------------------

Unfortunately, for reasons I don't understand at present, my 11.2.0.3 doesn't seem to like timestamps AT ALL, even when the query isn't going remote. It keeps giving me error ORA-08186: invalid timestamp specified; but it seems to work on 12.1.0.1 and 10.2.0.3

Regards

Jonathan Lewis

Now on twitter @jloracle

JohnWatson

Driving site,

Comments

Message was edited by: JohnWatson Yes, I see: I cannot get a compound query to run fully remotely when it is used for an insert. Thank you, JL, I've learnt something (two things, if one counts not making assumptions).

Jonathan Lewis

johnnied wrote:


'INSERT INTO [email protected]
SELECT * FROM [email protected]
MINUS
SELECT * FROM [email protected] AS AT :T'

*(where T is an arbitrary timestamp)

How do I force the SELECT and MINUS operations to take place on the SOURCE database only ?

At the moment the operations seem to always take place on the target database - that is, the legs of subquery are downloaded over SQL*Net and
the MINUS performed on the target DB. This is hideously inefficient.

Basically this is the way it has to work at present - CTAS, or insert as select, has to run at the target site - there is no parameter or hint to change it. There are, however, various ways to re-engineer the problem so that the MINUS will be done remotely, a simple one (if you have suitable privileges on the remote database) is to create a view on the remote database.  An alternative is to create a pipelined function on the local database that returns the result of a select on the remote database you can then insert into local select from pipelined_function.

Sample here: Distributed Pipelines | Oracle Scratchpad

Regards

Jonathan Lewis

Now on twitter @jloracle

johnnie d

many thanks jonathan

I kind of gathered that there were no other options, I'll probably try the view route or something similar.

Johnnie

Jonathan Lewis

I hope you can "un-correct" my answer, because it's not correct.

I thought I'd run up a quick copy of your code to see if it worked on 12c - and discovered that it did work, and then I discovered that it worked on 11.2.0.3 - and then realised that I'd done a couple of things differently from you.

Code that doesn't work (two remote selects)

insert into t3
select * from t1
minus
select * from t2
;

Code that does work (one remote select)

insert into t3
select * 
from (
      select * from t1
      minus
      select * from t2
     )
;

Having discovered that I had tested a different piece of code from yours, I then realised that I hadn't tested the effect of the AS AT (flashback) clause - and remembered that I'd seen something somewhere that said this caused problems anyway with distributed queries. So I have a little more investigation to do.  In the meantime you could try wrapping an extra inline view around your query - the MINUS seems to be a special case.

Regards

Jonathan Lewis

Now on twitter @jloracle

johnnie d

Jonathan

As you wish, I've re-marked as 'helpful'.

I need the AS AT because (to give you part of the wider picture) I'm synchronizing a large table over a slow network connection prior to setting up streaming rules. The tables are changing over the course of the initial INSERT AS SELECT transaction, so they need a later 'catchup' using the timestamp from which the INSERT AS SELECT was originall started. Fortunately it is all insert/delete so the processing is quite straighforward.

I'll try the extra inline view and get back to you ..

johnnie

Jonathan Lewis

Johnnied,

I've done a little testing  - it gets awkward because of the need to waste some time and resources to avoid the error ORA-01466: unable to read data - table definition has changed

Here's a sample:

SQL> l
  1  insert into t3
  2  select  *
  3  from    (
  4          select * from [email protected]&m_remote
  5          minus
  6          select * from [email protected]&m_remote as of timestamp (timestamp ' 2013-07-09 15:00:36')
  7*         )
SQL> /

100 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 1788691278

----------------------------------------------------------------------
| Id  | Operation                | Name | Cost (%CPU)| Inst   |IN-OUT|
----------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     0   (0)|        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |            |        |      |
|   2 |   REMOTE                 |      |            | [email protected]~ | R->S |
----------------------------------------------------------------------

Unfortunately, for reasons I don't understand at present, my 11.2.0.3 doesn't seem to like timestamps AT ALL, even when the query isn't going remote. It keeps giving me error ORA-08186: invalid timestamp specified; but it seems to work on 12.1.0.1 and 10.2.0.3

Regards

Jonathan Lewis

Now on twitter @jloracle

johnnie d

Jonathan

I've tried running the inline view code and it works - on 11.2.0.3 64 bit , linux OEL 6.0 (target) OEL 5.2 (source database). It's a MASSIVE improvement for us, about 100th of the time !

Regs

johnnie