Home » RDBMS Server » Performance Tuning » Query Tuning (Oracle 9i)
Query Tuning [message #548599] Fri, 23 March 2012 06:36 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I am executing a view PRONELT_METALIC_V.
And it's using the following query
it's taking around 40 to 50 minutes.
it's fetching 7,50,000 records.
The query is dam slow.
Could any body please help me.
I am sending the explain plan as well.
  SELECT   miln.project_id,
           miln.institution_nbr,
           miln.institution_nme,
           institution.institution_legal_nme,
           institution_status_code,
           DECODE (miln.project_status_code, 'A', 1, 0)
              AS project_reporting_count_code,
           miln.project_approval_actual_date,
           miln.project_commitment_actual_date,
           miln.project_size_usd_amt,
           miln.environment_category_code,
           miln.region_nme,
           miln.regional_dept_nme,
           miln.country_nme,
           country.ibrd_country_code,
           country.ida_member_date,
           institution.incorporation_country_code,
           miln.dept_alpha_code,
           miln.master_project_id,
           miln.project_status_nme,
           miln.current_stage_code,
           miln.project_short_nme,
           miln.export_ind,
           miln.div_alpha_code,
           miln.dept_alpha_code || miln.div_alpha_code AS moc_alpha_code,
           miln.special_ops_code,
           company_credit_risk_summary_v.loan_risk_rating_nbr,
           company_credit_risk_summary_v.loan_granular_rating_code,
           company_credit_risk_summary_v.loan_direction_rating_code,
           company_credit_risk_summary_v.eqty_risk_rating_nbr,
           company_credit_risk_summary_v.eqty_granular_rating_code,
           company_credit_risk_summary_v.eqty_direction_rating_code,
           lis.project_team_staff_info (miln.project_id, 'I', 'N')
              investment_officer_nme,
           lis.project_team_staff_info (miln.project_id, '8', 'N')
              industry_director_nme,
           lis.project_team_staff_info (miln.project_id, '6', 'N')
              portfolio_assistant_nme,
           lis.project_team_staff_info (miln.project_id, 'B', 'N')
              lead_env_soc_specialist_nme,
           lis.project_team_staff_info (miln.project_id, 'C', 'N')
              credit_officer_nme,
           lis.project_team_staff_info (miln.project_id, 'D', 'N')
              b_loan_mgmt_officer_nme,
           lis.project_team_staff_info (miln.project_id, 'L', 'N') lawyer_nme,
           lis.project_team_staff_info (miln.project_id, 'P', 'N')
              portfolio_manager_nme,
           lis.project_team_staff_info (miln.project_id, 'U', 'N')
              insurance_specialist_nme,
           lis.project_team_staff_info (miln.project_id, 'V', 'N')
              env_specialist_nme,
           lis.project_team_staff_info (miln.project_id, 'Y', 'N')
              portfolio_officer_nme,
           lis.project_team_staff_info (miln.project_id, 'J', 'N')
              relationship_manager_nme,
           miln.greenfield_code,
           miln.sme_type_code,
           kfc_sector_v.kfc_sector_code,
           kfc_sector_v.kfc_sector_nme AS tertiary_sector_nme,
           kfc_sector_v.secondary_sector_nme AS secondary_sector_nme,
           kfc_sector_v.primary_sector_nme AS primary_sector_nme,
           lis.valuation_summary_v.effective_kfc_ownership_pct,
           kfc_companycommonownership_pct AS kfc_cmpy_common_ownership_pct,
           SUM (tbv.iocmt_usd_amt + tbv.guarocmt_usd_amt + tbv.crmcmt_usd_amt)
           / 1000
              kfc_orig_commit_bal_amt,
           SUM (pocmt_usd_amt + pgtocmt_usd_amt) / 1000
              AS part_orig_commit_bal_amt,
           SUM (guarout_usd_amt + dout_usd_amt + crmdout_usd_amt) / 1000
              AS kfc_outst_bal_amt,
           (SUM (guarout_usd_amt + dout_usd_amt + crmdout_usd_amt)
            + SUM (guarunds_usd_amt + unds_usd_amt + crmunds_usd_amt))
           / 1000
              AS kfc_committed_bal_amt,
           SUM (pguarout_usd_amt + pdot_usd_amt) / 1000 AS part_outst_bal_amt,
           SUM (pguarund_usd_amt + pund_usd_amt) / 1000 AS part_undisb_bal_amt,
           (SUM (tbv.ippy_usd_amt)) / 1000 AS kfc_prepay_bal_amt,
           (SUM (tbv.irpy_usd_amt)) / 1000 AS kfc_repay_bal_amt,
           (SUM (tbv.wrtoff_usd_amt)) / 1000 AS kfc_wrtoff_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'ET', tbv.dout_usd_amt,
                        0)))
           / 1000
              AS et_kfc_outst_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'ET', tbv.unds_usd_amt,
                        0)))
           / 1000
              AS et_kfc_undisb_bal_amt,
           (SUM(DECODE (
                   NVL (
                      UPPER (
                         TRIM (institution_loss_reserve.investment_risk_ind)
                      ),
                      'N'
                   ),
                   'N',
                   (DECODE (RTRIM (tbv.tertiary_product_category_code),
                            'ET', slr.specific_reserve_usd_amt,
                            0)),
                   0
                )))
           / 1000
              AS et_loss_reserve_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'ET', tbv.dout_usd_amt,
                        'QE', tbv.dout_usd_amt,
                        0)))
           / 1000
              AS et_qe_kfc_outst_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'ET', tbv.unds_usd_amt,
                        'QE', tbv.unds_usd_amt,
                        0)))
           / 1000
              AS et_qe_kfc_undisb_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'ET', tbv.idsb_usd_amt,
                        'QE', tbv.idsb_usd_amt,
                        0)))
           / 1000
              AS et_qe_kfc_disb_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'ET', tbv.ccni_usd_amt,
                        'QE', tbv.ccni_usd_amt,
                        0)))
           / 1000
              AS et_qe_kfc_cancel_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'ET', tbv.iocmt_usd_amt,
                        'QE', tbv.iocmt_usd_amt,
                        0)))
           / 1000
              AS et_qe_kfc_orig_commit_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'ET', tbv.icmt_usd_amt,
                        'QE', tbv.icmt_usd_amt,
                        0)))
           / 1000
              AS et_qe_kfc_net_commit_bal_amt,
           SUM (project_fytd_activity.kfc_cash_divnd_fytd_amt) / 1000
              AS kfc_cash_divnd_fytd_act_amt,
           SUM (project_fytd_activity.et_plus_qe_fyd_disbursed) / 1000
              AS et_qe_kfc_fytd_disb_act_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'LN', tbv.dout_usd_amt,
                        0)))
           / 1000
              AS ln_kfc_outs_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'LN', tbv.unds_usd_amt,
                        0)))
           / 1000
              AS ln_kfc_undisb_bal_amt,
           (SUM(DECODE (
                   NVL (
                      UPPER (
                         TRIM (institution_loss_reserve.investment_risk_ind)
                      ),
                      'N'
                   ),
                   'N',
                   (DECODE (RTRIM (tbv.tertiary_product_category_code),
                            'LN', slr.specific_reserve_usd_amt,
                            0)),
                   0
                )))
           / 1000
              AS ln_loss_reserve_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'LN', tbv.dout_usd_amt,
                        'QL', tbv.dout_usd_amt,
                        0)))
           / 1000
              AS ln_ql_kfc_outst_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'LN', tbv.unds_usd_amt,
                        'QL', tbv.unds_usd_amt,
                        0)))
           / 1000
              AS ln_ql_kfc_undisb_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'LN', tbv.idsb_usd_amt,
                        'QL', tbv.idsb_usd_amt,
                        0)))
           / 1000
              AS ln_ql_kfc_disb_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'LN', tbv.ccni_usd_amt,
                        'QL', tbv.ccni_usd_amt,
                        0)))
           / 1000
              AS ln_ql_kfc_cancel_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'LN', tbv.iocmt_usd_amt,
                        'QL', tbv.iocmt_usd_amt,
                        0)))
           / 1000
              AS ln_ql_kfc_orig_commit_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'LN', tbv.icmt_usd_amt,
                        'QL', tbv.icmt_usd_amt,
                        0)))
           / 1000
              AS ln_ql_kfc_net_commit_bal_amt,
           SUM (project_fytd_activity.ln_plus_ql_fyd_disbursed) / 1000
              AS ln_ql_kfc_fytd_disb_act_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'QE', tbv.dout_usd_amt,
                        0)))
           / 1000
              AS qe_kfc_outst_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'QE', tbv.unds_usd_amt,
                        0)))
           / 1000
              AS qe_kfc_undisb_bal_amt,
           (SUM(DECODE (
                   NVL (
                      UPPER (
                         TRIM (institution_loss_reserve.investment_risk_ind)
                      ),
                      'N'
                   ),
                   'N',
                   (DECODE (RTRIM (tbv.tertiary_product_category_code),
                            'QE', slr.specific_reserve_usd_amt,
                            0)),
                   0
                )))
           / 1000
              AS qe_loss_reserve_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'QL', tbv.dout_usd_amt,
                        0)))
           / 1000
              AS ql_kfc_outst_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'QL', tbv.unds_usd_amt,
                        0)))
           / 1000
              AS ql_kfc_undisb_bal_amt,
           (SUM(DECODE (
                   NVL (
                      UPPER (
                         TRIM (institution_loss_reserve.investment_risk_ind)
                      ),
                      'N'
                   ),
                   'N',
                   (DECODE (RTRIM (tbv.tertiary_product_category_code),
                            'QL', slr.specific_reserve_usd_amt,
                            0)),
                   0
                )))
           / 1000
              AS ql_loss_reserve_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'GT', tbv.guarout_usd_amt + tbv.dout_usd_amt,
                        0)))
           / 1000
              AS gt_kfc_outst_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'GT', tbv.guarunds_usd_amt + tbv.unds_usd_amt,
                        0)))
           / 1000
              AS gt_kfc_undisb_bal_amt,
           (SUM(DECODE (
                   NVL (
                      UPPER (
                         TRIM (institution_loss_reserve.investment_risk_ind)
                      ),
                      'N'
                   ),
                   'N',
                   (DECODE (RTRIM (tbv.tertiary_product_category_code),
                            'GT', slr.specific_reserve_usd_amt,
                            0)),
                   0
                )))
           / 1000
              AS gt_loss_reserve_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'GT', tbv.guarcnc_usd_amt + tbv.ccni_usd_amt,
                        0)))
           / 1000
              AS gt_kfc_cancel_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'GT', tbv.guarocmt_usd_amt + tbv.iocmt_usd_amt,
                        0)))
           / 1000
              AS gt_kfc_orig_commit_bal_amt,
           (SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
                        'GT', tbv.guarcmt_usd_amt + tbv.icmt_usd_amt,
                        0)))
           / 1000
              AS gt_kfc_net_commit_bal_amt,
           SUM (tbv.crmdout_usd_amt) / 1000 AS rm_kfc_outst_bal_amt,
           SUM (tbv.crmunds_usd_amt) / 1000 AS rm_kfc_undisb_bal_amt,
           SUM (tbv.crmcmt_usd_amt) / 1000 AS rm_kfc_orig_commit_bal_amt,
           SUM (tbv.crmcmt_usd_amt - tbv.crmcn_usd_amt) / 1000
              AS rm_kfc_net_commit_bal_amt,
           SUM (tbv.crmcn_usd_amt) / 1000 AS rm_kfc_cancel_bal_amt,
           (SUM(DECODE (
                   NVL (
                      UPPER (
                         TRIM (institution_loss_reserve.investment_risk_ind)
                      ),
                      'N'
                   ),
                   'N',
                   (DECODE (RTRIM (tbv.tertiary_product_category_code),
                            'RM', slr.specific_reserve_usd_amt,
                            0)),
                   0
                )))
           / 1000
              AS rm_loss_reserve_bal_amt,
           MAX (project_amount_due_summary.net_prin_balance_usd_amt)
              AS arrears_net_principal_bal_amt,
           MAX (project_amount_due_summary.interest_balance_usd_amt)
              AS arrears_interest_bal_amt,
           MAX(project_amount_due_summary.net_prin_balance_usd_amt
               + project_amount_due_summary.interest_balance_usd_amt)
              AS arrears_prin_and_intr_bal_amt,
           SUM(DECODE (tranche_info_lookup_v.securitized_ind,
                       'Y',
                       pguarout_usd_amt + pdot_usd_amt))
           / 1000
              AS part_sec_outst_bal_amt,
           SUM(DECODE (tranche_info_lookup_v.securitized_ind,
                       'N',
                       pguarout_usd_amt + pdot_usd_amt))
           / 1000
              AS part_non_sec_outst_bal_amt,
           (CASE
               WHEN ( (SUM(DECODE (npl.cy_non_performing_ind,
                                   'Y', npl.cy_npl_principal_usd_amt,
                                   0)))
                     / 1000) <> 0
               THEN
                  'P'
               ELSE
                  ''
            END)
              AS principal_npl_code,
           SUM (npl.cy_npl_principal_usd_amt) / 1000 AS npl_principal_bal_amt,
           SUM (npl.cy_npl_int_usd_amt) / 1000 AS npl_interest_bal_amt,
           ods.proj_info_lookup.regional_dept_nme proj_regional_dept_nme,
           ods.proj_info_lookup.dept_alpha_code proj_dept_alpha_code,
           miln.sector_group_nme sector_group_nme,
           miln.summary_date,
           SUM (project_fytd_activity.kfc_wrtoff_fytd_amt) / 1000
              kfc_wrtoff_fytd_amt,
           (SUM (tbv.sbpripyb_usd_amt)) / 1000 AS alp_outst_usd_amt,
           miln.south_south_project_ind south_south_project_ind,
           miln.existing_sponsor_ind existing_sponsor_ind,
           (SUM (tbv.billout_usd_amt)) / 1000 AS billout_usd_amt,
           (SUM (tbv.ieqinvst_usd_amt)) / 1000 AS ieqinvst_usd_amt,
           cleav.lending_eligibility_code,
           cleav.lending_eligibility_sub_code,
           SUM(CASE
                  WHEN UPPER(lis.evaluate_pipeline_role_code (
                                tbv.Product_Type_Code,
                                tbv.Product_Sub_Type_Code
                             )) = 'G'
                  THEN
                     tbv.synocmt_usd_amt
                  ELSE
                     0
               END)
           / 1000
              AS agt_ORIG_COMMIT_BAL_AMT,
           SUM(CASE
                  WHEN UPPER(lis.evaluate_pipeline_role_code (
                                tbv.Product_Type_Code,
                                tbv.Product_Sub_Type_Code
                             )) = 'G'
                  THEN
                     tbv.synoutst_usd_amt
                  ELSE
                     0
               END)
           / 1000
              AS agt_outst_bal_amt,
           SUM(CASE
                  WHEN UPPER(lis.evaluate_pipeline_role_code (
                                tbv.Product_Type_Code,
                                tbv.Product_Sub_Type_Code
                             )) = 'G'
                  THEN
                     tbv.synunds_usd_amt
                  ELSE
                     0
               END)
           / 1000
              AS agt_undisb_bal_amt,
           miln.INDUSTRY_SUB_GROUP_ID,
           miln.INDUSTRY_SUB_GROUP_NME,
           miln.SUBNATIONAL_FINANCE_IND,
           miln.INDUSTRY_LVL2_SUB_GROUP_ID,
           miln.INDUSTRY_LVL2_SUB_GROUP_NME,
           miln.INDUSTRY_LVL3_SUB_GROUP_ID,
           miln.INDUSTRY_LVL3_SUB_GROUP_NME,
           miln.industry_cluster_nme,
           lis.get_Institution_Tier_Detail (miln.institution_nbr,
                                            miln.summary_date,
                                            'INSTITUTION_TIER_CODE')
              INSTITUTION_TIER_CODE,
           lis.get_Institution_Tier_Detail (miln.institution_nbr,
                                            miln.summary_date,
                                            'INSTITUTION_TIER_NME')
              INSTITUTION_TIER_NME,
           lis.get_Institution_Tier_Detail (miln.institution_nbr,
                                            miln.summary_date,
                                            'TIER_CALC_METHOD_FLAG')
              TIER_CALC_METHOD_FLAG,
           CASE
              WHEN lis.get_Institution_Tier_Detail (miln.institution_nbr,
                                                    miln.summary_date,
                                                    'TIER_CALC_METHOD_FLAG') =
                      'S'
              THEN
                 lis.get_Institution_Tier_Detail (miln.institution_nbr,
                                                  miln.summary_date,
                                                  'EFFECTIVE_DATE')
              ELSE
                 NULL
           END
              TIER_CALC_METHOD_DATE,
           miln.SHORT_TERM_FINANCE_IND,
           miln.SUPER_REGION_CODE,
           miln.SUPER_REGION_SHORT_NME,
           miln.CLIMATE_CHANGE_IND,
           miln.CLIMATE_CHANGE_PCT,
           lis.get_Income_Participation_ind (miln.project_id,
                                             miln.summary_date)
              Income_Participation_Ind
    FROM   lis.kfc_sector_v kfc_sector_v,
           ods.country country,
           ods.country institution_country,
           ods.institution institution,
           ods.proj_info_lookup_monthly pilm,
           lis.tranche_balance tbv,
           (SELECT   r.project_id,
                     r.tranche_nbr,
                     (CASE
                         WHEN r.tranche_nbr >= 50 THEN t.currency_code
                         WHEN r.tranche_nbr < 50 THEN r.currency_code
                      END)
                        currency_code,
                     summary_date,
                     NVL (specific_reserve_usd_amt, 0) specific_reserve_usd_amt,
                     NVL (specific_reserve_crncy_amt, 0)
                        specific_reserve_crncy_amt,
                     NVL (proposed_write_off_usd_amt, 0)
                        proposed_write_off_usd_amt,
                     NVL (proposed_write_off_crncy_amt, 0)
                        proposed_write_off_crncy_amt
              FROM   ods.specific_loss_reserve r, ods.tranche t
             WHERE   r.project_id = t.project_id
                     AND r.tranche_nbr = t.tranche_nbr) slr,
           ods.proj_info_lookup proj_info_lookup,
           ods.institution_loss_reserve,
           lis.company_credit_risk_summary_v,
           lis.non_performing_loans_by_trch npl,
           lis.tranche_info_lookup_v,
           (  SELECT   ads.project_id,
                       SUM (ads.net_prin_balance_usd_amt) / 1000
                          AS net_prin_balance_usd_amt,
                       SUM (interest_balance_usd_amt) / 1000
                          AS interest_balance_usd_amt,
                       process_date summary_date
                FROM   lis.amount_due_summary ads
               WHERE   ads.portfolio_report_ind = 'Y'
                       AND ads.participant_nbr IN (1, 1588)
            GROUP BY   ads.project_id, process_date) project_amount_due_summary,
           (  SELECT   institution_nbr,
                       summary_date,
                       DECODE (MIN (project_status_code), 'A', 'Y', 'N')
                          institution_status_code
                FROM   ods.proj_info_lookup_monthly pilm
               WHERE   miln.project_category_code IN ('F', 'I')
                       AND miln.project_status_code NOT IN ('X', 'D')
            GROUP BY   institution_nbr, summary_date) institution_status_code,
           lis.equity_valuation_summary valuation_summary_v,
           (  SELECT   institution_nbr,
                       summary_date,
                       SUM (TO_NUMBER (feature_value_text))
                          AS kfc_companycommonownership_pct
                FROM   (SELECT   institution_nbr,
                                 share_type_code,
                                 summary_date,
                                 value_date,
                                 feature_type_code,
                                 feature_value_text,
                                 MAX(value_date)
                                    OVER (
                                       PARTITION BY institution_nbr,
                                                    share_type_code,
                                                    dt.summary_date
                                    )
                                    max_value_date
                          FROM   ods.valuation_feature vf,
                                 lis.summary_date_table dt
                         WHERE   vf.value_date <= dt.summary_date
                                 AND vf.share_type_code = 'COM')
               WHERE       value_date = max_value_date
                       AND LTRIM (feature_value_text, '0123456789.') IS NULL
                       AND feature_type_code = 'OWN'
            GROUP BY   institution_nbr, summary_date)
           kfc_companycommonownership,
           (SELECT   abc.project_id,
                     abc.tranche_nbr,
                     abc.currency_code,
                     abc.summary_date,
                     abc.fiscal_year,
                     SUM(NVL (ln_plus_ql_fyd_disbursed, 0))
                        OVER (
                           PARTITION BY abc.project_id,
                                        abc.tranche_nbr,
                                        abc.currency_code,
                                        abc.fiscal_year
                           ORDER BY abc.project_id,
                                    abc.tranche_nbr,
                                    abc.currency_code,
                                    abc.summary_date
                           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                        )
                        ln_plus_ql_fyd_disbursed,
                     SUM(NVL (et_plus_qe_fyd_disbursed, 0))
                        OVER (
                           PARTITION BY abc.project_id,
                                        abc.tranche_nbr,
                                        abc.currency_code,
                                        abc.fiscal_year
                           ORDER BY abc.project_id,
                                    abc.tranche_nbr,
                                    abc.currency_code,
                                    abc.summary_date
                           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                        )
                        et_plus_qe_fyd_disbursed,
                     SUM(NVL (kfc_cash_divnd_fytd_amt, 0))
                        OVER (
                           PARTITION BY abc.project_id,
                                        abc.tranche_nbr,
                                        abc.currency_code,
                                        abc.fiscal_year
                           ORDER BY abc.project_id,
                                    abc.tranche_nbr,
                                    abc.currency_code,
                                    abc.summary_date
                           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                        )
                        kfc_cash_divnd_fytd_amt,
                     SUM(NVL (kfc_wrtoff_fytd_amt, 0))
                        OVER (
                           PARTITION BY abc.project_id,
                                        abc.tranche_nbr,
                                        abc.currency_code,
                                        abc.fiscal_year
                           ORDER BY abc.project_id,
                                    abc.tranche_nbr,
                                    abc.currency_code,
                                    abc.summary_date
                           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                        )
                        kfc_wrtoff_fytd_amt
              FROM   (SELECT   ics_1.project_id,
                               ics_1.tranche_nbr,
                               ics_1.currency_code,
                               ics_1.summary_date,
                               CASE
                                  WHEN TO_CHAR (ics_1.summary_date, 'MM') <= 6
                                  THEN
                                     EXTRACT (YEAR FROM ics_1.summary_date)
                                  ELSE
                                     EXTRACT (YEAR FROM ics_1.summary_date) + 1
                               END
                                  fiscal_year
                        FROM   lis.tranche_activity ics_1
                       WHERE   ics_1.summary_date >= '31-JUL-1999') abc,
                     (  SELECT   project_id,
                                 tranche_nbr,
                                 currency_code,
                                 summary_date,
                                 CASE
                                    WHEN TO_CHAR (summary_date, 'MM') <= 6
                                    THEN
                                       EXTRACT (YEAR FROM summary_date)
                                    ELSE
                                       EXTRACT (YEAR FROM summary_date) + 1
                                 END
                                    fiscal_year,
                                 SUM(CASE
                                        WHEN tertiary_product_category_code IN
                                                   ('LN', 'QL')
                                             AND amount_type_code = 'IDSB'
                                        THEN
                                           usd_amt
                                        ELSE
                                           0
                                     END)
                                    AS ln_plus_ql_fyd_disbursed,
                                 SUM(CASE
                                        WHEN tertiary_product_category_code IN
                                                   ('ET', 'QE')
                                             AND amount_type_code = 'IDSB'
                                        THEN
                                           usd_amt
                                        ELSE
                                           0
                                     END)
                                    AS et_plus_qe_fyd_disbursed,
                                 SUM(CASE
                                        WHEN amount_type_code IN
                                                   ('DIVINC', 'OILINCM')
                                        THEN
                                           usd_amt
                                        ELSE
                                           0
                                     END)
                                    AS kfc_cash_divnd_fytd_amt,
                                 SUM(CASE
                                        WHEN amount_type_code = 'WRTOFF'
                                        THEN
                                           usd_amt
                                        ELSE
                                           0
                                     END)
                                    AS kfc_wrtoff_fytd_amt
                          FROM   (  SELECT   la.project_id,
                                             la.tranche_nbr,
                                             la.summary_date,
                                             atp.amount_type_code,
                                             ics.tertiary_product_category_code,
                                             la.trans_crncy_code currency_code,
                                             SUM (atrm.action_taken * la.usd_amt)
                                                usd_amt,
                                             SUM (atrm.action_taken * la.crncy_amt)
                                                crncy_amt
                                      FROM   (SELECT   A.gl_account_nbr,
                                                       s.gl_sub_account_nbr,
                                                       A.gl_account_code,
                                                       s.gl_sub_account_code,
                                                       DECODE (
                                                          TRIM (B.sub_account_code),
                                                          '%',
                                                          '',
                                                          TRIM (B.sub_account_code)
                                                       )
                                                          fee_code,
                                                       B.source_system_nme,
                                                       DECODE (
                                                          A.gl_account_code,
                                                          s.gl_sub_account_code,
                                                          1,
                                                          -1
                                                       )
                                                       * DECODE (B.action_code,
                                                                 'CR', -1,
                                                                 1)
                                                          action_taken,
                                                       B.balance_rule_nbr
                                                FROM   ods.gl_account A,
                                                       ods.gl_sub_account s,
                                                       ods.balance_rule_detail B
                                               WHERE   s.gl_account_nbr =
                                                          A.gl_account_nbr
                                                       AND B.gl_account_nbr =
                                                             A.gl_account_nbr
                                                       AND B.gl_account_nbr =
                                                             s.gl_account_nbr
                                                       AND B.gl_sub_account_nbr =
                                                             s.gl_sub_account_nbr)
                                             atrm,
                                             ods.ledger_posting_date_summary la,
                                             lis.investment_class_summary ics,
                                             ods.amt_type_rule atr,
                                             ods.amount_type atp
                                     WHERE       la.summary_date >= '31-JUL-1999'
                                             AND la.project_id = ics.project_id
                                             AND la.tranche_nbr = ics.tranche_nbr
                                             AND la.summary_date = ics.summary_date
                                             AND NVL (ics.obligation_nbr, 0) = 0
                                             AND atr.investment_level_code = 'T'
                                             AND atr.active_ind = 'Y'
                                             AND atr.balance_rule_nbr =
                                                   atrm.balance_rule_nbr
                                             AND atr.source_system_nme =
                                                   atrm.source_system_nme
                                             AND la.gl_account_nbr =
                                                   atrm.gl_account_nbr
                                             AND la.gl_sub_account_nbr =
                                                   atrm.gl_sub_account_nbr
                                             AND la.sub_account_code =
                                                   NVL (RPAD (atrm.fee_code, 5),
                                                        la.sub_account_code)
                                             AND atp.amount_type_code =
                                                   atr.amount_type_code
                                             AND atp.amount_type_code IN
                                                      ('IDSB',
                                                       'OILINCM',
                                                       'DIVINC',
                                                       'WRTOFF')
                                  GROUP BY   la.project_id,
                                             la.tranche_nbr,
                                             la.summary_date,
                                             atp.amount_type_code,
                                             ics.tertiary_product_category_code,
                                             la.trans_crncy_code)
                      GROUP BY   project_id,
                                 tranche_nbr,
                                 currency_code,
                                 summary_date) xyz
             WHERE       abc.project_id = xyz.project_id(+)
                     AND abc.tranche_nbr = xyz.tranche_nbr(+)
                     AND abc.currency_code = xyz.currency_code(+)
                     AND abc.summary_date = xyz.summary_date(+))
           project_fytd_activity,
           investment_dm.cntry_lend_eligibility_all_v cleav
   WHERE   miln.summary_date = lis.valuation_summary_v.summary_date(+)
           AND miln.institution_nbr =
                 lis.valuation_summary_v.institution_nbr(+)
           AND miln.summary_date = kfc_companycommonownership.summary_date(+)
           AND miln.institution_nbr =
                 kfc_companycommonownership.institution_nbr(+)
           AND tbv.summary_date = project_fytd_activity.summary_date(+)
           AND tbv.project_id = project_fytd_activity.project_id(+)
           AND tbv.tranche_nbr = project_fytd_activity.tranche_nbr(+)
           AND tbv.currency_code = project_fytd_activity.currency_code(+)
           AND tbv.summary_date = slr.summary_date(+)
           AND tbv.project_id = slr.project_id(+)
           AND tbv.tranche_nbr = slr.tranche_nbr(+)
           AND tbv.currency_code = slr.currency_code(+)
           AND miln.summary_date = project_amount_due_summary.summary_date(+)
           AND miln.project_id = project_amount_due_summary.project_id(+)
           AND miln.project_id = proj_info_lookup.project_id
           AND miln.summary_date = tbv.summary_date(+)
           AND miln.project_id = tbv.project_id(+)
           AND miln.summary_date = institution_status_code.summary_date
           AND miln.institution_nbr = institution_status_code.institution_nbr
           AND miln.kfc_sector_code = kfc_sector_v.kfc_sector_code
           AND miln.country_code = country.country_code
           AND institution.country_code = institution_country.country_code(+)
           AND miln.institution_nbr = institution.institution_nbr
           AND tranche_info_lookup_v.summary_date = tbv.summary_date
           AND tranche_info_lookup_v.project_id = tbv.project_id
           AND tranche_info_lookup_v.tranche_nbr = tbv.tranche_nbr
           AND miln.summary_date =
                 company_credit_risk_summary_v.summary_date(+)
           AND miln.institution_nbr =
                 company_credit_risk_summary_v.institution_nbr(+)
           AND tbv.summary_date = npl.summary_date(+)
           AND tbv.project_id = npl.project_id(+)
           AND tbv.tranche_nbr = npl.tranche_nbr(+)
           AND tbv.currency_code = npl.currency_code(+)
           AND miln.project_category_code IN ('F', 'I')
           AND miln.project_status_code NOT IN ('X', 'D')
           AND proj_info_lookup.institution_nbr =
                 institution_loss_reserve.institution_nbr(+)
           AND tbv.tranche_nbr >= 0
           AND (tbv.project_id, tbv.tranche_nbr) IN
                    (SELECT   project_id, tranche_nbr
                       FROM   ods.tranche
                      WHERE   portfolio_id = 'IF')
           AND miln.summary_date = cleav.summary_date(+)
           AND miln.country_code = cleav.country_code(+)
