I have following query and explain plan for the query. I want some guidence on how to optimize the query.
select /*+ ALL_ROWS */
(select distinct installment_id,project_id from gms_project_fundings)gms,
--(select distinct project_id,resource_list_member_id,budget_version_id from pa_resource_assignments) pra
not exists (select 1 from gms_budgetary_controls where a.project_id = project_id)
and a.project_id = c.project_id
and a.org_id = 0
and a.template_flag = 'N'
and c.project_id = gms.project_id
and gms.installment_id = gi.installment_id
and pbv.project_id = gms.project_id
and pbv.budget_type_Code = to_char(gi.award_id)
and pbv.budget_status_code = 'W'
and length(pbv.budget_type_Code) = 7
and pra.budget_version_id = pbv.budget_version_id
and pra.project_id = pbv.project_id
and pra.resourcE_list_member_id = prla.resourcE_list_member_id