Strange behavior of INSERT via DB-link
- From: Michael Rosenblum <mrosenblum@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Aug 2013 13:36:45 -0400
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
a. 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
b. Database SourceDB:
create table misha01 (a number, b varchar2(256));
c. Database DestinationDB:
create table misha01 (a number, b varchar2(256));
create sequence misha_seq;
2. Statement to be executed from destination:
insert into misha01 (a,b)
select misha_seq.nextval,
substr(b,1,2)
from misha01@sourceDb.server1
3. What happens:
a. Oracle creates reverse session from SourceDB to DestinationDB
b. That session serves the only purpose - to fire MISHA_SEQ.NEXTVAL
4. 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@ 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@! 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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
