Oracle Scratchpad

November 14, 2016

Distributed Trap

Filed under: distributed,Oracle — Jonathan Lewis @ 1:19 pm GMT Nov 14,2016

Here’s an interesting (and potentially very useful) observation from an OTN database forum thread that appeared at the end of last week. It concerns the problems of pulling data from remote systems, and I’ll start by building some data:

rem
rem     Script:         remote_insert_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2016
rem
rem     Last tested
rem             12.1.0.2
rem             11.2.0.4
rem

create table t1
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        rownum                  id,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',100)           padding
from
        generator       v1,
        generator       v2
where
        rownum <= 50000
;

alter table t1 add constraint t1_pk primary key(id);

create table t2
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        trunc(sysdate) + rownum d1,
        rownum                  id,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',50)            padding
from
        generator
where
        rownum <= 500
;

alter table t2 add constraint t2_pk primary key(id);

create table t3
as
select sysdate d1, t1.* from t1
where rownum = 0
;

--  Now gather stats if you need to (depending on version)

I’ve created three tables. Table t3 is an empty copy of table t1 with a date column added, and t2 is some sort of reference table that looks rather like table t1 but has a lot less data. Now I’m going to pretend that t1 and t2 are in a remote database while t3 is in the local database and copy data from t1 to t3, omitting any data that is referenced in t2. The SQL is simple:


define m_target=test@loopback

insert into t3(
        id, small_vc, padding
)
select
        t1.id, t1.small_vc, t1.padding
from
        t1@&m_target    t1
where
        t1.id not in (
                select t2.id from t2@&m_target
        )
;

----------------------------------------------------------------------
| Id  | Operation                | Name | Cost (%CPU)| Inst   |IN-OUT|
----------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     0   (0)|        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |            |        |      |
|   2 |   REMOTE                 |      |            |   TEST | R->S |
----------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------
   2 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT
       "A1"."ID","A1"."SMALL_VC","A1"."PADDING" FROM "T1" "A1" WHERE
       "A1"."ID"<>ALL (SELECT "A2"."ID" FROM "T2" "A2") (accessing
       'TEST.LOCALDOMAIN@LOOPBACK' )

