7 Replies Latest reply on Jul 11, 2013 9:33 AM by johnnie d

    optimization over db link

    johnnie d

      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

          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

            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

            1 person found this helpful
            • 3. Re: optimization over db link
              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

              • 4. Re: optimization over db link
                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

                1 person found this helpful
                • 5. Re: optimization over db link
                  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

                  • 6. Re: optimization over db link
                    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 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
                      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