Home -> Mailing Lists -> Oracle-L -> RE: Strange behavior of INSERT via DB-link

RE: Strange behavior of INSERT via DB-link

From: Michael Rosenblum <mrosenblum_at_dulcian.com>
Date: Wed, 14 Aug 2013 14:24:17 -0400
Message-ID: <2673A0170447634DA7F7ABCA51E89FA901BFFE7F8902_at_MAIL2.dulcian.local> 





Here at Dulcian we took standardization of all environments too seriously - I don't have anything available other than 11.2.0.3, unfortunately :)
10053 trace - see attached. IMHO, trace also looks strange.



Thanks,


Michael



From: Sayan Malakshinov [mailto:xt.and.r_at_gmail.com]
Sent: Wednesday, August 14, 2013 2:16 PM
To: Michael Rosenblum


Cc: oracle-l_at_freelists.org


Subject: Re: Strange behavior of INSERT via DB-link



Hi, Michael!



Could you show 10053 trace? Because I've tested your example on 11.2.0.3 (Linux) and on 11.2.0.1(Windows) successfully:



    
  
  •   11.2.0.3
SQL> explain plan for
  2  insert into misha01 (a,b)
  3  select misha_seq.nextval,
  4             substr(b,1,2)
  5  from misha01_at_pdb12c;






Explained.



SQL> _at_xplan



PLAN_TABLE_OUTPUT





Plan hash value: 1591010168





| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|





|   0 | INSERT STATEMENT         |           |    82 | 10660 |     2   (0)| 00:00:01 |        |      |


|   1 |  LOAD TABLE CONVENTIONAL | MISHA01   |       |       |            |          |        |      |


|   2 |   SEQUENCE               | MISHA_SEQ |       |       |            |          |        |      |


|   3 |    REMOTE                | MISHA01   |    82 | 10660 |     2   (0)| 00:00:01 | PDB12C | R->S |







Remote SQL Information (identified by operation id):





   3 - SELECT /*+ OPAQUE_TRANSFORM */ "B" FROM "MISHA01" "A1" (accessing 'PDB12C.ORG<http://PDB12C.ORG>' )




16 rows selected.



Also could you try this:



insert into misha01 (a,b)


with t_remote as (select/*+ inline no_merge */ substr(b,1,2) c from misha01_at_sourceDb.server1<mailto:misha01_at_sourceDb.server1>)
select misha_seq.nextval,


            c


from t_remote



On Wed, Aug 14, 2013 at 9:36 PM, Michael Rosenblum <mrosenblum_at_dulcian.com<mailto:mrosenblum_at_dulcian.com>> wrote:
Hi, all!



I have a bad feeling that I am missing something is the following scenario, and really hope that somebody can explain to me what's going on.



    
  
  1.   Settings
  
  2.   Environment:

  
      
    
    •   Two Oracle database. Both - 11.2.0.3  on 64-bit  MS Windows 2008
    
    •   GLOBAL_NAMES=TRUE on both sides
    
    •   Database are linked by a DB-link


  
    
  
  3.   Database SourceDB:
                 create table misha01 (a number, b varchar2(256));

  
  4.   Database DestinationDB:
                create table misha01 (a number, b varchar2(256));
                create sequence misha_seq;

  
  5.   Statement to be executed from destination:






insert into misha01 (a,b)


select misha_seq.nextval,


            substr(b,1,2)


from misha01_at_sourceDb.server1<mailto:misha01_at_sourceDb.server1>



3. What happens:


    
  
  1.   Oracle creates reverse session from SourceDB to DestinationDB
  
  2.   That session serves the only purpose - to fire MISHA_SEQ.NEXTVAL

  
  3.   What's even more interesting, if I remove SUBSTR function call, the behavior changes:


  
      
    
    •   explain plan for  insert into misha01
                                     select misha_seq.nextval,
                                      substr(b,1,2) --------------- use function -----------------
                                     from misha01_at_ sourceDb.server1


      

  
    





| Id  | Operation                | Name    | Cost (%CPU)| Inst   |IN-OUT|





|   0 | INSERT STATEMENT         |         |     0   (0)|        |      |


|   1 |  LOAD TABLE CONVENTIONAL | MISHA01 |            |        |      |


|   2 |   REMOTE                 |         |            | SOURCE | R->S |






Remote SQL Information (identified by operation id):




2 - EXPLAIN PLAN INTO PLAN_TABLE_at_! FOR SELECT   "MISHA_SEQ"."NEXTVAL"@DESTDB.SERVER2,SUBSTR("A1"."B",1,2) FROM  "MISHA01" "A1" (accessing 'SOURCEDB.SERVER1' )



    
  
  •   explain plan for  insert into misha01
                                     select misha_seq.nextval,
                                                  b --------------- no function -----------------
                                    from misha01_at_sourceDB.server1<mailto:misha01_at_sourceDB.server1>


    





| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|





|   0 | INSERT STATEMENT         |           |    82 | 10660 |     2   (0)| 00:00:01 |        |      |


|   1 |  LOAD TABLE CONVENTIONAL | MISHA01   |       |       |            |          |        |      |


|   2 |   SEQUENCE               | MISHA_SEQ |       |       |            |          |        |      |


|   3 |    REMOTE                | MISHA01   |    82 | 10660 |     2   (0)| 00:00:01 | SOURCE | R->S |






Remote SQL Information (identified by operation id):




3 - SELECT /*+ OPAQUE_TRANSFORM */ "B" FROM "MISHA01" "MISHA01" (accessing  'SOURCEDB.SERVER1' )



It is obvious, that the second case does what I would expect - bring the data from the remote, run sequence, insert. But In the first case (with SUBSTR) Oracle for some reasons decided to fire sequence from the other side. Why would it do an extra roundtrip???



Colleagues, I am puzzled... Any comments/suggestions?



Best regards,


Michael Rosenblum


Oracle ACE


Dulcian Inc

--
http://www.freelists.org/webpage/oracle-l




--
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org


--
http://www.freelists.org/webpage/oracle-l


Received on Wed Aug 14 2013 - 20:24:17 CEST












Original text of this message