Home » RDBMS Server » Performance Tuning » Please help for tuning .sql query (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Please help for tuning .sql query [message #644241] Sun, 01 November 2015 08:26 Go to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi Expert,

Please help for tuning the view query:



select  *
from(
  SELECT          /*+ noPARALLEL(a) */
          DISTINCT x.dir, x.a_b_number, x.caller_name, x.alias_name,
                   x.call_date, x.call_time, x.call_type, x.duration_in_sec,
                   x.service_type, x.swich, asterminating_cell_site_id,
                   x.originating_cell_site_id, x.imie, x.imsi,
                   x.mobile_number, x.call_start_time, x.start_cell_id,
                   x.end_cell_id, x.start_lac_id, x.end_lac_id, lkp.latitude,
                   lkp.longitude
              FROM (SELECT /*+ ordered use_nl(a b)*/
                           call_start_time, 'In' dir,
                           a.dest_phone_nbr a_b_number,
                           NVL
                              ((SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN SUBSTR (a.phone_nbr, -9)
                                              ELSE '1'
                                           END
                                          )),
                               (SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN LPAD
                                                        (SUBSTR (a.phone_nbr,
                                                                 -9
                                                                ),
                                                         20,
                                                         '0'
                                                        )
                                              ELSE '1'
                                           END
                                          ))
                              ) AS caller_name,
                           NVL
                              ((SELECT alias_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN SUBSTR (a.phone_nbr, -9)
                                              ELSE '1'
                                           END
                                          )),
                               (SELECT alias_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN LPAD
                                                        (SUBSTR (a.phone_nbr,
                                                                 -9
                                                                ),
                                                         20,
                                                         '0'
                                                        )
                                              ELSE '1'
                                           END
                                          ))
                              ) AS alias_name,
                           TO_CHAR (a.call_start_time,
                                    'mm/dd/yyyy') call_date,
                           TO_CHAR (a.call_start_time,
                                    'hh24:mi:ss') call_time,
                           (CASE
                               WHEN INSTR (loading_from, '_msc', 1, 1) <> 0
                                  THEN CASE
                                         WHEN TRIM (a.call_type) = '3V'
                                            THEN 'Video Call'
                                         ELSE 'Voice Call'
                                      END
                               WHEN INSTR (loading_from, '_gprs', 1, 1) <> 0
                                  THEN CASE
                                         WHEN TRIM (a.call_type) = '3T'
                                            THEN 'TV Watch'
                                         WHEN TRIM (a.call_type) = '3I'
                                            THEN '3G internet'
                                         ELSE 'GPRS'
                                      END
                               WHEN INSTR (loading_from, '_mms', 1, 1) <> 0
                                  THEN 'MMS'
                               WHEN INSTR (loading_from, '_ota', 1, 1) <> 0
                                  THEN 'Abwab'
                               WHEN INSTR (loading_from, '_lbs', 1, 1) <> 0
                                  THEN 'LBS'
                               WHEN INSTR (loading_from, '_sms', 1, 1) <> 0
                                  THEN CASE
                                         WHEN TRIM (a.call_type) = 'SB'
                                            THEN 'bulck SMS'
                                         WHEN (TRIM (a.call_type) = 'S1')
                                          OR (TRIM (a.call_type) = 'S3')
                                          OR (   TRIM (a.call_type) = 'S5'
                                              OR (TRIM (a.call_type) = 'S7')
                                             )
                                            THEN 'SMS'
                                         WHEN (TRIM (a.call_type) = 'S2')
                                          OR (TRIM (a.call_type) = 'S4')
                                          OR (   TRIM (a.call_type) = 'S6'
                                              OR (TRIM (a.call_type) = 'S8')
                                             )
                                            THEN 'Confirmation SMS'
                                         WHEN TRIM (a.call_type) = 'SM'
                                            THEN 'Interactive SMS'
                                         WHEN TRIM (a.call_type) = 'R2'
                                            THEN 'Confirmation SMS - Saudi Roamer'
                                         WHEN TRIM (a.call_type) = 'D1'
                                            THEN 'SMS from 905'
                                         ELSE 'SMS'
                                      END
                               ELSE a.call_type
                            END
                           ) AS call_type,
                           ((talk_minutes * 60) + talk_seconds
                           ) duration_in_sec,
                           (CASE
                               WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'I'
                                  THEN 'International'
                               WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'G'
                                  THEN 'ALJAWAL'
                               WHEN a.call_type = 'PL' OR a.call_type = 'PG'
                                  THEN 'ALJAWAL'
                               WHEN TRIM (a.call_type) IN
                                      ('S1', 'S2', 'S3', 'S4', 'S5', 'S6',
                                       'S7', 'S8')
                                  THEN 'ALJAWAL'
                               WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'Q'
                                  THEN 'MOBILY'
                               WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'L'
                                  THEN 'Land Line'
                               WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'M'
                                  THEN 'Call to Operator'
                               WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'F'
                                  THEN 'Call to 800'
                               WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'R'
                                  THEN 'JAWALNET'
                               ELSE a.call_type
                            END
                           ) service_type,
                              a.hdr_file_seq_nbr_part1
                           || a.hdr_file_seq_nbr_part2
                           || a.hdr_file_seq_nbr_part3 swich,
                           a.last_calling_location asterminating_cell_site_id,
                           a.first_calling_location originating_cell_site_id,
                           a.calling_imei imie, a.calling_imsi imsi,
                           phone_nbr mobile_number, a.start_cell_id,
                           a.end_cell_id, a.start_lac_id, a.end_lac_id
                      /*Added as part of 6.7 CR Alshamel system*/
                    FROM   
