1 2 3 Previous Next 41 Replies Latest reply: Apr 13, 2012 8:50 AM by NM RSS

    Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec

    NM
      Hi ,

      One of the Query which takes around 120 seconds to retrieve 50 records.The main table contains around 20 Million records.Looking at the trace file most of the time spent on "direct path write temp,direct path read temp,direct path read"
      So if somebody can see and find any tuning can be done to his Query
      select * from TIBEX_QSCACHELOADORDERING  where qsid='QS1';
      
      select *
      from
       TIBEX_QSCACHELOADORDERING  where qsid='QS1'
      
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.07       0.08          0          0          0           0
      Execute      1      0.00       0.00          0          0          0           0
      Fetch        2    103.12     334.43    1909083    1719325          0          25
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total        4    103.19     334.51    1909083    1719325          0          25
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 108
      Number of plan statistics captured: 1
      
      Rows (1st) Rows (avg) Rows (max)  Row Source Operation
      ---------- ---------- ----------  ---------------------------------------------------
              25         25         25  VIEW  TIBEX_QSCACHELOADORDERING (cr=1719325 pr=1909083 pw=216889 time=334431866 us cost=8447082 size=756 card=36)
              25         25         25   SORT ORDER BY (cr=1719325 pr=1909083 pw=216889 time=334431851 us cost=8447082 size=1224 card=36)
              25         25         25    HASH GROUP BY (cr=1719325 pr=1909083 pw=216889 time=334431790 us cost=8447082 size=1224 card=36)
              44         44         44     VIEW  (cr=1719325 pr=1909083 pw=216889 time=334431590 us cost=8447080 size=1224 card=36)
              44         44         44      SORT UNIQUE (cr=1719325 pr=1909083 pw=216889 time=334431502 us cost=8447080 size=836 card=36)
              44         44         44       UNION-ALL  (cr=1719325 pr=1909083 pw=216889 time=333055166 us)
              11         11         11        HASH GROUP BY (cr=1692530 pr=1909083 pw=216889 time=333055073 us cost=8445797 size=30 card=3)
         2357126    2357126    2357126         VIEW  TIBEX_ORDERSBYQSIDVIEW (cr=1692530 pr=1909083 pw=216889 time=299959977 us cost=8445795 size=30 card=3)
         2357126    2357126    2357126          UNION-ALL  (cr=1692530 pr=1909083 pw=216889 time=298473588 us)
         2356921    2356921    2356921           FILTER  (cr=1128083 pr=1344953 pw=216889 time=290728635 us)
         4719769    4719769    4719769            HASH GROUP BY (cr=1128083 pr=1344953 pw=216889 time=290860343 us cost=56333 size=280 card=2)
         9572738    9572738    9572738             HASH JOIN  (cr=1128083 pr=1288378 pw=160314 time=304861351 us cost=56232 size=161486500 card=1153475)
              50         50         50              TABLE ACCESS BY INDEX ROWID TIBEX_PARTICIPANT (cr=2 pr=0 pw=0 time=339 us cost=2 size=456 card=19)
              50         50         50               INDEX RANGE SCAN TIBEX_PARTICIPANTQSID (cr=1 pr=0 pw=0 time=153 us cost=1 size=0 card=19)(object id 2244322)
        30856746   30856746   30856746              HASH JOIN  (cr=1128081 pr=1288378 pw=160314 time=248543048 us cost=56218 size=138417000 card=1193250)
               7          7          7               TABLE ACCESS FULL TIBEX_ORDERSTATUSENUM (cr=2 pr=0 pw=0 time=73 us cost=2 size=182 card=7)
        30858869   30858869   30858869               HASH JOIN  (cr=1128079 pr=1288378 pw=160314 time=391599345 us cost=56201 size=138076110 card=1534179)
        15359079   15359079   15359079                TABLE ACCESS FULL TIBEX_ORDER (cr=564040 pr=564032 pw=0 time=16036799 us cost=20898 size=28849011 card=779703)
        15594068   15594068   15594068                TABLE ACCESS FULL TIBEX_ORDER (cr=564039 pr=564032 pw=0 time=28073241 us cost=21106 size=826485604 card=15594068)
             205        205        205           FILTER  (cr=564447 pr=564130 pw=0 time=8600124 us)
             205        205        205            HASH JOIN  (cr=564043 pr=564032 pw=0 time=8587229 us cost=20707 size=102169998 card=1674918)
              50         50         50             MERGE JOIN CARTESIAN (cr=4 pr=0 pw=0 time=210 us cost=3 size=494 card=19)
               1          1          1              TABLE ACCESS BY INDEX ROWID TIBEX_ORDERSTATUSENUM (cr=2 pr=0 pw=0 time=75 us cost=2 size=14 card=1)
               1          1          1               INDEX RANGE SCAN TIBEX_ORDERSTAT_ID_DESC (cr=1 pr=0 pw=0 time=43 us cost=1 size=0 card=1)(object id 2244312)
              50         50         50              BUFFER SORT (cr=2 pr=0 pw=0 time=81 us cost=1 size=228 card=19)
              50         50         50               TABLE ACCESS BY INDEX ROWID TIBEX_PARTICIPANT (cr=2 pr=0 pw=0 time=166 us cost=1 size=228 card=19)
              50         50         50                INDEX RANGE SCAN TIBEX_PARTICIPANTQSID (cr=1 pr=0 pw=0 time=59 us cost=0 size=0 card=19)(object id 2244322)
        15594068   15594068   15594068             TABLE ACCESS FULL TIBEX_ORDER (cr=564039 pr=564032 pw=0 time=32004783 us cost=20555 size=545792380 card=15594068)
             205        205        205            SORT AGGREGATE (cr=404 pr=98 pw=0 time=55185 us)
             205        205        205             TABLE ACCESS BY INDEX ROWID TIBEX_ORDER (cr=404 pr=98 pw=0 time=53469 us cost=5 size=27 card=1)
             205        205        205              INDEX RANGE SCAN IX_ORDERBOOK (cr=202 pr=0 pw=0 time=4892 us cost=3 size=0 card=2)(object id 2244435)
               1          1          1        HASH GROUP BY (cr=22543 pr=0 pw=0 time=454434 us cost=790 size=202 card=2)
            1359       1359       1359         NESTED LOOPS  (cr=22543 pr=0 pw=0 time=473609 us)
            2102       2102       2102          NESTED LOOPS  (cr=20441 pr=0 pw=0 time=451487 us cost=788 size=202 card=2)
            2102       2102       2102           HASH JOIN  (cr=20437 pr=0 pw=0 time=444789 us cost=786 size=178 card=2)
            2102       2102       2102            HASH JOIN RIGHT SEMI (cr=20435 pr=0 pw=0 time=256778 us cost=784 size=225 card=3)
            2102       2102       2102             VIEW  VW_NSO_2 (cr=10218 pr=0 pw=0 time=249846 us cost=395 size=71468 card=2102)
            2102       2102       2102              HASH GROUP BY (cr=10218 pr=0 pw=0 time=248484 us cost=395 size=77774 card=2102)
          281857     281857     281857               TABLE ACCESS FULL TIBEX_QUOTE (cr=10218 pr=0 pw=0 time=238919 us cost=384 size=5232133 card=141409)
          282817     282817     282817             TABLE ACCESS FULL TIBEX_QUOTE (cr=10217 pr=0 pw=0 time=214277 us cost=385 size=11595497 card=282817)
               3          3          3            TABLE ACCESS FULL TIBEX_QUOTESTATUSENUM (cr=2 pr=0 pw=0 time=60 us cost=2 size=42 card=3)
            2102       2102       2102           INDEX UNIQUE SCAN XPKTIBEX_PARTICIPANT (cr=4 pr=0 pw=0 time=3088 us cost=0 size=0 card=1)(object id 2244321)
            1359       1359       1359          TABLE ACCESS BY INDEX ROWID TIBEX_PARTICIPANT (cr=2102 pr=0 pw=0 time=3037 us cost=1 size=12 card=1)
              14         14         14        HASH GROUP BY (cr=4241 pr=0 pw=0 time=920695 us cost=476 size=525 card=25)
           11572      11572      11572         VIEW  (cr=4241 pr=0 pw=0 time=928372 us cost=474 size=525 card=25)
           11572      11572      11572          SORT UNIQUE (cr=4241 pr=0 pw=0 time=920866 us cost=474 size=5450 card=25)
           20678      20678      20678           UNION-ALL  (cr=4241 pr=0 pw=0 time=522144 us)
           11572      11572      11572            FILTER  (cr=2121 pr=0 pw=0 time=470259 us)
           43696      43696      43696             HASH GROUP BY (cr=2121 pr=0 pw=0 time=473424 us cost=239 size=2616 card=12)
          169892     169892     169892              HASH JOIN  (cr=2121 pr=0 pw=0 time=217325 us cost=233 size=7889856 card=36192)
              50         50         50               TABLE ACCESS BY INDEX ROWID TIBEX_PARTICIPANT (cr=2 pr=0 pw=0 time=184 us cost=2 size=456 card=19)
              50         50         50                INDEX RANGE SCAN TIBEX_PARTICIPANTQSID (cr=1 pr=0 pw=0 time=22 us cost=1 size=0 card=19)(object id 2244322)
          179936     179936     179936               HASH JOIN  (cr=2119 pr=0 pw=0 time=195651 us cost=230 size=7626528 card=39312)
           48412      48412      48412                HASH JOIN RIGHT ANTI (cr=1061 pr=0 pw=0 time=37665 us cost=45 size=2201472 card=39312)
               1          1          1                 TABLE ACCESS FULL TIBEX_BESTEXECSTATUSENUM (cr=2 pr=0 pw=0 time=37 us cost=2 size=14 card=1)
           52416      52416      52416                 TABLE ACCESS FULL TIBEX_BESTEXREL (cr=1059 pr=0 pw=0 time=25838 us cost=42 size=2201472 card=52416)
           52416      52416      52416                TABLE ACCESS FULL TIBEX_BESTEXREL (cr=1058 pr=0 pw=0 time=36355 us cost=43 size=7233408 card=52416)
            9106       9106       9106            FILTER  (cr=2120 pr=0 pw=0 time=394016 us)
           36385      36385      36385             HASH GROUP BY (cr=2120 pr=0 pw=0 time=401230 us cost=235 size=2834 card=13)
          145495     145495     145495              HASH JOIN  (cr=2120 pr=0 pw=0 time=232933 us cost=228 size=8284436 card=38002)
              50         50         50               TABLE ACCESS BY INDEX ROWID TIBEX_PARTICIPANT (cr=2 pr=0 pw=0 time=218 us cost=2 size=456 card=19)
              50         50         50                INDEX RANGE SCAN TIBEX_PARTICIPANTQSID (cr=1 pr=0 pw=0 time=89 us cost=1 size=0 card=19)(object id 2244322)
          159757     159757     159757               HASH JOIN  (cr=2118 pr=0 pw=0 time=179667 us cost=225 size=7626528 card=39312)
           48412      48412      48412                HASH JOIN RIGHT ANTI (cr=1060 pr=0 pw=0 time=38332 us cost=45 size=2201472 card=39312)
               1          1          1                 TABLE ACCESS FULL TIBEX_BESTEXECSTATUSENUM (cr=2 pr=0 pw=0 time=53 us cost=2 size=14 card=1)
           52416      52416      52416                 TABLE ACCESS FULL TIBEX_BESTEXREL (cr=1058 pr=0 pw=0 time=28524 us cost=42 size=2201472 card=52416)
           47003      47003      47003                TABLE ACCESS FULL TIBEX_BESTEXREL (cr=1058 pr=0 pw=0 time=29431 us cost=43 size=6888960 card=49920)
               0          0          0        HASH GROUP BY (cr=1 pr=0 pw=0 time=240 us cost=7 size=19 card=1)
               0          0          0         VIEW  TIBEX_TSTRADEBYQSIDVIEW (cr=1 pr=0 pw=0 time=167 us cost=5 size=19 card=1)
               0          0          0          HASH UNIQUE (cr=1 pr=0 pw=0 time=164 us cost=5 size=820 card=1)
               0          0          0           FILTER  (cr=1 pr=0 pw=0 time=120 us)
               0          0          0            NESTED LOOPS  (cr=1 pr=0 pw=0 time=119 us)
               0          0          0             NESTED LOOPS  (cr=1 pr=0 pw=0 time=113 us cost=2 size=820 card=1)
               0          0          0              TABLE ACCESS FULL TIBEX_TSTRADE (cr=1 pr=0 pw=0 time=111 us cost=2 size=808 card=1)
               0          0          0              INDEX UNIQUE SCAN XPKTIBEX_PARTICIPANT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 2244321)
               0          0          0             TABLE ACCESS BY INDEX ROWID TIBEX_PARTICIPANT (cr=0 pr=0 pw=0 time=0 us cost=0 size=12 card=1)
               0          0          0            SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
               0          0          0             TABLE ACCESS FULL TIBEX_TSTRADE (cr=0 pr=0 pw=0 time=0 us cost=2 size=35 card=1)
              18         18         18        HASH GROUP BY (cr=10 pr=0 pw=0 time=851 us cost=9 size=60 card=5)
              18         18         18         VIEW  VM_NWVW_6 (cr=10 pr=0 pw=0 time=490 us cost=7 size=72 card=6)
              18         18         18          HASH UNIQUE (cr=10 pr=0 pw=0 time=487 us cost=7 size=210 card=6)
              18         18         18           NESTED LOOPS  (cr=10 pr=0 pw=0 time=421 us cost=2 size=210 card=6)
              43         43         43            TABLE ACCESS BY INDEX ROWID TIBEX_PARTICIPANT (cr=4 pr=0 pw=0 time=208 us cost=2 size=351 card=13)
              50         50         50             INDEX RANGE SCAN TIBEX_PARTICIPANTQSID (cr=1 pr=0 pw=0 time=84 us cost=1 size=0 card=19)(object id 2244322)
               1          1          1             TABLE ACCESS FULL TIBEX_PARTICIPANTSTATUSENUM (cr=2 pr=0 pw=0 time=24 us cost=2 size=13 card=1)
              18         18         18            INDEX UNIQUE SCAN XPKTIBEX_PARTICIPANTROLEMAP (cr=6 pr=0 pw=0 time=84 us cost=0 size=8 card=1)(object id 2244325)
               1          1          1             TABLE ACCESS FULL TIBEX_ROLE (cr=2 pr=0 pw=0 time=16 us cost=2 size=12 card=1)
      
      Elapsed times include waiting on following events:
        Event waited on                             Times   Max. Wait  Total Waited
        ----------------------------------------   Waited  ----------  ------------
        SQL*Net message to client                       2        0.00          0.00
        direct path read                             5218        0.21         53.62
        Disk file operations I/O                        1        0.00          0.00
        direct path write temp                       4173        1.29        102.31
        direct path read temp                       22278        0.80         96.90
        db file sequential read                        98        0.00          0.03
        SQL*Net message from client                     2        0.00          0.00
      ********************************************************************************
      
      TIBEX_QSCACHELOADORDERING
      SELECT  A."ORDERID", A."USERORDERID", A."ORDERSIDE", A."ORDERTYPE",
                A.ORDERSTATUS, A.BOARDID, A.TIMEINFORCE, A.INSTRUMENTID,
                A.REFERENCEID, A.PRICETYPE, A.PRICE, A.AVERAGEPRICE,
                A.QUANTITY, A.MINIMUMFILL, A.DISCLOSEDQTY, A.REMAINQTY,
                A.AON, A.PARTICIPANTID, A.ACCOUNTTYPE, A.ACCOUNTNO,
                A.CLEARINGAGENCY, A.LASTINSTRESULT, A.LASTINSTMESSAGESEQUENCE,
                A.LASTEXECUTIONID, A.NOTE, A.TIMESTAMP, A.QTYFILLED, A.MEID,
                A.LASTINSTREJECTCODE, A.LASTEXECPRICE, A.LASTEXECQTY,
                A.LASTINSTTYPE, A.LASTEXECUTIONCOUNTERPARTY, A.VISIBLEQTY,
                A.STOPPRICE, A.LASTEXECCLEARINGAGENCY, A.LASTEXECACCOUNTNO,
                A.LASTEXECCPCLEARINGAGENCY, A.MESSAGESEQUENCE,
                A.LASTINSTUSERALIAS, A.BOOKTIMESTAMP, A.PARTICIPANTIDMM,
                A.MARKETSTATE, A.PARTNEREXID, A.LastExecSETTLEMENTCYCLE,
                A.LASTEXECPOSTTRADEVENUETYPE, A.PRICELEVELPOSITION,
                A.PREVREFERENCEID, A.EXPIRYTIMESTAMP, matchType,
                a.lastExecutionRole, a.MDEntryID, a.PegOffset,
                a.haltReason, A.COMPARISONPRICE, A.ENTEREDPRICETYPE,
                A.ISPEX, A.CLEARINGHANDLING, A.SubPartID, B.qsid
          FROM  tibex_Order A,
                tibex_Participant b
          WHERE a.participantID = b.participantID
            AND (A.MessageSequence, A.OrderID) IN (
                  SELECT  max(C.MessageSequence), C.OrderID
                    FROM  tibex_Order C
                    WHERE LastInstRejectCode = 'OK'
                    and c.orderid=a.orderid
                    GROUP By C.OrderID
                )
            AND a.OrderStatus IN (
                  SELECT OrderStatus
                    FROM  tibex_orderStatusEnum
                    WHERE ShortDesc IN (
                            'ORD_OPEN', 'ORD_EXPIRE', 'ORD_CANCEL', 'ORD_FILLED','ORD_CREATE','ORD_PENDAMD','ORD_PENDCAN'
                          )
                )
        UNION ALL
        SELECT  A.ORDERID, A.USERORDERID, A.ORDERSIDE, A.ORDERTYPE,
                A.ORDERSTATUS, A.BOARDID, A.TIMEINFORCE, A.INSTRUMENTID,
                A.REFERENCEID, A.PRICETYPE, A.PRICE, A.AVERAGEPRICE,
                A.QUANTITY, A.MINIMUMFILL, A.DISCLOSEDQTY, A.REMAINQTY,
                A.AON, A.PARTICIPANTID, A.ACCOUNTTYPE, A.ACCOUNTNO,
                A.CLEARINGAGENCY, A.LASTINSTRESULT, A.LASTINSTMESSAGESEQUENCE,
                A.LASTEXECUTIONID, A.NOTE, A.TIMESTAMP, A.QTYFILLED, A.MEID,
                A.LASTINSTREJECTCODE, A.LASTEXECPRICE, A.LASTEXECQTY,
                A.LASTINSTTYPE, A.LASTEXECUTIONCOUNTERPARTY, A.VISIBLEQTY,
                A.STOPPRICE, A.LASTEXECCLEARINGAGENCY, A.LASTEXECACCOUNTNO,
                A.LASTEXECCPCLEARINGAGENCY, A.MESSAGESEQUENCE,
                A.LASTINSTUSERALIAS, A.BOOKTIMESTAMP, A.PARTICIPANTIDMM,
                A.MARKETSTATE, A.PARTNEREXID, A.LastExecSETTLEMENTCYCLE,
                A.LASTEXECPOSTTRADEVENUETYPE, A.PRICELEVELPOSITION,
                A.PREVREFERENCEID, A.EXPIRYTIMESTAMP, matchType,
                a.lastExecutionRole, A.MDEntryID, a.PegOffset,
                a.haltReason, A.COMPARISONPRICE, A.ENTEREDPRICETYPE,
                A.ISPEX, A.CLEARINGHANDLING, A.SubPartID, B.qsid
          FROM  tibex_Order A,
                tibex_Participant b
          WHERE a.participantID = b.participantID
            AND orderstatus in (
                  SELECT  orderstatus
                    FROM  tibex_orderStatusEnum
                    WHERE ShortDesc in ('ORD_REJECT')
                )
            AND 1 IN (
                    SELECT count(*)
                      FROM tibex_order c
                      WHERE c.orderid=a.orderid
                        AND c.instrumentID=a.instrumentID
                )
      Regards
      NM
        • 1. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
          EmaxG
          It is doing a lot of sorting, can you please post the explain plan? might help identify if some table full scans can be avoided
          • 2. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
            NM
            Hi,

            Thanks for the Response.

            Explain Plan.
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 2112122497
            
            --------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                                | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
            --------------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                         |                             |    36 |   756 |       |  8447K  (1)| 10:14:07 |
            |   1 |  VIEW                                    | TIBEX_QSCACHELOADORDERING   |    36 |   756 |       |  8447K  (1)| 10:14:07 |
            |   2 |   SORT ORDER BY                          |                             |    36 |  1224 |       |  8447K  (1)| 10:14:07 |
            |   3 |    HASH GROUP BY                         |                             |    36 |  1224 |       |  8447K  (1)| 10:14:07 |
            |   4 |     VIEW                                 |                             |    36 |  1224 |       |  8447K  (1)| 10:14:07 |
            |   5 |      SORT UNIQUE                         |                             |    36 |   836 |       |  8447K  (1)| 10:14:07 |
            |   6 |       UNION-ALL                          |                             |       |       |       |            |          |
            |   7 |        HASH GROUP BY                     |                             |     3 |    30 |       |  8445K  (1)| 10:14:01 |
            |   8 |         VIEW                             | TIBEX_ORDERSBYQSIDVIEW      |     3 |    30 |       |  8445K  (1)| 10:14:01 |
            |   9 |          UNION-ALL                       |                             |       |       |       |            |          |
            |* 10 |           FILTER                         |                             |       |       |       |            |          |
            |  11 |            HASH GROUP BY                 |                             |     2 |   280 |       | 56333   (7)| 00:04:06 |
            |* 12 |             HASH JOIN                    |                             |  1153K|   154M|       | 56232   (7)| 00:04:06 |
            |  13 |              TABLE ACCESS BY INDEX ROWID | TIBEX_PARTICIPANT           |    19 |   456 |       |     2   (0)| 00:00:01 |
            |* 14 |               INDEX RANGE SCAN           | TIBEX_PARTICIPANTQSID       |    19 |       |       |     1   (0)| 00:00:01 |
            |* 15 |              HASH JOIN                   |                             |  1193K|   132M|       | 56218   (7)| 00:04:06 |
            |* 16 |               TABLE ACCESS FULL          | TIBEX_ORDERSTATUSENUM       |     7 |   182 |       |     2   (0)| 00:00:01 |
            |* 17 |               HASH JOIN                  |                             |  1534K|   131M|    36M| 56201   (7)| 00:04:06 |
            |* 18 |                TABLE ACCESS FULL         | TIBEX_ORDER                 |   779K|    27M|       | 20898   (8)| 00:01:32 |
            |  19 |                TABLE ACCESS FULL         | TIBEX_ORDER                 |    15M|   788M|       | 21106   (9)| 00:01:33 |
            |* 20 |           FILTER                         |                             |       |       |       |            |          |
            |* 21 |            HASH JOIN                     |                             |  1674K|    97M|       | 20707   (7)| 00:01:31 |
            |  22 |             MERGE JOIN CARTESIAN         |                             |    19 |   494 |       |     3   (0)| 00:00:01 |
            |  23 |              TABLE ACCESS BY INDEX ROWID | TIBEX_ORDERSTATUSENUM       |     1 |    14 |       |     2   (0)| 00:00:01 |
            |* 24 |               INDEX RANGE SCAN           | TIBEX_ORDERSTAT_ID_DESC     |     1 |       |       |     1   (0)| 00:00:01 |
            |  25 |              BUFFER SORT                 |                             |    19 |   228 |       |     1   (0)| 00:00:01 |
            |  26 |               TABLE ACCESS BY INDEX ROWID| TIBEX_PARTICIPANT           |    19 |   228 |       |     1   (0)| 00:00:01 |
            |* 27 |                INDEX RANGE SCAN          | TIBEX_PARTICIPANTQSID       |    19 |       |       |     0   (0)| 00:00:01 |
            |  28 |             TABLE ACCESS FULL            | TIBEX_ORDER                 |    15M|   520M|       | 20555   (6)| 00:01:30 |
            |  29 |            SORT AGGREGATE                |                             |     1 |    27 |       |            |          |
            |* 30 |             TABLE ACCESS BY INDEX ROWID  | TIBEX_ORDER                 |     1 |    27 |       |     5   (0)| 00:00:01 |
            |* 31 |              INDEX RANGE SCAN            | IX_ORDERBOOK                |     2 |       |       |     3   (0)| 00:00:01 |
            |  32 |        HASH GROUP BY                     |                             |     2 |   202 |       |   791  (10)| 00:00:04 |
            |* 33 |         HASH JOIN                        |                             |     3 |   303 |       |   789  (10)| 00:00:04 |
            |* 34 |          HASH JOIN                       |                             |     3 |   267 |       |   786  (10)| 00:00:04 |
            |* 35 |           HASH JOIN RIGHT SEMI           |                             |     3 |   225 |       |   784  (10)| 00:00:04 |
            |  36 |            VIEW                          | VW_NSO_2                    |  2102 | 71468 |       |   395  (10)| 00:00:02 |
            |  37 |             HASH GROUP BY                |                             |  2102 | 77774 |       |   395  (10)| 00:00:02 |
            |* 38 |              TABLE ACCESS FULL           | TIBEX_QUOTE                 |   141K|  5109K|       |   384   (8)| 00:00:02 |
            |  39 |            TABLE ACCESS FULL             | TIBEX_QUOTE                 |   282K|    11M|       |   385   (8)| 00:00:02 |
            |* 40 |           TABLE ACCESS FULL              | TIBEX_QUOTESTATUSENUM       |     3 |    42 |       |     2   (0)| 00:00:01 |
            |  41 |          TABLE ACCESS BY INDEX ROWID     | TIBEX_PARTICIPANT           |    19 |   228 |       |     2   (0)| 00:00:01 |
            |* 42 |           INDEX RANGE SCAN               | TIBEX_PARTICIPANTQSID       |    19 |       |       |     1   (0)| 00:00:01 |
            |  43 |        HASH GROUP BY                     |                             |    25 |   525 |       |   476   (9)| 00:00:03 |
            |  44 |         VIEW                             |                             |    25 |   525 |       |   474   (8)| 00:00:03 |
            |  45 |          SORT UNIQUE                     |                             |    25 |  5450 |       |   474  (54)| 00:00:03 |
            |  46 |           UNION-ALL                      |                             |       |       |       |            |          |
            |* 47 |            FILTER                        |                             |       |       |       |            |          |
            |  48 |             HASH GROUP BY                |                             |    12 |  2616 |       |   239   (8)| 00:00:02 |
            |* 49 |              HASH JOIN                   |                             | 36192 |  7704K|       |   233   (6)| 00:00:02 |
            |  50 |               TABLE ACCESS BY INDEX ROWID| TIBEX_PARTICIPANT           |    19 |   456 |       |     2   (0)| 00:00:01 |
            |* 51 |                INDEX RANGE SCAN          | TIBEX_PARTICIPANTQSID       |    19 |       |       |     1   (0)| 00:00:01 |
            |* 52 |               HASH JOIN                  |                             | 39312 |  7447K|  2616K|   230   (5)| 00:00:02 |
            |* 53 |                HASH JOIN RIGHT ANTI      |                             | 39312 |  2149K|       |    45   (9)| 00:00:01 |
            |* 54 |                 TABLE ACCESS FULL        | TIBEX_BESTEXECSTATUSENUM    |     1 |    14 |       |     2   (0)| 00:00:01 |
            |  55 |                 TABLE ACCESS FULL        | TIBEX_BESTEXREL             | 52416 |  2149K|       |    42   (8)| 00:00:01 |
            |  56 |                TABLE ACCESS FULL         | TIBEX_BESTEXREL             | 52416 |  7063K|       |    43  (10)| 00:00:01 |
            |* 57 |            FILTER                        |                             |       |       |       |            |          |
            |  58 |             HASH GROUP BY                |                             |    13 |  2834 |       |   235   (9)| 00:00:02 |
            |* 59 |              HASH JOIN                   |                             | 38002 |  8090K|       |   228   (6)| 00:00:01 |
            |  60 |               TABLE ACCESS BY INDEX ROWID| TIBEX_PARTICIPANT           |    19 |   456 |       |     2   (0)| 00:00:01 |
            |* 61 |                INDEX RANGE SCAN          | TIBEX_PARTICIPANTQSID       |    19 |       |       |     1   (0)| 00:00:01 |
            |* 62 |               HASH JOIN                  |                             | 39312 |  7447K|  2616K|   225   (5)| 00:00:01 |
            |* 63 |                HASH JOIN RIGHT ANTI      |                             | 39312 |  2149K|       |    45   (9)| 00:00:01 |
            |* 64 |                 TABLE ACCESS FULL        | TIBEX_BESTEXECSTATUSENUM    |     1 |    14 |       |     2   (0)| 00:00:01 |
            |  65 |                 TABLE ACCESS FULL        | TIBEX_BESTEXREL             | 52416 |  2149K|       |    42   (8)| 00:00:01 |
            |* 66 |                TABLE ACCESS FULL         | TIBEX_BESTEXREL             | 49920 |  6727K|       |    43  (10)| 00:00:01 |
            |  67 |        HASH GROUP BY                     |                             |     1 |    19 |       |     7  (43)| 00:00:01 |
            |  68 |         VIEW                             | TIBEX_TSTRADEBYQSIDVIEW     |     1 |    19 |       |     5  (20)| 00:00:01 |
            |  69 |          HASH UNIQUE                     |                             |     1 |   820 |       |     5  (20)| 00:00:01 |
            |* 70 |           FILTER                         |                             |       |       |       |            |          |
            |  71 |            NESTED LOOPS                  |                             |       |       |       |            |          |
            |  72 |             NESTED LOOPS                 |                             |     1 |   820 |       |     2   (0)| 00:00:01 |
            |  73 |              TABLE ACCESS FULL           | TIBEX_TSTRADE               |     1 |   808 |       |     2   (0)| 00:00:01 |
            |* 74 |              INDEX UNIQUE SCAN           | XPKTIBEX_PARTICIPANT        |     1 |       |       |     0   (0)| 00:00:01 |
            |* 75 |             TABLE ACCESS BY INDEX ROWID  | TIBEX_PARTICIPANT           |     1 |    12 |       |     0   (0)| 00:00:01 |
            |  76 |            SORT AGGREGATE                |                             |     1 |    35 |       |            |          |
            |* 77 |             TABLE ACCESS FULL            | TIBEX_TSTRADE               |     1 |    35 |       |     2   (0)| 00:00:01 |
            |  78 |        HASH GROUP BY                     |                             |     5 |    60 |       |     9  (34)| 00:00:01 |
            |  79 |         VIEW                             | VM_NWVW_6                   |     6 |    72 |       |     7  (15)| 00:00:01 |
            |  80 |          HASH UNIQUE                     |                             |     6 |   210 |       |     7  (15)| 00:00:01 |
            |  81 |           NESTED LOOPS                   |                             |     6 |   210 |       |     2   (0)| 00:00:01 |
            |* 82 |            TABLE ACCESS BY INDEX ROWID   | TIBEX_PARTICIPANT           |    13 |   351 |       |     2   (0)| 00:00:01 |
            |* 83 |             INDEX RANGE SCAN             | TIBEX_PARTICIPANTQSID       |    19 |       |       |     1   (0)| 00:00:01 |
            |* 84 |             TABLE ACCESS FULL            | TIBEX_PARTICIPANTSTATUSENUM |     1 |    13 |       |     2   (0)| 00:00:01 |
            |* 85 |            INDEX UNIQUE SCAN             | XPKTIBEX_PARTICIPANTROLEMAP |     1 |     8 |       |     0   (0)| 00:00:01 |
            |* 86 |             TABLE ACCESS FULL            | TIBEX_ROLE                  |     1 |    12 |       |     2   (0)| 00:00:01 |
            --------------------------------------------------------------------------------------------------------------------------------
            Predicate Information (identified by operation id):
            ---------------------------------------------------
              10 - filter("A"."MESSAGESEQUENCE"=MAX("C"."MESSAGESEQUENCE"))
              12 - access("A"."PARTICIPANTID"="B"."PARTICIPANTID")
              14 - access("B"."QSID"='QS1')
              15 - access("A"."ORDERSTATUS"="ORDERSTATUS")
              16 - filter("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE' OR "SHORTDESC"='ORD_EXPIRE' OR
                          "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR "SHORTDESC"='ORD_PENDAMD' OR "SHORTDESC"='ORD_PENDCAN')
              17 - access("A"."ORDERID"="C"."ORDERID")
              18 - filter("LASTINSTREJECTCODE"='OK')
              20 - filter( (SELECT COUNT(*) FROM TRD_PRE_EOD."TIBEX_ORDER" "C" WHERE "C"."ORDERID"=:B1 AND
                          "C"."INSTRUMENTID"=:B2)=1)
              21 - access("A"."PARTICIPANTID"="B"."PARTICIPANTID" AND "ORDERSTATUS"="ORDERSTATUS")
              24 - access("SHORTDESC"='ORD_REJECT')
              27 - access("B"."QSID"='QS1')
              30 - filter("C"."INSTRUMENTID"=:B1)
              31 - access("C"."ORDERID"=:B1)
              33 - access("A"."PARTICIPANTID"="B"."PARTICIPANTID")
              34 - access("A"."QUOTESTATUS"="QUOTESTATUS")
              35 - access("A"."MESSAGESEQUENCE"="MAX(C.MESSAGESEQUENCE)" AND "A"."QUOTEID"="QUOTEID")
              38 - filter("LASTINSTREJECTCODE"='OK')
              40 - filter("SHORTDESC"='QUO_OFFMKT' OR "SHORTDESC"='QUO_ONMKT' OR "SHORTDESC"='QUO_PREOPN')
              42 - access("B"."QSID"='QS1')
              47 - filter("MESSAGESEQUENCE"=MAX("MESSAGESEQUENCE"))
              49 - access("PARTICIPANTID"="B"."PARTICIPANTID")
              51 - access("B"."QSID"='QS1')
              52 - access("A"."INSTRUMENTID"="INSTRUMENTID" AND "A"."PARTNEREXID"="PARTNEREXID")
              53 - access("B"."BESTEXECSTATUS"="BESTEXECSTATUS")
              54 - filter("SHORTDESC"='BESTEX_REJ')
              57 - filter("MESSAGESEQUENCE"=MAX("MESSAGESEQUENCE"))
              59 - access("PARTICIPANTIDMM"="D"."PARTICIPANTID")
              61 - access("D"."QSID"='QS1')
              62 - access("A"."INSTRUMENTID"="INSTRUMENTID" AND "A"."PARTNEREXID"="PARTNEREXID")
              63 - access("B"."BESTEXECSTATUS"="BESTEXECSTATUS")
              64 - filter("SHORTDESC"='BESTEX_REJ')
              66 - filter("PARTICIPANTID"<>"PARTICIPANTIDMM")
              70 - filter("MESSAGESEQUENCE"= (SELECT MAX("MESSAGESEQUENCE") FROM TRD_PRE_EOD."TIBEX_TSTRADE" "C" WHERE  "C"."TSTRADEID"=:B1))
              74 - access("A"."PARTICIPANTID"="B"."PARTICIPANTID")
              75 - filter("B"."QSID"='QS1')
              77 - filter("C"."TSTRADEID"=:B1)
              82 - filter("PARTICIPANTSTATUS"<> (SELECT "PARTICIPANTSTATUS" FROM TRD_PRE_EOD."TIBEX_PARTICIPANTSTATUSENUM"
                          "TIBEX_PARTICIPANTSTATUSENUM" WHERE "SHORTDESC"='PART_DEL'))
              83 - access("QSID"='QS1')
              84 - filter("SHORTDESC"='PART_DEL')
              85 - access("PARTICIPANTID"="PARTICIPANTID" AND "ROLE"= (SELECT "ROLE" FROM TRD_PRE_EOD."TIBEX_ROLE" "TIBEX_ROLE"  WHERE "ROLENAME"='FIXMEMBER'))
              86 - filter("ROLENAME"='FIXMEMBER')
            
            Statistics
            ----------------------------------------------------------
                   2362  recursive calls
                      0  db block gets
                1724015  consistent gets
                1909263  physical reads
                    232  redo size
                   1106  bytes sent via SQL*Net to client
                    535  bytes received via SQL*Net from client
                      3  SQL*Net roundtrips to/from client
                     24  sorts (memory)
                      0  sorts (disk)
                     25  rows processed
            Regards
            NM
            • 3. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
              EmaxG
              Try adding indexes where possible, this way you might avoid some full table scans.
              Furthermore, are your statistics up-to-date?

              If the table full scans are not avoidable, maybe using PARALLEL is an option which you can benefit from.

              Greetings,
              N K
              • 4. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                user503699
                NM wrote:
                Hi ,

                One of the Query which takes around 120 seconds to retrieve 50 records.The main table contains around 20 Million records.Looking at the trace file most of the time spent on "direct path write temp,direct path read temp,direct path read"
                So if somebody can see and find any tuning can be done to his Query
                You may want to refer to one of your old posts to get an idea of how you can change your view query for good.
                Re: Query that Never comes out

                p.s. BTW, you said your query takes around 120 seconds to fetch 50 records but your TkProf tells a different story. It shows it takes around 334 seconds to fetch 25 records. So which one is correct? And, as usual, what is the acceptable performance (aka goal)?
                • 5. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                  Nikolay Savvinov
                  Hi,

                  a few comments on posting tuning requests first:

                  1) while tkprofed trace file contains all relevant information, it's not as easy to read as dbms_xplan(format=>'allstats') is
                  2) operands of UNION can be treated for tuning purposes as separate queries. Pick the one that is causing the performance problem and post it, the other one is irrelevant and just clutters the post.

                  Now about the query:

                  direct path read/write temp events are most likely coming from the hash join of TIBEX_ORDER to itself, which originates from the subquery below:
                      WHERE a.participantID = b.participantID
                        AND (A.MessageSequence, A.OrderID) IN (
                              SELECT  max(C.MessageSequence), C.OrderID
                                FROM  tibex_Order C
                                WHERE LastInstRejectCode = 'OK'
                                and c.orderid=a.orderid
                                GROUP By C.OrderID
                            )
                  I would recommend you to replace it with an analytic function, were it not for the LastInstRejectCode = 'OK' bit... so that won't work, but there is another thing that might -- how restrictive is
                  a.OrderStatus IN (
                              SELECT OrderStatus
                                FROM  tibex_orderStatusEnum
                                WHERE ShortDesc IN (
                                        'ORD_OPEN', 'ORD_EXPIRE', 'ORD_CANCEL', 'ORD_FILLED','ORD_CREATE','ORD_PENDAMD','ORD_PENDCAN'
                                      )
                  ?

                  If it can provide a strong selectivity, then we can use it to avoid the full table scan and reduce the size of HASH JOIN inputs. If not, then I don't see how to improve the plan significantly, but you can still improve its performance by reducing disk spills during the hash join. Make your workarea bigger so that the HASH JOIN would occur in memory (or at least in fewer passes).

                  Best regards,
                  Nikolay
                  • 6. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                    NM
                    Hi,

                    I looked into that modified query but still query takes >90 seconds which is the main issue here because our application will fail to startup once the DB response time >90 seconds.
                    CREATE OR REPLACE VIEW TIBEX_ORDERSBYQSIDVIEW_6
                    as 
                    ( SELECT  A."ORDERID", A."USERORDERID", A."ORDERSIDE", A."ORDERTYPE",
                              A.ORDERSTATUS, A.BOARDID, A.TIMEINFORCE, A.INSTRUMENTID,
                              A.REFERENCEID, A.PRICETYPE, A.PRICE, A.AVERAGEPRICE,
                              A.QUANTITY, A.MINIMUMFILL, A.DISCLOSEDQTY, A.REMAINQTY,
                              A.AON, A.PARTICIPANTID, A.ACCOUNTTYPE, A.ACCOUNTNO,
                              A.CLEARINGAGENCY, A.LASTINSTRESULT, A.LASTINSTMESSAGESEQUENCE,
                              A.LASTEXECUTIONID,a.NOTE,a.TIMESTAMP, A.QTYFILLED, A.MEID,
                              A.LASTINSTREJECTCODE, A.LASTEXECPRICE, A.LASTEXECQTY,
                              A.LASTINSTTYPE, A.LASTEXECUTIONCOUNTERPARTY, A.VISIBLEQTY,
                              A.STOPPRICE, A.LASTEXECCLEARINGAGENCY, A.LASTEXECACCOUNTNO,
                              A.LASTEXECCPCLEARINGAGENCY, A.MESSAGESEQUENCE,
                              A.LASTINSTUSERALIAS, A.BOOKTIMESTAMP, A.PARTICIPANTIDMM,
                              A.MARKETSTATE, A.PARTNEREXID, A.LastExecSETTLEMENTCYCLE,
                              A.LASTEXECPOSTTRADEVENUETYPE, A.PRICELEVELPOSITION,
                              A.PREVREFERENCEID, A.EXPIRYTIMESTAMP, A.matchType,
                              A.lastExecutionRole, A.MDEntryID, A.PegOffset,
                              A.haltReason,A.COMPARISONPRICE,A.ENTEREDPRICETYPE,
                              A.ISPEX, A.CLEARINGHANDLING, A.SubPartID, B.qsid
                      FROM (
                                SELECT  "ORDERID", "USERORDERID", "ORDERSIDE", "ORDERTYPE",
                                      ORDERSTATUS, BOARDID, TIMEINFORCE, INSTRUMENTID,
                                      REFERENCEID, PRICETYPE, PRICE, AVERAGEPRICE,
                                      QUANTITY, MINIMUMFILL, DISCLOSEDQTY, REMAINQTY,
                                      AON, PARTICIPANTID, ACCOUNTTYPE, ACCOUNTNO,
                                      CLEARINGAGENCY, LASTINSTRESULT, LASTINSTMESSAGESEQUENCE,
                                      LASTEXECUTIONID, NOTE,TIMESTAMP, QTYFILLED, MEID,
                                      LASTINSTREJECTCODE, LASTEXECPRICE, LASTEXECQTY,
                                      LASTINSTTYPE, LASTEXECUTIONCOUNTERPARTY, VISIBLEQTY,
                                      STOPPRICE, LASTEXECCLEARINGAGENCY, LASTEXECACCOUNTNO,
                                      LASTEXECCPCLEARINGAGENCY, MESSAGESEQUENCE,
                                      LASTINSTUSERALIAS, BOOKTIMESTAMP, PARTICIPANTIDMM,
                                      MARKETSTATE, PARTNEREXID, LastExecSETTLEMENTCYCLE,
                                      LASTEXECPOSTTRADEVENUETYPE, PRICELEVELPOSITION,
                                      PREVREFERENCEID, EXPIRYTIMESTAMP, matchType,
                                      lastExecutionRole, MDEntryID, PegOffset,
                                      haltReason, COMPARISONPRICE,ENTEREDPRICETYPE,
                              ISPEX, CLEARINGHANDLING, SubPartID
                                 FROM (
                                         SELECT  "ORDERID", "USERORDERID", "ORDERSIDE", "ORDERTYPE",
                                                  ORDERSTATUS, BOARDID, TIMEINFORCE, INSTRUMENTID,
                                                  REFERENCEID, PRICETYPE, PRICE, AVERAGEPRICE,
                                                  QUANTITY, MINIMUMFILL, DISCLOSEDQTY, REMAINQTY,
                                                  AON, PARTICIPANTID, ACCOUNTTYPE, ACCOUNTNO,
                                                  CLEARINGAGENCY, LASTINSTRESULT, LASTINSTMESSAGESEQUENCE,
                                                  LASTEXECUTIONID, NOTE,TIMESTAMP, QTYFILLED, MEID,
                                                  LASTINSTREJECTCODE, LASTEXECPRICE, LASTEXECQTY,
                                                  LASTINSTTYPE, LASTEXECUTIONCOUNTERPARTY, VISIBLEQTY,
                                                  STOPPRICE, LASTEXECCLEARINGAGENCY, LASTEXECACCOUNTNO,
                                                  LASTEXECCPCLEARINGAGENCY, MESSAGESEQUENCE,
                                                  LASTINSTUSERALIAS, BOOKTIMESTAMP, PARTICIPANTIDMM,
                                                  MARKETSTATE, PARTNEREXID, LastExecSETTLEMENTCYCLE,
                                                  LASTEXECPOSTTRADEVENUETYPE, PRICELEVELPOSITION,
                                                  PREVREFERENCEID, EXPIRYTIMESTAMP, matchType,
                                                  lastExecutionRole, MDEntryID, PegOffset,
                                                  haltReason, COMPARISONPRICE,ENTEREDPRICETYPE,ISPEX, CLEARINGHANDLING, SubPartID,
                                                  max(case when LastInstRejectCode = 'OK' then MessageSequence end) over (partition by OrderID) as maxseq
                                              FROM  tibex_Order
                                           )
                                  WHERE MessageSequence = maxseq
                                ) A,  tibex_Participant b
                               WHERE a.participantID = b.participantID
                                 AND a.OrderStatus IN (SELECT OrderStatus    FROM  tibex_orderStatusEnum   WHERE ShortDesc IN ( 'ORD_OPEN', 'ORD_EXPIRE', 'ORD_CANCEL', 'ORD_FILLED','ORD_CREATE','ORD_PENDAMD','ORD_PENDCAN'))
                    UNION ALL
                      SELECT  A.ORDERID,A.USERORDERID,A.ORDERSIDE,A.ORDERTYPE,
                              A.ORDERSTATUS,A.BOARDID,A.TIMEINFORCE,A.INSTRUMENTID,
                              A.REFERENCEID, A.PRICETYPE, A.PRICE, A.AVERAGEPRICE,
                              A.QUANTITY, A.MINIMUMFILL, A.DISCLOSEDQTY, A.REMAINQTY,
                              A.AON, A.PARTICIPANTID, A.ACCOUNTTYPE, A.ACCOUNTNO,
                              A.CLEARINGAGENCY, A.LASTINSTRESULT, A.LASTINSTMESSAGESEQUENCE,
                              A.LASTEXECUTIONID, A.NOTE, A.TIMESTAMP, A.QTYFILLED, A.MEID,
                              A.LASTINSTREJECTCODE, A.LASTEXECPRICE, A.LASTEXECQTY,
                              A.LASTINSTTYPE, A.LASTEXECUTIONCOUNTERPARTY, A.VISIBLEQTY,
                              A.STOPPRICE, A.LASTEXECCLEARINGAGENCY, A.LASTEXECACCOUNTNO,
                              A.LASTEXECCPCLEARINGAGENCY, A.MESSAGESEQUENCE,
                              A.LASTINSTUSERALIAS, A.BOOKTIMESTAMP, A.PARTICIPANTIDMM,
                              A.MARKETSTATE, A.PARTNEREXID, A.LastExecSETTLEMENTCYCLE,
                              A.LASTEXECPOSTTRADEVENUETYPE, A.PRICELEVELPOSITION,
                              A.PREVREFERENCEID, A.EXPIRYTIMESTAMP, A.matchType,
                              A.lastExecutionRole, A.MDEntryID, A.PegOffset,
                              A.haltReason,A.COMPARISONPRICE,A.ENTEREDPRICETYPE,
                              A.ISPEX, A.CLEARINGHANDLING, A.SubPartID,b.qsid
                       FROM (
                                 SELECT  ORDERID, USERORDERID, ORDERSIDE, ORDERTYPE,
                                         ORDERSTATUS, BOARDID, TIMEINFORCE, INSTRUMENTID,
                                         REFERENCEID, PRICETYPE, PRICE, AVERAGEPRICE,
                                         QUANTITY, MINIMUMFILL, DISCLOSEDQTY, REMAINQTY,
                                         AON, PARTICIPANTID, ACCOUNTTYPE, ACCOUNTNO,
                                         CLEARINGAGENCY, LASTINSTRESULT, LASTINSTMESSAGESEQUENCE,
                                         LASTEXECUTIONID, NOTE, TIMESTAMP, QTYFILLED, MEID,
                                         LASTINSTREJECTCODE, LASTEXECPRICE, LASTEXECQTY,
                                         LASTINSTTYPE, LASTEXECUTIONCOUNTERPARTY, VISIBLEQTY,
                                         STOPPRICE, LASTEXECCLEARINGAGENCY, LASTEXECACCOUNTNO,
                                         LASTEXECCPCLEARINGAGENCY, MESSAGESEQUENCE,
                                         LASTINSTUSERALIAS, BOOKTIMESTAMP, PARTICIPANTIDMM,
                                         MARKETSTATE, PARTNEREXID, LastExecSETTLEMENTCYCLE,
                                         LASTEXECPOSTTRADEVENUETYPE, PRICELEVELPOSITION,
                                         PREVREFERENCEID, EXPIRYTIMESTAMP, matchType,
                                         lastExecutionRole, MDEntryID, PegOffset,
                                         haltReason,COMPARISONPRICE,ENTEREDPRICETYPE,
                              ISPEX, CLEARINGHANDLING,SubPartID
                                  FROM (
                                            SELECT  ORDERID, USERORDERID, ORDERSIDE, ORDERTYPE,
                                                   ORDERSTATUS, BOARDID, TIMEINFORCE, INSTRUMENTID,
                                                   REFERENCEID, PRICETYPE, PRICE, AVERAGEPRICE,
                                                   QUANTITY, MINIMUMFILL, DISCLOSEDQTY, REMAINQTY,
                                                   AON, PARTICIPANTID, ACCOUNTTYPE, ACCOUNTNO,
                                                   CLEARINGAGENCY, LASTINSTRESULT, LASTINSTMESSAGESEQUENCE,
                                                   LASTEXECUTIONID, NOTE, TIMESTAMP, QTYFILLED, MEID,
                                                   LASTINSTREJECTCODE, LASTEXECPRICE, LASTEXECQTY,
                                                   LASTINSTTYPE, LASTEXECUTIONCOUNTERPARTY, VISIBLEQTY,
                                                   STOPPRICE, LASTEXECCLEARINGAGENCY, LASTEXECACCOUNTNO,
                                                   LASTEXECCPCLEARINGAGENCY, MESSAGESEQUENCE,
                                                   LASTINSTUSERALIAS, BOOKTIMESTAMP, PARTICIPANTIDMM,
                                                   MARKETSTATE, PARTNEREXID, LastExecSETTLEMENTCYCLE,
                                                   LASTEXECPOSTTRADEVENUETYPE, PRICELEVELPOSITION,
                                                   PREVREFERENCEID, EXPIRYTIMESTAMP, matchType,
                                                   lastExecutionRole, MDEntryID, PegOffset,
                                                   haltReason,COMPARISONPRICE,ENTEREDPRICETYPE,
                              ISPEX,CLEARINGHANDLING,SubPartID,
                                                   count(*) over (partition by orderid, instrumentID) cnt
                                           FROM  tibex_Order
                                         )
                                   WHERE cnt = 1
                                ) A,  tibex_Participant b
                              WHERE a.participantID = b.participantID
                                  AND orderstatus in (
                                                              SELECT  orderstatus
                                                                 FROM  tibex_orderStatusEnum
                                                              WHERE ShortDesc = 'ORD_REJECT'
                                                            ));
                    Regards
                    NM
                    • 7. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                      user503699
                      NM wrote:
                      Hi,

                      I looked into that modified query but still query takes >90 seconds which is the main issue here because our application will fail to startup once the DB response time >90 seconds.
                      I am not sure if it would be functionally equivalent to your query using UNION ALL but you may want to check if it helps to convert 2 queries (used in UNION ALL) into one query.
                      Something like following (of course, NOT TESTED)
                      CREATE OR REPLACE VIEW TIBEX_ORDERSBYQSIDVIEW_6
                      as 
                      ( SELECT  A."ORDERID", A."USERORDERID", A."ORDERSIDE", A."ORDERTYPE",
                                A.ORDERSTATUS, A.BOARDID, A.TIMEINFORCE, A.INSTRUMENTID,
                                A.REFERENCEID, A.PRICETYPE, A.PRICE, A.AVERAGEPRICE,
                                A.QUANTITY, A.MINIMUMFILL, A.DISCLOSEDQTY, A.REMAINQTY,
                                A.AON, A.PARTICIPANTID, A.ACCOUNTTYPE, A.ACCOUNTNO,
                                A.CLEARINGAGENCY, A.LASTINSTRESULT, A.LASTINSTMESSAGESEQUENCE,
                                A.LASTEXECUTIONID,a.NOTE,a.TIMESTAMP, A.QTYFILLED, A.MEID,
                                A.LASTINSTREJECTCODE, A.LASTEXECPRICE, A.LASTEXECQTY,
                                A.LASTINSTTYPE, A.LASTEXECUTIONCOUNTERPARTY, A.VISIBLEQTY,
                                A.STOPPRICE, A.LASTEXECCLEARINGAGENCY, A.LASTEXECACCOUNTNO,
                                A.LASTEXECCPCLEARINGAGENCY, A.MESSAGESEQUENCE,
                                A.LASTINSTUSERALIAS, A.BOOKTIMESTAMP, A.PARTICIPANTIDMM,
                                A.MARKETSTATE, A.PARTNEREXID, A.LastExecSETTLEMENTCYCLE,
                                A.LASTEXECPOSTTRADEVENUETYPE, A.PRICELEVELPOSITION,
                                A.PREVREFERENCEID, A.EXPIRYTIMESTAMP, A.matchType,
                                A.lastExecutionRole, A.MDEntryID, A.PegOffset,
                                A.haltReason,A.COMPARISONPRICE,A.ENTEREDPRICETYPE,
                                A.ISPEX, A.CLEARINGHANDLING, A.SubPartID, B.qsid
                        FROM (
                                  SELECT  "ORDERID", "USERORDERID", "ORDERSIDE", "ORDERTYPE",
                                        ORDERSTATUS, BOARDID, TIMEINFORCE, INSTRUMENTID,
                                        REFERENCEID, PRICETYPE, PRICE, AVERAGEPRICE,
                                        QUANTITY, MINIMUMFILL, DISCLOSEDQTY, REMAINQTY,
                                        AON, PARTICIPANTID, ACCOUNTTYPE, ACCOUNTNO,
                                        CLEARINGAGENCY, LASTINSTRESULT, LASTINSTMESSAGESEQUENCE,
                                        LASTEXECUTIONID, NOTE,TIMESTAMP, QTYFILLED, MEID,
                                        LASTINSTREJECTCODE, LASTEXECPRICE, LASTEXECQTY,
                                        LASTINSTTYPE, LASTEXECUTIONCOUNTERPARTY, VISIBLEQTY,
                                        STOPPRICE, LASTEXECCLEARINGAGENCY, LASTEXECACCOUNTNO,
                                        LASTEXECCPCLEARINGAGENCY, MESSAGESEQUENCE,
                                        LASTINSTUSERALIAS, BOOKTIMESTAMP, PARTICIPANTIDMM,
                                        MARKETSTATE, PARTNEREXID, LastExecSETTLEMENTCYCLE,
                                        LASTEXECPOSTTRADEVENUETYPE, PRICELEVELPOSITION,
                                        PREVREFERENCEID, EXPIRYTIMESTAMP, matchType,
                                        lastExecutionRole, MDEntryID, PegOffset,
                                        haltReason, COMPARISONPRICE,ENTEREDPRICETYPE,
                                ISPEX, CLEARINGHANDLING, SubPartID
                                   FROM (
                                           SELECT  "ORDERID", "USERORDERID", "ORDERSIDE", "ORDERTYPE",
                                                    ORDERSTATUS, BOARDID, TIMEINFORCE, INSTRUMENTID,
                                                    REFERENCEID, PRICETYPE, PRICE, AVERAGEPRICE,
                                                    QUANTITY, MINIMUMFILL, DISCLOSEDQTY, REMAINQTY,
                                                    AON, PARTICIPANTID, ACCOUNTTYPE, ACCOUNTNO,
                                                    CLEARINGAGENCY, LASTINSTRESULT, LASTINSTMESSAGESEQUENCE,
                                                    LASTEXECUTIONID, NOTE,TIMESTAMP, QTYFILLED, MEID,
                                                    LASTINSTREJECTCODE, LASTEXECPRICE, LASTEXECQTY,
                                                    LASTINSTTYPE, LASTEXECUTIONCOUNTERPARTY, VISIBLEQTY,
                                                    STOPPRICE, LASTEXECCLEARINGAGENCY, LASTEXECACCOUNTNO,
                                                    LASTEXECCPCLEARINGAGENCY, MESSAGESEQUENCE,
                                                    LASTINSTUSERALIAS, BOOKTIMESTAMP, PARTICIPANTIDMM,
                                                    MARKETSTATE, PARTNEREXID, LastExecSETTLEMENTCYCLE,
                                                    LASTEXECPOSTTRADEVENUETYPE, PRICELEVELPOSITION,
                                                    PREVREFERENCEID, EXPIRYTIMESTAMP, matchType,
                                                    lastExecutionRole, MDEntryID, PegOffset,
                                                    haltReason, COMPARISONPRICE,ENTEREDPRICETYPE,ISPEX, CLEARINGHANDLING, SubPartID,
                                                    max(case when LastInstRejectCode = 'OK' then MessageSequence end) over (partition by OrderID) as maxseq,
                                                    count(*) over (partition by orderid, instrumentID) cnt
                                                FROM  tibex_Order
                                             )
                                    WHERE (
                                           (
                                            MessageSequence = maxseq AND 
                                            a.OrderStatus IN (
                                                              SELECT OrderStatus 
                                                                FROM  tibex_orderStatusEnum   
                                                               WHERE ShortDesc IN ( 'ORD_OPEN', 'ORD_EXPIRE', 'ORD_CANCEL', 'ORD_FILLED','ORD_CREATE','ORD_PENDAMD','ORD_PENDCAN')
                                                             )
                                           ) OR
                                           (cnt = 1 AND
                                             AND orderstatus in (
                                                                 SELECT  orderstatus
                                                                   FROM  tibex_orderStatusEnum
                                                                  WHERE ShortDesc = 'ORD_REJECT'
                                                                )
                                           )
                                          )
                                  ) A,  tibex_Participant b
                                 WHERE a.participantID = b.participantID ;
                      • 8. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                        NM
                        Hi,

                        Is this looks OK now.
                        CREATE OR REPLACE VIEW TIBEX_ORDERSBYQSIDVIEW_6
                        as 
                        ( SELECT  A."ORDERID", A."USERORDERID", A."ORDERSIDE", A."ORDERTYPE",
                                  A.ORDERSTATUS, A.BOARDID, A.TIMEINFORCE, A.INSTRUMENTID,
                                  A.REFERENCEID, A.PRICETYPE, A.PRICE, A.AVERAGEPRICE,
                                  A.QUANTITY, A.MINIMUMFILL, A.DISCLOSEDQTY, A.REMAINQTY,
                                  A.AON, A.PARTICIPANTID, A.ACCOUNTTYPE, A.ACCOUNTNO,
                                  A.CLEARINGAGENCY, A.LASTINSTRESULT, A.LASTINSTMESSAGESEQUENCE,
                                  A.LASTEXECUTIONID,a.NOTE,a.TIMESTAMP, A.QTYFILLED, A.MEID,
                                  A.LASTINSTREJECTCODE, A.LASTEXECPRICE, A.LASTEXECQTY,
                                  A.LASTINSTTYPE, A.LASTEXECUTIONCOUNTERPARTY, A.VISIBLEQTY,
                                  A.STOPPRICE, A.LASTEXECCLEARINGAGENCY, A.LASTEXECACCOUNTNO,
                                  A.LASTEXECCPCLEARINGAGENCY, A.MESSAGESEQUENCE,
                                  A.LASTINSTUSERALIAS, A.BOOKTIMESTAMP, A.PARTICIPANTIDMM,
                                  A.MARKETSTATE, A.PARTNEREXID, A.LastExecSETTLEMENTCYCLE,
                                  A.LASTEXECPOSTTRADEVENUETYPE, A.PRICELEVELPOSITION,
                                  A.PREVREFERENCEID, A.EXPIRYTIMESTAMP, A.matchType,
                                  A.lastExecutionRole, A.MDEntryID, A.PegOffset,
                                  A.haltReason,A.COMPARISONPRICE,A.ENTEREDPRICETYPE,
                                  A.ISPEX, A.CLEARINGHANDLING, A.SubPartID, B.qsid
                          FROM (
                                    SELECT  "ORDERID", "USERORDERID", "ORDERSIDE", "ORDERTYPE",
                                          ORDERSTATUS, BOARDID, TIMEINFORCE, INSTRUMENTID,
                                          REFERENCEID, PRICETYPE, PRICE, AVERAGEPRICE,
                                          QUANTITY, MINIMUMFILL, DISCLOSEDQTY, REMAINQTY,
                                          AON, PARTICIPANTID, ACCOUNTTYPE, ACCOUNTNO,
                                          CLEARINGAGENCY, LASTINSTRESULT, LASTINSTMESSAGESEQUENCE,
                                          LASTEXECUTIONID, NOTE,TIMESTAMP, QTYFILLED, MEID,
                                          LASTINSTREJECTCODE, LASTEXECPRICE, LASTEXECQTY,
                                          LASTINSTTYPE, LASTEXECUTIONCOUNTERPARTY, VISIBLEQTY,
                                          STOPPRICE, LASTEXECCLEARINGAGENCY, LASTEXECACCOUNTNO,
                                          LASTEXECCPCLEARINGAGENCY, MESSAGESEQUENCE,
                                          LASTINSTUSERALIAS, BOOKTIMESTAMP, PARTICIPANTIDMM,
                                          MARKETSTATE, PARTNEREXID, LastExecSETTLEMENTCYCLE,
                                          LASTEXECPOSTTRADEVENUETYPE, PRICELEVELPOSITION,
                                          PREVREFERENCEID, EXPIRYTIMESTAMP, matchType,
                                          lastExecutionRole, MDEntryID, PegOffset,
                                          haltReason, COMPARISONPRICE,ENTEREDPRICETYPE,
                                  ISPEX, CLEARINGHANDLING, SubPartID
                                     FROM (
                                             SELECT  "ORDERID", "USERORDERID", "ORDERSIDE", "ORDERTYPE",
                                                      ORDERSTATUS, BOARDID, TIMEINFORCE, INSTRUMENTID,
                                                      REFERENCEID, PRICETYPE, PRICE, AVERAGEPRICE,
                                                      QUANTITY, MINIMUMFILL, DISCLOSEDQTY, REMAINQTY,
                                                      AON, PARTICIPANTID, ACCOUNTTYPE, ACCOUNTNO,
                                                      CLEARINGAGENCY, LASTINSTRESULT, LASTINSTMESSAGESEQUENCE,
                                                      LASTEXECUTIONID, NOTE,TIMESTAMP, QTYFILLED, MEID,
                                                      LASTINSTREJECTCODE, LASTEXECPRICE, LASTEXECQTY,
                                                      LASTINSTTYPE, LASTEXECUTIONCOUNTERPARTY, VISIBLEQTY,
                                                      STOPPRICE, LASTEXECCLEARINGAGENCY, LASTEXECACCOUNTNO,
                                                      LASTEXECCPCLEARINGAGENCY, MESSAGESEQUENCE,
                                                      LASTINSTUSERALIAS, BOOKTIMESTAMP, PARTICIPANTIDMM,
                                                      MARKETSTATE, PARTNEREXID, LastExecSETTLEMENTCYCLE,
                                                      LASTEXECPOSTTRADEVENUETYPE, PRICELEVELPOSITION,
                                                      PREVREFERENCEID, EXPIRYTIMESTAMP, matchType,
                                                      lastExecutionRole, MDEntryID, PegOffset,
                                                      haltReason, COMPARISONPRICE,ENTEREDPRICETYPE,ISPEX, CLEARINGHANDLING, SubPartID,
                                                      max(case when LastInstRejectCode = 'OK' then MessageSequence end) over (partition by OrderID) as maxseq,
                                                      count(*) over (partition by orderid, instrumentID) cnt
                                                  FROM  tibex_Order
                                               )
                                      WHERE ((
                                              MessageSequence = maxseq AND 
                                              OrderStatus IN (
                                                                SELECT OrderStatus 
                                                                  FROM  tibex_orderStatusEnum   
                                                                 WHERE ShortDesc IN ( 'ORD_OPEN', 'ORD_EXPIRE', 'ORD_CANCEL', 'ORD_FILLED','ORD_CREATE','ORD_PENDAMD','ORD_PENDCAN')
                                                               )
                                             ) OR
                                             (cnt = 1 AND orderstatus in (
                                                                   SELECT  orderstatus
                                                                     FROM  tibex_orderStatusEnum
                                                                    WHERE ShortDesc = 'ORD_REJECT'
                                                                  )
                                             )
                                            )
                                    ) A,  tibex_Participant b
                                   WHERE a.participantID = b.participantID) ;
                        Edited by: NM on 04-Apr-2012 05:05
                        • 9. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                          NM
                          Hi,

                          I tried your modified query.

                          Existing Query Time
                          trd_pre_eod@SURV1>
                          
                          4982429 rows selected.
                          
                          Elapsed: 00:48:58.43
                          
                          Execution Plan
                          ----------------------------------------------------------
                          Plan hash value: 3695525072
                          
                          ----------------------------------------------------------------------------------------------------------------------
                          | Id  | Operation                          | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                          ----------------------------------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT                   |                         |     3 |  2433 |       |   782K  (1)| 01:12:26 |
                          |   1 |  VIEW                              | TIBEX_ORDERSBYQSIDVIEW  |     3 |  2433 |       |   782K  (1)| 01:12:26 |
                          |   2 |   UNION-ALL                        |                         |       |       |       |            |          |
                          |*  3 |    FILTER                          |                         |       |       |       |            |          |
                          |   4 |     HASH GROUP BY                  |                         |     2 |   678 |       | 60272   (5)| 00:05:35 |
                          |*  5 |      HASH JOIN                     |                         |   883K|   285M|    50M| 60197   (5)| 00:05:35 |
                          |*  6 |       TABLE ACCESS FULL            | TIBEX_ORDER             |  1089K|    38M|       | 27547   (6)| 00:02:33 |
                          |*  7 |       HASH JOIN                    |                         |   988K|   284M|       | 27323   (5)| 00:02:32 |
                          |   8 |        NESTED LOOPS                |                         |     7 |   350 |       |     3   (0)| 00:00:01 |
                          |   9 |         TABLE ACCESS BY INDEX ROWID| TIBEX_PARTICIPANT       |     1 |    24 |       |     1   (0)| 00:00:01 |
                          |* 10 |          INDEX UNIQUE SCAN         | XPKTIBEX_PARTICIPANT    |     1 |       |       |     0   (0)| 00:00:01 |
                          |* 11 |         TABLE ACCESS FULL          | TIBEX_ORDERSTATUSENUM   |     7 |   182 |       |     2   (0)| 00:00:01 |
                          |* 12 |        TABLE ACCESS FULL           | TIBEX_ORDER             |  1271K|   305M|       | 27307   (5)| 00:02:32 |
                          |* 13 |    FILTER                          |                         |       |       |       |            |          |
                          |* 14 |     HASH JOIN                      |                         |   141K|    37M|       | 27323   (5)| 00:02:32 |
                          |  15 |      TABLE ACCESS BY INDEX ROWID   | TIBEX_ORDERSTATUSENUM   |     1 |    14 |       |     2   (0)| 00:00:01 |
                          |* 16 |       INDEX RANGE SCAN             | TIBEX_ORDERSTAT_ID_DESC |     1 |       |       |     1   (0)| 00:00:01 |
                          |  17 |      NESTED LOOPS                  |                         |  1271K|   320M|       | 27309   (5)| 00:02:32 |
                          |  18 |       TABLE ACCESS BY INDEX ROWID  | TIBEX_PARTICIPANT       |     1 |    12 |       |     1   (0)| 00:00:01 |
                          |* 19 |        INDEX UNIQUE SCAN           | XPKTIBEX_PARTICIPANT    |     1 |       |       |     0   (0)| 00:00:01 |
                          |* 20 |       TABLE ACCESS FULL            | TIBEX_ORDER             |  1271K|   305M|       | 27308   (5)| 00:02:32 |
                          |  21 |     SORT AGGREGATE                 |                         |     1 |    27 |       |            |          |
                          |* 22 |      TABLE ACCESS BY INDEX ROWID   | TIBEX_ORDER             |     1 |    27 |       |     5   (0)| 00:00:01 |
                          |* 23 |       INDEX RANGE SCAN             | IX_ORDERBOOK            |     2 |       |       |     3   (0)| 00:00:01 |
                          ----------------------------------------------------------------------------------------------------------------------
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             3 - filter("A"."MESSAGESEQUENCE"=MAX("C"."MESSAGESEQUENCE"))
                             5 - access("A"."ORDERID"="C"."ORDERID")
                             6 - filter("LASTINSTREJECTCODE"='OK')
                             7 - access("A"."PARTICIPANTID"="B"."PARTICIPANTID" AND "A"."ORDERSTATUS"="ORDERSTATUS")
                            10 - access("B"."PARTICIPANTID"='NITE')
                            11 - filter("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE' OR "SHORTDESC"='ORD_EXPIRE' OR
                                        "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR "SHORTDESC"='ORD_PENDAMD' OR "SHORTDESC"='ORD_PENDCA
                          N')
                          
                            12 - filter("A"."PARTICIPANTID"='NITE')
                            13 - filter( (SELECT COUNT(*) FROM TRD_PRE_EOD."TIBEX_ORDER" "C" WHERE "C"."ORDERID"=:B1 AND
                                        "C"."INSTRUMENTID"=:B2)=1)
                            14 - access("ORDERSTATUS"="ORDERSTATUS")
                            16 - access("SHORTDESC"='ORD_REJECT')
                            19 - access("B"."PARTICIPANTID"='NITE')
                            20 - filter("A"."PARTICIPANTID"='NITE')
                            22 - filter("C"."INSTRUMENTID"=:B1)
                            23 - access("C"."ORDERID"=:B1)
                          
                          
                          Statistics
                          ----------------------------------------------------------
                                10743  recursive calls
                                    0  db block gets
                              1657781  consistent gets
                              2995627  physical reads
                                    0  redo size
                            984094382  bytes sent via SQL*Net to client
                               548588  bytes received via SQL*Net from client
                                49826  SQL*Net roundtrips to/from client
                                    0  sorts (memory)
                                    0  sorts (disk)
                              4982429  rows processed
                          
                          -------------------------------------------------------------------------------------------------------------------------------------------
                          Modified Query
                          trd_pre_eod@SURV1>
                          
                          4982429 rows selected.
                          
                          Elapsed: 00:23:22.07
                          
                          Execution Plan
                          ----------------------------------------------------------
                          Plan hash value: 2209805969
                          
                          ------------------------------------------------------------------------------------------------------------------
                          | Id  | Operation                     | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                          ------------------------------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT              |                          |  7628 |  6190K|       |   729K  (1)| 01:07:33 |
                          |*  1 |  FILTER                       |                          |       |       |       |            |          |
                          |   2 |   NESTED LOOPS                |                          |    15M|    11G|       |   729K  (1)| 01:07:33 |
                          |   3 |    TABLE ACCESS BY INDEX ROWID| TIBEX_PARTICIPANT        |     1 |    12 |       |     1   (0)| 00:00:01 |
                          |*  4 |     INDEX UNIQUE SCAN         | XPKTIBEX_PARTICIPANT     |     1 |       |       |     0   (0)| 00:00:01 |
                          |*  5 |    VIEW                       |                          |    15M|    11G|       |   729K  (1)| 01:07:33 |
                          |   6 |     WINDOW SORT               |                          |    15M|  3666M|  5675M|   729K  (1)| 01:07:33 |
                          |   7 |      TABLE ACCESS FULL        | TIBEX_ORDER              |    15M|  3666M|       | 28322   (9)| 00:02:38 |
                          |*  8 |   TABLE ACCESS BY INDEX ROWID | TIBEX_ORDERSTATUSENUM    |     1 |    14 |       |     1   (0)| 00:00:01 |
                          |*  9 |    INDEX UNIQUE SCAN          | XPKTIBEX_ORDERSTATUSENUM |     1 |       |       |     0   (0)| 00:00:01 |
                          |* 10 |   TABLE ACCESS BY INDEX ROWID | TIBEX_ORDERSTATUSENUM    |     1 |    14 |       |     1   (0)| 00:00:01 |
                          |* 11 |    INDEX UNIQUE SCAN          | XPKTIBEX_ORDERSTATUSENUM |     1 |       |       |     0   (0)| 00:00:01 |
                          ------------------------------------------------------------------------------------------------------------------
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             1 - filter("MESSAGESEQUENCE"="MAXSEQ" AND  EXISTS (SELECT 0 FROM TRD_PRE_EOD."TIBEX_ORDERSTATUSENUM"
                                        "TIBEX_ORDERSTATUSENUM" WHERE "ORDERSTATUS"=:B1 AND ("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE'
                                        OR "SHORTDESC"='ORD_EXPIRE' OR "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR
                                        "SHORTDESC"='ORD_PENDAMD' OR "SHORTDESC"='ORD_PENDCAN')) OR "CNT"=1 AND  EXISTS (SELECT 0 FROM
                                        TRD_PRE_EOD."TIBEX_ORDERSTATUSENUM" "TIBEX_ORDERSTATUSENUM" WHERE "ORDERSTATUS"=:B2 AND
                                        "SHORTDESC"='ORD_REJECT'))
                             4 - access("B"."PARTICIPANTID"='NITE')
                             5 - filter("PARTICIPANTID"='NITE')
                             8 - filter("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE' OR "SHORTDESC"='ORD_EXPIRE' OR
                                        "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR "SHORTDESC"='ORD_PENDAMD' OR
                                        "SHORTDESC"='ORD_PENDCAN')
                             9 - access("ORDERSTATUS"=:B1)
                            10 - filter("SHORTDESC"='ORD_REJECT')
                            11 - access("ORDERSTATUS"=:B1)
                          
                          
                          Statistics
                          ----------------------------------------------------------
                                 4904  recursive calls
                                  131  db block gets
                               552360  consistent gets
                              1761483  physical reads
                                    0  redo size
                            793879909  bytes sent via SQL*Net to client
                               548587  bytes received via SQL*Net from client
                                49826  SQL*Net roundtrips to/from client
                                    0  sorts (memory)
                                    1  sorts (disk)
                              4982429  rows processed
                          
                          trd_pre_eod@SURV1> trd_pre_eod@SURV1> trd_pre_eod@SURV1>
                          
                          
                          select *
                          from
                           TIBEX_ORDERSBYQSIDVIEW_6 where PARTICIPANTID='NITE'
                          
                          
                          call     count       cpu    elapsed       disk      query    current        rows
                          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                          Parse        1      0.01       0.01          0          0          0           0
                          Execute      1      0.00       0.00          0          0          0           0
                          Fetch    49826    310.06    1333.10    1761483     552358        131     4982429
                          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                          total    49828    310.07    1333.12    1761483     552358        131     4982429
                          
                          Misses in library cache during parse: 1
                          Optimizer mode: ALL_ROWS
                          Parsing user id: 145
                          Number of plan statistics captured: 1
                          
                          Rows (1st) Rows (avg) Rows (max)  Row Source Operation
                          ---------- ---------- ----------  ---------------------------------------------------
                             4982429    4982429    4982429  FILTER  (cr=552358 pr=1761483 pw=1209145 time=1300694739 us)
                            10177511   10177511   10177511   NESTED LOOPS  (cr=552346 pr=1761481 pw=1209145 time=1321894747 us cost=729847 size=12676546008 card=15254568)
                                   1          1          1    TABLE ACCESS BY INDEX ROWID TIBEX_PARTICIPANT (cr=2 pr=0 pw=0 time=86 us cost=1 size=12 card=1)
                                   1          1          1     INDEX UNIQUE SCAN XPKTIBEX_PARTICIPANT (cr=1 pr=0 pw=0 time=64 us cost=0 size=0 card=1)(object id 951351)
                            10177511   10177511   10177511    VIEW  (cr=552344 pr=1761481 pw=1209145 time=1317882940 us cost=729846 size=12493491192 card=15254568)
                            15254568   15254568   15254568     WINDOW SORT (cr=552344 pr=1761481 pw=1209145 time=1341599446 us cost=729846 size=3844151136 card=15254568)
                            15254568   15254568   15254568      TABLE ACCESS FULL TIBEX_ORDER (cr=552344 pr=552336 pw=0 time=10426663 us cost=28322 size=3844151136 card=15254568)
                                   3          3          3   TABLE ACCESS BY INDEX ROWID TIBEX_ORDERSTATUSENUM (cr=6 pr=2 pw=0 time=22071 us cost=1 size=14 card=1)
                                   3          3          3    INDEX UNIQUE SCAN XPKTIBEX_ORDERSTATUSENUM (cr=3 pr=1 pw=0 time=21917 us cost=0 size=0 card=1)(object id 951341)
                                   1          1          1   TABLE ACCESS BY INDEX ROWID TIBEX_ORDERSTATUSENUM (cr=6 pr=0 pw=0 time=100 us cost=1 size=14 card=1)
                                   3          3          3    INDEX UNIQUE SCAN XPKTIBEX_ORDERSTATUSENUM (cr=3 pr=0 pw=0 time=61 us cost=0 size=0 card=1)(object id 951341)
                          
                          
                          Elapsed times include waiting on following events:
                            Event waited on                             Times   Max. Wait  Total Waited
                            ----------------------------------------   Waited  ----------  ------------
                            SQL*Net message to client                   49826        0.00          0.04
                            direct path read                               26        0.10          0.24
                            direct path write temp                      24837        3.08        617.29
                            direct path read temp                       95634        4.32        500.85
                            db file sequential read                         2        0.02          0.02
                            SQL*Net message from client                 49826        0.02         64.43
                            SQL*Net more data to client                 56989        0.00          1.05
                          ********************************************************************************
                          Edited by: NM on 04-Apr-2012 05:35
                          • 10. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                            user503699
                            NM wrote:
                            Hi,

                            I tried your modified query.
                            And ??
                            • 11. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                              NM
                              Hi,

                              I tried your modified query but is query response time quite high.

                              Existing Query Time
                              trd_pre_eod@SURV1>
                               
                              4982429 rows selected.
                               
                              Elapsed: 00:48:58.43
                               
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 3695525072
                               
                              ----------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                          | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                              ----------------------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT                   |                         |     3 |  2433 |       |   782K  (1)| 01:12:26 |
                              |   1 |  VIEW                              | TIBEX_ORDERSBYQSIDVIEW  |     3 |  2433 |       |   782K  (1)| 01:12:26 |
                              |   2 |   UNION-ALL                        |                         |       |       |       |            |          |
                              |*  3 |    FILTER                          |                         |       |       |       |            |          |
                              |   4 |     HASH GROUP BY                  |                         |     2 |   678 |       | 60272   (5)| 00:05:35 |
                              |*  5 |      HASH JOIN                     |                         |   883K|   285M|    50M| 60197   (5)| 00:05:35 |
                              |*  6 |       TABLE ACCESS FULL            | TIBEX_ORDER             |  1089K|    38M|       | 27547   (6)| 00:02:33 |
                              |*  7 |       HASH JOIN                    |                         |   988K|   284M|       | 27323   (5)| 00:02:32 |
                              |   8 |        NESTED LOOPS                |                         |     7 |   350 |       |     3   (0)| 00:00:01 |
                              |   9 |         TABLE ACCESS BY INDEX ROWID| TIBEX_PARTICIPANT       |     1 |    24 |       |     1   (0)| 00:00:01 |
                              |* 10 |          INDEX UNIQUE SCAN         | XPKTIBEX_PARTICIPANT    |     1 |       |       |     0   (0)| 00:00:01 |
                              |* 11 |         TABLE ACCESS FULL          | TIBEX_ORDERSTATUSENUM   |     7 |   182 |       |     2   (0)| 00:00:01 |
                              |* 12 |        TABLE ACCESS FULL           | TIBEX_ORDER             |  1271K|   305M|       | 27307   (5)| 00:02:32 |
                              |* 13 |    FILTER                          |                         |       |       |       |            |          |
                              |* 14 |     HASH JOIN                      |                         |   141K|    37M|       | 27323   (5)| 00:02:32 |
                              |  15 |      TABLE ACCESS BY INDEX ROWID   | TIBEX_ORDERSTATUSENUM   |     1 |    14 |       |     2   (0)| 00:00:01 |
                              |* 16 |       INDEX RANGE SCAN             | TIBEX_ORDERSTAT_ID_DESC |     1 |       |       |     1   (0)| 00:00:01 |
                              |  17 |      NESTED LOOPS                  |                         |  1271K|   320M|       | 27309   (5)| 00:02:32 |
                              |  18 |       TABLE ACCESS BY INDEX ROWID  | TIBEX_PARTICIPANT       |     1 |    12 |       |     1   (0)| 00:00:01 |
                              |* 19 |        INDEX UNIQUE SCAN           | XPKTIBEX_PARTICIPANT    |     1 |       |       |     0   (0)| 00:00:01 |
                              |* 20 |       TABLE ACCESS FULL            | TIBEX_ORDER             |  1271K|   305M|       | 27308   (5)| 00:02:32 |
                              |  21 |     SORT AGGREGATE                 |                         |     1 |    27 |       |            |          |
                              |* 22 |      TABLE ACCESS BY INDEX ROWID   | TIBEX_ORDER             |     1 |    27 |       |     5   (0)| 00:00:01 |
                              |* 23 |       INDEX RANGE SCAN             | IX_ORDERBOOK            |     2 |       |       |     3   (0)| 00:00:01 |
                              ----------------------------------------------------------------------------------------------------------------------
                               
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                               
                                 3 - filter("A"."MESSAGESEQUENCE"=MAX("C"."MESSAGESEQUENCE"))
                                 5 - access("A"."ORDERID"="C"."ORDERID")
                                 6 - filter("LASTINSTREJECTCODE"='OK')
                                 7 - access("A"."PARTICIPANTID"="B"."PARTICIPANTID" AND "A"."ORDERSTATUS"="ORDERSTATUS")
                                10 - access("B"."PARTICIPANTID"='NITE')
                                11 - filter("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE' OR "SHORTDESC"='ORD_EXPIRE' OR
                                            "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR "SHORTDESC"='ORD_PENDAMD' OR "SHORTDESC"='ORD_PENDCA
                              N')
                               
                                12 - filter("A"."PARTICIPANTID"='NITE')
                                13 - filter( (SELECT COUNT(*) FROM TRD_PRE_EOD."TIBEX_ORDER" "C" WHERE "C"."ORDERID"=:B1 AND
                                            "C"."INSTRUMENTID"=:B2)=1)
                                14 - access("ORDERSTATUS"="ORDERSTATUS")
                                16 - access("SHORTDESC"='ORD_REJECT')
                                19 - access("B"."PARTICIPANTID"='NITE')
                                20 - filter("A"."PARTICIPANTID"='NITE')
                                22 - filter("C"."INSTRUMENTID"=:B1)
                                23 - access("C"."ORDERID"=:B1)
                               
                               
                              Statistics
                              ----------------------------------------------------------
                                    10743  recursive calls
                                        0  db block gets
                                  1657781  consistent gets
                                  2995627  physical reads
                                        0  redo size
                                984094382  bytes sent via SQL*Net to client
                                   548588  bytes received via SQL*Net from client
                                    49826  SQL*Net roundtrips to/from client
                                        0  sorts (memory)
                                        0  sorts (disk)
                                  4982429  rows processed
                               
                              -------------------------------------------------------------------------------------------------------------------------------------------
                              Modified Query
                              trd_pre_eod@SURV1>
                               
                              4982429 rows selected.
                               
                              Elapsed: 00:23:22.07
                               
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 2209805969
                               
                              ------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                     | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                              ------------------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT              |                          |  7628 |  6190K|       |   729K  (1)| 01:07:33 |
                              |*  1 |  FILTER                       |                          |       |       |       |            |          |
                              |   2 |   NESTED LOOPS                |                          |    15M|    11G|       |   729K  (1)| 01:07:33 |
                              |   3 |    TABLE ACCESS BY INDEX ROWID| TIBEX_PARTICIPANT        |     1 |    12 |       |     1   (0)| 00:00:01 |
                              |*  4 |     INDEX UNIQUE SCAN         | XPKTIBEX_PARTICIPANT     |     1 |       |       |     0   (0)| 00:00:01 |
                              |*  5 |    VIEW                       |                          |    15M|    11G|       |   729K  (1)| 01:07:33 |
                              |   6 |     WINDOW SORT               |                          |    15M|  3666M|  5675M|   729K  (1)| 01:07:33 |
                              |   7 |      TABLE ACCESS FULL        | TIBEX_ORDER              |    15M|  3666M|       | 28322   (9)| 00:02:38 |
                              |*  8 |   TABLE ACCESS BY INDEX ROWID | TIBEX_ORDERSTATUSENUM    |     1 |    14 |       |     1   (0)| 00:00:01 |
                              |*  9 |    INDEX UNIQUE SCAN          | XPKTIBEX_ORDERSTATUSENUM |     1 |       |       |     0   (0)| 00:00:01 |
                              |* 10 |   TABLE ACCESS BY INDEX ROWID | TIBEX_ORDERSTATUSENUM    |     1 |    14 |       |     1   (0)| 00:00:01 |
                              |* 11 |    INDEX UNIQUE SCAN          | XPKTIBEX_ORDERSTATUSENUM |     1 |       |       |     0   (0)| 00:00:01 |
                              ------------------------------------------------------------------------------------------------------------------
                               
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                               
                                 1 - filter("MESSAGESEQUENCE"="MAXSEQ" AND  EXISTS (SELECT 0 FROM TRD_PRE_EOD."TIBEX_ORDERSTATUSENUM"
                                            "TIBEX_ORDERSTATUSENUM" WHERE "ORDERSTATUS"=:B1 AND ("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE'
                                            OR "SHORTDESC"='ORD_EXPIRE' OR "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR
                                            "SHORTDESC"='ORD_PENDAMD' OR "SHORTDESC"='ORD_PENDCAN')) OR "CNT"=1 AND  EXISTS (SELECT 0 FROM
                                            TRD_PRE_EOD."TIBEX_ORDERSTATUSENUM" "TIBEX_ORDERSTATUSENUM" WHERE "ORDERSTATUS"=:B2 AND
                                            "SHORTDESC"='ORD_REJECT'))
                                 4 - access("B"."PARTICIPANTID"='NITE')
                                 5 - filter("PARTICIPANTID"='NITE')
                                 8 - filter("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE' OR "SHORTDESC"='ORD_EXPIRE' OR
                                            "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR "SHORTDESC"='ORD_PENDAMD' OR
                                            "SHORTDESC"='ORD_PENDCAN')
                                 9 - access("ORDERSTATUS"=:B1)
                                10 - filter("SHORTDESC"='ORD_REJECT')
                                11 - access("ORDERSTATUS"=:B1)
                               
                               
                              Statistics
                              ----------------------------------------------------------
                                     4904  recursive calls
                                      131  db block gets
                                   552360  consistent gets
                                  1761483  physical reads
                                        0  redo size
                                793879909  bytes sent via SQL*Net to client
                                   548587  bytes received via SQL*Net from client
                                    49826  SQL*Net roundtrips to/from client
                                        0  sorts (memory)
                                        1  sorts (disk)
                                  4982429  rows processed
                               
                              trd_pre_eod@SURV1> trd_pre_eod@SURV1> trd_pre_eod@SURV1>
                               
                               
                              select *
                              from
                               TIBEX_ORDERSBYQSIDVIEW_6 where PARTICIPANTID='NITE'
                               
                               
                              call     count       cpu    elapsed       disk      query    current        rows
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              Parse        1      0.01       0.01          0          0          0           0
                              Execute      1      0.00       0.00          0          0          0           0
                              Fetch    49826    310.06    1333.10    1761483     552358        131     4982429
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              total    49828    310.07    1333.12    1761483     552358        131     4982429
                               
                              Misses in library cache during parse: 1
                              Optimizer mode: ALL_ROWS
                              Parsing user id: 145
                              Number of plan statistics captured: 1
                               
                              Rows (1st) Rows (avg) Rows (max)  Row Source Operation
                              ---------- ---------- ----------  ---------------------------------------------------
                                 4982429    4982429    4982429  FILTER  (cr=552358 pr=1761483 pw=1209145 time=1300694739 us)
                                10177511   10177511   10177511   NESTED LOOPS  (cr=552346 pr=1761481 pw=1209145 time=1321894747 us cost=729847 size=12676546008 card=15254568)
                                       1          1          1    TABLE ACCESS BY INDEX ROWID TIBEX_PARTICIPANT (cr=2 pr=0 pw=0 time=86 us cost=1 size=12 card=1)
                                       1          1          1     INDEX UNIQUE SCAN XPKTIBEX_PARTICIPANT (cr=1 pr=0 pw=0 time=64 us cost=0 size=0 card=1)(object id 951351)
                                10177511   10177511   10177511    VIEW  (cr=552344 pr=1761481 pw=1209145 time=1317882940 us cost=729846 size=12493491192 card=15254568)
                                15254568   15254568   15254568     WINDOW SORT (cr=552344 pr=1761481 pw=1209145 time=1341599446 us cost=729846 size=3844151136 card=15254568)
                                15254568   15254568   15254568      TABLE ACCESS FULL TIBEX_ORDER (cr=552344 pr=552336 pw=0 time=10426663 us cost=28322 size=3844151136 card=15254568)
                                       3          3          3   TABLE ACCESS BY INDEX ROWID TIBEX_ORDERSTATUSENUM (cr=6 pr=2 pw=0 time=22071 us cost=1 size=14 card=1)
                                       3          3          3    INDEX UNIQUE SCAN XPKTIBEX_ORDERSTATUSENUM (cr=3 pr=1 pw=0 time=21917 us cost=0 size=0 card=1)(object id 951341)
                                       1          1          1   TABLE ACCESS BY INDEX ROWID TIBEX_ORDERSTATUSENUM (cr=6 pr=0 pw=0 time=100 us cost=1 size=14 card=1)
                                       3          3          3    INDEX UNIQUE SCAN XPKTIBEX_ORDERSTATUSENUM (cr=3 pr=0 pw=0 time=61 us cost=0 size=0 card=1)(object id 951341)
                               
                               
                              Elapsed times include waiting on following events:
                                Event waited on                             Times   Max. Wait  Total Waited
                                ----------------------------------------   Waited  ----------  ------------
                                SQL*Net message to client                   49826        0.00          0.04
                                direct path read                               26        0.10          0.24
                                direct path write temp                      24837        3.08        617.29
                                direct path read temp                       95634        4.32        500.85
                                db file sequential read                         2        0.02          0.02
                                SQL*Net message from client                 49826        0.02         64.43
                                SQL*Net more data to client                 56989        0.00          1.05
                              ********************************************************************************
                              Regards
                              NM

                              Edited by: NM on 04-Apr-2012 06:29
                              • 12. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                                user503699
                                NM wrote:
                                Hi,

                                I tried your modified query but is query response time quite high.
                                The stats you posted appear to suggest otherwise. "Existing query" time is about 49 minutes and "modified Query" time is just under 23 minutes.
                                So which one are you talking about?
                                • 13. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                                  NM
                                  Hi,

                                  Is it possible to reduce the response time under 90 seconds by any other method the reasons is Our application fails if the response time >90 seconds.

                                  I tried the both the queries on the production Box after EOD:The existing query comes out in 19 min and modified takes 22 min

                                  Existing Query response time
                                  trd_pre_eod@TRADE1>  select * from TIBEX_ORDERSBYQSIDVIEW where PARTICIPANTID='NITE';
                                  
                                  exit;
                                  
                                  4982429 rows selected.
                                  
                                  Elapsed: 00:19:27.39
                                  
                                  Execution Plan
                                  ----------------------------------------------------------
                                  Plan hash value: 3695525072
                                  ----------------------------------------------------------------------------------------------------------------------
                                  | Id  | Operation                          | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                  ----------------------------------------------------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT                   |                         |    18 | 14598 |       |  6760K  (1)| 08:11:29 |
                                  |   1 |  VIEW                              | TIBEX_ORDERSBYQSIDVIEW  |    18 | 14598 |       |  6760K  (1)| 08:11:29 |
                                  |   2 |   UNION-ALL                        |                         |       |       |       |            |          |
                                  |*  3 |    FILTER                          |                         |       |       |       |            |          |
                                  |   4 |     HASH GROUP BY                  |                         |    17 |  5763 |       | 86635   (7)| 00:06:18 |
                                  |*  5 |      HASH JOIN                     |                         |    15M|  4870M|   717M| 85087   (6)| 00:06:12 |
                                  |*  6 |       TABLE ACCESS FULL            | TIBEX_ORDER             |    15M|   541M|       | 21115   (9)| 00:01:33 |
                                  |*  7 |       HASH JOIN                    |                         |  7771K|  2238M|       | 21376  (10)| 00:01:34 |
                                  |   8 |        NESTED LOOPS                |                         |     7 |   350 |       |     3   (0)| 00:00:01 |
                                  |   9 |         TABLE ACCESS BY INDEX ROWID| TIBEX_PARTICIPANT       |     1 |    24 |       |     1   (0)| 00:00:01 |
                                  |* 10 |          INDEX UNIQUE SCAN         | XPKTIBEX_PARTICIPANT    |     1 |       |       |     0   (0)| 00:00:01 |
                                  |* 11 |         TABLE ACCESS FULL          | TIBEX_ORDERSTATUSENUM   |     7 |   182 |       |     2   (0)| 00:00:01 |
                                  |* 12 |        TABLE ACCESS FULL           | TIBEX_ORDER             |  9992K|  2401M|       | 21277  (10)| 00:01:33 |
                                  |* 13 |    FILTER                          |                         |       |       |       |            |          |
                                  |* 14 |     HASH JOIN                      |                         |  1110K|   294M|       | 21377  (10)| 00:01:34 |
                                  |  15 |      TABLE ACCESS BY INDEX ROWID   | TIBEX_ORDERSTATUSENUM   |     1 |    14 |       |     2   (0)| 00:00:01 |
                                  |* 16 |       INDEX RANGE SCAN             | TIBEX_ORDERSTAT_ID_DESC |     1 |       |       |     1   (0)| 00:00:01 |
                                  |  17 |      NESTED LOOPS                  |                         |  9992K|  2515M|       | 21279  (10)| 00:01:33 |
                                  |  18 |       TABLE ACCESS BY INDEX ROWID  | TIBEX_PARTICIPANT       |     1 |    12 |       |     1   (0)| 00:00:01 |
                                  |* 19 |        INDEX UNIQUE SCAN           | XPKTIBEX_PARTICIPANT    |     1 |       |       |     0   (0)| 00:00:01 |
                                  |* 20 |       TABLE ACCESS FULL            | TIBEX_ORDER             |  9992K|  2401M|       | 21278  (10)| 00:01:33 |
                                  |  21 |     SORT AGGREGATE                 |                         |     1 |    27 |       |            |          |
                                  |* 22 |      TABLE ACCESS BY INDEX ROWID   | TIBEX_ORDER             |     1 |    27 |       |     6   (0)| 00:00:01 |
                                  |* 23 |       INDEX RANGE SCAN             | IX_ORDERBOOK            |     2 |       |       |     4   (0)| 00:00:01 |
                                  ----------------------------------------------------------------------------------------------------------------------
                                  Predicate Information (identified by operation id):
                                  ---------------------------------------------------
                                  
                                     3 - filter("A"."MESSAGESEQUENCE"=MAX("C"."MESSAGESEQUENCE"))
                                     5 - access("A"."ORDERID"="C"."ORDERID")
                                     6 - filter("LASTINSTREJECTCODE"='OK')
                                     7 - access("A"."PARTICIPANTID"="B"."PARTICIPANTID" AND "A"."ORDERSTATUS"="ORDERSTATUS")
                                    10 - access("B"."PARTICIPANTID"='NITE')
                                    11 - filter("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE' OR "SHORTDESC"='ORD_EXPIRE' OR
                                                "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR "SHORTDESC"='ORD_PENDAMD' OR "SHORTDESC"='O
                                  RD_PENDCAN')
                                  
                                    12 - filter("A"."PARTICIPANTID"='NITE')
                                    13 - filter( (SELECT COUNT(*) FROM TRD_PRE_EOD."TIBEX_ORDER" "C" WHERE "C"."ORDERID"=:B1 AND
                                                "C"."INSTRUMENTID"=:B2)=1)
                                    14 - access("ORDERSTATUS"="ORDERSTATUS")
                                    16 - access("SHORTDESC"='ORD_REJECT')
                                    19 - access("B"."PARTICIPANTID"='NITE')
                                    20 - filter("A"."PARTICIPANTID"='NITE')
                                    22 - filter("C"."INSTRUMENTID"=:B1)
                                    23 - access("C"."ORDERID"=:B1)
                                  
                                  
                                  Statistics
                                  ----------------------------------------------------------
                                         9961  recursive calls
                                            0  db block gets
                                      1658926  consistent gets
                                      2858629  physical reads
                                            0  redo size
                                    985330471  bytes sent via SQL*Net to client
                                       548588  bytes received via SQL*Net from client
                                        49826  SQL*Net roundtrips to/from client
                                            8  sorts (memory)
                                            0  sorts (disk)
                                      4982429  rows processed
                                  Modified Query
                                  trd_pre_eod@TRADE1> set autotrace traceonly arraysize 100
                                  trd_pre_eod@TRADE1> set timing on
                                  trd_pre_eod@TRADE1> select * from TIBEX_ORDERSBYQSIDVIEW_6 where PARTICIPANTID='NITE';
                                  4982429 rows selected.
                                  Elapsed: 00:22:59.31
                                  Execution Plan
                                  ----------------------------------------------------------
                                  Plan hash value: 2209805969
                                  ------------------------------------------------------------------------------------------------------------------
                                  | Id  | Operation                     | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |
                                  ------------------------------------------------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT              |                          |  7798 |  6328K|       |   733K  (1)| 00:53:19 |
                                  |*  1 |  FILTER                       |                          |       |       |       |            |          |
                                  |   2 |   NESTED LOOPS                |                          |    15M|    12G|       |   733K  (1)| 00:53:19 |
                                  |   3 |    TABLE ACCESS BY INDEX ROWID| TIBEX_PARTICIPANT        |     1 |    12 |       |     1   (0)| 00:00:01 |
                                  |*  4 |     INDEX UNIQUE SCAN         | XPKTIBEX_PARTICIPANT     |     1 |       |       |     0   (0)| 00:00:01 |
                                  |*  5 |    VIEW                       |                          |    15M|    11G|       |   733K  (1)| 00:53:19 |
                                  |   6 |     WINDOW SORT               |                          |    15M|  3747M|  5801M|   733K  (1)| 00:53:19 |
                                  |   7 |      TABLE ACCESS FULL        | TIBEX_ORDER              |    15M|  3747M|       | 21641  (11)| 00:01:35 |
                                  |*  8 |   TABLE ACCESS BY INDEX ROWID | TIBEX_ORDERSTATUSENUM    |     1 |    14 |       |     1   (0)| 00:00:01 |
                                  |*  9 |    INDEX UNIQUE SCAN          | XPKTIBEX_ORDERSTATUSENUM |     1 |       |       |     0   (0)| 00:00:01 |
                                  |* 10 |   TABLE ACCESS BY INDEX ROWID | TIBEX_ORDERSTATUSENUM    |     1 |    14 |       |     1   (0)| 00:00:01 |
                                  |* 11 |    INDEX UNIQUE SCAN          | XPKTIBEX_ORDERSTATUSENUM |     1 |       |       |     0   (0)| 00:00:01 |
                                  ------------------------------------------------------------------------------------------------------------------
                                  Predicate Information (identified by operation id):
                                  ---------------------------------------------------
                                     1 - filter("MESSAGESEQUENCE"="MAXSEQ" AND  EXISTS (SELECT 0 FROM TRD_PRE_EOD."TIBEX_ORDERSTATUSENUM"
                                                "TIBEX_ORDERSTATUSENUM" WHERE "ORDERSTATUS"=:B1 AND ("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE'
                                                OR "SHORTDESC"='ORD_EXPIRE' OR "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR
                                                "SHORTDESC"='ORD_PENDAMD' OR "SHORTDESC"='ORD_PENDCAN')) OR "CNT"=1 AND  EXISTS (SELECT 0 FROM
                                                TRD_PRE_EOD."TIBEX_ORDERSTATUSENUM" "TIBEX_ORDERSTATUSENUM" WHERE "ORDERSTATUS"=:B2 AND
                                                "SHORTDESC"='ORD_REJECT'))
                                     4 - access("B"."PARTICIPANTID"='NITE')
                                     5 - filter("PARTICIPANTID"='NITE')
                                     8 - filter("SHORTDESC"='ORD_CANCEL' OR "SHORTDESC"='ORD_CREATE' OR "SHORTDESC"='ORD_EXPIRE' OR
                                                "SHORTDESC"='ORD_FILLED' OR "SHORTDESC"='ORD_OPEN' OR "SHORTDESC"='ORD_PENDAMD' OR
                                                "SHORTDESC"='ORD_PENDCAN')
                                     9 - access("ORDERSTATUS"=:B1)
                                    10 - filter("SHORTDESC"='ORD_REJECT')
                                    11 - access("ORDERSTATUS"=:B1)
                                  Statistics
                                  ----------------------------------------------------------
                                         5126  recursive calls
                                          131  db block gets
                                       552874  consistent gets
                                      1761521  physical reads
                                            0  redo size
                                    793879909  bytes sent via SQL*Net to client
                                       548588  bytes received via SQL*Net from client
                                        49826  SQL*Net roundtrips to/from client
                                           55  sorts (memory)
                                            1  sorts (disk)
                                      4982429  rows processed
                                  Regards
                                  NM

                                  Edited by: NM on 04-Apr-2012 11:18
                                  • 14. Re: Query Tuning Suggestion when scanning 20 Millions records,retriving 100 rec
                                    user503699
                                    NM wrote:
                                    Hi,

                                    Is it possible to reduce the response time under 90 seconds by any other method the reasons is Our application fails if the response time >90 seconds.

                                    I tried the both the queries on the production Box after EOD:The existing query comes out in 19 min and modified takes 22 min
                                    Few points for you to consider:
                                    1) You started by saying you have a query that fetches only 25 rows that take more than 90 seconds. However, in your latest post, you are executing a query that is producing nearly 5 million rows. So which one is correct? If you want to fetching nearly 5 million records, you have a bigger problem to solve first i.e. what do you do with those many records ?
                                    2) When you measure time, make sure you note the time of subsequent executions and not just first execution (for both cases). The first execution will tend to have lots of recursive parse calls and physical I/Os that may disappear in subsequent executions.
                                    3) What is your PGA_AGGREGATE_TARGET size?
                                    4) You may want to change the PARTITION BY clause of analytical functions in the modified query to include PARTICIPANTID after existing column(s) and test to see if it makes any difference. At present, it appears your query is sorting all the rows of TIBEX_ORDER table before applying the filter. That appears to be causing large no. of sorts for the modified query and that appears to be consuming most of the execution time.
                                    1 2 3 Previous Next