1 2 3 Previous Next 32 Replies Latest reply: Aug 26, 2010 4:40 AM by NM Go to original post RSS
      • 30. Re: Query Takes more Time when i execute from the Instant client.
        NM
        Hi Charles,

        Thanks for the information.


        This job is created automatically at database creation time and is managed by the Scheduler. The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends."

        This has been disabled because it used run and gather the stats when the tables had zero records because of Truncate.We used have performance issue so we disabled this job.


        Outlines will lock an execute plan so that the same plan in used when the table contains 200 rows as is used when it contains 10,000,000 rows.  Should the same plan always be used?

        How effective this plan will be when the user run with 200 records or with 3 million records.Does the Cost will be the same for 200 records or with 3 million records.

        DBA_TAB_MODIFICATIONS

        If i run regularly the following process does this help in our ENV instead of Gathering Stats during production hours.

        begin
        dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
        end;

        Anyway optimizer look the stats infrom the DBA_TAB_MODIFICATIONS and stats present on the table.

        Kindly clarify me the below information.
          FINDING 2: 67% impact (12569 seconds)
        -------------------------------------
        Individual SQL statements responsible for significant user I/O wait were
        found.
        
           RECOMMENDATION 1: SQL Tuning, 50% benefit (9470 seconds)
              ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
                 "bts4adqvbs1u3".
                 RELEVANT OBJECT: SQL statement with SQL_ID bts4adqvbs1u3 and
                 PLAN_HASH 1203832759
                 SELECT  instrumentID, count(*) OpenOrders
                 FROM  tibex_orderBook
                 GROUP BY instrumentID
                 ORDER BY instrumentID
              RATIONALE: SQL statement with SQL_ID "bts4adqvbs1u3" was executed 4
                 times and had an average elapsed time of 2344 seconds.
              RATIONALE: Average time spent in User I/O wait events per execution was
                 2294 seconds.
        
           RECOMMENDATION 2: SQL Tuning, 27% benefit (5134 seconds)
              ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
                 "2yrr9gdghrdbs".
                 RELEVANT OBJECT: SQL statement with SQL_ID 2yrr9gdghrdbs
                 INSERT INTO tibex_Order(ORDERID,USERORDERID,ORDERSIDE,ORDERTYPE,ORDER
                 STATUS,BOARDID,TIMEINFORCE,INSTRUMENTID,REFERENCEID,PRICETYPE,PRICE,A
                 VERAGEPRICE,QUANTITY,MINIMUMFILL,DISCLOSEDQTY,REMAINQTY,AON,PARTICIPA
                 NTID,ACCOUNTTYPE,ACCOUNTNO,CLEARINGAGENCY,LASTINSTRESULT,LASTINSTMESS
                 AGESEQUENCE,LASTEXECUTIONID,NOTE,TIMESTAMP,QTYFILLED,MEID,LASTINSTREJ
                 ECTCODE,LASTEXECPRICE,LASTEXECQTY,LASTINSTTYPE,LASTEXECUTIONCOUNTERPA
                 RTY,VISIBLEQTY,STOPPRICE,LASTEXECCLEARINGAGENCY,LASTEXECACCOUNTNO,LAS
                 TEXECCPCLEARINGAGENCY,MESSAGESEQUENCE,LASTINSTUSERALIAS,BOOKTIMESTAMP
                 ,PARTICIPANTIDMM,MARKETSTATE,PARTNEREXID,LASTEXECSETTLEMENTCYCLE,LAST
                 EXECPOSTTRADEVENUETYPE,PRICELEVELPOSITION,PREVREFERENCEID,EXPIRYTIMES
                 TAMP,MATCHTYPE,LASTEXECUTIONROLE,MDENTRYID,PEGOFFSET,HALTREASON,LASTI
                 NSTFIXSEQUENCE,COMPARISONPRICE) VALUES (: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     ,:LASTINSTFIXSEQUENCE
                 ,:COMPARISONPRICE        )
              ACTION: Investigate the SQL statement with SQL_ID "2yrr9gdghrdbs" for
                 possible performance improvements.
                 RELEVANT OBJECT: SQL statement with SQL_ID 2yrr9gdghrdbs
                 INSERT INTO tibex_Order(ORDERID,USERORDERID,ORDERSIDE,ORDERTYPE,ORDER
                 STATUS,BOARDID,TIMEINFORCE,INSTRUMENTID,REFERENCEID,PRICETYPE,PRICE,A
                 VERAGEPRICE,QUANTITY,MINIMUMFILL,DISCLOSEDQTY,REMAINQTY,AON,PARTICIPA
                 NTID,ACCOUNTTYPE,ACCOUNTNO,CLEARINGAGENCY,LASTINSTRESULT,LASTINSTMESS
                 AGESEQUENCE,LASTEXECUTIONID,NOTE,TIMESTAMP,QTYFILLED,MEID,LASTINSTREJ
                 ECTCODE,LASTEXECPRICE,LASTEXECQTY,LASTINSTTYPE,LASTEXECUTIONCOUNTERPA
                 RTY,VISIBLEQTY,STOPPRICE,LASTEXECCLEARINGAGENCY,LASTEXECACCOUNTNO,LAS
                 TEXECCPCLEARINGAGENCY,MESSAGESEQUENCE,LASTINSTUSERALIAS,BOOKTIMESTAMP
                 ,PARTICIPANTIDMM,MARKETSTATE,PARTNEREXID,LASTEXECSETTLEMENTCYCLE,LAST
                 EXECPOSTTRADEVENUETYPE,PRICELEVELPOSITION,PREVREFERENCEID,EXPIRYTIMES
                 TAMP,MATCHTYPE,LASTEXECUTIONROLE,MDENTRYID,PEGOFFSET,HALTREASON,LASTI
                 NSTFIXSEQUENCE,COMPARISONPRICE) VALUES (: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     ,:LASTINSTFIXSEQUENCE
                 ,:COMPARISONPRICE        )
              RATIONALE: SQL statement with SQL_ID "2yrr9gdghrdbs" was executed
                 1341301 times and had an average elapsed time of 0.0038 seconds.
              RATIONALE: Waiting for event "enq: TX - index contention" in wait class
                 "Concurrency" accounted for 58% of the database time spent in
                 processing the SQL statement with SQL_ID "2yrr9gdghrdbs".
              RATIONALE: Waiting for event "db file sequential read" in wait class
                 "User I/O" accounted for 19% of the database time spent in processing
                 the SQL statement with SQL_ID "2yrr9gdghrdbs".
              RATIONALE: Waiting for event "buffer exterminate" in wait class "Other"
                 accounted for 7% of the database time spent in processing the SQL
                 statement with SQL_ID "2yrr9gdghrdbs".
              RATIONALE: Average time spent in User I/O wait events per execution was
                 0.00079 seconds.
        
           RECOMMENDATION 3: SQL Tuning, 8.3% benefit (1558 seconds)
              ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
                 "4faqv5m86gjn1".
                 RELEVANT OBJECT: SQL statement with SQL_ID 4faqv5m86gjn1
                 INSERT INTO tibex_FixSessionState(USERALIAS,FIXMESSAGETYPE,FROMFIXSEQ
                 UENCE,TOETSSEQUENCE,FROMETSSEQUENCE,TOFIXSEQUENCE,INSTRUMENTID,FROMET
                 STIMESTAMP,TIMESTAMP,FROMRESETCOUNT,SENDTYPE) VALUES (:USERALIAS
                 ,:FIXMESSAGETYPE         ,:FROMFIXSEQUENCE        ,:TOETSSEQUENCE
                 ,:FROMETSSEQUENCE        ,:TOFIXSEQUENCE        ,:INSTRUMENTID
                 ,:FROMETSTIMESTAMP        ,:TIMESTAMP        ,:FROMRESETCOUNT
                 ,:SENDTYPE     )
              RATIONALE: SQL statement with SQL_ID "4faqv5m86gjn1" was executed 528153
                 times and had an average elapsed time of 0.0034 seconds.
              RATIONALE: Average time spent in User I/O wait events per execution was
                 0.0025 seconds.
        Regards
        NM

        Edited by: NM on 18-Aug-2010 06:36
        • 31. Re: Query Takes more Time when i execute from the Instant client.
          NM
          Hi Charles,

          Today was using sqlt for problematic Query and i found the following information.
          GENERAL INFORMATION SECTION
          -------------------------------------------------------------------------------
          Tuning Task Name                  : s5339_trade1_prdfa001
          Tuning Task Owner                 : SAT_ETS
          Tuning Task ID                    : 19185
          Scope                             : COMPREHENSIVE
          Time Limit(seconds)               : 1800
          Completion Status                 : COMPLETED
          Started at                        : 08/25/2010 21:54:09
          Completed at                      : 08/25/2010 21:54:10
          
          -------------------------------------------------------------------------------
          Schema Name: SAT_ETS
          SQL ID     : 03fqwamsdv6pf
          SQL Text   : select mod(lastinstmessagesequence, :"SYS_B_0") LastInstIDSeqNo
                       from tibex_msgseqbyuseralias where useralias=:"SYS_B_1"
          
          -------------------------------------------------------------------------------
          ADDITIONAL INFORMATION SECTION
          -------------------------------------------------------------------------------
          - The optimizer could not merge the view at line ID 2 of the execution plan.
            The optimizer cannot merge a view that contains a set operator.
          
          -------------------------------------------------------------------------------
          EXPLAIN PLANS SECTION
          -------------------------------------------------------------------------------
          
          1- Original
          -----------
          Plan hash value: 1841765387
          
          -------------------------------------------------------------------------------------------------
          | Id  | Operation               | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT        |                       |    21 |   399 | 61980   (1)| 00:12:24 |
          |   1 |  SORT GROUP BY NOSORT   |                       |    21 |   399 | 61980   (1)| 00:12:24 |
          |   2 |   VIEW                  |                       |    21 |   399 | 61980   (1)| 00:12:24 |
          |   3 |    UNION-ALL            |                       |       |       |            |          |
          |   4 |     SORT GROUP BY NOSORT|                       |     1 |     7 |   132   (2)| 00:00:02 |
          |*  5 |      TABLE ACCESS FULL  | TIBEX_QUOTE           |  1552 | 10864 |   132   (2)| 00:00:02 |
          |   6 |     SORT GROUP BY NOSORT|                       |     1 |    17 | 55123   (2)| 00:11:02 |
          |*  7 |      TABLE ACCESS FULL  | TIBEX_ORDER           |  1386K|    22M| 55123   (2)| 00:11:02 |
          |   8 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
          |*  9 |      TABLE ACCESS FULL  | TIBEX_TSTRADE         |     1 |    19 |     2   (0)| 00:00:01 |
          |  10 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
          |* 11 |      TABLE ACCESS FULL  | TIBEX_IOIREQUEST      |     1 |    19 |     2   (0)| 00:00:01 |
          |  12 |     SORT GROUP BY NOSORT|                       |     1 |    14 |  6712   (1)| 00:01:21 |
          |* 13 |      TABLE ACCESS FULL  | TIBEX_BESTEXREL       |     1 |    14 |  6712   (1)| 00:01:21 |
          |  14 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 15 |      INDEX FULL SCAN    | SYS_C00125408         |     1 |    19 |     0   (0)| 00:00:01 |
          |  16 |     SORT GROUP BY NOSORT|                       |     1 |    17 |     3   (0)| 00:00:01 |
          |* 17 |      TABLE ACCESS FULL  | TIBEX_EDPPULLORDERS   |     1 |    17 |     3   (0)| 00:00:01 |
          |  18 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 19 |      INDEX RANGE SCAN   | SYS_C00125886         |     1 |    19 |     0   (0)| 00:00:01 |
          |  20 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 21 |      INDEX RANGE SCAN   | SYS_C00124868         |     1 |    19 |     0   (0)| 00:00:01 |
          |  22 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 23 |      INDEX RANGE SCAN   | SYS_C00124910         |     1 |    19 |     0   (0)| 00:00:01 |
          |  24 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
          |* 25 |      TABLE ACCESS FULL  | TIBEX_DELETEADMIN     |     1 |    19 |     2   (0)| 00:00:01 |
          |  26 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 27 |      INDEX RANGE SCAN   | SYS_C00125231         |     1 |    19 |     0   (0)| 00:00:01 |
          |  28 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 29 |      INDEX RANGE SCAN   | SYS_C00125347         |     1 |    19 |     0   (0)| 00:00:01 |
          |  30 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 31 |      INDEX RANGE SCAN   | SYS_C00125599         |     1 |    19 |     0   (0)| 00:00:01 |
          |  32 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 33 |      INDEX RANGE SCAN   | SYS_C00125644         |     1 |    19 |     0   (0)| 00:00:01 |
          |  34 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 35 |      INDEX RANGE SCAN   | SYS_C00125866         |     1 |    19 |     0   (0)| 00:00:01 |
          |  36 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 37 |      INDEX RANGE SCAN   | SYS_C00126060         |     1 |    19 |     0   (0)| 00:00:01 |
          |  38 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 39 |      INDEX RANGE SCAN   | SYS_C00125942         |     1 |    19 |     0   (0)| 00:00:01 |
          |  40 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
          |* 41 |      INDEX RANGE SCAN   | SYS_C00126280         |     1 |    19 |     0   (0)| 00:00:01 |
          |  42 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
          |* 43 |      TABLE ACCESS FULL  | TIBEX_CANCELTRDADMIN  |     1 |    19 |     2   (0)| 00:00:01 |
          |  44 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
          |* 45 |      TABLE ACCESS FULL  | TIBEX_BULKCANCELADMIN |     1 |    19 |     2   (0)| 00:00:01 |
          -------------------------------------------------------------------------------------------------
          
          Query Block Name / Object Alias (identified by operation id):
          -------------------------------------------------------------
          
             1 - SEL$F5BB74E1
             2 - SET$1        / from$_subquery$_002@SEL$2
             3 - SET$1
             4 - SEL$3
             5 - SEL$3        / TIBEX_QUOTE@SEL$3
             6 - SEL$4
             7 - SEL$4        / TIBEX_ORDER@SEL$4
             8 - SEL$5
             9 - SEL$5        / TIBEX_TSTRADE@SEL$5
            10 - SEL$6
            11 - SEL$6        / TIBEX_IOIREQUEST@SEL$6
            12 - SEL$7
            13 - SEL$7        / TIBEX_BESTEXREL@SEL$7
            14 - SEL$8
            15 - SEL$8        / TIBEX_INSTRUMENTADMIN@SEL$8
            16 - SEL$9
            17 - SEL$9        / TIBEX_EDPPULLORDERS@SEL$9
            18 - SEL$10
            19 - SEL$10       / TIBEX_PARTICIPANTADMIN@SEL$10
            20 - SEL$11
            21 - SEL$11       / TIBEX_BESTEXRELATIONSADMIN@SEL$11
            22 - SEL$12
            23 - SEL$12       / TIBEX_BOARDADMIN@SEL$12
            24 - SEL$13
            25 - SEL$13       / TIBEX_DELETEADMIN@SEL$13
            26 - SEL$14
            27 - SEL$14       / TIBEX_EXCHANGERATEADMIN@SEL$14
            28 - SEL$15
            29 - SEL$15       / TIBEX_HYBRIDMMINSTRADMIN@SEL$15
            30 - SEL$16
            31 - SEL$16       / TIBEX_MARKETADMIN@SEL$16
            32 - SEL$17
            33 - SEL$17       / TIBEX_MMBBOLISTADMIN@SEL$17
            34 - SEL$18
            35 - SEL$18       / TIBEX_PARTCLRACCTMAPADMIN@SEL$18
            36 - SEL$19
            37 - SEL$19       / TIBEX_SERVERADMIN@SEL$19
            38 - SEL$20
            39 - SEL$20       / TIBEX_QUOTEADMIN@SEL$20
            40 - SEL$21
            41 - SEL$21       / TIBEX_USERADMIN@SEL$21
            42 - SEL$22
            43 - SEL$22       / TIBEX_CANCELTRDADMIN@SEL$22
            44 - SEL$23
            45 - SEL$23       / TIBEX_BULKCANCELADMIN@SEL$23
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             5 - filter("LASTINSTUSERALIAS"=:SYS_B_1)
             7 - filter("LASTINSTUSERALIAS"=:SYS_B_1)
             9 - filter("LASTINSTUSERALIAS" IS NOT NULL AND "LASTINSTUSERALIAS"=:SYS_B_1)
            11 - filter("LASTINSTUSERALIAS" IS NOT NULL AND "LASTINSTUSERALIAS"=:SYS_B_1)
            13 - filter("LASTINSTUSERALIAS" IS NOT NULL AND "LASTINSTUSERALIAS"=:SYS_B_1)
            15 - access("USERALIAS"=:SYS_B_1)
                 filter("USERALIAS"=:SYS_B_1)
            17 - filter("USERALIAS"=:SYS_B_1)
            19 - access("USERALIAS"=:SYS_B_1)
            21 - access("USERALIAS"=:SYS_B_1)
            23 - access("USERALIAS"=:SYS_B_1)
            25 - filter("USERALIAS" IS NOT NULL AND "USERALIAS"=:SYS_B_1)
            27 - access("USERALIAS"=:SYS_B_1)
            29 - access("USERALIAS"=:SYS_B_1)
            31 - access("USERALIAS"=:SYS_B_1)
            33 - access("USERALIAS"=:SYS_B_1)
            35 - access("USERALIAS"=:SYS_B_1)
            37 - access("USERALIAS"=:SYS_B_1)
            39 - access("USERALIAS"=:SYS_B_1)
            41 - access("USERALIAS"=:SYS_B_1)
            43 - filter("USERALIAS"=:SYS_B_1)
            45 - filter("USERALIAS"=:SYS_B_1)
          
          Column Projection Information (identified by operation id):
          -----------------------------------------------------------
          
             1 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
             2 - "USERALIAS"[CHARACTER,4], "LASTINSTMESSAGESEQUENCE"[NUMBER,22]
             3 - STRDEF[4], STRDEF[22]
             4 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
             5 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
             6 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
             7 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
             8 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
             9 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
            10 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
            11 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
            12 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
            13 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
            14 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            15 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            16 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            17 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            18 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            19 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            20 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            21 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            22 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            23 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            24 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            25 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            26 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            27 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            28 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            29 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            30 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            31 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            32 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            33 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            34 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            35 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            36 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            37 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            38 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            39 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            40 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            41 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            42 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            43 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            44 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
            45 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
          
          
          View Details
          CREATE OR REPLACE FORCE VIEW "SAT_ETS"."TIBEX_MSGSEQBYUSERALIAS" ("USERALIAS", "LASTINSTMESSAGESEQUENCE") AS
            SELECT  userAlias, DECODE(NVL(max(LastInstMessageSequence), 0),-1,0,NVL(max(LastInstMessageSequence),0)) as LastInstMessageSequence
              FROM  (SELECT  LastInstUserAlias as UserAlias,
                            max(LastInstMessageSequence) as LastInstMessageSequence
                      FROM  tibex_quote
                      WHERE LastInstUserAlias IS NOT NULL
                      GROUP BY LastInstUserAlias
                    UNION ALL
                    SELECT  LastInstUserAlias, max(LastInstMessageSequence)
                      FROM  tibex_order
                      WHERE LastInstUserAlias IS NOT NULL
                      GROUP BY LastInstUserAlias
                    UNION ALL
                    SELECT  LastInstUserAlias, max(LastInstMessageSequence)
                      FROM  tibex_TsTrade
                      WHERE LastInstUserAlias IS NOT NULL
                      GROUP BY LastInstUserAlias
                    UNION ALL
                    SELECT  LastInstUserAlias, max(LastInstMessageSequence)
                      FROM  tibex_IOIRequest
                      WHERE LastInstUserAlias IS NOT NULL
                      GROUP BY LastInstUserAlias
                    UNION ALL
                    SELECT  LastInstUserAlias, max(LastInstMessageSequence)
                      FROM  tibex_BestExRel
                      WHERE LastInstUserAlias IS NOT NULL
                      GROUP BY LastInstUserAlias
          -- BUGZID:17352 Remove EDPBookEntry table and views
          /*
                    UNION ALL
                    SELECT LastInstUserAlias, max(LastInstMessageSequence)
                      FROM tibex_EDPBOOKENTRYVIEW
                      WHERE LastInstUserAlias IS NOT NULL
                      GROUP BY LastInstUserAlias
           */
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_INSTRUMENTADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_EDPPullOrders
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_PARTICIPANTADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_BESTEXRELATIONSADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_BOARDADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_DELETEADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_EXCHANGERATEADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_HYBRIDMMINSTRADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_MARKETADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_MMBBOLISTADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
          --          SELECT UserAlias, max(MessageSequence)
          --            FROM tibex_MMINSTRUMENTLISTADMIN
          --            WHERE UserAlias IS NOT NULL
          --            GROUP BY UserAlias
          --          UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_PARTCLRACCTMAPADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_SERVERADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_QUOTEADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_USERADMIN
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_cancelTrdAdmin
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    UNION ALL -- BUGZID:14129
                    SELECT UserAlias, max(MessageSequence)
                      FROM tibex_bulkCancelAdmin
                      WHERE UserAlias IS NOT NULL
                      GROUP BY UserAlias
                    )
              GROUP BY userAlias;
          Regards
          NM
          • 32. Re: Query Takes more Time when i execute from the Instant client.
            NM
            Hi Charles,

            Today was using sqlt for problematic Query and i found the following information.
            GENERAL INFORMATION SECTION
            -------------------------------------------------------------------------------
            Tuning Task Name                  : s5339_trade1_prdfa001
            Tuning Task Owner                 : SAT_ETS
            Tuning Task ID                    : 19185
            Scope                             : COMPREHENSIVE
            Time Limit(seconds)               : 1800
            Completion Status                 : COMPLETED
            Started at                        : 08/25/2010 21:54:09
            Completed at                      : 08/25/2010 21:54:10
            
            -------------------------------------------------------------------------------
            Schema Name: SAT_ETS
            SQL ID     : 03fqwamsdv6pf
            SQL Text   : select mod(lastinstmessagesequence, :"SYS_B_0") LastInstIDSeqNo
                         from tibex_msgseqbyuseralias where useralias=:"SYS_B_1"
            
            -------------------------------------------------------------------------------
            ADDITIONAL INFORMATION SECTION
            -------------------------------------------------------------------------------
            - The optimizer could not merge the view at line ID 2 of the execution plan.
              The optimizer cannot merge a view that contains a set operator.
            
            -------------------------------------------------------------------------------
            EXPLAIN PLANS SECTION
            -------------------------------------------------------------------------------
            
            1- Original
            -----------
            Plan hash value: 1841765387
            
            -------------------------------------------------------------------------------------------------
            | Id  | Operation               | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
            -------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT        |                       |    21 |   399 | 61980   (1)| 00:12:24 |
            |   1 |  SORT GROUP BY NOSORT   |                       |    21 |   399 | 61980   (1)| 00:12:24 |
            |   2 |   VIEW                  |                       |    21 |   399 | 61980   (1)| 00:12:24 |
            |   3 |    UNION-ALL            |                       |       |       |            |          |
            |   4 |     SORT GROUP BY NOSORT|                       |     1 |     7 |   132   (2)| 00:00:02 |
            |*  5 |      TABLE ACCESS FULL  | TIBEX_QUOTE           |  1552 | 10864 |   132   (2)| 00:00:02 |
            |   6 |     SORT GROUP BY NOSORT|                       |     1 |    17 | 55123   (2)| 00:11:02 |
            |*  7 |      TABLE ACCESS FULL  | TIBEX_ORDER           |  1386K|    22M| 55123   (2)| 00:11:02 |
            |   8 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
            |*  9 |      TABLE ACCESS FULL  | TIBEX_TSTRADE         |     1 |    19 |     2   (0)| 00:00:01 |
            |  10 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
            |* 11 |      TABLE ACCESS FULL  | TIBEX_IOIREQUEST      |     1 |    19 |     2   (0)| 00:00:01 |
            |  12 |     SORT GROUP BY NOSORT|                       |     1 |    14 |  6712   (1)| 00:01:21 |
            |* 13 |      TABLE ACCESS FULL  | TIBEX_BESTEXREL       |     1 |    14 |  6712   (1)| 00:01:21 |
            |  14 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 15 |      INDEX FULL SCAN    | SYS_C00125408         |     1 |    19 |     0   (0)| 00:00:01 |
            |  16 |     SORT GROUP BY NOSORT|                       |     1 |    17 |     3   (0)| 00:00:01 |
            |* 17 |      TABLE ACCESS FULL  | TIBEX_EDPPULLORDERS   |     1 |    17 |     3   (0)| 00:00:01 |
            |  18 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 19 |      INDEX RANGE SCAN   | SYS_C00125886         |     1 |    19 |     0   (0)| 00:00:01 |
            |  20 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 21 |      INDEX RANGE SCAN   | SYS_C00124868         |     1 |    19 |     0   (0)| 00:00:01 |
            |  22 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 23 |      INDEX RANGE SCAN   | SYS_C00124910         |     1 |    19 |     0   (0)| 00:00:01 |
            |  24 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
            |* 25 |      TABLE ACCESS FULL  | TIBEX_DELETEADMIN     |     1 |    19 |     2   (0)| 00:00:01 |
            |  26 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 27 |      INDEX RANGE SCAN   | SYS_C00125231         |     1 |    19 |     0   (0)| 00:00:01 |
            |  28 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 29 |      INDEX RANGE SCAN   | SYS_C00125347         |     1 |    19 |     0   (0)| 00:00:01 |
            |  30 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 31 |      INDEX RANGE SCAN   | SYS_C00125599         |     1 |    19 |     0   (0)| 00:00:01 |
            |  32 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 33 |      INDEX RANGE SCAN   | SYS_C00125644         |     1 |    19 |     0   (0)| 00:00:01 |
            |  34 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 35 |      INDEX RANGE SCAN   | SYS_C00125866         |     1 |    19 |     0   (0)| 00:00:01 |
            |  36 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 37 |      INDEX RANGE SCAN   | SYS_C00126060         |     1 |    19 |     0   (0)| 00:00:01 |
            |  38 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 39 |      INDEX RANGE SCAN   | SYS_C00125942         |     1 |    19 |     0   (0)| 00:00:01 |
            |  40 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     0   (0)| 00:00:01 |
            |* 41 |      INDEX RANGE SCAN   | SYS_C00126280         |     1 |    19 |     0   (0)| 00:00:01 |
            |  42 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
            |* 43 |      TABLE ACCESS FULL  | TIBEX_CANCELTRDADMIN  |     1 |    19 |     2   (0)| 00:00:01 |
            |  44 |     SORT GROUP BY NOSORT|                       |     1 |    19 |     2   (0)| 00:00:01 |
            |* 45 |      TABLE ACCESS FULL  | TIBEX_BULKCANCELADMIN |     1 |    19 |     2   (0)| 00:00:01 |
            -------------------------------------------------------------------------------------------------
            
            Query Block Name / Object Alias (identified by operation id):
            -------------------------------------------------------------
            
               1 - SEL$F5BB74E1
               2 - SET$1        / from$_subquery$_002@SEL$2
               3 - SET$1
               4 - SEL$3
               5 - SEL$3        / TIBEX_QUOTE@SEL$3
               6 - SEL$4
               7 - SEL$4        / TIBEX_ORDER@SEL$4
               8 - SEL$5
               9 - SEL$5        / TIBEX_TSTRADE@SEL$5
              10 - SEL$6
              11 - SEL$6        / TIBEX_IOIREQUEST@SEL$6
              12 - SEL$7
              13 - SEL$7        / TIBEX_BESTEXREL@SEL$7
              14 - SEL$8
              15 - SEL$8        / TIBEX_INSTRUMENTADMIN@SEL$8
              16 - SEL$9
              17 - SEL$9        / TIBEX_EDPPULLORDERS@SEL$9
              18 - SEL$10
              19 - SEL$10       / TIBEX_PARTICIPANTADMIN@SEL$10
              20 - SEL$11
              21 - SEL$11       / TIBEX_BESTEXRELATIONSADMIN@SEL$11
              22 - SEL$12
              23 - SEL$12       / TIBEX_BOARDADMIN@SEL$12
              24 - SEL$13
              25 - SEL$13       / TIBEX_DELETEADMIN@SEL$13
              26 - SEL$14
              27 - SEL$14       / TIBEX_EXCHANGERATEADMIN@SEL$14
              28 - SEL$15
              29 - SEL$15       / TIBEX_HYBRIDMMINSTRADMIN@SEL$15
              30 - SEL$16
              31 - SEL$16       / TIBEX_MARKETADMIN@SEL$16
              32 - SEL$17
              33 - SEL$17       / TIBEX_MMBBOLISTADMIN@SEL$17
              34 - SEL$18
              35 - SEL$18       / TIBEX_PARTCLRACCTMAPADMIN@SEL$18
              36 - SEL$19
              37 - SEL$19       / TIBEX_SERVERADMIN@SEL$19
              38 - SEL$20
              39 - SEL$20       / TIBEX_QUOTEADMIN@SEL$20
              40 - SEL$21
              41 - SEL$21       / TIBEX_USERADMIN@SEL$21
              42 - SEL$22
              43 - SEL$22       / TIBEX_CANCELTRDADMIN@SEL$22
              44 - SEL$23
              45 - SEL$23       / TIBEX_BULKCANCELADMIN@SEL$23
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               5 - filter("LASTINSTUSERALIAS"=:SYS_B_1)
               7 - filter("LASTINSTUSERALIAS"=:SYS_B_1)
               9 - filter("LASTINSTUSERALIAS" IS NOT NULL AND "LASTINSTUSERALIAS"=:SYS_B_1)
              11 - filter("LASTINSTUSERALIAS" IS NOT NULL AND "LASTINSTUSERALIAS"=:SYS_B_1)
              13 - filter("LASTINSTUSERALIAS" IS NOT NULL AND "LASTINSTUSERALIAS"=:SYS_B_1)
              15 - access("USERALIAS"=:SYS_B_1)
                   filter("USERALIAS"=:SYS_B_1)
              17 - filter("USERALIAS"=:SYS_B_1)
              19 - access("USERALIAS"=:SYS_B_1)
              21 - access("USERALIAS"=:SYS_B_1)
              23 - access("USERALIAS"=:SYS_B_1)
              25 - filter("USERALIAS" IS NOT NULL AND "USERALIAS"=:SYS_B_1)
              27 - access("USERALIAS"=:SYS_B_1)
              29 - access("USERALIAS"=:SYS_B_1)
              31 - access("USERALIAS"=:SYS_B_1)
              33 - access("USERALIAS"=:SYS_B_1)
              35 - access("USERALIAS"=:SYS_B_1)
              37 - access("USERALIAS"=:SYS_B_1)
              39 - access("USERALIAS"=:SYS_B_1)
              41 - access("USERALIAS"=:SYS_B_1)
              43 - filter("USERALIAS"=:SYS_B_1)
              45 - filter("USERALIAS"=:SYS_B_1)
            
            Column Projection Information (identified by operation id):
            -----------------------------------------------------------
            
               1 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
               2 - "USERALIAS"[CHARACTER,4], "LASTINSTMESSAGESEQUENCE"[NUMBER,22]
               3 - STRDEF[4], STRDEF[22]
               4 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
               5 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
               6 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
               7 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
               8 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
               9 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
              10 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
              11 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
              12 - (#keys=1) "LASTINSTUSERALIAS"[CHARACTER,4], MAX("LASTINSTMESSAGESEQUENCE")[22]
              13 - "LASTINSTMESSAGESEQUENCE"[NUMBER,22], "LASTINSTUSERALIAS"[CHARACTER,4]
              14 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              15 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              16 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              17 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              18 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              19 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              20 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              21 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              22 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              23 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              24 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              25 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              26 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              27 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              28 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              29 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              30 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              31 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              32 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              33 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              34 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              35 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              36 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              37 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              38 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              39 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              40 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              41 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              42 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              43 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
              44 - (#keys=1) "USERALIAS"[CHARACTER,4], MAX("MESSAGESEQUENCE")[22]
              45 - "USERALIAS"[CHARACTER,4], "MESSAGESEQUENCE"[NUMBER,22]
            
            
            View Details
            CREATE OR REPLACE FORCE VIEW "SAT_ETS"."TIBEX_MSGSEQBYUSERALIAS" ("USERALIAS", "LASTINSTMESSAGESEQUENCE") AS
              SELECT  userAlias, DECODE(NVL(max(LastInstMessageSequence), 0),-1,0,NVL(max(LastInstMessageSequence),0)) as LastInstMessageSequence
                FROM  (SELECT  LastInstUserAlias as UserAlias,
                              max(LastInstMessageSequence) as LastInstMessageSequence
                        FROM  tibex_quote
                        WHERE LastInstUserAlias IS NOT NULL
                        GROUP BY LastInstUserAlias
                      UNION ALL
                      SELECT  LastInstUserAlias, max(LastInstMessageSequence)
                        FROM  tibex_order
                        WHERE LastInstUserAlias IS NOT NULL
                        GROUP BY LastInstUserAlias
                      UNION ALL
                      SELECT  LastInstUserAlias, max(LastInstMessageSequence)
                        FROM  tibex_TsTrade
                        WHERE LastInstUserAlias IS NOT NULL
                        GROUP BY LastInstUserAlias
                      UNION ALL
                      SELECT  LastInstUserAlias, max(LastInstMessageSequence)
                        FROM  tibex_IOIRequest
                        WHERE LastInstUserAlias IS NOT NULL
                        GROUP BY LastInstUserAlias
                      UNION ALL
                      SELECT  LastInstUserAlias, max(LastInstMessageSequence)
                        FROM  tibex_BestExRel
                        WHERE LastInstUserAlias IS NOT NULL
                        GROUP BY LastInstUserAlias
            -- BUGZID:17352 Remove EDPBookEntry table and views
            /*
                      UNION ALL
                      SELECT LastInstUserAlias, max(LastInstMessageSequence)
                        FROM tibex_EDPBOOKENTRYVIEW
                        WHERE LastInstUserAlias IS NOT NULL
                        GROUP BY LastInstUserAlias
             */
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_INSTRUMENTADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_EDPPullOrders
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_PARTICIPANTADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_BESTEXRELATIONSADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_BOARDADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_DELETEADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_EXCHANGERATEADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_HYBRIDMMINSTRADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_MARKETADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_MMBBOLISTADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
            --          SELECT UserAlias, max(MessageSequence)
            --            FROM tibex_MMINSTRUMENTLISTADMIN
            --            WHERE UserAlias IS NOT NULL
            --            GROUP BY UserAlias
            --          UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_PARTCLRACCTMAPADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_SERVERADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_QUOTEADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_USERADMIN
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_cancelTrdAdmin
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      UNION ALL -- BUGZID:14129
                      SELECT UserAlias, max(MessageSequence)
                        FROM tibex_bulkCancelAdmin
                        WHERE UserAlias IS NOT NULL
                        GROUP BY UserAlias
                      )
                GROUP BY userAlias;
            Regards
            NM
            1 2 3 Previous Next