Re: Strange behavior of INSERT via DB-link

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Wed, 14 Aug 2013 22:16:22 +0400
Message-ID: <CAOVevU7bQPk_2CoNFrMsV3V5mSVSMCjg7pdFXPd_tBDU8fU24Q_at_mail.gmail.com>

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' ) 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)
select misha_seq.nextval,

            c
from t_remote

On Wed, Aug 14, 2013 at 9:36 PM, Michael Rosenblum <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
>       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_at_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_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"_at_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
>
>
>

-- 
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:16:22 CEST