0 Replies Latest reply on Sep 4, 2014 2:40 PM by chris_c

    select query performance help,

    chris_c

      Oracle Enterprise edition version 12.1.0.2 with partitioning and in memory options.

      Operating system:- SUSE 11sp2 64 BIT.

      Optimiser Parameters:-

      SQL> show parameter optimi
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      optimizer_adaptive_features          boolean     TRUE
      optimizer_adaptive_reporting_only    boolean     FALSE
      optimizer_capture_sql_plan_baselines boolean     FALSE
      optimizer_dynamic_sampling           integer     2
      optimizer_features_enable            string      12.1.0.2
      optimizer_index_caching              integer     0
      optimizer_index_cost_adj             integer     100
      optimizer_inmemory_aware             boolean     TRUE
      optimizer_mode                       string      ALL_ROWS
      optimizer_secure_view_merging        boolean     TRUE
      optimizer_use_invisible_indexes      boolean     FALSE
      optimizer_use_pending_statistics     boolean     FALSE
      optimizer_use_sql_plan_baselines     boolean     TRUE
      plsql_optimize_level                 integer     2
      

      We have a message logging system that logs messages in and out of our application, what we want to find is the average response time of outbound messages for any period where the number of inbound messages is > 25 per milliseconds by gateway, volume of data is quite high (around 50-70 million rows per day) currently the query is as follows this currently takes just over 24:-

       

      select /*+ gather_plan_statistics */
        VNTG.gatewayipaddress,
        count(responsetime),
        avg(responsetime) 
      from
        V_NTG_MESSAGE_BY_GATEWAY VNTG,
        MV_NTG_MICROBURSTS_GW MVMG
      where 
        VNTG.messageType='8' and
        VNTG.responsetime is not null and
        VNTG.GATEWAYIPADDRESS=MVMG.GATEWAYIPADDRESS and
        VNTG.messagetimestamp >= MVMG.STARTINTERVAL and
        VNTG.messagetimestamp <= MVMG.ENDINTERVAL
      and 
        MVMG.STARTINTERVAL between timestamp'2014-07-07 08:00:00' and timestamp'2014-07-07 09:00:00'
      group by VNTG.gatewayipaddress;
      

       

      dbms xplan gives the following output the problem seems to be around the hash join and group by but I'm not sure why or how to make the query more efficient.

       

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                 | Name                  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                          |                       |      1 |        |       |  1843 (100)|          |       |       |        |      |            |      7 |00:24:23.86 |     441 |       |       |          |
      |   1 |  PX COORDINATOR                           |                       |      1 |        |       |            |          |       |       |        |      |            |      7 |00:24:23.86 |     441 |       |       |          |
      |   2 |   PX SEND QC (RANDOM)                     | :TQ10002              |      0 |     10 |  1050 |  1843  (23)| 00:00:01 |       |       |  Q1,02 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |       |       |          |
      |   3 |    HASH GROUP BY                          |                       |      2 |     10 |  1050 |  1843  (23)| 00:00:01 |       |       |  Q1,02 | PCWP |            |      7 |00:00:00.03 |       0 |   834K|   834K|     2/0/0|
      |   4 |     PX RECEIVE                            |                       |      2 |     10 |  1050 |  1843  (23)| 00:00:01 |       |       |  Q1,02 | PCWP |            |      7 |00:00:00.01 |       0 |       |       |          |
      |   5 |      PX SEND HASH                         | :TQ10001              |      0 |     10 |  1050 |  1843  (23)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |      0 |00:00:00.01 |       0 |       |       |          |
      |   6 |       HASH GROUP BY                       |                       |      2 |     10 |  1050 |  1843  (23)| 00:00:01 |       |       |  Q1,01 | PCWP |            |      7 |00:48:02.44 |     340 |   834K|   834K|     2/0/0|
      |*  7 |        HASH JOIN                          |                       |      2 |    305K|    30M|  1839  (22)| 00:00:01 |       |       |  Q1,01 | PCWP |            |  36386 |00:27:32.54 |     340 |   542M|    18M|     2/0/0|
      |   8 |         JOIN FILTER CREATE                | :BF0000               |      2 |   2425K|   185M|    12  (34)| 00:00:01 |       |       |  Q1,01 | PCWP |            |   4844K|00:00:04.89 |       0 |       |       |          |
      |   9 |          PX RECEIVE                       |                       |      2 |   2425K|   185M|    12  (34)| 00:00:01 |       |       |  Q1,01 | PCWP |            |   4844K|00:00:03.74 |       0 |       |       |          |
      |  10 |           PX SEND BROADCAST               | :TQ10000              |      0 |   2425K|   185M|    12  (34)| 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |      0 |00:00:00.01 |       0 |       |       |          |
      |* 11 |            HASH JOIN                      |                       |      2 |   2425K|   185M|    12  (34)| 00:00:01 |       |       |  Q1,00 | PCWP |            |   2422K|00:00:01.37 |      30 |  1087K|  1087K|     2/0/0|
      |  12 |             PARTITION RANGE ITERATOR      |                       |      2 |    786 | 27510 |     3   (0)| 00:00:01 | 11410 | 11411 |  Q1,00 | PCWC |            |   1572 |00:00:00.01 |      14 |       |       |          |
      |* 13 |              MAT_VIEW ACCESS INMEMORY FULL| MV_NTG_MICROBURSTS_GW |      4 |    786 | 27510 |     3   (0)| 00:00:01 | 11410 | 11411 |  Q1,00 | PCWP |            |   1572 |00:00:00.01 |      14 |       |       |          |
      |  14 |             PX BLOCK ITERATOR             |                       |      2 |  78916 |  3467K|     7  (15)| 00:00:01 |       |       |  Q1,00 | PCWC |            |  79026 |00:00:00.01 |      16 |       |       |          |
      |* 15 |              TABLE ACCESS INMEMORY FULL   | T_NTG_CONNECTION      |      2 |  78916 |  3467K|     7  (15)| 00:00:01 |       |       |  Q1,00 | PCWP |            |  79026 |00:00:00.01 |      16 |       |       |          |
      |  16 |         JOIN FILTER USE                   | :BF0000               |      2 |     65M|  1556M|  1764  (20)| 00:00:01 |       |       |  Q1,01 | PCWP |            |     63M|00:00:14.98 |     340 |       |       |          |
      |  17 |          PX BLOCK ITERATOR                |                       |      2 |     65M|  1556M|  1764  (20)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWC |            |     63M|00:00:12.16 |     340 |       |       |          |
      |* 18 |           TABLE ACCESS INMEMORY FULL      | T_NTG_MESSAGE         |     57 |     65M|  1556M|  1764  (20)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |     63M|00:00:09.51 |     340 |       |       |          |
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Query Block Name / Object Alias (identified by operation id):
      -------------------------------------------------------------
      
         1 - SEL$F5BB74E1
        13 - SEL$F5BB74E1 / MVMG@SEL$1
        15 - SEL$F5BB74E1 / T_NTG_CONNECTION@SEL$2
        18 - SEL$F5BB74E1 / T_NTG_MESSAGE@SEL$2
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         7 - access("T_NTG_MESSAGE"."STREAMID"="T_NTG_CONNECTION"."STREAMID" AND "T_NTG_MESSAGE"."CONNECTIONID"="T_NTG_CONNECTION"."CONNECTIONID")
             filter(("T_NTG_MESSAGE"."MESSAGETIMESTAMP"<="MVMG"."ENDINTERVAL" AND "T_NTG_MESSAGE"."MESSAGETIMESTAMP"<="T_NTG_CONNECTION"."LOGOFFTIME" AND "T_NTG_MESSAGE"."MESSAGETIMESTAMP">="T_NTG_CONNECTION"."LOGONTIME" AND
                    "T_NTG_MESSAGE"."MESSAGETIMESTAMP">="MVMG"."STARTINTERVAL"))
        11 - access("T_NTG_CONNECTION"."GATEWAYIPADDRESS"="MVMG"."GATEWAYIPADDRESS")
        13 - inmemory(("MVMG"."STARTINTERVAL"<=TIMESTAMP' 2014-07-07 09:00:00.000000000' AND "MVMG"."STARTINTERVAL">=TIMESTAMP' 2014-07-07 08:00:00.000000000' AND "MVMG"."ENDINTERVAL">=TIMESTAMP' 2014-07-0708:00:00.000000000'))
             filter(("MVMG"."STARTINTERVAL"<=TIMESTAMP' 2014-07-07 09:00:00.000000000' AND "MVMG"."STARTINTERVAL">=TIMESTAMP' 2014-07-07 08:00:00.000000000' AND "MVMG"."ENDINTERVAL">=TIMESTAMP' 2014-07-07 08:00:00.000000000'))
        15 - inmemory(:Z>=:Z AND :Z<=:Z AND "T_NTG_CONNECTION"."LOGOFFTIME">=TIMESTAMP' 2014-07-07 08:00:00.000000000')
             filter("T_NTG_CONNECTION"."LOGOFFTIME">=TIMESTAMP' 2014-07-07 08:00:00.000000000')
        18 - inmemory(:Z>=:Z AND :Z<=:Z AND ("T_NTG_MESSAGE"."RESPONSETIME" IS NOT NULL AND "T_NTG_MESSAGE"."MESSAGETIMESTAMP">=TIMESTAMP' 2014-07-07 08:00:00.000000000' AND
                    SYS_OP_BLOOM_FILTER(:BF0000,"T_NTG_MESSAGE"."STREAMID")))
             filter(("T_NTG_MESSAGE"."RESPONSETIME" IS NOT NULL AND "T_NTG_MESSAGE"."MESSAGETIMESTAMP">=TIMESTAMP' 2014-07-07 08:00:00.000000000' AND SYS_OP_BLOOM_FILTER(:BF0000,"T_NTG_MESSAGE"."STREAMID")))
      
      Column Projection Information (identified by operation id):
      -----------------------------------------------------------
         1 - "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], COUNT()[22], SUM()[22]
         2 - (#keys=0) "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], COUNT()[22], SUM()[22]
         3 - (rowset=200) "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], COUNT()[22], SUM()[22]
         4 - (rowset=200) "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], SYS_OP_MSR()[33]
         5 - (#keys=1) "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], SYS_OP_MSR()[33]
         6 - (rowset=200) "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], SYS_OP_MSR()[33]
         7 - (#keys=2; rowset=200) "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], "T_NTG_MESSAGE"."RESPONSETIME"[NUMBER,22]
         8 - (rowset=200) "T_NTG_CONNECTION"."STREAMID"[NUMBER,22], "T_NTG_CONNECTION"."CONNECTIONID"[NUMBER,22], "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], "MVMG"."ENDINTERVAL"[TIMESTAMP,11],
             "MVMG"."STARTINTERVAL"[TIMESTAMP,11], "T_NTG_CONNECTION"."LOGOFFTIME"[TIMESTAMP,11], "T_NTG_CONNECTION"."LOGONTIME"[TIMESTAMP,11]
         9 - (rowset=200) "T_NTG_CONNECTION"."STREAMID"[NUMBER,22], "T_NTG_CONNECTION"."CONNECTIONID"[NUMBER,22], "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], "MVMG"."ENDINTERVAL"[TIMESTAMP,11],
             "MVMG"."STARTINTERVAL"[TIMESTAMP,11], "T_NTG_CONNECTION"."LOGOFFTIME"[TIMESTAMP,11], "T_NTG_CONNECTION"."LOGONTIME"[TIMESTAMP,11]
        10 - (#keys=0) "T_NTG_CONNECTION"."STREAMID"[NUMBER,22], "T_NTG_CONNECTION"."CONNECTIONID"[NUMBER,22], "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], "MVMG"."ENDINTERVAL"[TIMESTAMP,11],
             "MVMG"."STARTINTERVAL"[TIMESTAMP,11], "T_NTG_CONNECTION"."LOGOFFTIME"[TIMESTAMP,11], "T_NTG_CONNECTION"."LOGONTIME"[TIMESTAMP,11]
        11 - (#keys=1; rowset=200) "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15], "MVMG"."ENDINTERVAL"[TIMESTAMP,11], "MVMG"."STARTINTERVAL"[TIMESTAMP,11], "T_NTG_CONNECTION"."STREAMID"[NUMBER,22],
             "T_NTG_CONNECTION"."CONNECTIONID"[NUMBER,22], "T_NTG_CONNECTION"."LOGONTIME"[TIMESTAMP,11], "T_NTG_CONNECTION"."LOGOFFTIME"[TIMESTAMP,11]
        12 - (rowset=200) "MVMG"."GATEWAYIPADDRESS"[VARCHAR2,15], "MVMG"."STARTINTERVAL"[TIMESTAMP,11], "MVMG"."ENDINTERVAL"[TIMESTAMP,11]
        13 - (rowset=200) "MVMG"."GATEWAYIPADDRESS"[VARCHAR2,15], "MVMG"."STARTINTERVAL"[TIMESTAMP,11], "MVMG"."ENDINTERVAL"[TIMESTAMP,11]
        14 - (rowset=200) "T_NTG_CONNECTION"."STREAMID"[NUMBER,22], "T_NTG_CONNECTION"."CONNECTIONID"[NUMBER,22], "T_NTG_CONNECTION"."LOGONTIME"[TIMESTAMP,11], "T_NTG_CONNECTION"."LOGOFFTIME"[TIMESTAMP,11],
             "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15]
        15 - (rowset=200) "T_NTG_CONNECTION"."STREAMID"[NUMBER,22], "T_NTG_CONNECTION"."CONNECTIONID"[NUMBER,22], "T_NTG_CONNECTION"."LOGONTIME"[TIMESTAMP,11], "T_NTG_CONNECTION"."LOGOFFTIME"[TIMESTAMP,11],
             "T_NTG_CONNECTION"."GATEWAYIPADDRESS"[VARCHAR2,15]
        16 - (rowset=200) "T_NTG_MESSAGE"."MESSAGETIMESTAMP"[TIMESTAMP,11], "T_NTG_MESSAGE"."STREAMID"[NUMBER,22], "T_NTG_MESSAGE"."RESPONSETIME"[NUMBER,22], "T_NTG_MESSAGE"."CONNECTIONID"[NUMBER,22]
        17 - (rowset=200) "T_NTG_MESSAGE"."MESSAGETIMESTAMP"[TIMESTAMP,11], "T_NTG_MESSAGE"."STREAMID"[NUMBER,22], "T_NTG_MESSAGE"."RESPONSETIME"[NUMBER,22], "T_NTG_MESSAGE"."CONNECTIONID"[NUMBER,22]
        18 - (rowset=200) "T_NTG_MESSAGE"."MESSAGETIMESTAMP"[TIMESTAMP,11], "T_NTG_MESSAGE"."STREAMID"[NUMBER,22], "T_NTG_MESSAGE"."RESPONSETIME"[NUMBER,22], "T_NTG_MESSAGE"."CONNECTIONID"[NUMBER,22]
      
      Note
      -----
         - dynamic statistics used: dynamic sampling (level=AUTO)
         - automatic DOP: Computed Degree of Parallelism is 2
         - parallel scans affinitized for inmemory
         
      

       

      The tables MVIEW and view involved are defined as follows:-

       

      CREATE TABLE t_NTG_Message(
        MessageTimestamp timestamp(9) NOT NULL,
        StreamID number(10,0) NOT NULL,
        TCPSequenceNumber number(38,0) NOT NULL,
        PacketNumber number(38,0) NOT NULL,
        MessageDirection char(1) NOT NULL,
        MessageType char(1) NOT NULL,
        ResponseTime number(38,0) NULL,
        SequenceNumber number(38,0) NULL,
        msg_decode MESSAGE_DECODE_TYPE null)
      PARTITION BY RANGE (MessageTimestamp) 
      INTERVAL(NUMTODSINTERVAL(1, 'HOUR'))-- Partition every hour
      SUBPARTITION by LIST (MESSAGETYPE)
      SUBPARTITION TEMPLATE
       (SUBPARTITION UA values ('A'),SUBPARTITION UB values ('B'),SUBPARTITION N5 values ('5'),SUBPARTITION N0 values ('0'),SUBPARTITION UM values ('M'),SUBPARTITION UN values ('N'), 
        SUBPARTITION UP values ('P'),SUBPARTITION N3 values ('3'),SUBPARTITION Ln values ('n'),SUBPARTITION UD values ('D'),SUBPARTITION UF values ('F'),SUBPARTITION LQ values ('q'), 
        SUBPARTITION UG values ('G'),SUBPARTITION N8 values ('8'),SUBPARTITION N9 values ('9'),SUBPARTITION LR values ('r'),SUBPARTITION LS values ('S'),SUBPARTITION LJ values ('j') 
      )
      (PARTITION "BEFORE" VALUES LESS THAN (TO_DATE ('2013-03-19 00', 'YYYY-MM-DD hh24', 'NLS_CALENDAR=GREGORIAN')));
      ALTER TABLE t_NTG_Message2 ADD CONSTRAINT t_NTG_Message2_PK PRIMARY KEY(MessageTimestamp,StreamID,TCPSequenceNumber,messagetype,MessageDirection)
      using index (create unique index t_NTG_Message2_PK_I on t_NTG_Message2 (MessageTimestamp,StreamID,TCPSequenceNumber,messagetype,MessageDirection) LOCAL);
      alter table t_NTG_Message inmemory memcompress for query high priority high;
      

       

      create table t_NTG_connection
        (streamID          number(10,0) not null,
         ConnectionID      number (10,0) not null,
         Username          varchar2(20),
         Password          varchar2(20),
         NewPassword       varchar2(20),
         Status            char(1),
         Logontime         timestamp(9) not null,
         Logofftime        timestamp(9),
         CustomerIPAddress varchar2(15),
         CustomerPort      number(10,0),
         GateWayIPAddress  varchar2(15),
         GatewayPort       number(10,0));  
      ALTER TABLE t_NTG_connection ADD CONSTRAINT t_NTG_connection_PK PRIMARY KEY(StreamID,LogonTime,ConnectionID) 
      using index (create unique index t_NTG_connection_PK_I on t_NTG_connection (StreamID,LogonTime,ConnectionID));
      Alter table t_NTG_connection inmemory memcompress for query high priority high;
      

       

      Finding the time periods we are interested in we use the following materialized view:-

       

      create materialized view MV_NTG_MICROBURSTS_GW
      select
        gatewayIPADDRESS,
        startinterval,endinterval from
      (select * from (
       select
        c.gatewayIPADDRESS,
        a.MESSAGETIMESTAMP startinterval,
        a.MESSAGETIMESTAMP + (interval '0.001' second) as endinterval,
        count(*) over (partition by gatewayIPADDRESS order by MESSAGETIMESTAMP range between current row and interval '0.001' second following) countmsg
      from
        t_NTG_Message a,
        t_NTG_Connection c
      where
        a.streamID = c.streamid and
        a.messagedirection ='I' and
        a.messagetype in ('D','F','q','G') and
        trunc(a.messagetimestamp) = trunc(c.logontime)) t
      where t.countmsg > 25) t2
      match_recognize(
        partition by gatewayIPADDRESS
        order by startinterval,  endinterval
        measures
          first(startinterval) startinterval,
              last(endinterval) endinterval
        pattern(A B*)
        define B as startinterval <= prev(endinterval)
        ) )
      

       

      And the following view to tie the message to the IP address of the gateway.

       

      Create View V_NTG_MESSAGE_BY_GATEWAY as 
      Select
        b.gatewayIPADDRESS,
        a.*
      from
        t_NTG_Message a,
        t_NTG_Connection b
      where
        a.streamID = b.streamid and
        a.connectionid = b.connectionid and
        trunc(a.messagetimestamp) = trunc(b.logontime)