-----------------------------------------------------------------------------------------------------------------
                           (SELECT /*+ noPARALLEL(cdr)  noPARALLEL_INDEX(cdr POSTMED_CDR_DEST_PHONE_NBR_IX) noPARALLEL_INDEX(cdr POSTMED_CDR_PHONE_NBR_IX)*/
                                   hdr_rec_type, hdr_create_date,
                                   hdr_file_seq_nbr_part1,
                                   hdr_file_seq_nbr_part2,
                                   hdr_file_seq_nbr_part3, cdr_record_type,
                                   phone_nbr, dest_phone_nbr, bill_svc_code,
                                   bill_phone_nbr, call_start_time,
                                   talk_minutes, talk_seconds, call_type,
                                   calling_imsi, calling_imei, dissconn_party,
                                   time_stop_charge, interruption_time,
                                   time_register_to_charge, charged_party,
                                   exchange_identity, msc_id, outgoing_route,
                                   incomming_route, data_volume,
                                   originating_loc_num,
                                   first_calling_location,
                                   last_calling_location, fault_code,
                                   switch_id, translated_num, msc_address,
                                   cdr_type, msisdn, cell_id,
                                   cause_for_record_closing,
                                   message_submission, call_reference,
                                   message_length, originating_msc,
                                   call_amount1, call_amount2,
                                   jurisdiction_of_usage, repriced_ind,
                                   original_called_num, first_called_location,
                                   last_called_location, destination_address,
                                   sms_result, chargable_duration,
                                   loading_date, loading_from, start_cell_id,
                                   end_cell_id, start_lac_id, end_lac_id
                              FROM dm_exa_dwh.postmed_cdr cdr) a
                    UNION ALL
-----------------------------------------------------------------------------------------------------------
                    SELECT call_start_time, 'Out' dir, a.phone_nbr a_b_number,
                           NVL
                              ((SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN SUBSTR
                                                            (a.dest_phone_nbr,
                                                             -9
                                                            )
                                              ELSE '1'
                                           END
                                          )),
                               (SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN LPAD
                                                        (SUBSTR
                                                            (a.dest_phone_nbr,
                                                             -9
                                                            ),
                                                         20,
                                                         '0'
                                                        )
                                              ELSE '1'
                                           END
                                          ))
                              ) AS caller_name,
                           NVL
                              ((SELECT alias_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN SUBSTR
                                                            (a.dest_phone_nbr,
                                                             -9
                                                            )
                                              ELSE '1'
                                           END
                                          )),
                               (SELECT alias_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN LPAD
                                                        (SUBSTR
                                                            (a.dest_phone_nbr,
                                                             -9
                                                            ),
                                                         20,
                                                         '0'
                                                        )
                                              ELSE '1'
                                           END
                                          ))
                              ) AS alias_name,
                           TO_CHAR (a.call_start_time,
                                    'mm/dd/yyyy') call_date,
                           TO_CHAR (a.call_start_time,
                                    'hh24:mi:ss') call_time,
                           (CASE
                               WHEN INSTR (loading_from, '_msc', 1, 1) <> 0
                                  THEN CASE
                                         WHEN TRIM (a.call_type) = '3V'
                                            THEN 'Video Call'
                                         ELSE 'Voice Call'
                                      END
                               WHEN INSTR (loading_from, '_gprs', 1, 1) <> 0
                                  THEN CASE
                                         WHEN TRIM (a.call_type) = '3T'
                                            THEN 'TV Watch'
                                         WHEN TRIM (a.call_type) = '3I'
                                            THEN '3G internet'
                                         ELSE 'GPRS'
                                      END
                               WHEN INSTR (loading_from, '_mms', 1, 1) <> 0
                                  THEN 'MMS'
                               WHEN INSTR (loading_from, '_ota', 1, 1) <> 0
                                  THEN 'Abwab'
                               WHEN INSTR (loading_from, '_lbs', 1, 1) <> 0
                                  THEN 'LBS'
                               WHEN INSTR (loading_from, '_sms', 1, 1) <> 0
                                  THEN CASE
                                         WHEN TRIM (a.call_type) = 'SB'
                                            THEN 'bulck SMS'
                                         WHEN (TRIM (a.call_type) = 'S1')
                                          OR (TRIM (a.call_type) = 'S3')
                                          OR (   TRIM (a.call_type) = 'S5'
                                              OR (TRIM (a.call_type) = 'S7')
                                             )
                                            THEN 'SMS'
                                         WHEN (TRIM (a.call_type) = 'S2')
                                          OR (TRIM (a.call_type) = 'S4')
                                          OR (   TRIM (a.call_type) = 'S6'
                                              OR (TRIM (a.call_type) = 'S8')
                                             )
                                            THEN 'Confirmation SMS'
                                         WHEN TRIM (a.call_type) = 'SM'
                                            THEN 'Interactive SMS'
                                         WHEN TRIM (a.call_type) = 'R2'
                                            THEN 'Confirmation SMS - Saudi Roamer'
                                         WHEN TRIM (a.call_type) = 'D1'
                                            THEN 'SMS from 905'
                                         ELSE 'SMS'
                                      END
                               ELSE a.call_type
                            END
                           ) AS call_type,
                           ((talk_minutes * 60) + talk_seconds
                           ) duration_in_sec,
                           (CASE
                               WHEN SUBSTR (TRIM (a.call_type), 1, 1) = 'I'
                                  THEN 'International'
                               WHEN SUBSTR (TRIM (a.call_type), 1, 1) = 'G'
                                  THEN 'ALJAWAL'
                               WHEN a.call_type = 'PL' OR a.call_type = 'PG'
                                  THEN 'ALJAWAL'
                               WHEN TRIM (a.call_type) IN
                                         ('S1', 'S2', 'S3', 'S4', 'S5', 'S6')
                                  THEN 'ALJAWAL'
                               WHEN SUBSTR (TRIM (a.call_type), 1, 1) = 'Q'
                                  THEN 'MOBILY'
                               WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'Q'
                                  THEN 'ALJAWAL'
                               WHEN SUBSTR (TRIM (a.call_type), 1, 1) = 'L'
                                  THEN 'Land Line'
                               WHEN TRIM (a.call_type) IN ('MG', 'MI')
                                  THEN 'Mobile Internet Services'
                               WHEN TRIM (a.call_type) = 'MA'
                                  THEN 'Mobile Application'
                               ELSE a.call_type
                            END
                           ) service_type,
                              a.hdr_file_seq_nbr_part1
                           || a.hdr_file_seq_nbr_part2
                           || a.hdr_file_seq_nbr_part3 swich,
                           a.last_calling_location asterminating_cell_site_id,
                           a.first_calling_location originating_cell_site_id,
                           a.calling_imei imie, a.calling_imsi imsi,
                           dest_phone_nbr mobile_number, a.start_cell_id,
                           a.end_cell_id, a.start_lac_id, a.end_lac_id
                      /*added as part of 6.7 CR alshamel system*/
                    FROM   (SELECT /*+ noPARALLEL(cdr)  noPARALLEL_INDEX(cdr POSTMED_CDR_DEST_PHONE_NBR_IX) noPARALLEL_INDEX(cdr POSTMED_CDR_PHONE_NBR_IX)*/
                                   hdr_rec_type, hdr_create_date,
                                   hdr_file_seq_nbr_part1,
                                   hdr_file_seq_nbr_part2,
                                   hdr_file_seq_nbr_part3, cdr_record_type,
                                   phone_nbr, dest_phone_nbr, bill_svc_code,
                                   bill_phone_nbr, call_start_time,
                                   talk_minutes, talk_seconds, call_type,
                                   calling_imsi, calling_imei, dissconn_party,
                                   time_stop_charge, interruption_time,
                                   time_register_to_charge, charged_party,
                                   exchange_identity, msc_id, outgoing_route,
                                   incomming_route, data_volume,
                                   originating_loc_num,
                                   first_calling_location,
                                   last_calling_location, fault_code,
                                   switch_id, translated_num, msc_address,
                                   cdr_type, msisdn, cell_id,
                                   cause_for_record_closing,
                                   message_submission, call_reference,
                                   message_length, originating_msc,
                                   call_amount1, call_amount2,
                                   jurisdiction_of_usage, repriced_ind,
                                   original_called_num, first_called_location,
                                   last_called_location, destination_address,
                                   sms_result, chargable_duration,
                                   loading_date, loading_from, start_cell_id,
                                   end_cell_id, start_lac_id, end_lac_id
                              FROM dm_exa_dwh.postmed_cdr cdr) a
                    UNION ALL
