AnsweredAssumed Answered

Performance Issue

Question asked by fabricio.de.marchi on Sep 4, 2012
Latest reply on Sep 13, 2012 by steve_seaney
Hi All,
We are having performance issues on our non-prod environment.
I have enabled the SQL TRACE in order to monitor database and I got a curious behavior.
When I check the trace log I can see a query taking over 360,000 seconds to run, it is pretty obvious that query is one of the issues we have.
The query in question is the one Clarity runs to get the project data when I click on a project. So, nothing I can change.

I got that query to run through any SQL tool in order to confirm the database problem and here it starts confusing me.
When I run the same query using Toad for example, form my own machine against the same database, it takes less then 2 seconds to run the same query.

Then I tried runing the same query from one of the application (weblogic) servers on the environment with issues, it also takes less then 2 seconds to run.

Finally I have created a Java program to run the same query from one of our application servers using the Clarity JDBC drivers, same result, less then 2 seconds to run the same query.

I can't figure out what else I could check so I am open to any suggestions.

This is the query in question:
SELECT 'x' pmd_analytical_partition_by     , inv_investments.id odf_pk     , fin_financials.forecast_roi forecast_roi     , odf_ca_inv.dell_it_eff_hrs_ssv dell_it_eff_hrs_ssv     , odf_ca_project.dell_start_by dell_start_by     , odf_ca_project.dell_cancel_by dell_cancel_by     , ROUND(          (  odf_ca_project.dell_it_cntry_deploy * 90.000000           + odf_ca_project.dell_it_end_usr_dply * 0.700000           + odf_ca_project.dell_it_lang_deploy * 80.000000           + odf_ca_project.dell_it_prog_deploy * 20.000000           + odf_ca_project.dell_it_requirements * 50.000000)          / (90.000000 + 0.700000 + 80.000000 + 20.000000 + 50.000000)        , 2)          dell_it_output_mtric     , odf_ca_project.dell_it_align_issues dell_it_align_issues     , odf_ca_project.dell_it_req_bus_unit dell_it_req_bus_unit     , odf_ca_project.dell_it_oasis_prog dell_it_oasis_prog     , odf_ca_project.dell_it_use_dpis_q_0 dell_it_use_dpis_q_0     , odf_ca_inv.dell_it_alock_start dell_it_alock_start     , odf_ca_inv.dell_bdgt_cst_start dell_bdgt_cst_start     , baserec.finish_date baseline_finish     , odf_ca_project.dell_it_impair_q_4 dell_it_impair_q_4     , odf_ca_inv.dell_dm_bl_finish dell_dm_bl_finish     , (  NVL((odf_ca_inv.dell_it_eff_hrs_csv * 1), 0)        + NVL((odf_ca_inv.dell_it_eff_hrs_dir * 1), 0)        + NVL((odf_ca_inv.dell_it_eff_hrs_ssv * 1), 0)        + NVL((odf_ca_inv.dell_it_eff_hrs_test * 1), 0))          dell_it_eff_hrs_ttl     , inv_investments.labor_variance_curve labor_variance     , odf_ca_project.dell_it_sp_id dell_it_sp_id     , odf_ca_inv.dell_cbase_deff_y0q4 dell_cbase_deff_y0q4     , fin_financials.planned_roi planned_roi     , odf_ca_project.dell_it_cntry_deploy dell_it_cntry_deploy     , pac_mnt_projects.materials_exchange_rate_type mtl_xchg_rate_type     , odf_ca_project.dell_it_fte_fy11 dell_it_fte_fy11     , odf_ca_project.dell_it_strategic_cl dell_it_strategic_cl     , odf_ca_inv.dell_fcst_cst_finish dell_fcst_cst_finish     , odf_ca_project.dell_it_val_q_1 dell_it_val_q_1     , odf_ca_inv.dell_it_sl_director dell_it_sl_director     , inv_projects.process_scale_work process_scale_work     , odf_ca_project.dell_notstart_date dell_notstart_date     , odf_ca_project.dell_db_platform dell_db_platform     , pac_mnt_projects.project_code project_code     , odf_ca_project.dell_add_infra dell_add_infra     , inv_projects.process_scale_bdgt process_scale_bdgt     , odf_ca_project.dell_it_dev_mgr dell_it_dev_mgr     , fin_financials.planned_cost plan_cost     , odf_ca_inv.dell_dep_bl_start dell_dep_bl_start     , odf_ca_project.dell_it_flagfas86_by dell_it_flagfas86_by     , inv_investments.odf_object_code investment_type     , fin_financials.is_calc_pv_info is_calc_pv_info     , odf_ca_project.dell_weblogic dell_weblogic     , fin_financials.planned_breakeven planned_breakeven     , odf_ca_inv.dell_plan_bl_start dell_plan_bl_start     , inv_projects.prusertext2 prusertext2     , odf_ca_inv.dell_it_eff_bsl_test dell_it_eff_bsl_test     , odf_ca_project.dell_it_dev_director dell_it_dev_director     , inv_investments.baseline_id baseline_id     , odf_ca_project.dell_it_impair_q_6 dell_it_impair_q_6     , odf_ca_project.dell_start_date dell_start_date     , pac_mnt_projects.status npio_status     , inv_projects.rcf_interdependency rcf_interdependency     , odf_ca_project.dell_it_excep_app_st dell_it_excep_app_st     , odf_ca_project.dell_it_acctg_valida dell_it_acctg_valida     , inv_investments.sla_compliance sla_compliance     , pac_mnt_projects.transratesourceequipment trans_rate_eqp_src     , odf_ca_inv.dell_cplan_teff_f_yr dell_cplan_teff_f_yr     , odf_ca_project.dell_it_impair_by dell_it_impair_by     , odf_ca_project.dell_it_impair_q_2 dell_it_impair_q_2     , odf_ca_inv.dell_dm_bl_start dell_dm_bl_start     , odf_ca_inv.dell_it_sl_segment dell_it_sl_segment     , inv_projects.prusertext4 prusertext4     , inv_projects.prguidelines prguidelines     , inv_projects.rcf_implementation rcf_implementation     , odf_ca_inv.dell_alock_teff_y0q2 dell_alock_teff_y0q2     , inv_projects.rcf_human_interface rcf_human_interface     , fin_financials.budget_rev_finish budget_rev_finish     , odf_ca_project.dell_it_ier_rank dell_it_ier_rank     , odf_ca_inv.dell_cplan_deff_p_yr dell_cplan_deff_p_yr     , (  NVL((odf_ca_inv.dell_cplan_teff_y0q1 * 1), 0)        + NVL((odf_ca_inv.dell_cplan_teff_y0q2 * 1), 0)        + NVL((odf_ca_inv.dell_cplan_teff_y0q3 * 1), 0)        + NVL((odf_ca_inv.dell_cplan_teff_y0q4 * 1), 0))          dell_cplan_teff_y0tl     , odf_ca_inv.dell_alock_teff_y0q4 dell_alock_teff_y0q4     , odf_ca_inv.dell_alock_deff_y0q4 dell_alock_deff_y0q4     , odf_ca_project.dell_it_impaired dell_it_impaired     , odf_ca_project.dell_it_sla_exempt dell_it_sla_exempt     , inv_projects.prcpmtype prcpmtype     , odf_ca_project.dell_ss_notes dell_ss_notes     , inv_investments.entity_code entity     , inv_projects.rcf_org_culture rcf_org_culture     , inv_projects.prusertext6 prusertext6     , inv_investments.chargecodeid chargecodeid     , pac_mnt_projects.transcostsourceexpenses trans_cost_exp_src     , odf_ca_inv.dell_env_bl_finish dell_env_bl_finish     , (NVL((odf_ca_project.dell_admin_ss_flag * 1), 0)) dell_ss_review     , odf_ca_project.dell_it_bus_sponsor dell_it_bus_sponsor     , NVL(inv_investments.purge_flag, 0) purge_flag     , inv_investments.schedule_start schedule_start     , odf_ca_project.dell_it_lu_prcss_flg dell_it_lu_prcss_flg     , inv_investments.stage_code stage_code     , odf_ca_project.dell_it_pad_ex_reas dell_it_pad_ex_reas     , odf_ca_inv.dell_alock_deff_y0q2 dell_alock_deff_y0q2     , pac_mnt_projects.transratesourcelabor trans_rate_lbr_src     , odf_ca_project.dell_it_ben_impact dell_it_ben_impact     , inv_investments.cbk_type cbk_type     , inv_projects.is_program is_program     , odf_ca_project.dell_approve_by dell_approve_by     , inv_investments.priority priority     , odf_ca_project.dell_it_set_plan_dat dell_it_set_plan_dat     , pac_mnt_projects.approved approved     , odf_ca_project.dell_it_gov_notes dell_it_gov_notes     , odf_ca_project.dell_it_theme dell_it_theme     , fin_financials.budget_roi budget_roi     , odf_ca_project.dell_it_initiative dell_it_initiative     , pac_mnt_projects.transratesourcematerials trans_rate_mtl_src     , odf_ca_project.dell_it_impair_q_8 dell_it_impair_q_8     , inv_investments.status status     , odf_ca_project.dell_test_alloc_crv dell_test_alloc_crv     , odf_ca_inv.dell_cbase_deff_y0q2 dell_cbase_deff_y0q2     , odf_ca_project.dell_it_cust_sat dell_it_cust_sat     , odf_ca_inv.obj_align_factor3 obj_align_factor3     , inv_projects.is_template is_template     , (baserec.usage_sum / 3600) / prj_hpd_factor_fct() baseline_usage     , odf_ca_project.dell_it_planned_fte dell_it_planned_fte     , (  NVL((odf_ca_project.dell_it_impair_q_1 * 1), 0)        + NVL((odf_ca_project.dell_it_impair_q_3 * 1), 0)        + NVL((odf_ca_project.dell_it_impair_q_5 * 1), 0)        + NVL((odf_ca_project.dell_it_impair_q_7 * 1), 0)        + NVL((odf_ca_project.dell_it_impair_q_9 * 1), 0))          dell_it_prop_impair     , odf_ca_project.dell_it_strategy_typ dell_it_strategy_typ     , pac_mnt_projects.departcode department_code     , odf_ca_project.dell_it_fas_val_q_2 dell_it_fas_val_q_2     , inv_investments.currency_code currency_code     , odf_ca_project.dell_it_val_q_5 dell_it_val_q_5     , fin_financials.budget_cst_finish budget_cst_finish     , odf_ca_inv.dell_acctg_practice dell_acctg_practice     , (  NVL((odf_ca_inv.dell_alock_deff_y0q1 * 1), 0)        + NVL((odf_ca_inv.dell_alock_deff_y0q2 * 1), 0)        + NVL((odf_ca_inv.dell_alock_deff_y0q3 * 1), 0)        + NVL((odf_ca_inv.dell_alock_deff_y0q4 * 1), 0))          dell_alock_deff_y0tl     , odf_ca_project.dell_it_fas_val_q_4 dell_it_fas_val_q_4     , odf_ca_project.dell_it_pm_mgr dell_it_pm_mgr     , odf_ca_inv.dell_alock_deff_f_yr dell_alock_deff_f_yr     , odf_ca_project.dell_it_flagsop98_by dell_it_flagsop98_by     , CASE          WHEN inv_projects.prbasefinish IS NULL THEN NULL          ELSE TRUNC(inv_projects.prbasefinish - 0.0001) - TRUNC(inv_investments.schedule_finish - 0.0001)       END          schedule_variance     , odf_ca_project.dell_it_fte_fy10 dell_it_fte_fy10     , odf_ca_project.dell_it_fas_val_q_6 dell_it_fas_val_q_6     , odf_ca_project.dell_admin_ss_flag dell_admin_ss_flag     , odf_ca_inv.dell_cbase_teff_y0q1 dell_cbase_teff_y0q1     , inv_projects.prpctcomplete prpctcomplete     , odf_ca_inv.dell_stab_bl_start dell_stab_bl_start     , odf_ca_project.dell_it_val_q_4 dell_it_val_q_4     , inv_investments.idea_id idea_id     , pac_mnt_projects.labor_exchange_rate_type lbr_xchg_rate_type     , odf_ca_project.dell_complete_date dell_complete_date     , pac_mnt_projects.ex_bill_expenses bill_expenses     , odf_ca_project.dell_it_bell_crv_cod dell_it_bell_crv_cod     , odf_ca_project.dell_reject_date dell_reject_date     , pac_mnt_projects.billcycle batch_cycle     , odf_ca_project.dell_it_sdlcappgrant dell_it_sdlcappgrant     , (CASE           WHEN inv_investments.schedule_start IS NOT NULL            AND inv_investments.schedule_finish IS NOT NULL THEN              (NVL(inv_investments.labor_etcsum, 0) / 3600)           ELSE              0        END)          labor_etcsum     , fin_financials.planned_npv planned_npv     , odf_ca_inv.dell_cplan_deff_y0q1 dell_cplan_deff_y0q1     , (CASE           WHEN (SELECT pma_calc_pv_cost_for_inv(inv_investments.id)                   FROM DUAL) > 0 THEN              ((SELECT fin_financials.planned_npv                  FROM fin_financials                     , odf_object_instance_mapping                 WHERE odf_object_instance_mapping.primary_object_instance_id = inv_investments.id                   AND odf_object_instance_mapping.secondary_object_instance_id = fin_financials.id                   AND odf_object_instance_mapping.primary_object_instance_code = inv_investments.odf_object_code                   AND odf_object_instance_mapping.secondary_object_instance_code = 'financials')               / (SELECT pma_calc_pv_cost_for_inv(inv_investments.id)                    FROM DUAL))           ELSE              0        END)          inv_planned_roi     , fin_financials.budget_npv budget_npv     , odf_ca_project.dell_it_oasis_code dell_it_oasis_code     , odf_ca_inv.dell_rm_bl_start dell_rm_bl_start     , odf_ca_inv.dell_cplan_teff_y0q3 dell_cplan_teff_y0q3     , inv_projects.rcf_technical rcf_technical     , odf_ca_project.dell_it_impair_date dell_it_impair_date     , odf_ca_project.dell_it_pad_ex_date dell_it_pad_ex_date     , odf_ca_project.dell_it_fgfas86_date dell_it_fgfas86_date     , inv_projects.prbasefinish prbasefinish     , odf_ca_project.dell_it_win_culture dell_it_win_culture     , pac_mnt_projects.billing_currency_code bill_currency_code     , odf_ca_project.dell_it_schdl_status dell_it_schdl_status     , (NVL((odf_ca_project.dell_it_cncl_rjt_rsn * 1), 0)) dell_it_cnclrjt_calc     , odf_ca_project.dell_stor_need dell_stor_need     , inv_projects.prfiscalstart prfiscalstart     , inv_investments.labor_etc_curve labor_etc     , fin_financials.planned_ben_finish planned_ben_finish     , odf_ca_project.dell_it_rdy_status dell_it_rdy_status     , (  NVL((odf_ca_inv.dell_alock_teff_y0q1 * 1), 0)        + NVL((odf_ca_inv.dell_alock_teff_y0q2 * 1), 0)        + NVL((odf_ca_inv.dell_alock_teff_y0q3 * 1), 0)        + NVL((odf_ca_inv.dell_alock_teff_y0q4 * 1), 0))          dell_alock_teff_y0tl     , odf_ca_project.dell_dr_class dell_dr_class     , odf_ca_project.dell_it_impair_q_1 dell_it_impair_q_1     , odf_ca_project.dell_it_sit_environ dell_it_sit_environ     , odf_ca_inv.dell_it_eff_bsl_ssv dell_it_eff_bsl_ssv     , odf_ca_inv.dell_bdgcst_currency dell_bdgcst_currency     , odf_ca_project.dell_it_weight_score dell_it_weight_score     , odf_ca_project.dell_it_requirements dell_it_requirements     , odf_ca_project.dell_it_req_dev_mgr dell_it_req_dev_mgr     , inv_investments.status_comment status_comment     , odf_ca_inv.obj_align_factor1 obj_align_factor1     , ROUND(          (  odf_ca_inv.obj_align_factor1 * 1.000000           + odf_ca_inv.obj_align_factor2 * 1.000000           + odf_ca_inv.obj_align_factor3 * 1.000000           + odf_ca_inv.obj_align_factor4 * 1.000000)          / (1.000000 + 1.000000 + 1.000000 + 1.000000)        , 0)          obj_alignment     , pac_mnt_projects.transratesourceexpenses trans_rate_exp_src     , odf_ca_project.dell_it_roi dell_it_roi     , odf_ca_inv.dell_cbase_teff_f_yr dell_cbase_teff_f_yr     , odf_ca_inv.dell_cplan_deff_y0q3 dell_cplan_deff_y0q3     , inv_investments.is_required is_required     , fin_financials.planned_cst_total planned_cst_total     , odf_ca_inv.dell_alock_deff_p_yr dell_alock_deff_p_yr     , odf_ca_project.dell_it_resrc_status dell_it_resrc_status     , odf_ca_project.dell_it_just dell_it_just     , odf_ca_project.dell_it_use_dpis_q_2 dell_it_use_dpis_q_2     , odf_ca_project.dell_it_set_stb_by dell_it_set_stb_by     , odf_ca_project.dell_it_acctg_cmmts dell_it_acctg_cmmts     , odf_ca_inv.dell_it_sl_prg_mgr dell_it_sl_prg_mgr     , odf_ca_project.dell_it_elt_review dell_it_elt_review     , odf_ca_project.dell_it_tlt_est_cost dell_it_tlt_est_cost     , odf_ca_project.dell_it_sdlc_status dell_it_sdlc_status     , odf_ca_project.dell_it_nonprj_class dell_it_nonprj_class     , odf_ca_project.dell_it_sdlc_metused dell_it_sdlc_metused     , odf_ca_project.dell_it_set_dep_by dell_it_set_dep_by     , odf_ca_inv.dell_stab_bl_finish dell_stab_bl_finish     , (SELECT NVL(SUM(b.totalcost), 0)          FROM ppa_wip a             , ppa_wip_values b         WHERE a.transno = b.transno           AND b.currency_type = 'HOME'           AND a.status = 0           AND a.project_code = NLS_UPPER(inv_investments.code)        GROUP BY a.project_code)          aggr_actual_cost     , (SELECT ix.name          FROM inv_investments ix         WHERE ix.id = inv_investments.idea_id)          idea_name     , inv_investments.progress progress     , odf_ca_inv.dell_cplan_teff_y0q1 dell_cplan_teff_y0q1     , odf_ca_inv.dell_it_eff_bsl_dir dell_it_eff_bsl_dir     , odf_ca_project.dell_it_ranking dell_it_ranking     , odf_ca_project.dell_it_prpsd_in_pln dell_it_prpsd_in_pln     , inv_investments.schedule_finish schedule_finish     , odf_ca_inv.dell_it_eff_b_finish dell_it_eff_b_finish     , odf_ca_project.dell_it_flagtf_date dell_it_flagtf_date     , updated.full_name updated_by     , inv_investments.manager_id manager_id     , (  NVL((odf_ca_inv.dell_it_eff_bsl_csv * 1), 0)        + NVL((odf_ca_inv.dell_it_eff_bsl_dir * 1), 0)        + NVL((odf_ca_inv.dell_it_eff_bsl_ssv * 1), 0)        + NVL((odf_ca_inv.dell_it_eff_bsl_test * 1), 0))          dell_it_eff_bsl_ttl     , odf_ca_project.dell_it_bdgt_status dell_it_bdgt_status     , inv_projects.rcf_objectives rcf_objectives     , odf_ca_inv.dell_it_alock_end dell_it_alock_end     , odf_ca_project.dell_it_bot_prgm dell_it_bot_prgm     , odf_ca_project.dell_it_sftwr_reltyp dell_it_sftwr_reltyp     , odf_ca_project.dell_admin_val_2 dell_admin_val_2     , odf_ca_project.dell_elt_sponsor dell_elt_sponsor     , odf_ca_inv.dell_opa_prj_id dell_opa_prj_id     , odf_ca_project.dell_rem_access_cp dell_rem_access_cp     , fin_financials.budget_rev_start budget_rev_start     , odf_ca_project.dell_centrl_pmo_cont dell_centrl_pmo_cont     , odf_ca_inv.dell_cbase_teff_y0q3 dell_cbase_teff_y0q3     , created.full_name created_by     , odf_ca_project.dell_it_bus_prg_mgr dell_it_bus_prg_mgr     , odf_ca_project.dell_it_set_env_by dell_it_set_env_by     , odf_ca_project.dell_it_fte_fy09 dell_it_fte_fy09     , fin_financials.forecast_rev_finish forecast_rev_finish     , odf_ca_inv.dell_cbase_deff_y0q3 dell_cbase_deff_y0q3     , inv_investments.is_open_for_te ISOPEN     , pac_mnt_projects.transcostsourcelabor trans_cost_lbr_src     , inv_projects.req_approval_reqd req_approval_reqd     , inv_projects.prbasetime prbasetime     , pac_mnt_projects.billing_project_id billing_project_id     , (CASE           WHEN inv_investments.schedule_start IS NOT NULL            AND inv_investments.schedule_finish IS NOT NULL THEN              (NVL(inv_investments.labor_eacsum, 0) / 3600)           ELSE              0        END)          labor_eacsum     , odf_ca_project.dell_it_impair_up_by dell_it_impair_up_by     , inv_investments.rsf_obs_unit rsf_obs_unit     , pac_mnt_projects.affiliateproject affiliate_project     , odf_ca_project.dell_it_fas_val_q_1 dell_it_fas_val_q_1     , inv_investments.labor_act_curve labor_act     , odf_ca_inv.obj_align_factor4 obj_align_factor4     , odf_ca_project.dell_it_size dell_it_size     , inv_projects.prversion prversion     , odf_ca_inv.dell_alock_teff_p_yr dell_alock_teff_p_yr     , odf_ca_project.dell_it_impair_q_3 dell_it_impair_q_3     , updated.id updated_by_id     , inv_projects.prusertext1 prusertext1     , odf_ca_project.dell_resume_date dell_resume_date     , inv_projects.pruid pruid     , odf_ca_project.dell_it_impair_q_10 dell_it_impair_q_10     , inv_investments.code unique_code     , odf_ca_project.dell_r_ss_review dell_r_ss_review     , odf_ca_inv.dell_cplan_deff_f_yr dell_cplan_deff_f_yr     , odf_ca_project.dell_it_tlt_est_ben dell_it_tlt_est_ben     , odf_ca_project.dell_it_sop98 dell_it_sop98     , inv_projects.prusertext5 prusertext5     , fin_financials.budget_rev_total budget_rev_total     , (  NVL((odf_ca_inv.dell_cplan_deff_y0q1 * 1), 0)        + NVL((odf_ca_inv.dell_cplan_deff_y0q2 * 1), 0)       + NVL((odf_ca_inv.dell_cplan_deff_y0q3 * 1), 0)        + NVL((odf_ca_inv.dell_cplan_deff_y0q4 * 1), 0))          dell_cplan_deff_y0tl     , inv_projects.rcf_sponsorship rcf_sponsorship     , fin_financials.forecast_rev_total forecast_rev_total     , odf_ca_project.dell_it_prgm_support dell_it_prgm_support     , inv_projects.prformat prformat     , odf_ca_project.dell_onhold_date dell_onhold_date     , ((NVL(inv_investments.labor_etcsum, 0) + NVL(inv_investments.labor_actsum, 0)) / 3600) labor_effort     , fin_financials.is_fcst_eq_bdgt is_fcst_eq_bdgt     , fin_financials.budget_cst_start budget_cst_start     , odf_ca_project.dell_it_pdm_mgr dell_it_pdm_mgr     , odf_ca_project.dell_it_pis dell_it_pis     , CASE          WHEN NVL(inv_investments.labor_eacsum, 0) > 0 THEN             NVL(inv_investments.labor_actsum, 0) / NVL(inv_investments.labor_eacsum, 0)          ELSE             0       END          pct_complete     , odf_ca_project.dell_it_impair_q_5 dell_it_impair_q_5     , odf_ca_project.dell_it_eval_dep_stm dell_it_eval_dep_stm     , baserec.start_date baseline_start     , odf_ca_project.dell_new_soft dell_new_soft     , pac_mnt_projects.master_project_code master_project_code     , odf_ca_project.dell_db_impact dell_db_impact     , odf_ca_inv.dell_it_eff_b_start dell_it_eff_b_start     , inv_investments.created_date created_date     , inv_projects.prdepartment prdepartment     , odf_ca_project.dell_it_test_mgr dell_it_test_mgr     , odf_ca_project.dell_it_ss_needed dell_it_ss_needed     , inv_projects.prfinishimposed prfinishimposed     , (NVL((odf_ca_project.dell_it_use_dpis_q_0 * 1), 0)) dell_it_prop_pis     , odf_ca_project.dell_complete_by dell_complete_by     , pac_mnt_projects.locationid location_code     , fin_financials.forecast_rev_start forecast_rev_start     , inv_projects.prusertext3 prusertext3     , fin_financials.planned_cst_start planned_cst_start     , inv_investments.labor_eac_curve labor_eac     , odf_ca_inv.dell_alock_teff_y0q3 dell_alock_teff_y0q3     , (NVL(inv_investments.labor_etcsum, 0) / 3600)       / (SELECT hours_per_day            FROM prj_hours_per_day_v)          etc     , inv_projects.prrequestedtime prrequestedtime     , odf_ca_project.dell_it_impair_up_on dell_it_impair_up_on     , odf_ca_inv.dell_alock_deff_y0q1 dell_alock_deff_y0q1     , (  NVL((odf_ca_inv.dell_cbase_deff_y0q1 * 1), 0)        + NVL((odf_ca_inv.dell_cbase_deff_y0q2 * 1), 0)        + NVL((odf_ca_inv.dell_cbase_deff_y0q3 * 1), 0)        + NVL((odf_ca_inv.dell_cbase_deff_y0q4 * 1), 0))          dell_cbase_deff_y0tl     , odf_ca_project.dell_it_growth dell_it_growth     , odf_ca_inv.dell_cbase_teff_p_yr dell_cbase_teff_p_yr     , odf_ca_project.dell_cancel_date dell_cancel_date     , inv_projects.rcf_supportability rcf_supportability     , inv_projects.rcf_resource_avail rcf_resource_avail     , inv_investments.labor_baseline_curve labor_base     , inv_investments.last_updated_date last_updated_date     , pac_mnt_projects.equipment_exchange_rate_type eqp_xchg_rate_type     , odf_ca_project.dell_resume_by dell_resume_by     , odf_ca_project.dell_it_set_env_date dell_it_set_env_date     , inv_investments.goal_code goal_code     , inv_investments.labor_variancesum labor_variancesum     , inv_projects.prbudget prbudget     , odf_ca_project.dell_it_impair_q_9 dell_it_impair_q_9     , fin_financials.budget_breakeven budget_breakeven     , CASE WHEN inv_investments.status = 1 THEN 1 WHEN inv_investments.status = 5 THEN 1 ELSE 0 END is_approved     , odf_ca_project.dell_lead_architect dell_lead_architect     , odf_ca_project.dell_it_rllout_rgion dell_it_rllout_rgion     , odf_ca_project.dell_it_impair_q_7 dell_it_impair_q_7     , odf_ca_inv.dell_inv_cat dell_inv_cat     , odf_ca_project.dell_it_fin_treatmnt dell_it_fin_treatmnt     , pac_mnt_projects.class investment_class     , odf_ca_inv.dell_alock_deff_y0q3 dell_alock_deff_y0q3     , odf_ca_project.dell_os_platform dell_os_platform     , odf_ca_inv.obj_align_factor2 obj_align_factor2     , odf_ca_project.dell_it_excep_apprvr dell_it_excep_apprvr     , odf_ca_inv.dell_alock_teff_y0q1 dell_alock_teff_y0q1     , odf_ca_project.dell_it_is_bus_prgm dell_it_is_bus_prgm     , inv_projects.prusertext7 prusertext7     , odf_ca_project.dell_it_flagexp_by dell_it_flagexp_by     , fin_financials.budget_cst_total budget_cst_total     , odf_ca_project.dell_it_exec_owner dell_it_exec_owner     , fin_financials.planned_ben_start planned_ben_start     , odf_ca_inv.dell_cbase_deff_y0q1 dell_cbase_deff_y0q1     , (  inv_projects.rcf_flexibility * 1.000000        + inv_projects.rcf_funding * 1.000000        + inv_projects.rcf_human_interface * 1.000000        + inv_projects.rcf_implementation * 1.000000        + inv_projects.rcf_interdependency * 1.000000        + inv_projects.rcf_objectives * 1.000000        + inv_projects.rcf_org_culture * 1.000000        + inv_projects.rcf_resource_avail * 1.000000        + inv_projects.rcf_sponsorship * 1.000000        + inv_projects.rcf_supportability * 1.000000        + inv_projects.rcf_technical * 1.000000)       / (  1.000000          + 1.000000          + 1.000000          + 1.000000          + 1.000000          + 1.000000          + 1.000000          + 1.000000          + 1.000000          + 1.000000          + 1.000000)          risk     , odf_ca_inv.dell_fctcst_currency dell_fctcst_currency     , odf_ca_project.dell_it_flagexp_date dell_it_flagexp_date     , inv_investments.approvedtime approvedtime     , inv_projects.prbasestart prbasestart     , odf_ca_project.dell_it_set_stb_date dell_it_set_stb_date     , odf_ca_project.dell_n_agent dell_n_agent     , odf_ca_project.dell_it_flagtf_by dell_it_flagtf_by     , odf_ca_project.dell_it_priorty_clas dell_it_priorty_clas     , odf_ca_project.dell_it_test_status dell_it_test_status     , odf_ca_inv.dell_it_req_dacct_m dell_it_req_dacct_m     , odf_ca_project.dell_approve_date dell_approve_date     , odf_ca_project.dell_it_ea_comp_req dell_it_ea_comp_req     , inv_projects.prrequestedby prrequestedby     , (NVL(inv_investments.labor_basesum, 0) / 3600) labor_basesum     , fin_financials.forecast_cst_finish forecast_cst_finish     , odf_ca_inv.dell_cplan_teff_y0q4 dell_cplan_teff_y0q4     , fin_financials.planned_benefit plan_benefit     , odf_ca_project.dell_it_fas_val_q_5 dell_it_fas_val_q_5     , inv_projects.prstartimposed prstartimposed     , baserec.cost_sum baseline_cost     , inv_projects.prsponsoredby prsponsoredby     , pac_mnt_projects.bill_to_company_code bill_to_id     , inv_projects.prasof prasof     , odf_ca_project.dell_it_val_sop_amt dell_it_val_sop_amt     , odf_ca_project.dell_it_mstr_prog dell_it_mstr_prog     , inv_projects.prclosedtime prclosedtime     , odf_ca_project.dell_it_fas_val_q_3 dell_it_fas_val_q_3     , inv_projects.rcf_funding rcf_funding     , odf_ca_project.partition_code partition_code     , (SELECT fin_financials.planned_npv          FROM fin_financials             , odf_object_instance_mapping         WHERE odf_object_instance_mapping.primary_object_instance_id = inv_investments.id           AND odf_object_instance_mapping.secondary_object_instance_id = fin_financials.id           AND odf_object_instance_mapping.primary_object_instance_code = inv_investments.odf_object_code           AND odf_object_instance_mapping.secondary_object_instance_code = 'financials')          inv_planned_npv     , NVL(          (SELECT COUNT(id)             FROM cmn_lookups_v            WHERE parent_lookup_code = inv_investments.process_code              AND language_code = 'en'              AND is_active = 1              AND sort_order <= (SELECT sort_order                                   FROM cmn_lookups_v                                  WHERE lookup_code = inv_investments.stage_code                                    AND lookup_type = 'INV_STAGE_TYPE'                                    AND language_code = 'en'))        , 0)          stage_number     , odf_ca_project.dell_it_sdlc_dev_app dell_it_sdlc_dev_app     , odf_ca_project.dell_it_lang_deploy dell_it_lang_deploy     , odf_ca_project.dell_it_prog_deploy dell_it_prog_deploy     , (SELECT COUNT(id)          FROM cmn_lookups_v         WHERE parent_lookup_code = inv_investments.process_code           AND is_active = 1           AND language_code = 'en')          stage_count     , fin_financials.forecast_cst_total forecast_cst_total     , inv_investments.is_active is_active     , odf_ca_project.dell_admin_val_3 dell_admin_val_3     , odf_ca_project.dell_it_pad_ex_auth dell_it_pad_ex_auth     , odf_ca_inv.dell_it_eff_hrs_dir dell_it_eff_hrs_dir     , odf_ca_project.dell_data_class dell_data_class     , odf_ca_project.dell_it_set_dep_date dell_it_set_dep_date     , odf_ca_project.dell_it_breakout dell_it_breakout     , fin_financials.forecast_cst_start forecast_cst_start     , odf_ca_inv.dell_fctcst dell_fctcst     , odf_ca_inv.dell_bdgt_cst_finish dell_bdgt_cst_finish     , odf_ca_project.dell_base_cost dell_base_cost     , odf_ca_project.dell_it_tf_flag dell_it_tf_flag     , odf_ca_inv.dell_it_eff_hrs_test dell_it_eff_hrs_test     , fin_financials.forecast_npv forecast_npv     , odf_ca_project.dell_it_excep_reason dell_it_excep_reason     , inv_investments.name name     , odf_ca_inv.dell_cplan_deff_y0q2 dell_cplan_deff_y0q2     , odf_ca_project.dell_it_dpisupdat_on dell_it_dpisupdat_on     , (  NVL((odf_ca_inv.dell_cbase_teff_y0q1 * 1), 0)        + NVL((odf_ca_inv.dell_cbase_teff_y0q2 * 1), 0)       + NVL((odf_ca_inv.dell_cbase_teff_y0q3 * 1), 0)        + NVL((odf_ca_inv.dell_cbase_teff_y0q4 * 1), 0))          dell_cbase_teff_y0tl     , (NVL(inv_investments.labor_actsum, 0) / 3600)       / (SELECT hours_per_day            FROM prj_hours_per_day_v)          actuals     , odf_ca_project.dell_it_burdened dell_it_burdened     , inv_investments.approvedby_id approvedby_id     , odf_ca_inv.dell_cbase_deff_f_yr dell_cbase_deff_f_yr     , odf_ca_inv.dell_cbase_deff_p_yr dell_cbase_deff_p_yr     , odf_ca_project.dell_vc_conn dell_vc_conn     , odf_ca_inv.dell_fcst_cst_start dell_fcst_cst_start     , odf_ca_inv.dell_cplan_deff_y0q4 dell_cplan_deff_y0q4     , odf_ca_project.dell_reject_by dell_reject_by     , odf_ca_project.dell_it_cncl_rjt_rsn dell_it_cncl_rjt_rsn     , odf_ca_inv.dell_cplan_teff_y0q2 dell_cplan_teff_y0q2     , pac_mnt_projects.wipclass wip_class     , odf_ca_project.dell_ser_impact dell_ser_impact     , odf_ca_inv.dell_it_eff_hrs_csv dell_it_eff_hrs_csv     , inv_investments.alignment alignment     , odf_ca_project.dell_it_use_dpis_q_1 dell_it_use_dpis_q_1     , odf_ca_inv.dell_it_ccrb_id dell_it_ccrb_id     , odf_ca_project.dell_db_change dell_db_change     , odf_ca_inv.dell_rm_bl_finish dell_rm_bl_finish     , odf_ca_project.dell_not_start_by dell_not_start_by     , inv_investments.bill_expense_type bill_expense_type     , (CASE           WHEN inv_investments.schedule_start IS NOT NULL            AND inv_investments.schedule_finish IS NOT NULL THEN              (NVL(inv_investments.labor_actsum, 0) / 3600)           ELSE              0        END)          labor_actsum     , odf_ca_project.dell_it_vit dell_it_vit     , odf_ca_project.dell_it_use_dpis_q_3 dell_it_use_dpis_q_3     , odf_ca_project.dell_onhold_by dell_onhold_by     , inv_projects.TYPE inv_fin_type     , odf_ca_project.dell_it_moscow dell_it_moscow     , odf_ca_project.dell_it_set_plan_by dell_it_set_plan_by     , odf_ca_inv.dell_cplan_teff_p_yr dell_cplan_teff_p_yr     , odf_ca_project.dell_it_val_q_2 dell_it_val_q_2     , odf_ca_inv.dell_it_ccrb dell_it_ccrb     , odf_ca_project.dell_it_eacom_status dell_it_eacom_status     , inv_investments.status_indicator status_indicator     , pac_mnt_projects.resource_code resource_code     , odf_ca_project.dell_plan_eff_hrs dell_plan_eff_hrs     , odf_ca_inv.dell_bdgcst dell_bdgcst     , odf_ca_inv.dell_alock_teff_f_yr dell_alock_teff_f_yr     , odf_ca_project.dell_it_ben_valdated dell_it_ben_valdated     , inv_projects.rcf_flexibility rcf_flexibility     , odf_ca_inv.odf_object_code odf_object_code     , pac_mnt_projects.transcostsourceequipment trans_cost_eqp_src     , pac_mnt_projects.company_code company_code     , odf_ca_project.dell_it_dpisupdat_by dell_it_dpisupdat_by     , pac_mnt_projects.transcostsourcematerials trans_cost_mtl_src     , odf_ca_inv.dell_it_sl_pmo_cont dell_it_sl_pmo_cont     , odf_ca_inv.dell_cbase_teff_y0q2 dell_cbase_teff_y0q2     , odf_ca_project.dell_it_fgsop98_date dell_it_fgsop98_date     , (SELECT pma_calc_pv_cost_for_inv(inv_investments.id)          FROM DUAL)          planned_pv_cost     , odf_ca_project.dell_alloc_crv dell_alloc_crv     , fin_financials.planned_cst_finish planned_cst_finish     , odf_ca_inv.dell_dev_bl_start dell_dev_bl_start     , pac_mnt_projects.expense_exchange_rate_type exp_xchg_rate_type     , odf_ca_project.dell_test10 dell_test10     , odf_ca_project.dell_it_ben_descrip dell_it_ben_descrip     , odf_ca_project.dell_it_set_dev_date dell_it_set_dev_date     , fin_financials.planned_ben_total planned_ben_total     , odf_ca_project.dell_it_deploy_focus dell_it_deploy_focus     , odf_ca_project.dell_it_prgm_obj_cap dell_it_prgm_obj_cap     , (NVL(inv_investments.labor_eacsum, 0) / 3600)       / (SELECT hours_per_day            FROM prj_hours_per_day_v)          ttl_effort     , odf_ca_project.dell_rem_access_emp dell_rem_access_emp     , odf_ca_project.dell_admin_val_1 dell_admin_val_1     , odf_ca_project.dell_it_ben_detail dell_it_ben_detail     , odf_ca_project.dell_it_pis_by dell_it_pis_by     , odf_ca_inv.dell_it_eff_bsl_csv dell_it_eff_bsl_csv     , inv_investments.track_mode trackmode     , odf_ca_project.dell_it_end_usr_dply dell_it_end_usr_dply     , odf_ca_project.dell_it_scope_status dell_it_scope_status     , odf_ca_project.dell_it_product_mang dell_it_product_mang     , fin_financials.forecast_breakeven forecast_breakeven     , (SELECT DECODE(cmn_pages.principal_type, 'SYSTEM', cmn_pages.id, NVL(cmn_pages.originating_page_id, cmn_pages.id))          FROM cmn_instance_pages             , cmn_pages         WHERE cmn_instance_pages.page_frame_id = cmn_pages.id           AND cmn_instance_pages.object_instance_id = inv_investments.id           AND cmn_instance_pages.object_type = 'SRM_PROJECTS')          page_layout     , (NVL(           (SELECT fin_financials.planned_cst_total              FROM fin_financials                 , odf_object_instance_mapping             WHERE odf_object_instance_mapping.primary_object_instance_id = inv_investments.id               AND odf_object_instance_mapping.secondary_object_instance_id = fin_financials.id               AND odf_object_instance_mapping.primary_object_instance_code = inv_investments.odf_object_code               AND odf_object_instance_mapping.secondary_object_instance_code = 'financials')         , 0))       - (NVL(             (SELECT SUM(b.totalcost)                FROM ppa_wip a                   , ppa_wip_values b               WHERE a.transno = b.transno                 AND b.currency_type = 'HOME'                 AND a.status = 0                 AND a.project_code = NLS_UPPER(inv_investments.code)              GROUP BY a.project_code)           , 0))          cost_variance     , inv_investments.sync_inv_n_bdg_dates sync_inv_n_bdg_dates     , odf_ca_project.dell_it_set_dev_by dell_it_set_dev_by     , odf_ca_inv.dell_cbase_teff_y0q4 dell_cbase_teff_y0q4     , inv_investments.description description     , odf_ca_project.dell_it_pis_date dell_it_pis_date     , odf_ca_project.dell_annual_cost dell_annual_cost     , created.id created_by_id     , odf_ca_inv.dell_plan_bl_finish dell_plan_bl_finish     , odf_ca_inv.dell_dev_bl_finish dell_dev_bl_finish  FROM inv_investments     , inv_projects     , odf_ca_inv     , fin_financials     , odf_object_instance_mapping oim3     , odf_ca_financials     , pac_mnt_projects     , odf_ca_projfinproperties     , odf_ca_project     , prj_baseline_details baserec     , srm_resources created     , srm_resources updated WHERE inv_investments.id = inv_projects.prid   AND inv_projects.prid = pac_mnt_projects.id   AND inv_investments.baseline_id = baserec.baseline_id(+)   AND 'PROJECT' = baserec.object_type(+)   AND inv_investments.created_by = created.user_id   AND inv_investments.last_updated_by = updated.user_id   AND inv_investments.id = inv_projects.prid   AND inv_investments.id = odf_ca_inv.id   AND inv_investments.id = oim3.primary_object_instance_id   AND oim3.primary_object_instance_code = 'project'   AND fin_financials.id = oim3.secondary_object_instance_id   AND oim3.secondary_object_instance_code = 'financials'   AND fin_financials.id = odf_ca_financials.id   AND inv_investments.id = pac_mnt_projects.id   AND inv_investments.id = odf_ca_projfinproperties.id   AND inv_investments.id = odf_ca_project.id   AND odf_ca_inv.odf_object_code = 'project'   AND inv_investments.id = 5007305
Also, here it is the SQL TRACE results for the same query:
  NDE:CONN@8747: prepareStatement(string)
  NDE:PSTM@8857: setObject(): 1, project
  NDE:PSTM@8857: setLong(): 2, 5007305
  NDE:PSTM@8857: execute(): ExecuteTime: 376344
Regards,
Fabricio

Outcomes