2 Replies Latest reply: Apr 1, 2013 8:35 AM by jeneesh RSS

    Types and Joins

    Naveenkumar
      Hi ,


      CREATE OR REPLACE TYPE smsc_MSG_SEQ_TT AS OBJECT (seq NUMBER(15))

      CREATE OR REPLACE TYPE t_smsc_MSG_SEQ_TT IS TABLE OF smsc_MSG_SEQ_TT

      -- Create table
      create table smsc_msg_retry
      (
      MSG_SEQ_NO NUMBER(15) primary key ,
      ORIG_MSISDN VARCHAR2(20) not null,
      ORIG_IMSI VARCHAR2(20),
      DEST_MSISDN VARCHAR2(20) not null,
      SC_TIMESTAMP DATE not null,
      VALIDITY_PERIOD DATE,
      SCHEDULED_DELIVERY DATE,
      DATA_CODING_SCHEME NUMBER(3) not null,
      PRIORITY NUMBER(3),
      SUBMIT_REPORT NUMBER(1) not null,
      REJECT_DUPLICATE NUMBER(1),
      REPLY_PATH NUMBER(3),
      UDH_INDICATOR NUMBER(3),
      MESSAGE_REFERNCE NUMBER(5),
      PROTOCOL_IDENTIFIER NUMBER(3),
      UD_LENGTH NUMBER(3) not null,
      USER_DATA VARCHAR2(300),
      MAP_VERSION NUMBER(1) not null,
      RETRY_ATTEMPT NUMBER(2),
      STATUS NUMBER(2),
      MSG_TEXT VARCHAR2(160),
      IS_CONTINUE NUMBER(1) not null,
      BILLING_STATUS NUMBER(1) default 0,
      PORTED_IN NUMBER(1) default 0,
      PORTED_OUT NUMBER(1) default 0,
      PREFIX VARCHAR2(10),
      INT_NAT_DEST NUMBER(1) default 0 not null,
      CAMPAIGN_SEQ VARCHAR2(7),
      ERROR_CODE NUMBER(5),
      INSTANCE_ID VARCHAR2(10) default 'ORCA1',
      OUTFLOW NUMBER(1) default 0,
      HUB_ID VARCHAR2(20),
      RETRY_INPROGRESS NUMBER(1) default 0,
      DEST_IMSI VARCHAR2(20),
      DEST_MSC VARCHAR2(20),
      DELIVERY_TIMESTAMP DATE,
      ESME_ID VARCHAR2(20),
      ALERT_MSISDN VARCHAR2(20),
      DELIV_FROM VARCHAR2(6),
      DELIV_TO VARCHAR2(6),
      ORIG_TON NUMBER(3),
      ORIG_NPI NUMBER(3),
      TIMEZONE NUMBER(2)
      )

      Below query getting high CPU cost.will this query create any performance impact?

      SELECT msg_seq_no, orig_ton, orig_npi,
      nvl(orig_msisdn, '') orig_msisdn, nvl(orig_imsi, '') orig_imsi,
      nvl(dest_msisdn, '') dest_msisdn,
      to_char(sc_timestamp, 'dd/mm/yyyy hh24:mi:ss') sc_timestamp,
      to_char(validity_period, 'dd/mm/yyyy hh24:mi:ss') validity_period,
      (scheduled_delivery - to_date('01-jan-1970', 'dd-mon-yyyy')) *
      (24 * 60 * 60) scheduled_delivery,
      nvl(data_coding_scheme, 0) data_coding_scheme,
      nvl(priority, 0) priority, nvl(submit_report, 0) submit_report,
      nvl(reject_duplicate, 0) reject_duplicate,
      nvl(reply_path, 0) reply_path,
      nvl(udh_indicator, 0) udh_indicator,
      nvl(message_refernce, 0) message_refernce,
      nvl(protocol_identifier, 0) protocol_identifier,
      nvl(ud_length, 0) ud_length, nvl(user_data, '') user_data,
      nvl(map_version, 0) map_version, retry_attempt, status,
      is_continue, ported_in, ported_out, prefix, int_nat_dest,
      outflow, hub_id, esme_id, ERROR_CODE,
      to_char(delivery_timestamp, 'dd/mm/yyyy hh24:mi:ss') delivery_timestamp,
      deliv_from, deliv_to, timezone
      FROM smsc_msg_retry
      WHERE msg_seq_no IN
      (SELECT seq
      FROM TABLE(CAST(l_t_smsc_msg_seq_tt AS t_smsc_msg_seq_tt)))


      the plan is
      1          6566     4/1/2013 4:34:53 PM          SELECT STATEMENT                                        ALL_ROWS          0          0     536     536     255     21675                         <Long>     <CLOB>          *25149921     *534                         7     
      2          6566     4/1/2013 4:34:53 PM          NESTED LOOPS                                                  1     0     1     1     536     255     21675                         <Long>     <CLOB>          25149921     534                    (#keys=0) "MSG_SEQ_NO"[NUMBER,22], "ORIG_MSISDN"[VARCHAR2,20], "ORIG_IMSI"[VARCHAR2,20], "DEST_MSISDN"[VARCHAR2,20], "SC_TIMESTAMP"[DATE,7], "VALIDITY_PERIOD"[DATE,7], "SCHEDULED_DELIVERY"[DATE,7], "DATA_CODING_SCHEME"[NUMBER,22], "PRIORITY"[NUMBER,22], "SUBMIT_REPORT"[NUMBER,22], "REJECT_DUPLICATE"[NUMBER,22], "REPLY_PATH"[NUMBER,22], "UDH_INDICATOR"[NUMBER,22], "MESSAGE_REFERNCE"[NUMBER,22], "PROTOCOL_IDENTIFIER"[NUMBER,22], "UD_LENGTH"[NUMBER,22], "USER_DATA"[VARCHAR2,300], "MAP_VERSION"[NUMBER,22], "RETRY_ATTEMPT"[NUMBER,22], "STATUS"[NUMBER,22], "IS_CONTINUE"[NUMBER,22], "PORTED_IN"[NUMBER,22], "PORTED_OUT"[NUMBER,22], "PREFIX"[VARCHAR2,10], "INT_NAT_DEST"[NUMBER,22], "ERROR_CODE"[NUMBER,22], "OUTFLOW"[NUMBER,22], "HUB_ID"[VARCHAR2,20], "DELIVERY_TIMESTAMP"[DATE,7], "ESME_ID"[VARCHAR2,20], "DELIV_FROM"[VARCHAR2,6], "DELIV_TO"[VARCHAR2,6], "ORIG_TON"[NUMBER,22], "ORIG_NPI"[NUMBER,22], "TIMEZONE"[NUMBER,22]     7     SEL$48F5A359
      3          6566     4/1/2013 4:34:53 PM          SORT     UNIQUE                                             2     1     2     1                                        <Long>     <CLOB>                                   (#keys=1) SYS_OP_ATG(VALUE(KOKBF$),1,2,2)[22]          
      4          6566     4/1/2013 4:34:53 PM          COLLECTION ITERATOR     PICKLER FETCH                                             3     2     3     1                                        <Long>     <CLOB>                                   VALUE(A0)[40]          
      5          6566     4/1/2013 4:34:53 PM          TABLE ACCESS     BY INDEX ROWID          SMSC     SMSC_MSG_RETRY     B@SEL$1     1     TABLE     ANALYZED          4     1     2     2     2     1     83                         <Long>     <CLOB>          17133     2                    "MSG_SEQ_NO"[NUMBER,22], "ORIG_MSISDN"[VARCHAR2,20], "ORIG_IMSI"[VARCHAR2,20], "DEST_MSISDN"[VARCHAR2,20], "SC_TIMESTAMP"[DATE,7], "VALIDITY_PERIOD"[DATE,7], "SCHEDULED_DELIVERY"[DATE,7], "DATA_CODING_SCHEME"[NUMBER,22], "PRIORITY"[NUMBER,22], "SUBMIT_REPORT"[NUMBER,22], "REJECT_DUPLICATE"[NUMBER,22], "REPLY_PATH"[NUMBER,22], "UDH_INDICATOR"[NUMBER,22], "MESSAGE_REFERNCE"[NUMBER,22], "PROTOCOL_IDENTIFIER"[NUMBER,22], "UD_LENGTH"[NUMBER,22], "USER_DATA"[VARCHAR2,300], "MAP_VERSION"[NUMBER,22], "RETRY_ATTEMPT"[NUMBER,22], "STATUS"[NUMBER,22], "IS_CONTINUE"[NUMBER,22], "PORTED_IN"[NUMBER,22], "PORTED_OUT"[NUMBER,22], "PREFIX"[VARCHAR2,10], "INT_NAT_DEST"[NUMBER,22], "ERROR_CODE"[NUMBER,22], "OUTFLOW"[NUMBER,22], "HUB_ID"[VARCHAR2,20], "DELIVERY_TIMESTAMP"[DATE,7], "ESME_ID"[VARCHAR2,20], "DELIV_FROM"[VARCHAR2,6], "DELIV_TO"[VARCHAR2,6], "ORIG_TON"[NUMBER,22], "ORIG_NPI"[NUMBER,22], "TIMEZONE"[NUMBER,22]     1     SEL$48F5A359
      6          6566     4/1/2013 4:34:53 PM          INDEX     UNIQUE SCAN          SMSC     SYS_C007222     B@SEL$1          INDEX (UNIQUE)     ANALYZED     1     5     4     3     1     1     1                              <Long>     <CLOB>          9021     1          "B"."MSG_SEQ_NO"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2)          "B".ROWID[ROWID,10], "MSG_SEQ_NO"[NUMBER,22]     1     SEL$48F5A359
      ___________________________________________________________________________________________________________________________________________________________

      what is the reason for getting full table scan in below query?

      SELECT msg_seq_no, orig_ton, orig_npi,
      nvl(orig_msisdn, '') orig_msisdn, nvl(orig_imsi, '') orig_imsi,
      nvl(dest_msisdn, '') dest_msisdn,
      to_char(sc_timestamp, 'dd/mm/yyyy hh24:mi:ss') sc_timestamp,
      to_char(validity_period, 'dd/mm/yyyy hh24:mi:ss') validity_period,
      (scheduled_delivery - to_date('01-jan-1970', 'dd-mon-yyyy')) *
      (24 * 60 * 60) scheduled_delivery,
      nvl(data_coding_scheme, 0) data_coding_scheme,
      nvl(priority, 0) priority, nvl(submit_report, 0) submit_report,
      nvl(reject_duplicate, 0) reject_duplicate,
      nvl(reply_path, 0) reply_path,
      nvl(udh_indicator, 0) udh_indicator,
      nvl(message_refernce, 0) message_refernce,
      nvl(protocol_identifier, 0) protocol_identifier,
      nvl(ud_length, 0) ud_length, nvl(user_data, '') user_data,
      nvl(map_version, 0) map_version, retry_attempt, status,
      is_continue, ported_in, ported_out, prefix, int_nat_dest,
      outflow, hub_id, esme_id, ERROR_CODE,
      to_char(delivery_timestamp, 'dd/mm/yyyy hh24:mi:ss') delivery_timestamp,
      deliv_from, deliv_to, timezone
      FROM smsc_msg_retry ,TABLE(CAST(l_t_smsc_msg_seq_tt AS t_smsc_msg_seq_tt)))
      WHERE msg_seq_no =seq


      1          6569     4/1/2013 5:56:58 PM          SELECT STATEMENT                                        ALL_ROWS          0          0     2279     2279     8168     694280                         <Long>     <CLOB>          *627098312     2234     *                    28     
      2          6569     4/1/2013 5:56:58 PM          HASH JOIN                                                  1     0     1     1     2279     8168     694280                         <Long>     <CLOB>          627098312     2234          "MSG_SEQ_NO"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2)          (#keys=1) "MSG_SEQ_NO"[NUMBER,22], "TIMEZONE"[NUMBER,22], "ORIG_MSISDN"[VARCHAR2,20], "ORIG_IMSI"[VARCHAR2,20], "DEST_MSISDN"[VARCHAR2,20], "SC_TIMESTAMP"[DATE,7], "VALIDITY_PERIOD"[DATE,7], "SCHEDULED_DELIVERY"[DATE,7], "DATA_CODING_SCHEME"[NUMBER,22], "PRIORITY"[NUMBER,22], "SUBMIT_REPORT"[NUMBER,22], "REJECT_DUPLICATE"[NUMBER,22], "REPLY_PATH"[NUMBER,22], "UDH_INDICATOR"[NUMBER,22], "MESSAGE_REFERNCE"[NUMBER,22], "PROTOCOL_IDENTIFIER"[NUMBER,22], "UD_LENGTH"[NUMBER,22], "USER_DATA"[VARCHAR2,300], "MAP_VERSION"[NUMBER,22], "RETRY_ATTEMPT"[NUMBER,22], "STATUS"[NUMBER,22], "IS_CONTINUE"[NUMBER,22], "PORTED_IN"[NUMBER,22], "PORTED_OUT"[NUMBER,22], "PREFIX"[VARCHAR2,10], "INT_NAT_DEST"[NUMBER,22], "ERROR_CODE"[NUMBER,22], "OUTFLOW"[NUMBER,22], "HUB_ID"[VARCHAR2,20], "DELIVERY_TIMESTAMP"[DATE,7], "ESME_ID"[VARCHAR2,20], "DELIV_FROM"[VARCHAR2,6], "DELIV_TO"[VARCHAR2,6], "ORIG_TON"[NUMBER,22], "ORIG_NPI"[NUMBER,22]     28     SEL$4A78348A
      3          6569     4/1/2013 5:56:58 PM          COLLECTION ITERATOR     PICKLER FETCH                                             2     1     2     1                                        <Long>     <CLOB>                                   VALUE(A0)[40]          
      4          6569     4/1/2013 5:56:58 PM          TABLE ACCESS     FULL          SMSC     SMSC_MSG_RETRY     SMSC_MSG_RETRY@SEL$1     1     TABLE     ANALYZED          3     1     2     2     2250     500000     41500000                         <Long>     <CLOB>          566905180     2210                    "MSG_SEQ_NO"[NUMBER,22], "ORIG_MSISDN"[VARCHAR2,20], "ORIG_IMSI"[VARCHAR2,20], "DEST_MSISDN"[VARCHAR2,20], "SC_TIMESTAMP"[DATE,7], "VALIDITY_PERIOD"[DATE,7], "SCHEDULED_DELIVERY"[DATE,7], "DATA_CODING_SCHEME"[NUMBER,22], "PRIORITY"[NUMBER,22], "SUBMIT_REPORT"[NUMBER,22], "REJECT_DUPLICATE"[NUMBER,22], "REPLY_PATH"[NUMBER,22], "UDH_INDICATOR"[NUMBER,22], "MESSAGE_REFERNCE"[NUMBER,22], "PROTOCOL_IDENTIFIER"[NUMBER,22], "UD_LENGTH"[NUMBER,22], "USER_DATA"[VARCHAR2,300], "MAP_VERSION"[NUMBER,22], "RETRY_ATTEMPT"[NUMBER,22], "STATUS"[NUMBER,22], "IS_CONTINUE"[NUMBER,22], "PORTED_IN"[NUMBER,22], "PORTED_OUT"[NUMBER,22], "PREFIX"[VARCHAR2,10], "INT_NAT_DEST"[NUMBER,22], "ERROR_CODE"[NUMBER,22], "OUTFLOW"[NUMBER,22], "HUB_ID"[VARCHAR2,20], "DELIVERY_TIMESTAMP"[DATE,7], "ESME_ID"[VARCHAR2,20], "DELIV_FROM"[VARCHAR2,6], "DELIV_TO"[VARCHAR2,6], "ORIG_TON"[NUMBER,22], "ORIG_NPI"[NUMBER,22], "TIMEZONE"[NUMBER,22]     28     SEL$4A78348A


      any suggestion Please?

      thanks in advance
      Naveen

      Edited by: Naveenkumar on Apr 1, 2013 5:55 PM