GROUP BY   miln.project_id,
           miln.institution_nbr,
           miln.institution_nme,
           miln.project_status_code,
           miln.project_category_code,
           miln.project_reporting_count_ind,
           miln.project_approval_actual_date,
           miln.project_commitment_actual_date,
           miln.project_size_usd_amt,
           miln.region_nme,
           miln.regional_dept_nme,
           miln.country_nme,
           miln.master_project_id,
           miln.project_status_nme,
           miln.current_stage_code,
           miln.project_short_nme,
           miln.export_ind,
           miln.div_alpha_code,
           miln.dept_alpha_code || miln.div_alpha_code,
           miln.dept_alpha_code,
           miln.special_ops_code,
           miln.kfc_sector_code,
           miln.environment_category_code,
           miln.greenfield_code,
           miln.sme_type_code,
           company_credit_risk_summary_v.loan_risk_rating_nbr,
           company_credit_risk_summary_v.loan_granular_rating_code,
           company_credit_risk_summary_v.loan_direction_rating_code,
           company_credit_risk_summary_v.eqty_risk_rating_nbr,
           company_credit_risk_summary_v.eqty_granular_rating_code,
           company_credit_risk_summary_v.eqty_direction_rating_code,
           kfc_sector_v.kfc_sector_code,
           kfc_sector_v.kfc_sector_nme,
           kfc_sector_v.secondary_sector_nme,
           kfc_sector_v.primary_sector_nme,
           institution_status_code,
           lis.valuation_summary_v.effective_kfc_ownership_pct,
           kfc_companycommonownership_pct,
           miln.summary_date,
           country.ibrd_country_code,
           country.ida_member_date,
           institution.institution_legal_nme,
           institution.incorporation_country_code,
           ods.proj_info_lookup.regional_dept_nme,
           ods.proj_info_lookup.dept_alpha_code,
           miln.sector_group_nme,
           miln.south_south_project_ind,
           miln.existing_sponsor_ind,
           cleav.lending_eligibility_code,
           cleav.lending_eligibility_sub_code,
           miln.INDUSTRY_SUB_GROUP_ID,
           miln.INDUSTRY_SUB_GROUP_NME,
           miln.SUBNATIONAL_FINANCE_IND,
           miln.INDUSTRY_LVL2_SUB_GROUP_ID,
           miln.INDUSTRY_LVL2_SUB_GROUP_NME,
           miln.INDUSTRY_LVL3_SUB_GROUP_ID,
           miln.INDUSTRY_LVL3_SUB_GROUP_NME,
           miln.industry_cluster_nme,
           miln.SHORT_TERM_FINANCE_IND,
           miln.SUPER_REGION_CODE,
           miln.SUPER_REGION_SHORT_NME,
           miln.CLIMATE_CHANGE_IND,
           miln.CLIMATE_CHANGE_PCT
ORDER BY   miln.project_id;
Re: Query Tuning [message #548602 is a reply to message #548599] Fri, 23 March 2012 07:04 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
I would gather statistics on all the tables, and try the cost based optimizer.
Re: Query Tuning [message #548645 is a reply to message #548602] Fri, 23 March 2012 13:47 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
He is trying CBO, just cost is missing for some reason from the attached plan. If it was RBO you wouldn't get rows or bytes either.
Re: Query Tuning [message #548647 is a reply to message #548645] Fri, 23 March 2012 13:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query Tuning [message #548672 is a reply to message #548647] Sat, 24 March 2012 05:05 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
This won't be helping the performance any:
lis.project_team_staff_info (miln.project_id, 'I', 'N')

You're calling that function 12 times.
So for 750,000 records that'd be 9 million times.
It won't show up in the explain plan but it will in the tkprof.
Doing whatever that function does in the main select will probably make a big difference.

You've also got this one five times:
lis.get_Institution_Tier_Detail
Previous Topic: very slow on insert
Next Topic: Understanding Execution Plan of a query
Goto Forum:
  


Current Time: Mon Aug 02 11:28:48 CDT 2021