-------------------------------------------------------------------------------------------------------
                    SELECT /*+ noPARALLEL(a) ordered use_nl(a b c)  noPARALLEL_INDEX(a PST_CDR_LL_B_NUM_IX) */
                           call_start_time, 'Out' dir, a.a_number a_b_number,
                           NVL
                              ((SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN SUBSTR (TRIM (b_number),
                                                           1,
                                                           2
                                                          ) <> '00'
                                                 THEN SUBSTR (a.b_number, -9)
                                              ELSE '1'
                                           END
                                          )),
                               (SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN SUBSTR (TRIM (b_number),
                                                           1,
                                                           2
                                                          ) <> '00'
                                                 THEN LPAD
                                                         (SUBSTR (a.b_number,
                                                                  -9
                                                                 ),
                                                          20,
                                                          '0'
                                                         )
                                              ELSE '1'
                                           END
                                          ))
                              ) AS caller_name,
                           NVL
                              ((SELECT alias_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN SUBSTR (TRIM (b_number),
                                                           1,
                                                           2
                                                          ) <> '00'
                                                 THEN SUBSTR (a.b_number, -9)
                                              ELSE '1'
                                           END
                                          )),
                               (SELECT alias_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN SUBSTR (TRIM (b_number),
                                                           1,
                                                           2
                                                          ) <> '00'
                                                 THEN LPAD
                                                         (SUBSTR (a.b_number,
                                                                  -9
                                                                 ),
                                                          20,
                                                          '0'
                                                         )
                                              ELSE '1'
                                           END
                                          ))
                              ) AS alias_name,
                           TO_CHAR (a.call_start_time,
                                    'mm/dd/yyyy') call_date,
                           TO_CHAR (a.call_start_time,
                                    'hh24:mi:ss') call_time,
                           NULL AS call_type,
                             (  SUBSTR (TRIM (chargeable_duration), 1, 2)
                              * 60
                              * 60
                             )
                           + (SUBSTR (TRIM (chargeable_duration), 4, 2) * 60)
                           + SUBSTR (TRIM (chargeable_duration), 7, 2)
                                                              duration_in_sec,
                           NULL service_type, NULL swich,
                           NULL asterminating_cell_site_id,
                           NULL originating_cell_site_id, NULL imie,
                           NULL imsi, b_number mobile_number,
                           NULL start_cell_id, NULL end_cell_id,
                           NULL start_lac_id, NULL end_lac_id
                      FROM dm_exa_dwh.pst_ll_cdr a
                    UNION ALL
