This discussion is archived
3 Replies Latest reply: Nov 20, 2012 8:06 AM by NM RSS

Query started taking longer time with SQL*Net message from dblink

NM Newbie
Currently Being Moderated
Hi,

Since Yesterday we started see one query which normally used to take 3 min but now it started taking 70 min after a small change do the query instead of accessing view we started accessing directly table.

Both Schema's are on same DB.
Oracle version=11.2.0.2
OS=Solaris 10
Existing Query

WITH ot_symbol_data_v AS
     (SELECT   dat.symbol, dat.startdate, dat.enddate, oi.currencycode,
               dat.primarymarket, primsymb.symbol primarysymbol, dat.mic,
               dat.universeid, dat.symbology
          FROM onetick_symbol_data@refdata_link dat
               LEFT JOIN
               (SELECT   symbology, universeid, mic, MAX (enddate) enddate
                    FROM onetick_symbol_data@refdata_link
                GROUP BY symbology, universeid, mic) prim
               ON prim.symbology = dat.symbology
             AND prim.universeid = dat.universeid
             AND prim.mic = dat.primarymarket
               LEFT JOIN onetick_symbol_data@refdata_link primsymb
               ON prim.symbology = primsymb.symbology
             AND prim.universeid = primsymb.universeid
             AND prim.mic = primsymb.mic
             AND prim.enddate = primsymb.enddate
               JOIN onetick_isincur_data@refdata_link oi
               ON dat.universeid = oi.universeid
               JOIN
               (SELECT   universeid, MAX (enddate) AS enddate
                    FROM onetick_isincur_data@refdata_link
                GROUP BY universeid) oilatest
               ON oi.universeid = oilatest.universeid
             AND oi.enddate = oilatest.enddate
      ORDER BY dat.universeid, dat.mic, dat.symbology, dat.enddate)
SELECT      i.instrumentid
         || '||'
         || i.firsttradingdate
         || '000000|'
         || NVL (i.delisteddate, '30001231')
         || '000000|'
         || i.home_market
         || '|'
         || DECODE (imfm.feedid, 0, 'FIXN_RFA', 1, 'ALGO', 2, 'FIXNETIX')
         || '::'
         || osdv.primarysymbol
    FROM tibex_meinstrumentview i JOIN tibex_instrumentmicfeedmapview imfm
         ON i.isin = imfm.isin
       AND i.currencycode = imfm.currencycode
       AND i.home_market = imfm.mic
         JOIN rd_universeview@refdata_link u
         ON i.instrumentid = u.instrumentid AND i.instrumentstatus != 3
         and active='Y'
         JOIN
         (SELECT   universeid, DECODE (symbology, 1, 0, 2, 2, -1) feedid,
                   primarysymbol
              FROM ot_symbol_data_v
          GROUP BY universeid, symbology, primarysymbol) osdv
         ON u.universeid = osdv.universeid
   WHERE osdv.feedid = imfm.feedid
ORDER BY i.isin, i.currencycode, i.instrumentid;

New Query
WITH ot_symbol_data_v AS
     (SELECT   dat.symbol, dat.startdate, dat.enddate, oi.currencycode,
               dat.primarymarket, primsymb.symbol primarysymbol, dat.mic,
               dat.universeid, dat.symbology
          FROM onetick_symbol_data@refdata_link dat
               LEFT JOIN
               (SELECT   symbology, universeid, mic, MAX (enddate) enddate
                    FROM onetick_symbol_data@refdata_link
                GROUP BY symbology, universeid, mic) prim
               ON prim.symbology = dat.symbology
             AND prim.universeid = dat.universeid
             AND prim.mic = dat.primarymarket
               LEFT JOIN onetick_symbol_data@refdata_link primsymb
               ON prim.symbology = primsymb.symbology
             AND prim.universeid = primsymb.universeid
             AND prim.mic = primsymb.mic
             AND prim.enddate = primsymb.enddate
               JOIN onetick_isincur_data@refdata_link oi
               ON dat.universeid = oi.universeid
               JOIN
               (SELECT   universeid, MAX (enddate) AS enddate
                    FROM onetick_isincur_data@refdata_link
                GROUP BY universeid) oilatest
               ON oi.universeid = oilatest.universeid
             AND oi.enddate = oilatest.enddate
      ORDER BY dat.universeid, dat.mic, dat.symbology, dat.enddate)
SELECT      i.instrumentid
         || '||'
         || i.firsttradingdate
         || '000000|'
         || NVL (i.delisteddate, '30001231')
         || '000000|'
         || i.home_market
         || '|'
         || DECODE (imfm.feedid, 0, 'FIXN_RFA', 1, 'ALGO', 2, 'FIXNETIX')
         || '::'
         || osdv.primarysymbol
    FROM tibex_meinstrumentview i JOIN tibex_instrumentmicfeedmapview imfm
         ON i.isin = imfm.isin
       AND i.currencycode = imfm.currencycode
       AND i.home_market = imfm.mic
         JOIN universe@refdata_link u
         ON i.instrumentid = u.instrumentid AND i.instrumentstatus != 3
         and active='Y'
         JOIN
         (SELECT   universeid, DECODE (symbology, 1, 0, 2, 2, -1) feedid,
                   primarysymbol
              FROM ot_symbol_data_v
          GROUP BY universeid, symbology, primarysymbol) osdv
         ON u.universeid = osdv.universeid
   WHERE osdv.feedid = imfm.feedid
