Strange behavior of INSERT via DB-link

From: Michael Rosenblum <mrosenblum_at_dulcian.com>
Date: Wed, 14 Aug 2013 13:36:45 -0400
Message-ID: <2673A0170447634DA7F7ABCA51E89FA901BFFE7F88FF_at_MAIL2.dulcian.local>

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

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

  ---

| 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

Received on Wed Aug 14 2013 - 19:36:45 CEST