I’ve set up an SQL*Plus substitution variable to hold a database link name (and used a loopback qualifier so that I can pretend t1 and t2 are in a remote database. The execution plan (taken from an explain plan, but confirmed by running the query and calling dbms_xplan.display_cursor) shows that Oracle has executed select part of the insert as a “fully remote” statement – which is nice.

Unfortunately I forgot to include a datestamp as I copied the data over. So let’s modify the query to do that:


insert into t3(
        d1,
        id, small_vc, padding
)
select
        sysdate,
        t1.id, t1.small_vc, t1.padding
from
        t1@&m_target    t1
where
        t1.id not in (
                select t2.id from t2@&m_target
        )
;

--------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |      1 |        |   123 (100)|      0 |00:00:27.42 |   10908 |
|   1 |  LOAD TABLE CONVENTIONAL |      |      1 |        |            |      0 |00:00:27.42 |   10908 |
|*  2 |   FILTER                 |      |      1 |        |            |  49500 |00:00:26.51 |       0 |
|   3 |    REMOTE                | T1   |      1 |  50000 |   113   (3)|  50000 |00:00:00.33 |       0 |
|   4 |    REMOTE                | T2   |  50000 |      1 |     0   (0)|    500 |00:00:25.29 |       0 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( NOT EXISTS (SELECT 0 FROM  "T2" WHERE "T2"."ID"=:B1))

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT /*+ OPAQUE_TRANSFORM */ "ID","SMALL_VC","PADDING" FROM "T1" "T1"
       (accessing 'TEST.LOCALDOMAIN@LOOPBACK' )

   4 - SELECT /*+ OPAQUE_TRANSFORM */ "ID" FROM "T2" "T2" WHERE "ID"=:1 (accessing
       'TEST.LOCALDOMAIN@LOOPBACK' )

Whoops, the plan just changed – it also took 27.4 seconds instead of the 1.1 seconds that it used to – that’s because of the 50,000 remote calls to execute a remote query for the subquery filter. The addition of sysdate (which is the local sysdate@!) to the select list has made the select statement distributed instead of fully remote, and the query for a CTAS or “insert/select” has to be driven from the local site if it’s a distributed query.

Okay, plan (b), don’t insert sysdate, add it to the table as a default:


alter table t3 modify d1 default sysdate;

This doesn’t help; even though the query doesn’t mention sysdate explicitly the query is still treated as disrtibuted query.

Okay, plan (c) – don’t insert sysdate, add a trigger to the table:

alter table t3 modify d1 default null;

create or replace trigger t3_bri
before insert on t3
for each row
begin
        :new.d1 := sysdate;
end;
/


And this works (in 11.2.0.4 and 12.1.0.2, at least, which are the versions I tested).

I could have made the trigger a little more sophisticated, of course, but the point of the post was simply to demonstrate a problem and a simple workaround.

There are probably several other commonly used features (various sys_context() calls, perhaps) that have the same effect.

 

9 Comments »

  1. What about using a constant or a bind? am sure the exact timing is not as important.

    Comment by Christo Kutrovsky — November 14, 2016 @ 5:00 pm GMT Nov 14,2016 | Reply

  2. Jonathan,

    One thing I have started paying attention to it when troublshooting performance issues with distributed queries is the materialization of
    Common Table Expression (aka as subquery factoring or with clause). According to a couple of real life issues I have recently solved, when a TEMP TABLE is materialized in the distant database Oracle has then no choice other than to send the complete materialized TEMP TABLE to the local site.

    With this trick in mind I have overcome a couple of performance issues due to Oracle going back and forth between the local and the distant database.

    Applied to your current case here, the idea is to materialized both t1 and t2 table separately so that Oracle will be forced to send them separately
    to the local site where they will be joined and filtered locally. Something resembling to the following:

    
    insert into t3(
         d1,
         id,
         small_vc,
         padding
         )
    with 
     got_my_t1 
       as (select /*+ materialize */
            t1.id, t1.small_vc, t1.padding
           from t1@dist@loopback t1
           ),
     got_my_t2 
       as (select /*+ materialize */
           t2.id from t2@dist@loopback t2
           )
    select
         sysdate
        ,r1.id
        ,r1.small_vc
        ,r1.padding
    from
        got_my_t1 r1
    where
        r1.id not in (select r2.id from got_my_t2 r2); 
        
    Plan hash value: 3835109406
    -----------------------------------------------------------------------------------------------------
    Id  | Operation                  | Name                      | Starts | E-Rows | A-Rows |   A-Time   | 
    ------------------------------------------------------------------------------------------------------
      0 | INSERT STATEMENT           |                           |      1 |        |      0 |00:00:00.94 |
      1 |  TEMP TABLE TRANSFORMATION |                           |      1 |        |      0 |00:00:00.94 |  
      2 |   LOAD AS SELECT           |                           |      1 |        |      0 |00:00:00.29 | 
      3 |    REMOTE                  | T1                        |      1 |  50000 |  50000 |00:00:00.09 |
      4 |   LOAD AS SELECT           |                           |      1 |        |      0 |00:00:00.01 |
      5 |    REMOTE                  | T2                        |      1 |    500 |    500 |00:00:00.01 |
      6 |   LOAD TABLE CONVENTIONAL  | T3                        |      1 |        |      0 |00:00:00.64 |
    * 7 |    HASH JOIN RIGHT ANTI    |                           |      1 |  50000 |  49500 |00:00:00.42 |  
      8 |     VIEW                   |                           |      1 |    500 |    500 |00:00:00.01 |  
      9 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6614_9AA0D2 |      1 |    500 |    500 |00:00:00.01 |  
     10 |     VIEW                   |                           |      1 |  50000 |  50000 |00:00:00.19 |  
     11 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6613_9AA0D2 |      1 |  50000 |  50000 |00:00:00.07 |  
    ------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       7 - access("R1"."ID"="R2"."ID")
     
     Remote SQL Information (identified by operation id):
    ----------------------------------------------------
       3 - SELECT "ID","SMALL_VC","PADDING" FROM "T1" "T1" (accessing 'DIST@LOOPBACK' )
       5 - SELECT "ID" FROM "T2" "T2" (accessing 'DIST@LOOPBACK' )
    
    

    Best Regards
    Mohamed Houri

    Comment by hourim — November 15, 2016 @ 8:03 pm GMT Nov 15,2016 | Reply

  3. I’ve often used SYSDATE@dblink to good effect in tuning cases like this, to make the entire query go remote.

    Comment by Jason Bucata — November 17, 2016 @ 3:26 pm GMT Nov 17,2016 | Reply

    • Jason,

      Good idea.

      Occasionally likely to cause problems, though, in multi-national systems with difference time-zones, especially if they have different dates for switching to daylight savings time. I wonder if you could select a systimestamp@dblink but with a call to convert to the (known, literally quoted) local timezone without experiencing the problem – the conversion function might end up operating at the local site and break things.

      Comment by Jonathan Lewis — November 22, 2016 @ 10:14 am GMT Nov 22,2016 | Reply

      • Jonathan (and Jason),

        If possible, could you please explain what is meant by “SYSDATE@dblink”? I’m asking that because selecting from a function on a remote database, e.g. “select custom_fnc@lnk from dual;” works, however “select sysdate@lnk from dual;” would return “ORA-00923: FROM keyword not found where expected”.
        How is it possible to make a query run as fully remote using this syntax?

        Another thing which seems interesting to me is that the first DML statement you presented above (the one without sysdate) actually ran fully remote. As you mentioned in this post from 2008: https://jonathanlewis.wordpress.com/2008/12/05/distributed-dml/ , and as MOS Bug 5517609 states, “A distributed DML statement must execute on the database where the DML target resides. The DRIVING_SITE hint cannot override this.”.
        Additionally, in this post https://jonathanlewis.wordpress.com/2013/09/18/distributed-sets/ you had to use a “trick” to make the DML statement run fully remote, even though both tables referenced in the SELECT part of the query were remote, as is the case in this blog post. So it seems that what’s stated in the bug mentioned above isn’t always true. Is it possible that’s also what’s happening in the case you presented in this post, or is there some difference between the two cases I quoted above and the one you are presenting in this post?

        Thank you and regards,
        Jure Bratina

        Comment by Jure Bratina — November 25, 2016 @ 2:31 pm GMT Nov 25,2016 | Reply

        • Jure,

          The example in the first blog note you reference isn’t a distributed query it’s fully remote and I should have made that distinction in the blog.

          The second example is ALSO fully remote – and I should have raised the point that this was a fully remote query that doesn’t follow the allowed pattern for fully remote queries. I don’t know why this is, but perhaps it’s case of the exact rule being about query blocks rather than queries.

          I have to confess that when I read Jason’s comment I thought of the way that you get references to “sysdate@!” (meaning the local sysdate) if you include sysdate in a predicate, so I assumed that “sysdate@{somewhere}” was legal and didn’t test for myself exactly how to do it; so I’m going to pass that one on to Jason. I did try “select sysdate@remote from dual@remote” but got the same error that you got.

          Comment by Jonathan Lewis — November 25, 2016 @ 3:49 pm GMT Nov 25,2016

  4. I cheated… that was shorthand for “SELECT SYSDATE FROM DUAL@remote”

    Comment by Jason B. — November 27, 2016 @ 1:39 am GMT Nov 27,2016 | Reply

    • Jason,

      Sorry – I still need some help on that. How is selecting sysdate from dual@remote going to make the query behave any differently from selecting sysdate from t1@remote ?
      I tried to introduce your cheat in two or three ways and the following worked:

      
      insert into t3(
              d1,
              id, small_vc, padding
      )
      select
              (select sysdate sd from dual@&m_target) sd,
              id, small_vc, padding
      from    (
              select  /*+ no_merge */
                      t1.id, t1.small_vc, t1.padding
              from
                      t1@&m_target    t1
              where
                      t1.id not in (
                              select t2.id from t2@&m_target
                      )
              )
      ;
      
      

      Then I realised that with the /*+ no_merge */ hint the selection of sysdate could be from the local dual rather than the remote, and that gave me the following plan:

      
      -------------------------------------------------------------------------------------------
      | Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
      -------------------------------------------------------------------------------------------
      |   0 | INSERT STATEMENT         |      |      1 |        |      0 |00:00:02.90 |   11128 |
      |   1 |  LOAD TABLE CONVENTIONAL |      |      1 |        |      0 |00:00:02.90 |   11128 |
      |   2 |   FAST DUAL              |      |      1 |      1 |      1 |00:00:00.01 |       0 |
      |   3 |   VIEW                   |      |      1 |  50000 |  49500 |00:00:00.76 |       0 |
      |   4 |    REMOTE                |      |      1 |        |  49500 |00:00:00.29 |       0 |
      -------------------------------------------------------------------------------------------
      
      
      

      Comment by Jonathan Lewis — November 29, 2016 @ 3:16 pm GMT Nov 29,2016 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.