This discussion is archived
2 Replies Latest reply: Apr 1, 2013 6:35 AM by jeneesh RSS

Types and Joins

Naveenkumar Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points