-------------------------------------------------------------------------------------------------------
                    SELECT /*+ noPARALLEL(a) ordered use_nl(a b c)  noPARALLEL_INDEX(a PST_CDR_LL_B_NUM_IX) */
                           call_start_time, 'In' dir, a.b_number a_b_number,
                           NVL
                              ((SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN SUBSTR (TRIM (a_number),
                                                           1,
                                                           2
                                                          ) <> '00'
                                                 THEN SUBSTR (a.a_number, -9)
                                              ELSE '1'
                                           END
                                          )),
                               (SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN SUBSTR (TRIM (a_number),
                                                           1,
                                                           2
                                                          ) <> '00'
                                                 THEN LPAD
                                                         (SUBSTR (a.a_number,
                                                                  -9
                                                                 ),
                                                          20,
                                                          '0'
                                                         )
                                              ELSE '1'
                                           END
                                          ))
                              ) AS caller_name,
                           NVL
                              ((SELECT alias_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN SUBSTR (TRIM (a_number),
                                                           1,
                                                           2
                                                          ) <> '00'
                                                 THEN SUBSTR (a.a_number, -9)
                                              ELSE '1'
                                           END
                                          )),
                               (SELECT alias_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN SUBSTR (TRIM (a_number),
                                                           1,
                                                           2
                                                          ) <> '00'
                                                 THEN LPAD
                                                         (SUBSTR (a.a_number,
                                                                  -9
                                                                 ),
                                                          20,
                                                          '0'
                                                         )
                                              ELSE '1'
                                           END
                                          ))
                              ) AS alias_name,
                           TO_CHAR (a.call_start_time,
                                    'mm/dd/yyyy') call_date,
                           TO_CHAR (a.call_start_time,
                                    'hh24:mi:ss') call_time,
                           NULL AS call_type,
                             (  SUBSTR (TRIM (chargeable_duration), 1, 2)
                              * 60
                              * 60
                             )
                           + (SUBSTR (TRIM (chargeable_duration), 4, 2) * 60)
                           + SUBSTR (TRIM (chargeable_duration), 7, 2)
                                                              duration_in_sec,
                           NULL service_type, NULL swich,
                           NULL asterminating_cell_site_id,
                           NULL originating_cell_site_id, NULL imie,
                           NULL imsi, a_number mobile_number,
                           NULL start_cell_id, NULL end_cell_id,
                           NULL start_lac_id, NULL end_lac_id
                      FROM dm_exa_dwh.pst_ll_cdr a) x,
                   abinitio_reports.postmed_coordinate_lookup lkp
             -- dm_exa_dwh.postmed_cdr cdr
   WHERE           a_b_number NOT LIKE '%559692565'
               AND mobile_number NOT LIKE '%559692565'
               AND a_b_number NOT LIKE '%014404011'
               AND mobile_number NOT LIKE '%014404011'
               AND x.start_cell_id = lkp.cell_id(+)
               AND x.start_lac_id = lkp.lac_id(+)
  )
  where   ----mobile_number = '503415880'
caLL_START_TIME BETWEEN TO_DATE('2015-09-29','yyyy-mm-dd') 
AND TO_DATE('2015-10-31','yyyy-mm-dd')+1 AND a_b_number IN('504000000','0504000000')

Explain Plan:

PLAN_TABLE_OUTPUT