ORDER BY i.isin, i.currencycode, i.instrumentid;
Most of the wait event is

SQL*Net message from dblink
SQL*Net message to dblink

Regards
NM
  • 1. Re: Query started taking longer time with SQL*Net message from dblink
    Kim Berg Hansen Expert
    Currently Being Moderated
    You say there is only a small difference between the two queries - then why don't you point it out?
    Do you expect us to keep scrolling up and down comparing line by line until we spot the difference?
    Sorry, but I haven't spotted any difference yet, so I give up - the queries look the same to me ;-)

    Anyway - please read this thread: {thread:id=863295}

    Particularly post a trace of both queries including the explain plans in the tkprof output.

    It might be the dblink is involved - it might not be. It is impossible to tell without giving us the explain plan and trace output.

    Give us some information to work on (see the thread above) and we might be able to help.

    But just two complex queries - it can only be guesswork. My guess is your problem is because the moon is in waxing phase ;-)
  • 2. Re: Query started taking longer time with SQL*Net message from dblink
    NM Newbie
    Currently Being Moderated
    Hi Kim,


    uat_trd_owner@UAT001> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 741667790
    
    -----------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
    -----------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                                |     1 |   137 | 21981   (2)| 00:04:24 |        |      |
    |   1 |  SORT ORDER BY                         |                                |     1 |   137 | 21981   (2)| 00:04:24 |        |      |
    |*  2 |   HASH JOIN OUTER                      |                                |     1 |   137 | 21980   (2)| 00:04:24 |        |      |
    |*  3 |    HASH JOIN OUTER                     |                                |     1 |   131 |   422   (4)| 00:00:06 |        |      |
    |   4 |     NESTED LOOPS                       |                                |       |       |            |          |        |      |
    |   5 |      NESTED LOOPS                      |                                |     1 |   125 |   107   (9)| 00:00:02 |        |      |
    |   6 |       NESTED LOOPS                     |                                |    20 |  1680 |    87  (11)| 00:00:02 |        |      |
    |*  7 |        HASH JOIN                       |                                |     1 |    64 |    86  (11)| 00:00:02 |        |      |
    |   8 |         VIEW                           | TIBEX_INSTRUMENTMICFEEDMAPVIEW |     1 |    34 |    84   (9)| 00:00:02 |        |      |
    |   9 |          HASH GROUP BY                 |                                |     1 |   166 |    84   (9)| 00:00:02 |        |      |
    |* 10 |           HASH JOIN RIGHT OUTER        |                                |   267 | 44322 |    83   (8)| 00:00:01 |        |      |
    |  11 |            TABLE ACCESS FULL           | TIBEX_BOARDFEEDMAP             |     1 |    20 |     3   (0)| 00:00:01 |        |      |
    |  12 |            NESTED LOOPS OUTER          |                                |   267 | 38982 |    80   (8)| 00:00:01 |        |      |
    |  13 |             NESTED LOOPS OUTER         |                                |   267 | 21627 |    80   (8)| 00:00:01 |        |      |
    |* 14 |              HASH JOIN                 |                                |   267 | 17088 |    80   (8)| 00:00:01 |        |      |
    |  15 |               MERGE JOIN CARTESIAN     |                                |  2004 | 88176 |    37   (0)| 00:00:01 |        |      |
    |  16 |                INDEX FULL SCAN         | TIBEX_EDPDEFAULTFEED_PK        |     1 |     3 |     1   (0)| 00:00:01 |        |      |
    |  17 |                BUFFER SORT             |                                |  2004 | 82164 |    36   (0)| 00:00:01 |        |      |
    |* 18 |                 TABLE ACCESS FULL      | TIBEX_INSTRUMENT               |  2004 | 82164 |    36   (0)| 00:00:01 |        |      |
    |  19 |               VIEW                     | TIBEX_EDPINSTRUMENTMARKETSVIEW | 22040 |   430K|    42  (12)| 00:00:01 |        |      |
    |  20 |                HASH GROUP BY           |                                | 22040 |   430K|    42  (12)| 00:00:01 |        |      |
    |  21 |                 VIEW                   |                                | 22040 |   430K|    41  (10)| 00:00:01 |        |      |
    |  22 |                  SORT UNIQUE           |                                | 22040 |   544K|    41  (57)| 00:00:01 |        |      |
    |  23 |                   UNION-ALL            |                                |       |       |            |          |        |      |
    |  24 |                    INDEX FAST FULL SCAN| TIBEX_EDPFIXNETIXL1_R01        |  7578 |   162K|    18   (0)| 00:00:01 |        |      |
    |  25 |                    TABLE ACCESS FULL   | TIBEX_EDPIXSYMBOLS             |  7494 |   197K|    12   (0)| 00:00:01 |        |      |
    |  26 |                    TABLE ACCESS FULL   | TIBEX_EDPRFALGOSUBSCRIPTION    |  6968 |   183K|     7   (0)| 00:00:01 |        |      |
    |* 27 |              INDEX RANGE SCAN          | TIBEX_MICFEEDMAP_PK            |     1 |    17 |     0   (0)| 00:00:01 |        |      |
    |  28 |             TABLE ACCESS BY INDEX ROWID| TIBEX_INSTRUMENTFEEDMAP        |     1 |    65 |     0   (0)| 00:00:01 |        |      |
    |* 29 |              INDEX UNIQUE SCAN         | TIBEX_INSTRUMENTFEEDMAP_PK     |     1 |       |     0   (0)| 00:00:01 |        |      |
    |  30 |         VIEW                           |                                |   100 |  3000 |     1 (100)| 00:00:01 |        |      |
    |  31 |          REMOTE                        |                                |       |       |            |          | REFDA~ | R->S |
    |  32 |        REMOTE                          | UNIVERSE                       |    20 |   400 |     1   (0)| 00:00:01 | REFDA~ | R->S |
    |* 33 |       INDEX UNIQUE SCAN                | XPKTIBEX_INSTRUMENT            |     1 |       |     0   (0)| 00:00:01 |        |      |
    |* 34 |      TABLE ACCESS BY INDEX ROWID       | TIBEX_INSTRUMENT               |     1 |    41 |     1   (0)| 00:00:01 |        |      |
    |  35 |     VIEW                               | TIBEX_MELASTEXPRICEINTVIEW     |    36 |   216 |   314   (2)| 00:00:04 |        |      |
    |  36 |      HASH UNIQUE                       |                                |    36 |  1656 |   314   (2)| 00:00:04 |        |      |
    |* 37 |       HASH JOIN                        |                                |    36 |  1656 |   313   (1)| 00:00:04 |        |      |
    |  38 |        VIEW                            | VW_SQ_1                        |   304 |  5776 |   157   (2)| 00:00:02 |        |      |
    |  39 |         HASH GROUP BY                  |                                |   304 |  7296 |   157   (2)| 00:00:02 |        |      |
    |* 40 |          TABLE ACCESS FULL             | TIBEX_EXECUTION                | 17462 |   409K|   156   (1)| 00:00:02 |        |      |
    |  41 |        TABLE ACCESS FULL               | TIBEX_EXECUTION                | 17463 |   460K|   156   (1)| 00:00:02 |        |      |
    |  42 |    VIEW                                | TIBEX_MSGSEQBYINSTRUMENT       |  3908 | 23448 | 21558   (2)| 00:04:19 |        |      |
    |  43 |     HASH GROUP BY                      |                                |  3908 | 74252 | 21558   (2)| 00:04:19 |        |      |
    |  44 |      VIEW                              |                                | 11626 |   215K| 21556   (2)| 00:04:19 |        |      |
    |  45 |       UNION-ALL                        |                                |       |       |            |          |        |      |
    |  46 |        HASH GROUP BY                   |                                |  1460 | 26280 |  8906   (1)| 00:01:47 |        |      |
    |  47 |         TABLE ACCESS FULL              | TIBEX_QUOTE                    |  1362K|    23M|  8866   (1)| 00:01:47 |        |      |
    |  48 |        HASH GROUP BY                   |                                |   677 | 12186 | 11750   (2)| 00:02:21 |        |      |
    |  49 |         TABLE ACCESS FULL              | TIBEX_ORDER                    |  1790K|    30M| 11696   (1)| 00:02:21 |        |      |
    |  50 |        HASH GROUP BY                   |                                |   304 |  5472 |   157   (2)| 00:00:02 |        |      |
    |* 51 |         TABLE ACCESS FULL              | TIBEX_EXECUTION                | 17463 |   306K|   156   (1)| 00:00:02 |        |      |
    |  52 |        HASH GROUP BY                   |                                |     1 |    40 |     3  (34)| 00:00:01 |        |      |
    |* 53 |         TABLE ACCESS FULL              | TIBEX_TSTRADE                  |     1 |    40 |     2   (0)| 00:00:01 |        |      |
    |  54 |        HASH GROUP BY                   |                                |   717 | 11472 |   229   (1)| 00:00:03 |        |      |
    |  55 |         INDEX FAST FULL SCAN           | IX_BESTEXREL                   |  7323 |   114K|   228   (0)| 00:00:03 |        |      |
    |  56 |        HASH GROUP BY                   |                                |  1911 | 34398 |    13   (8)| 00:00:01 |        |      |
    |* 57 |         TABLE ACCESS FULL              | TIBEX_MERESUMEPRDTRANSITION    |  5216 | 93888 |    12   (0)| 00:00:01 |        |      |
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |  58 |        HASH GROUP BY                   |                                |     3 |    51 |     5  (20)| 00:00:01 |        |      |
    |  59 |         TABLE ACCESS FULL              | TIBEX_EDPUPDATEREJECT          |    48 |   816 |     4   (0)| 00:00:01 |        |      |
    |  60 |        HASH GROUP BY                   |                                |  1587 | 46023 |   215   (2)| 00:00:03 |        |      |
    |* 61 |         HASH JOIN                      |                                | 35166 |   995K|   213   (1)| 00:00:03 |        |      |
    |  62 |          INDEX FULL SCAN               | XPKTIBEX_CONFIGMEGROUP         |     4 |    16 |     1   (0)| 00:00:01 |        |      |
    |  63 |          TABLE ACCESS FULL             | TIBEX_INSTRUMENTADMIN          | 87915 |  2146K|   212   (1)| 00:00:03 |        |      |
    |  64 |        HASH GROUP BY                   |                                |     6 |   102 |     5  (20)| 00:00:01 |        |      |
    |  65 |         TABLE ACCESS FULL              | TIBEX_BESTEXECPRICELOG         |   793 | 13481 |     4   (0)| 00:00:01 |        |      |
    |  66 |        HASH GROUP BY                   |                                |     1 |    40 |     3  (34)| 00:00:01 |        |      |
    |* 67 |         TABLE ACCESS FULL              | TIBEX_AUCTIONPRICE             |     1 |    40 |     2   (0)| 00:00:01 |        |      |
    |  68 |        HASH GROUP BY                   |                                |  1587 | 28566 |   236   (2)| 00:00:03 |        |      |
    |* 69 |         TABLE ACCESS FULL              | TIBEX_ADMINACK                 | 87915 |  1545K|   233   (1)| 00:00:03 |        |      |
    |  70 |        HASH GROUP BY                   |                                |  1914 | 34452 |    26   (8)| 00:00:01 |        |      |
    |  71 |         INDEX FAST FULL SCAN           | INSTRUMENTSTATEMSGSEQ          | 23705 |   416K|    24   (0)| 00:00:01 |        |      |
    |  72 |        HASH GROUP BY                   |                                |  1458 | 26244 |     8  (13)| 00:00:01 |        |      |
    |  73 |         INDEX FAST FULL SCAN           | TIBEX_FREEZEEOTPK              |  5890 |   103K|     7   (0)| 00:00:01 |        |      |
    -----------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("A"."INSTRUMENTID"="C"."INSTRUMENTID"(+))
       3 - access("A"."INSTRUMENTID"="B"."INSTRUMENTID"(+))
       7 - access("OSDV"."FEEDID"="IMFM"."FEEDID")
      10 - access("I"."PRIMARYSTATUSBOARDID"="BOARDFM"."BOARDID"(+))
      14 - access("SUBSC"."ISIN"="I"."ISIN" AND "SUBSC"."CURRENCYCODE"="I"."CURRENCYCODE")
           filter("SUBSC"."HOMEMARKET" IS NULL OR "SUBSC"."HOMEMARKET"="I"."HOME_MARKET")
      18 - filter("I"."INSTRUMENTSTATUS"<>3)
      27 - access("SUBSC"."MIC"="MICFM"."MIC"(+))
      29 - access("I"."INSTRUMENTID"="INSTRFM"."INSTRUMENTID"(+))
      33 - access("A"."INSTRUMENTID"="U"."INSTRUMENTID")
      34 - filter("A"."INSTRUMENTSTATUS"<>3 AND TO_DATE("A"."FIRSTTRADINGDATE",'YYYYMMDD')<=SYSDATE@! AND "A"."ISIN"="IMFM"."ISIN"
                  AND "A"."CURRENCYCODE"="IMFM"."CURRENCYCODE" AND "A"."HOME_MARKET"="IMFM"."MIC")
      37 - access("A"."MESSAGESEQUENCE"="MAX(B.MESSAGESEQUENCE)" AND "A"."INSTRUMENTID"="ITEM_0")
      40 - filter(("B"."SELLENTITYTYPE"=0 OR "B"."SELLENTITYTYPE"=2) AND ("B"."BUYENTITYTYPE"=0 OR "B"."BUYENTITYTYPE"=2))
      51 - filter("INSTRUMENTID" IS NOT NULL)
      53 - filter("INSTRUMENTID" IS NOT NULL)
      57 - filter("INSTRUMENTID" IS NOT NULL)
      61 - access("ADMINUSER"="MEGROUPID")
      67 - filter("INSTRUMENTID" IS NOT NULL)
      69 - filter("INSTRUMENTID" IS NOT NULL)
    
    Remote SQL Information (identified by operation id):
    ----------------------------------------------------
    
      31 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A1"."UNIVERSEID",DECODE("A1"."SYMBOLOGY",1,0,2,2,(-1)),"A1"."PRIMARYSYMBOL"
            FROM  (SELECT "A6"."SYMBOL" "SYMBOL","A6"."STARTDATE" "STARTDATE","A6"."ENDDATE" "ENDDATE","A3"."CURRENCYCODE"
            "CURRENCYCODE","A6"."PRIMARYMARKET" "PRIMARYMARKET","A4"."SYMBOL" "PRIMARYSYMBOL","A6"."MIC" "MIC","A6"."UNIVERSEID"
            "UNIVERSEID","A6"."SYMBOLOGY" "SYMBOLOGY" FROM "ONETICK_SYMBOL_DATA" "A6", (SELECT "A7"."SYMBOLOGY"
            "SYMBOLOGY","A7"."UNIVERSEID" "UNIVERSEID","A7"."MIC" "MIC",MAX("A7"."ENDDATE") "ENDDATE" FROM "ONETICK_SYMBOL_DATA" "A7" GROUP
            BY "A7"."SYMBOLOGY","A7"."UNIVERSEID","A7"."MIC") "A5","ONETICK_SYMBOL_DATA" "A4","ONETICK_ISINCUR_DATA" "A3", (SELECT
            "A8"."UNIVERSEID" "UNIVERSEID",MAX("A8"."ENDDATE") "ENDDATE" FROM "ONETICK_ISINCUR_DATA" "A8" GROUP BY "A8"."UNIVERSEID") "A2"
            WHERE "A3"."UNIVERSEID"="A2"."UNIVERSEID" AND "A3"."ENDDATE"="A2"."ENDDATE" AND "A6"."UNIVERSEID"="A3"."UNIVERSEID" AND
            "A5"."ENDDATE"="A4"."ENDDATE"(+) AND "A5"."MIC"="A4"."MIC"(+) AND "A5"."UNIVERSEID"="A4"."UNIVERSEID"(+) AND
            "A5"."SYMBOLOGY"="A4"."SYMBOLOGY"(+) AND "A5"."MIC"(+)="A6"."PRIMARYMARKET" AND "A5"."UNIVERSEID"(+)="A6"."UNIVERSEID" AND
            "A5"."SYMBOLOGY"(+)="A6"."SYMBOLOGY" ORDER BY "A6"."UNIVERSEID","A6"."MIC","A6"."SYMBOLOGY","A6"."ENDDATE") "A1" GROUP BY
            "A1"."UNIVERSEID","A1"."SYMBOLOGY","A1"."PRIMARYSYMBOL" (accessing 'REFDATA_LINK' )
    
      32 - SELECT "INSTRUMENTID","UNIVERSEID" FROM "UNIVERSE" "U" WHERE "UNIVERSEID"=:1 (accessing 'REFDATA_LINK' )
    
    
    127 rows selected.
    
    
    For trace files
    
    WAIT #18446741324892119016: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151855125079
    WAIT #18446741324892119016: nam='SQL*Net message from client' ela= 182 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151855125694
    =====================
    PARSING IN CURSOR #18446741324892117968 len=52 dep=0 uid=474 oct=47 lid=474 tim=42151855125777 hv=1029988163 ad='af4d0890' sqlid='9babjv8yq8ru3'
    BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
    END OF STMT
    PARSE #18446741324892117968:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=42151855125769
    WAIT #18446741324892117968: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151855126145
    EXEC #18446741324892117968:c=0,e=262,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=42151855126176
    
    *** 2012-11-20 15:18:56.839
    WAIT #18446741324892117968: nam='SQL*Net message from client' ela= 10252982 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151865379208
    CLOSE #18446741324892119016:c=0,e=13,dep=0,type=1,tim=42151865379327
    CLOSE #18446741324892117968:c=0,e=28,dep=0,type=3,tim=42151865379370
    WAIT #18446741324892082152: nam='single-task message' ela= 47849 p1=0 p2=0 p3=0 obj#=-1 tim=42151865429221
    WAIT #18446741324892082152: nam='SQL*Net message from dblink' ela= 107 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151865429886
    WAIT #18446741324892082152: nam='SQL*Net message to dblink' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151865429945
    WAIT #18446741324892082152: nam='SQL*Net message from dblink' ela= 926 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151865430901
    WAIT #18446741324892082152: nam='SQL*Net message to dblink' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151865431578
    WAIT #18446741324892082152: nam='SQL*Net message from dblink' ela= 2525 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151865434125
    
    ..................
    WAIT #18446741324892082152: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670108
    WAIT #18446741324892082152: nam='SQL*Net message from dblink' ela= 58 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670178
    WAIT #18446741324892082152: nam='SQL*Net message to dblink' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670235
    WAIT #18446741324892082152: nam='SQL*Net message from dblink' ela= 60 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670310
    WAIT #18446741324892082152: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670337
    WAIT #18446741324892082152: nam='SQL*Net message from dblink' ela= 59 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670407
    WAIT #18446741324892082152: nam='SQL*Net message to dblink' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670464
    WAIT #18446741324892082152: nam='SQL*Net message from dblink' ela= 60 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670539
    WAIT #18446741324892082152: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670566
    WAIT #18446741324892082152: nam='SQL*Net message from dblink' ela= 59 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670636
    WAIT #18446741324892082152: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670693
    WAIT #18446741324892082152: nam='SQL*Net message from dblink' ela= 60 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42151894670768
    Regards
    NM
  • 3. Re: Query started taking longer time with SQL*Net message from dblink
    NM Newbie
    Currently Being Moderated
    Hi Kim,

    Trace file details.
    WITH
    OT_SYMBOL_DATA_V AS
    (
    SELECT  DAT.SYMBOL,
                  DAT.STARTDATE,
                         DAT.ENDDATE,
            OI.CURRENCYCODE,
            DAT.PRIMARYMARKET,
            PRIMSYMB.SYMBOL PRIMARYSYMBOL,
                 DAT.MIC,
            DAT.UNIVERSEID,
            DAT.SYMBOLOGY
      FROM  ONETICK_SYMBOL_DATA@refdata_link DAT
      LEFT JOIN (
              SELECT  SYMBOLOGY, UNIVERSEID, MIC, MAX(ENDDATE) ENDDATE
                FROM  ONETICK_SYMBOL_DATA@refdata_link
                GROUP BY SYMBOLOGY, UNIVERSEID, MIC
            ) PRIM
        ON  PRIM.SYMBOLOGY = DAT.SYMBOLOGY AND
            PRIM.UNIVERSEID = DAT.UNIVERSEID AND
            PRIM.MIC = DAT.PRIMARYMARKET
      LEFT JOIN  ONETICK_SYMBOL_DATA@refdata_link PRIMSYMB
        ON  PRIM.SYMBOLOGY = PRIMSYMB.SYMBOLOGY AND
            PRIM.UNIVERSEID = PRIMSYMB.UNIVERSEID AND
            PRIM.MIC = PRIMSYMB.MIC AND
            PRIM.ENDDATE = PRIMSYMB.ENDDATE
      JOIN  ONETICK_ISINCUR_DATA@refdata_link OI
        ON  DAT.UNIVERSEID = OI.UNIVERSEID
      JOIN  ( SELECT  UNIVERSEID, MAX(ENDDATE) AS ENDDATE
                FROM  ONETICK_ISINCUR_DATA@refdata_link
                GROUP BY UNIVERSEID
            ) OILatest
        ON  OI.UNIVERSEID = OILatest.UNIVERSEID AND
            OI.ENDDATE = OILatest.ENDDATE
      ORDER BY DAT.UNIVERSEID, DAT.MIC, DAT.SYMBOLOGY, DAT.ENDDATE
    )
    select
        I.instrumentid || '||' ||
        I.firstTradingDate || '000000|' ||
        nvl(I.delisteddate,'30001231') || '000000|' ||
        I.home_market || '|' ||
        decode(IMFM.feedid,
               0, 'FIXN_RFA',
               1, 'ALGO',
               2, 'FIXNETIX') || '::' || OSDV.primarysymbol
    from tibex_meinstrumentview I
    join tibex_instrumentmicfeedmapview IMFM
    on I.isin = IMFM.isin and
        I.currencycode = IMFM.currencycode and
        I.home_market = IMFM.mic
    join universe@refdata_link U
    on I.instrumentid = U.instrumentid and I.instrumentstatus != 3
    and  active='Y'
    join
    (
        select
            universeid,
            decode(symbology, 1, 0, 2, 2, -1) feedid,
            primarysymbol
        from OT_SYMBOL_DATA_V
        group by universeid, symbology, primarysymbol
    ) OSDV
    on U.universeid = OSDV.universeid
    where OSDV.feedid = IMFM.feedid
    order by I.isin, I.currencycode, I.instrumentid
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.05       0.14          0          0          1           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1   1404.88    2208.46          0       6965          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3   1404.94    2208.60          0       6965          1           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 474
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             0          0          0  SORT ORDER BY (cr=0 pr=0 pw=0 time=154 us cost=218 size=160 card=1)
             0          0          0   HASH JOIN OUTER (cr=0 pr=0 pw=0 time=149 us cost=217 size=160 card=1)
             0          0          0    HASH JOIN OUTER (cr=0 pr=0 pw=0 time=128 us cost=104 size=154 card=1)
           923        923        923     NESTED LOOPS  (cr=6965 pr=0 pw=0 time=2158628812 us)
       9095015    9095015    9095015      NESTED LOOPS  (cr=6077 pr=0 pw=0 time=2104940057 us cost=102 size=127 card=1)
       9095015    9095015    9095015       NESTED LOOPS  (cr=238 pr=0 pw=0 time=2061504894 us cost=82 size=1720 card=20)
       9378508    9378508    9378508        HASH JOIN  (cr=238 pr=0 pw=0 time=8192464 us cost=81 size=64 card=1)
          8623       8623       8623         VIEW  TIBEX_INSTRUMENTMICFEEDMAPVIEW (cr=238 pr=0 pw=0 time=144093 us cost=80 size=34 card=1)
          8623       8623       8623          HASH GROUP BY (cr=238 pr=0 pw=0 time=136481 us cost=80 size=157 card=1)
         16111      16111      16111           HASH JOIN RIGHT OUTER (cr=238 pr=0 pw=0 time=175302 us cost=79 size=43332 card=276)
             0          0          0            TABLE ACCESS FULL TIBEX_BOARDFEEDMAP (cr=1 pr=0 pw=0 time=105 us cost=2 size=20 card=1)
         16111      16111      16111            NESTED LOOPS OUTER (cr=237 pr=0 pw=0 time=151960 us cost=76 size=37812 card=276)
         16111      16111      16111             HASH JOIN RIGHT OUTER (cr=236 pr=0 pw=0 time=95282 us cost=76 size=19872 card=276)
            13         13         13              INDEX FULL SCAN TIBEX_MICFEEDMAP_PK (cr=1 pr=0 pw=0 time=40 us cost=1 size=104 card=13)(object id 624577)
         16111      16111      16111              HASH JOIN  (cr=235 pr=0 pw=0 time=62081 us cost=75 size=17664 card=276)
          2004       2004       2004               MERGE JOIN CARTESIAN (cr=76 pr=0 pw=0 time=3581 us cost=23 size=88220 card=2005)
             1          1          1                INDEX FULL SCAN TIBEX_EDPDEFAULTFEED_PK (cr=1 pr=0 pw=0 time=18 us cost=1 size=3 card=1)(object id 624457)
          2004       2004       2004                BUFFER SORT (cr=75 pr=0 pw=0 time=2684 us cost=22 size=82205 card=2005)
          2004       2004       2004                 TABLE ACCESS FULL TIBEX_INSTRUMENT (cr=75 pr=0 pw=0 time=1693 us cost=22 size=82205 card=2005)
         16301      16301      16301               VIEW  TIBEX_EDPINSTRUMENTMARKETSVIEW (cr=159 pr=0 pw=0 time=56405 us cost=51 size=456160 card=22808)
         16301      16301      16301                HASH GROUP BY (cr=159 pr=0 pw=0 time=44888 us cost=51 size=456160 card=22808)
         16301      16301      16301                 VIEW  (cr=159 pr=0 pw=0 time=43234 us cost=49 size=456160 card=22808)
         16301      16301      16301                  SORT UNIQUE (cr=159 pr=0 pw=0 time=30753 us cost=49 size=577121 card=22808)
         22808      22808      22808                   UNION-ALL  (cr=159 pr=0 pw=0 time=22129 us)
          7739       7739       7739                    INDEX FAST FULL SCAN TIBEX_EDPFIXNETIXL1_R01 (cr=55 pr=0 pw=0 time=2998 us cost=15 size=170258 card=7739)(object id 624459)
          7827       7827       7827                    TABLE ACCESS FULL TIBEX_EDPIXSYMBOLS (cr=67 pr=0 pw=0 time=2962 us cost=19 size=211329 card=7827)
          7242       7242       7242                    TABLE ACCESS FULL TIBEX_EDPRFALGOSUBSCRIPTION (cr=37 pr=0 pw=0 time=2821 us cost=11 size=195534 card=7242)
             0          0          0             TABLE ACCESS BY INDEX ROWID TIBEX_INSTRUMENTFEEDMAP (cr=1 pr=0 pw=0 time=34161 us cost=0 size=65 card=1)
             0          0          0              INDEX UNIQUE SCAN TIBEX_INSTRUMENTFEEDMAP_PK (cr=1 pr=0 pw=0 time=11433 us cost=0 size=0 card=1)(object id 624539)
          2308       2308       2308         VIEW  (cr=0 pr=0 pw=0 time=121805 us cost=1 size=3000 card=100)
          2308       2308       2308          REMOTE  (cr=0 pr=0 pw=0 time=117431 us)
       9095015    9095015    9095015        REMOTE  UNIVERSE (cr=0 pr=0 pw=0 time=2101128020 us cost=1 size=440 card=20)
       9095015    9095015    9095015       INDEX UNIQUE SCAN XPKTIBEX_INSTRUMENT (cr=5839 pr=0 pw=0 time=25051702 us cost=0 size=0 card=1)(object id 624533)
           923        923        923      TABLE ACCESS BY INDEX ROWID TIBEX_INSTRUMENT (cr=888 pr=0 pw=0 time=41195415 us cost=1 size=41 card=1)
             0          0          0     VIEW  TIBEX_MELASTEXPRICEINTVIEW (cr=0 pr=0 pw=0 time=0 us cost=1 size=27 card=1)
             0          0          0      HASH UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=1 size=93 card=1)
             0          0          0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
             0          0          0        NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=93 card=1)
             0          0          0         VIEW  VW_SQ_1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=40 card=1)
             0          0          0          HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=0 size=66 card=1)
             0          0          0           TABLE ACCESS BY INDEX ROWID TIBEX_EXECUTION (cr=0 pr=0 pw=0 time=0 us cost=0 size=66 card=1)
             0          0          0            INDEX FULL SCAN TIBEX_EXECUTION_IDX4 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 624487)
             0          0          0         INDEX RANGE SCAN TIBEX_EXECUTION_IDX4 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 624487)
             0          0          0        TABLE ACCESS BY INDEX ROWID TIBEX_EXECUTION (cr=0 pr=0 pw=0 time=0 us cost=0 size=53 card=1)
             0          0          0    VIEW  TIBEX_MSGSEQBYINSTRUMENT (cr=0 pr=0 pw=0 time=0 us cost=113 size=14934 card=2489)
             0          0          0     HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=113 size=47291 card=2489)
             0          0          0      VIEW  (cr=0 pr=0 pw=0 time=0 us cost=112 size=60059 card=3161)
             0          0          0       UNION-ALL  (cr=0 pr=0 pw=0 time=0 us)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=70 size=32472 card=1804)
             0          0          0         TABLE ACCESS FULL TIBEX_QUOTE (cr=0 pr=0 pw=0 time=0 us cost=68 size=118584 card=6588)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=6 size=2142 card=119)
             0          0          0         TABLE ACCESS FULL TIBEX_ORDER (cr=0 pr=0 pw=0 time=0 us cost=5 size=3564 card=198)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=0 size=40 card=1)
             0          0          0         TABLE ACCESS BY INDEX ROWID TIBEX_EXECUTION (cr=0 pr=0 pw=0 time=0 us cost=0 size=40 card=1)
             0          0          0          INDEX FULL SCAN TIBEX_EXECUTION_IDX4 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 624487)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=3 size=40 card=1)
             0          0          0         TABLE ACCESS FULL TIBEX_TSTRADE (cr=0 pr=0 pw=0 time=0 us cost=2 size=40 card=1)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=18 size=17192 card=1228)
             0          0          0         INDEX FAST FULL SCAN IX_BESTEXREL (cr=0 pr=0 pw=0 time=0 us cost=17 size=125510 card=8965)(object id 624399)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=3 size=40 card=1)
             0          0          0         TABLE ACCESS FULL TIBEX_MERESUMEPRDTRANSITION (cr=0 pr=0 pw=0 time=0 us cost=2 size=40 card=1)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=3 size=40 card=1)
             0          0          0         TABLE ACCESS FULL TIBEX_EDPUPDATEREJECT (cr=0 pr=0 pw=0 time=0 us cost=2 size=40 card=1)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=0 size=58 card=1)
             0          0          0         NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=58 card=1)
             0          0          0          TABLE ACCESS BY INDEX ROWID TIBEX_INSTRUMENTADMIN (cr=0 pr=0 pw=0 time=0 us cost=0 size=54 card=1)
             0          0          0           INDEX FULL SCAN SYS_C00978598 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 624536)
             0          0          0          INDEX UNIQUE SCAN XPKTIBEX_CONFIGMEGROUP (cr=0 pr=0 pw=0 time=0 us cost=0 size=4 card=1)(object id 624427)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=3 size=40 card=1)
             0          0          0         TABLE ACCESS FULL TIBEX_BESTEXECPRICELOG (cr=0 pr=0 pw=0 time=0 us cost=2 size=40 card=1)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=3 size=40 card=1)
             0          0          0         TABLE ACCESS FULL TIBEX_AUCTIONPRICE (cr=0 pr=0 pw=0 time=0 us cost=2 size=40 card=1)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=3 size=40 card=1)
             0          0          0         TABLE ACCESS FULL TIBEX_ADMINACK (cr=0 pr=0 pw=0 time=0 us cost=2 size=40 card=1)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=0 size=40 card=1)
             0          0          0         INDEX FULL SCAN INSTRUMENTSTATEMSGSEQ (cr=0 pr=0 pw=0 time=0 us cost=0 size=40 card=1)(object id 624546)
             0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=0 size=40 card=1)
             0          0          0         INDEX FULL SCAN TIBEX_FREEZEEOTPK (cr=0 pr=0 pw=0 time=0 us cost=0 size=40 card=1)(object id 624501)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      single-task message                             1        0.04          0.04
      SQL*Net message from dblink               18757049        0.11       1122.56
      SQL*Net message to dblink                 18757047        0.00         12.66
      SQL*Net message to client                       1        0.00          0.00
      SQL*Net more data from dblink                   9        0.00          0.00
      latch: shared pool                             21        0.01          0.03
      SQL*Net break/reset to dblink                   2        0.00          0.00
      SQL*Net break/reset to client                   1        0.00          0.00
      SQL*Net message from client                     1        5.59          5.59
    ********************************************************************************
    This Query was still running but i cancelled it because of the disk space.

    Regards
    NM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points