3 Replies Latest reply: Nov 20, 2012 10:06 AM by NM RSS

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

    NM
      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
          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
            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
              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