This discussion is archived
7 Replies Latest reply: Jul 11, 2013 2:33 AM by johnnied RSS

optimization over db link

johnnied Explorer
Currently Being Moderated

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

 

'INSERT INTO jim@target

SELECT * FROM jim@source

MINUS

SELECT * FROM jim@source 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

  • 1. Re: optimization over db link
    JohnWatson Guru
    Currently Being Moderated

    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).

  • 2. Re: optimization over db link
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    johnnied wrote:

     

     

    'INSERT INTO jim@target

    SELECT * FROM jim@source

    MINUS

    SELECT * FROM jim@source 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

  • 3. Re: optimization over db link
    johnnied Explorer
    Currently Being Moderated

    many thanks jonathan

     

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

     

    Johnnie

  • 4. Re: optimization over db link
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated


    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

  • 5. Re: optimization over db link
    johnnied Explorer
    Currently Being Moderated

    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

  • 6. Re: optimization over db link
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    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 t1@&m_remote
      5          minus
      6          select * from t2@&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                 |      |            | ORCL@~ | 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

  • 7. Re: optimization over db link
    johnnied Explorer
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points