Plan hash value: 858624757
 
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                                |    11 | 14806 |     6  (17)| 00:00:01 |        |      |
|   1 |  VIEW                                    | DWH_SHAMEL_CDR_VW              |    11 | 14806 |     6  (17)| 00:00:01 |        |      |
|   2 |   HASH UNIQUE                            |                                |    11 | 13783 |     6  (17)| 00:00:01 |        |      |
|   3 |    NESTED LOOPS OUTER                    |                                |    11 | 13783 |     5   (0)| 00:00:01 |        |      |
|   4 |     VIEW                                 |                                |    11 | 13442 |     4   (0)| 00:00:01 |        |      |
|   5 |      UNION-ALL                           |                                |       |       |            |          |        |      |
|   6 |       VIEW                               | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
|   7 |        UNION-ALL                         |                                |       |       |            |          |        |      |
|   8 |         TABLE ACCESS BY INDEX ROWID      | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*  9 |          INDEX UNIQUE SCAN               | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|  10 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
|  11 |          NESTED LOOPS                    |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|* 12 |           TABLE ACCESS BY INDEX ROWID    | S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|* 13 |            INDEX RANGE SCAN              | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|* 14 |           INDEX UNIQUE SCAN              | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 15 |          TABLE ACCESS BY INDEX ROWID     | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
|  16 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
|  17 |          NESTED LOOPS                    |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|* 18 |           TABLE ACCESS BY INDEX ROWID    | CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|* 19 |            INDEX RANGE SCAN              | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|* 20 |           INDEX RANGE SCAN               | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 21 |          TABLE ACCESS BY INDEX ROWID     | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
|  22 |           VIEW                           | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
|  23 |            UNION-ALL                     |                                |       |       |            |          |        |      |
|  24 |             TABLE ACCESS BY INDEX ROWID  | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|* 25 |              INDEX UNIQUE SCAN           | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|  26 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
|  27 |              NESTED LOOPS                |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|* 28 |               TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|* 29 |                INDEX RANGE SCAN          | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|* 30 |               INDEX UNIQUE SCAN          | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 31 |              TABLE ACCESS BY INDEX ROWID | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
|  32 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
|  33 |              NESTED LOOPS                |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|* 34 |               TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|* 35 |                INDEX RANGE SCAN          | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|* 36 |               INDEX RANGE SCAN           | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 37 |              TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
|  38 |       VIEW                               | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
|  39 |        UNION-ALL                         |                                |       |       |            |          |        |      |
|  40 |         TABLE ACCESS BY INDEX ROWID      | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|* 41 |          INDEX UNIQUE SCAN               | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|  42 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
|  43 |          NESTED LOOPS                    |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|* 44 |           TABLE ACCESS BY INDEX ROWID    | S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|* 45 |            INDEX RANGE SCAN              | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|* 46 |           INDEX UNIQUE SCAN              | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 47 |          TABLE ACCESS BY INDEX ROWID     | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
|  48 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
|  49 |          NESTED LOOPS                    |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|* 50 |           TABLE ACCESS BY INDEX ROWID    | CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|* 51 |            INDEX RANGE SCAN              | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|* 52 |           INDEX RANGE SCAN               | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 53 |          TABLE ACCESS BY INDEX ROWID     | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
|  54 |           VIEW                           | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
|  55 |            UNION-ALL                     |                                |       |       |            |          |        |      |
|  56 |             TABLE ACCESS BY INDEX ROWID  | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|* 57 |              INDEX UNIQUE SCAN           | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|  58 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
|  59 |              NESTED LOOPS                |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|* 60 |               TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|* 61 |                INDEX RANGE SCAN          | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|* 62 |               INDEX UNIQUE SCAN          | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 63 |              TABLE ACCESS BY INDEX ROWID | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
|  64 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
|  65 |              NESTED LOOPS                |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|* 66 |               TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|* 67 |                INDEX RANGE SCAN          | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|* 68 |               INDEX RANGE SCAN           | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 69 |              TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
|  70 |       REMOTE                             | POSTMED_CDR                    |     1 |   338 |     1   (0)| 00:00:01 | DM_EX~ | R->S |
|  71 |       VIEW                               | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
|  72 |        UNION-ALL                         |                                |       |       |            |          |        |      |
|  73 |         TABLE ACCESS BY INDEX ROWID      | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|* 74 |          INDEX UNIQUE SCAN               | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|  75 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
|  76 |          NESTED LOOPS                    |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|* 77 |           TABLE ACCESS BY INDEX ROWID    | S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|* 78 |            INDEX RANGE SCAN              | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|* 79 |           INDEX UNIQUE SCAN              | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 80 |          TABLE ACCESS BY INDEX ROWID     | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
|  81 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
|  82 |          NESTED LOOPS                    |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|* 83 |           TABLE ACCESS BY INDEX ROWID    | CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|* 84 |            INDEX RANGE SCAN              | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|* 85 |           INDEX RANGE SCAN               | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 86 |          TABLE ACCESS BY INDEX ROWID     | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
|  87 |           VIEW                           | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
|  88 |            UNION-ALL                     |                                |       |       |            |          |        |      |
|  89 |             TABLE ACCESS BY INDEX ROWID  | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|* 90 |              INDEX UNIQUE SCAN           | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|  91 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
|  92 |              NESTED LOOPS                |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|* 93 |               TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|* 94 |                INDEX RANGE SCAN          | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|* 95 |               INDEX UNIQUE SCAN          | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 96 |              TABLE ACCESS BY INDEX ROWID | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
|  97 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
|  98 |              NESTED LOOPS                |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|* 99 |               TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*100 |                INDEX RANGE SCAN          | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*101 |               INDEX RANGE SCAN           | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*102 |              TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 103 |       VIEW                               | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 104 |        UNION-ALL                         |                                |       |       |            |          |        |      |
| 105 |         TABLE ACCESS BY INDEX ROWID      | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*106 |          INDEX UNIQUE SCAN               | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 107 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 108 |          NESTED LOOPS                    |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*109 |           TABLE ACCESS BY INDEX ROWID    | S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*110 |            INDEX RANGE SCAN              | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*111 |           INDEX UNIQUE SCAN              | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*112 |          TABLE ACCESS BY INDEX ROWID     | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 113 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 114 |          NESTED LOOPS                    |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*115 |           TABLE ACCESS BY INDEX ROWID    | CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*116 |            INDEX RANGE SCAN              | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*117 |           INDEX RANGE SCAN               | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*118 |          TABLE ACCESS BY INDEX ROWID     | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 119 |           VIEW                           | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 120 |            UNION-ALL                     |                                |       |       |            |          |        |      |
| 121 |             TABLE ACCESS BY INDEX ROWID  | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*122 |              INDEX UNIQUE SCAN           | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 123 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 124 |              NESTED LOOPS                |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*125 |               TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*126 |                INDEX RANGE SCAN          | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*127 |               INDEX UNIQUE SCAN          | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*128 |              TABLE ACCESS BY INDEX ROWID | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 129 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 130 |              NESTED LOOPS                |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*131 |               TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*132 |                INDEX RANGE SCAN          | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*133 |               INDEX RANGE SCAN           | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*134 |              TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 135 |       REMOTE                             | POSTMED_CDR                    |     1 |   338 |     1   (0)| 00:00:01 | DM_EX~ | R->S |
| 136 |       VIEW                               | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 137 |        UNION-ALL                         |                                |       |       |            |          |        |      |
| 138 |         TABLE ACCESS BY INDEX ROWID      | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*139 |          INDEX UNIQUE SCAN               | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 140 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 141 |          NESTED LOOPS                    |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*142 |           TABLE ACCESS BY INDEX ROWID    | S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*143 |            INDEX RANGE SCAN              | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*144 |           INDEX UNIQUE SCAN              | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*145 |          TABLE ACCESS BY INDEX ROWID     | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 146 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 147 |          NESTED LOOPS                    |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*148 |           TABLE ACCESS BY INDEX ROWID    | CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*149 |            INDEX RANGE SCAN              | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*150 |           INDEX RANGE SCAN               | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*151 |          TABLE ACCESS BY INDEX ROWID     | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 152 |           VIEW                           | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 153 |            UNION-ALL                     |                                |       |       |            |          |        |      |
| 154 |             TABLE ACCESS BY INDEX ROWID  | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*155 |              INDEX UNIQUE SCAN           | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 156 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 157 |              NESTED LOOPS                |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*158 |               TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*159 |                INDEX RANGE SCAN          | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*160 |               INDEX UNIQUE SCAN          | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*161 |              TABLE ACCESS BY INDEX ROWID | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 162 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 163 |              NESTED LOOPS                |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*164 |               TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*165 |                INDEX RANGE SCAN          | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*166 |               INDEX RANGE SCAN           | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*167 |              TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 168 |       VIEW                               | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 169 |        UNION-ALL                         |                                |       |       |            |          |        |      |
| 170 |         TABLE ACCESS BY INDEX ROWID      | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*171 |          INDEX UNIQUE SCAN               | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 172 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 173 |          NESTED LOOPS                    |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*174 |           TABLE ACCESS BY INDEX ROWID    | S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*175 |            INDEX RANGE SCAN              | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*176 |           INDEX UNIQUE SCAN              | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*177 |          TABLE ACCESS BY INDEX ROWID     | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 178 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 179 |          NESTED LOOPS                    |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*180 |           TABLE ACCESS BY INDEX ROWID    | CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*181 |            INDEX RANGE SCAN              | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*182 |           INDEX RANGE SCAN               | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*183 |          TABLE ACCESS BY INDEX ROWID     | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 184 |           VIEW                           | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 185 |            UNION-ALL                     |                                |       |       |            |          |        |      |
| 186 |             TABLE ACCESS BY INDEX ROWID  | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*187 |              INDEX UNIQUE SCAN           | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 188 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 189 |              NESTED LOOPS                |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*190 |               TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*191 |                INDEX RANGE SCAN          | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*192 |               INDEX UNIQUE SCAN          | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*193 |              TABLE ACCESS BY INDEX ROWID | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 194 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 195 |              NESTED LOOPS                |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*196 |               TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*197 |                INDEX RANGE SCAN          | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*198 |               INDEX RANGE SCAN           | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*199 |              TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 200 |       REMOTE                             | PST_LL_CDR                     |     5 |   455 |     1   (0)| 00:00:01 | DM_EX~ | R->S |
| 201 |       VIEW                               | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 202 |        UNION-ALL                         |                                |       |       |            |          |        |      |
| 203 |         TABLE ACCESS BY INDEX ROWID      | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*204 |          INDEX UNIQUE SCAN               | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 205 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 206 |          NESTED LOOPS                    |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*207 |           TABLE ACCESS BY INDEX ROWID    | S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*208 |            INDEX RANGE SCAN              | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*209 |           INDEX UNIQUE SCAN              | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*210 |          TABLE ACCESS BY INDEX ROWID     | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 211 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 212 |          NESTED LOOPS                    |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*213 |           TABLE ACCESS BY INDEX ROWID    | CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*214 |            INDEX RANGE SCAN              | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*215 |           INDEX RANGE SCAN               | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*216 |          TABLE ACCESS BY INDEX ROWID     | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 217 |           VIEW                           | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 218 |            UNION-ALL                     |                                |       |       |            |          |        |      |
| 219 |             TABLE ACCESS BY INDEX ROWID  | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*220 |              INDEX UNIQUE SCAN           | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 221 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 222 |              NESTED LOOPS                |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*223 |               TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*224 |                INDEX RANGE SCAN          | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*225 |               INDEX UNIQUE SCAN          | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*226 |              TABLE ACCESS BY INDEX ROWID | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 227 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 228 |              NESTED LOOPS                |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*229 |               TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*230 |                INDEX RANGE SCAN          | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*231 |               INDEX RANGE SCAN           | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*232 |              TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 233 |       VIEW                               | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 234 |        UNION-ALL                         |                                |       |       |            |          |        |      |
| 235 |         TABLE ACCESS BY INDEX ROWID      | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*236 |          INDEX UNIQUE SCAN               | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 237 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 238 |          NESTED LOOPS                    |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*239 |           TABLE ACCESS BY INDEX ROWID    | S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*240 |            INDEX RANGE SCAN              | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*241 |           INDEX UNIQUE SCAN              | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*242 |          TABLE ACCESS BY INDEX ROWID     | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 243 |         NESTED LOOPS                     |                                |       |       |            |          |        |      |
| 244 |          NESTED LOOPS                    |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*245 |           TABLE ACCESS BY INDEX ROWID    | CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*246 |            INDEX RANGE SCAN              | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*247 |           INDEX RANGE SCAN               | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*248 |          TABLE ACCESS BY INDEX ROWID     | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 249 |           VIEW                           | SHAMEL_CDR_NAME_VW             |     2 |   980 |     4   (0)| 00:00:01 |        |      |
| 250 |            UNION-ALL                     |                                |       |       |            |          |        |      |
| 251 |             TABLE ACCESS BY INDEX ROWID  | S_ORG_EXT                      |     1 |    48 |     1   (0)| 00:00:01 |        |      |
|*252 |              INDEX UNIQUE SCAN           | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
| 253 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 254 |              NESTED LOOPS                |                                |     1 |    90 |     2   (0)| 00:00:01 |        |      |
|*255 |               TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI                    |     1 |    54 |     1   (0)| 00:00:01 |        |      |
|*256 |                INDEX RANGE SCAN          | SERIAL_NUM_INDEX1              |     6 |       |     1   (0)| 00:00:01 |        |      |
|*257 |               INDEX UNIQUE SCAN          | S_ORG_EXT_PK                   |     1 |       |     1   (0)| 00:00:01 |        |      |
|*258 |              TABLE ACCESS BY INDEX ROWID | S_ORG_EXT                      |     1 |    36 |     1   (0)| 00:00:01 |        |      |
| 259 |             NESTED LOOPS                 |                                |       |       |            |          |        |      |
| 260 |              NESTED LOOPS                |                                |     1 |   161 |     2   (0)| 00:00:01 |        |      |
|*261 |               TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL    |     1 |    39 |     1   (0)| 00:00:01 |        |      |
|*262 |                INDEX RANGE SCAN          | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX |     2 |       |     1   (0)| 00:00:01 |        |      |
|*263 |               INDEX RANGE SCAN           | CUSTOMER_CMMAST_01_CUST_NO_IX  |     1 |       |     1   (0)| 00:00:01 |        |      |
|*264 |              TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00              |     1 |   122 |     1   (0)| 00:00:01 |        |      |
| 265 |       REMOTE                             | PST_LL_CDR                     |     4 |   364 |     1   (0)| 00:00:01 | DM_EX~ | R->S |
| 266 |     TABLE ACCESS BY INDEX ROWID          | POSTMED_COORDINATE_LOOKUP      |     1 |    31 |     1   (0)| 00:00:01 |        |      |
|*267 |      INDEX RANGE SCAN                    | POSTMED_COORDINATE_CELL_LAC_IX |     1 |       |     1   (0)| 00:00:01 |        |      |
-------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - access("X"."ROW_ID"=:B1)
  12 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
  13 - access("A"."SERIAL_NUM"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              SUBSTR(:B4,(-9)) ELSE '1' END )
  14 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
  15 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
  18 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
  19 - access("B"."ACCESS_NUMBER"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              SUBSTR(:B4,(-9)) ELSE '1' END )
  20 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
  21 - filter("C"."EXPIRY_DATE">SYSDATE@!)
  25 - access("X"."ROW_ID"=:B1)
  28 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
  29 - access("A"."SERIAL_NUM"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
  30 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
  31 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
  34 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
  35 - access("B"."ACCESS_NUMBER"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
  36 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
  37 - filter("C"."EXPIRY_DATE">SYSDATE@!)
  41 - access("X"."ROW_ID"=:B1)
  44 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
  45 - access("A"."SERIAL_NUM"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              SUBSTR(:B4,(-9)) ELSE '1' END )
  46 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
  47 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
  50 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
  51 - access("B"."ACCESS_NUMBER"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              SUBSTR(:B4,(-9)) ELSE '1' END )
  52 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
  53 - filter("C"."EXPIRY_DATE">SYSDATE@!)
  57 - access("X"."ROW_ID"=:B1)
  60 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
  61 - access("A"."SERIAL_NUM"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
  62 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
  63 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
  66 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
  67 - access("B"."ACCESS_NUMBER"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
  68 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
  69 - filter("C"."EXPIRY_DATE">SYSDATE@!)
  74 - access("X"."ROW_ID"=:B1)
  77 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
  78 - access("A"."SERIAL_NUM"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              SUBSTR(:B4,(-9)) ELSE '1' END )
  79 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
  80 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
  83 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
  84 - access("B"."ACCESS_NUMBER"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              SUBSTR(:B4,(-9)) ELSE '1' END )
  85 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
  86 - filter("C"."EXPIRY_DATE">SYSDATE@!)
  90 - access("X"."ROW_ID"=:B1)
  93 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
  94 - access("A"."SERIAL_NUM"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
  95 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
  96 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
  99 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 100 - access("B"."ACCESS_NUMBER"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
 101 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 102 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 106 - access("X"."ROW_ID"=:B1)
 109 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 110 - access("A"."SERIAL_NUM"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              SUBSTR(:B4,(-9)) ELSE '1' END )
 111 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 112 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 115 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 116 - access("B"."ACCESS_NUMBER"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              SUBSTR(:B4,(-9)) ELSE '1' END )
 117 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 118 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 122 - access("X"."ROW_ID"=:B1)
 125 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 126 - access("A"."SERIAL_NUM"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
 127 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 128 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 131 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 132 - access("B"."ACCESS_NUMBER"=CASE  WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN 
              LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
 133 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 134 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 139 - access("X"."ROW_ID"=:B1)
 142 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 143 - access("A"."SERIAL_NUM"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
 144 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 145 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 148 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 149 - access("B"."ACCESS_NUMBER"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
 150 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 151 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 155 - access("X"."ROW_ID"=:B1)
 158 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 159 - access("A"."SERIAL_NUM"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
 160 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 161 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 164 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 165 - access("B"."ACCESS_NUMBER"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
 166 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 167 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 171 - access("X"."ROW_ID"=:B1)
 174 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 175 - access("A"."SERIAL_NUM"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
 176 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 177 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 180 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 181 - access("B"."ACCESS_NUMBER"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
 182 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 183 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 187 - access("X"."ROW_ID"=:B1)
 190 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 191 - access("A"."SERIAL_NUM"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
 192 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 193 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 196 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 197 - access("B"."ACCESS_NUMBER"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
 198 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 199 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 204 - access("X"."ROW_ID"=:B1)
 207 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 208 - access("A"."SERIAL_NUM"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
 209 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 210 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 213 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 214 - access("B"."ACCESS_NUMBER"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
 215 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 216 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 220 - access("X"."ROW_ID"=:B1)
 223 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 224 - access("A"."SERIAL_NUM"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
 225 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 226 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 229 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 230 - access("B"."ACCESS_NUMBER"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
 231 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 232 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 236 - access("X"."ROW_ID"=:B1)
 239 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 240 - access("A"."SERIAL_NUM"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
 241 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 242 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 245 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 246 - access("B"."ACCESS_NUMBER"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
 247 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 248 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 252 - access("X"."ROW_ID"=:B1)
 255 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
 256 - access("A"."SERIAL_NUM"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
 257 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
 258 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
 261 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
 262 - access("B"."ACCESS_NUMBER"=CASE  WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
 263 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
 264 - filter("C"."EXPIRY_DATE">SYSDATE@!)
 267 - access("X"."START_CELL_ID"="LKP"."CELL_ID"(+) AND "X"."START_LAC_ID"="LKP"."LAC_ID"(+))
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
  70 - SELECT /*+ NOPARALLEL ("POSTMED_CDR") NO_PARALLEL_INDEX ("POSTMED_CDR" "POSTMED_CDR_DEST_PHONE_NBR_IX") NO_PARALLEL_INDEX 
        ("POSTMED_CDR" "POSTMED_CDR_PHONE_NBR_IX") */ "HDR_FILE_SEQ_NBR_PART1","HDR_FILE_SEQ_NBR_PART2","HDR_FILE_SEQ_NBR_PART3","PHONE_NBR
        ","DEST_PHONE_NBR","CALL_START_TIME","TALK_MINUTES","TALK_SECONDS","CALL_TYPE","CALLING_IMSI","CALLING_IMEI","FIRST_CALLING_LOCATIO
        N","LAST_CALLING_LOCATION","LOADING_FROM","START_CELL_ID","END_CELL_ID","START_LAC_ID","END_LAC_ID" FROM "PST_CDR"."POSTMED_CDR" 
        "POSTMED_CDR" WHERE "CALL_START_TIME"<=TO_DATE(' 2015-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
        ("DEST_PHONE_NBR"='0504000000' OR "DEST_PHONE_NBR"='504000000') AND "CALL_START_TIME">=TO_DATE(' 2015-09-29 00:00:00', 
        'syyyy-mm-dd hh24:mi:ss') AND "DEST_PHONE_NBR" NOT LIKE '%559692565' AND "PHONE_NBR" NOT LIKE '%559692565' AND "DEST_PHONE_NBR" 
        NOT LIKE '%014404011' AND "PHONE_NBR" NOT LIKE '%014404011' AND "DEST_PHONE_NBR" IS NOT NULL AND "PHONE_NBR" IS NOT NULL AND 
        "DEST_PHONE_NBR" IS NOT NULL AND "PHONE_NBR" IS NOT NULL (accessing 'DM_EXA_DWH_ODSDWH' )
 
 135 - SELECT /*+ NOPARALLEL ("POSTMED_CDR") NO_PARALLEL_INDEX ("POSTMED_CDR" "POSTMED_CDR_DEST_PHONE_NBR_IX") NO_PARALLEL_INDEX 
         ("POSTMED_CDR" "POSTMED_CDR_PHONE_NBR_IX") */ "HDR_FILE_SEQ_NBR_PART1","HDR_FILE_SEQ_NBR_PART2","HDR_FILE_SEQ_NBR_PART3","PHONE_NBR
         ","DEST_PHONE_NBR","CALL_START_TIME","TALK_MINUTES","TALK_SECONDS","CALL_TYPE","CALLING_IMSI","CALLING_IMEI","FIRST_CALLING_LOCATIO
         N","LAST_CALLING_LOCATION","LOADING_FROM","START_CELL_ID","END_CELL_ID","START_LAC_ID","END_LAC_ID" FROM "PST_CDR"."POSTMED_CDR" 
         "POSTMED_CDR" WHERE "CALL_START_TIME"<=TO_DATE(' 2015-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("PHONE_NBR"='0504000000' OR 
         "PHONE_NBR"='504000000') AND "CALL_START_TIME">=TO_DATE(' 2015-09-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PHONE_NBR" NOT LIKE 
         '%559692565' AND "DEST_PHONE_NBR" NOT LIKE '%559692565' AND "PHONE_NBR" NOT LIKE '%014404011' AND "DEST_PHONE_NBR" NOT LIKE 
         '%014404011' AND "PHONE_NBR" IS NOT NULL AND "DEST_PHONE_NBR" IS NOT NULL AND "PHONE_NBR" IS NOT NULL AND "DEST_PHONE_NBR" IS NOT 
         NULL (accessing 'DM_EXA_DWH_ODSDWH' )
 
 200 - SELECT /*+ NOPARALLEL ("PST_LL_CDR") USE_NL ("PST_LL_CDR") NO_PARALLEL_INDEX ("PST_LL_CDR" "PST_CDR_LL_B_NUM_IX") */ 
         "A_NUMBER","B_NUMBER","CALL_START_TIME","CHARGEABLE_DURATION" FROM "PST_CDR_LL"."PST_LL_CDR" "PST_LL_CDR" WHERE 
         "CALL_START_TIME">=TO_DATE(' 2015-09-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("A_NUMBER"='0504000000' OR 
         "A_NUMBER"='504000000') AND "A_NUMBER" NOT LIKE '%559692565' AND "B_NUMBER" NOT LIKE '%559692565' AND "A_NUMBER" NOT LIKE 
         '%014404011' AND "B_NUMBER" NOT LIKE '%014404011' AND "A_NUMBER" IS NOT NULL AND "B_NUMBER" IS NOT NULL AND "A_NUMBER" IS NOT NULL 
         AND "B_NUMBER" IS NOT NULL AND "CALL_START_TIME"<=TO_DATE(' 2015-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing 
         'DM_EXA_DWH_ODSDWH' )
 
 265 - SELECT /*+ NOPARALLEL ("PST_LL_CDR") USE_NL ("PST_LL_CDR") NO_PARALLEL_INDEX ("PST_LL_CDR" "PST_CDR_LL_B_NUM_IX") */ 
         "A_NUMBER","B_NUMBER","CALL_START_TIME","CHARGEABLE_DURATION" FROM "PST_CDR_LL"."PST_LL_CDR" "PST_LL_CDR" WHERE 
         "CALL_START_TIME">=TO_DATE(' 2015-09-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("B_NUMBER"='0504000000' OR 
         "B_NUMBER"='504000000') AND "B_NUMBER" NOT LIKE '%559692565' AND "A_NUMBER" NOT LIKE '%559692565' AND "B_NUMBER" NOT LIKE 
         '%014404011' AND "A_NUMBER" NOT LIKE '%014404011' AND "B_NUMBER" IS NOT NULL AND "A_NUMBER" IS NOT NULL AND "B_NUMBER" IS NOT NULL 
         AND "A_NUMBER" IS NOT NULL AND "CALL_START_TIME"<=TO_DATE(' 2015-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing 
         'DM_EXA_DWH_ODSDWH' )
 



View query is getting hanged up.Please can you help me to resolve the issue
Re: Please help for tuning .sql query [message #644242 is a reply to message #644241] Sun, 01 November 2015 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>View query is getting hanged up
How to reconcile statement above with the observation that EXPLAIN PLAN posted above shows it completes in 1 second?


ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof
Re: Please help for tuning .sql query [message #644260 is a reply to message #644242] Mon, 02 November 2015 03:10 Go to previous message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
There are a lot of replicated column calculations that are replicated. You can avoid that by moving the calculation up a level.
So for example:
                           NVL
                              ((SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN SUBSTR (a.phone_nbr, -9)
                                              ELSE '1'
                                           END
                                          )),
                               (SELECT full_name
                                  FROM edi_views.shamel_cdr_name_vw b
                                 WHERE b.access_number =
                                          (CASE
                                              WHEN     SUBSTR
                                                           (TRIM (a.call_type),
                                                            2,
                                                            1
                                                           ) <> 'I'
                                                   AND TRIM (a.call_type) <>
                                                                          'GR'
                                               OR a.call_type IS NULL
                                                 THEN LPAD
                                                        (SUBSTR (a.phone_nbr,
                                                                 -9
                                                                ),
                                                         20,
                                                         '0'
                                                        )
                                              ELSE '1'
                                           END
                                          ))
                              ) AS caller_name

Can be moved up into the 2nd select - select distinct .... from (<unioned queries>).
Same for alias_name, call_type and service_type
Previous Topic: Unexplained SQL Slowness (AWR Supplied)
Next Topic: dynamic sampling precedence
Goto Forum:
  


Current Time: Sun Oct 21 18:23:51 CDT 2018