Forum Stats

  • 3,826,750 Users
  • 2,260,703 Discussions
  • 7,897,071 Comments

Discussions

optimization over db link

johnnie d
johnnie d Member Posts: 274
edited Jul 11, 2013 5:33AM in General Database Discussions

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

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,975 Blue Diamond
    Answer ✓

    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

Answers

  • JohnWatson
    JohnWatson Member Posts: 2,461 Gold Trophy
    edited Jul 9, 2013 7:54AM

    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
    Jonathan Lewis Member Posts: 9,975 Blue Diamond
    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

    Jonathan Lewis
  • johnnie d
    johnnie d Member Posts: 274

    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
    Jonathan Lewis Member Posts: 9,975 Blue Diamond
    edited Jul 9, 2013 10:23AM


    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

    Jonathan Lewis
  • johnnie d
    johnnie d Member Posts: 274

    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
    Jonathan Lewis Member Posts: 9,975 Blue Diamond
    Answer ✓

    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
    johnnie d Member Posts: 274

    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

This discussion has been closed.