explain plan help

[Roshan]

Hello Erman,

can you please advise as per explain plan the delay for query below. I do not think it is index issue.

dware11_ora_3673.prf

select  unique M.serv_acc_link_code_n,  M.account_code_n,  M.sub_service_code_v,   M.serv_addnl_fld_14_v,
        M.telephone_number_v, M.status_code_v, to_char( M.contract_start_d, 'DD/MM/YYYY HH24:MI:SS') contract_start,
        to_char(R.chrg_start_date_d, 'DD/MM/YYYY HH24:MI:SS') chrg_start_date_d,
        to_char(R.chrg_end_date_d, 'DD/MM/YYYY HH24:MI:SS') chrg_end_date_d,
        M.last_name_v||' '|| M.subs_name_v name,  M.ic_number_v,  M.package_code_v,
        M.tariff_code_v,  M.parent_account_link_code_n,
        M.serv_addnl_fld_2_v,  R.charge_amt_n/100  rent_amt  , R.scheme_ref_code_n , R.scheme_type_v, R.status_optn_v, R.tax_plan_code_v,
        (select P.DISPLAY_DESCRIPTION_V
         from   cbsogg.cb_package P
         where  P.scheme_ref_code_n = R.scheme_ref_code_n
         and    R.scheme_type_v = 'P') pkg_DISPLAY_DESCRIPTION_V,
         (select P.SHORT_DESC_IN_BILL_V
         from   cbsogg.cb_package P
         where  P.scheme_ref_code_n = R.scheme_ref_code_n
         and    R.scheme_type_v = 'P') pkg_SHORT_DESC_IN_BILL_V,
         (select F.DISPLAY_DESCRIPTION_V
         from   cbsogg.cb_offers F
         where  F.scheme_ref_code_n = R.scheme_ref_code_n
         and    R.scheme_type_v = 'O') off_DISPLAY_DESCRIPTION_V,
         (select F.SHORT_DESC_IN_BILL_V
         from   cbsogg.cb_offers F
         where  F.scheme_ref_code_n = R.scheme_ref_code_n
         and    R.scheme_type_v = 'O') off_SHORT_DESC_IN_BILL_V,
         (select P2.DISPLAY_DESCRIPTION_V
         from   cbsogg.cb_package P2
         where  P2.package_code_v = M.package_code_v
         and    R.scheme_type_v is null) pkg2_DISPLAY_DESCRIPTION_V,
         (select P.SHORT_DESC_IN_BILL_V
         from   cbsogg.cb_package P
         where  P.package_code_v = M.package_code_v
         and    R.scheme_type_v is null) pkg2_SHORT_DESC_IN_BILL_V,
         (select L.service_info_v
         from    cbsogg.cb_account_service_list L
         where   L.account_link_code_n = M.serv_acc_link_code_n) Service_info_v,          
          A.SUBSCRIBER_CATEGORY_V,
         (select C1.subs_category_desc_v
         from   cbsogg.cb_subscriber_category C1
         where  C1.subs_category_code_v = A.subscriber_category_v) category_desc,
          A.subscriber_sub_category_v,
          (select T1.subs_sub_category_desc_v
         from   cbsogg.cb_subscriber_sub_category T1
         where  T1.subs_category_code_v = A.subscriber_category_v
         and    T1.subs_sub_category_code_v = A.subscriber_sub_category_v) sub_category_desc
--from   cbsogg.fxl_service_mast M, bireport.cb_subs_advance_rent_dtls R, cbsogg.cb_account_master A
from   cbsogg.fxl_service_mast M, cbsogg.cb_subs_advance_rent_dtls R, cbsogg.cb_account_master A
where  R.serv_acc_link_code_n(+) = M.serv_acc_link_code_n
and    A.account_code_n = M.account_code_n
union
select  unique M.serv_acc_link_code_n,  M.account_code_n,  M.sub_service_code_v,   M.serv_addnl_fld_14_v,
        M.telephone_number_v, M.status_code_v, to_char( M.contract_start_d, 'DD/MM/YYYY HH24:MI:SS') contract_start,
        to_char(O.start_date_d, 'DD/MM/YYYY HH24:MI:SS') start_date_d,
        to_char(O.end_date_d, 'DD/MM/YYYY HH24:MI:SS')  end_date_d,
        M.last_name_v||' '|| M.subs_name_v name,  M.ic_number_v,  M.package_code_v,
        M.tariff_code_v,  M.parent_account_link_code_n,
        M.serv_addnl_fld_2_v,  null  rent_amt  , O.scheme_ref_code_n , null, O.status_optn_v, null,
        (select P.DISPLAY_DESCRIPTION_V
         from   cbsogg.cb_package P
         where  P.scheme_ref_code_n = O.scheme_ref_code_n
         and    1=1) pkg_DISPLAY_DESCRIPTION_V,
         (select P.SHORT_DESC_IN_BILL_V
         from   cbsogg.cb_package P
         where  P.scheme_ref_code_n = O.scheme_ref_code_n
         and    1=1) pkg_SHORT_DESC_IN_BILL_V,
         (select F.DISPLAY_DESCRIPTION_V
         from   cbsogg.cb_offers F
         where  F.scheme_ref_code_n = O.scheme_ref_code_n
         and    1=1) off_DISPLAY_DESCRIPTION_V,
         (select F.SHORT_DESC_IN_BILL_V
         from   cbsogg.cb_offers F
         where  F.scheme_ref_code_n = O.scheme_ref_code_n
         and    1=1) off_SHORT_DESC_IN_BILL_V,
         (select P2.DISPLAY_DESCRIPTION_V
         from   cbsogg.cb_package P2
         where  P2.package_code_v = M.package_code_v
         and    1=1) pkg2_DISPLAY_DESCRIPTION_V,
         (select P.SHORT_DESC_IN_BILL_V
         from   cbsogg.cb_package P
         where  P.package_code_v = M.package_code_v
         and    1=1) pkg2_SHORT_DESC_IN_BILL_V,
         (select L.service_info_v
         from    cbsogg.cb_account_service_list L
         where   L.account_link_code_n = M.serv_acc_link_code_n) Service_info_v,          
          A.SUBSCRIBER_CATEGORY_V,
         (select C1.subs_category_desc_v
         from   cbsogg.cb_subscriber_category C1
         where  C1.subs_category_code_v = A.subscriber_category_v) category_desc,
          A.subscriber_sub_category_v,
          (select T1.subs_sub_category_desc_v
         from   cbsogg.cb_subscriber_sub_category T1
         where  T1.subs_category_code_v = A.subscriber_category_v
         and    T1.subs_sub_category_code_v = A.subscriber_sub_category_v) sub_category_desc
-- from   fxl_service_mast M, cb_subs_offer_details O, cb_account_master A
from   cbsogg.fxl_service_mast M, cbsogg.cb_subs_offer_details O, cbsogg.cb_account_master A
where  O.account_link_code_n(+) = M.serv_acc_link_code_n
AND    O.status_optn_v ='A'
and    O.scheme_ref_code_n  not in (SELECT R.scheme_ref_code_n
                                    from   cbsogg.cb_subs_advance_rent_dtls R
                                    where  R.serv_acc_link_code_n = O.account_link_code_n)
and    A.account_code_n = M.account_code_n
order by 2, 4;

Thanks,

Roshan