Query Optimization

Hi there,

Oracle 8.0.5/ NT 4.0.

I have following query and explain plan for the query. I want some guidence on how to optimize the query.

select /*+ ALL_ROWS */
a.project_id,
c.task_id,
gms.installment_id,
gi.award_id,
pra.resource_list_member_id,
nvl(prla.parent_member_id,-1)
from
gms_installments gi,
pa_projects_all a,
pa_Tasks c,
(select distinct installment_id,project_id from gms_project_fundings)gms,
pa_budget_versions pbv,
pa_resource_assignments pra
,pa_Resource_list_members prla
--(select distinct project_id,resource_list_member_id,budget_version_id from pa_resource_assignments) pra
where
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

Code:

Ope Exec Typ Order Explain Plan (coe_xplain_80.sql 8.0 20020120) --- ----- -------------------------------------------------------------------------------------------------------------------------------------------- ROW 18 SELECT STATEMENT Opt_Mode:HINT: ALL_ROWS ROW 17 . FILTER S/R 15 .. HASH JOIN ROW 1 ... TABLE ACCESS ***(FULL)*** OF 'PA.PA_PROJECTS_ALL' S/R 14 ... HASH JOIN S/R 12 .... HASH JOIN ROW 10 ....| NESTED LOOPS S/R 8 ....|. HASH JOIN ROW 2 ....|.. TABLE ACCESS ***(FULL)*** OF 'PA.PA_BUDGET_VERSIONS' S/R 7 ....|.. HASH JOIN ROW 3 ....|... TABLE ACCESS ***(FULL)*** OF 'GMS.GMS_INSTALLMENTS' ROW 6 ....|... VIEW OF 'APPS. SET 5 ....|.... SORT (UNIQUE) ROW 4 ....|....| TABLE ACCESS ***(FULL)*** OF 'GMS.GMS_PROJECT_FUNDINGS' ROW 9 ....|. INDEX (RANGE SCAN) OF 'PA.PA_RESOURCE_ASSIGNMENTS_U2' (UNIQUE) ROW 11 ....| TABLE ACCESS ***(FULL)*** OF 'PA.PA_RESOURCE_LIST_MEMBERS' ROW 13 .... TABLE ACCESS ***(FULL)*** OF 'PA.PA_TASKS' ROW 16 .. INDEX (RANGE SCAN) OF 'GMS.GMS_BUDGETARY_CONTROLS_U2' (UNIQUE) CBO Extended Plan ------------------------------------------------------------------------------------------------------------------------------------------ |Exec| Operation | Object Name | Rows | Bytes| Cost | TQ |IN-OUT| PQ Distrib | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------------------ | 18| SELECT STATEMENT | | 970 | 333K| 425 | | | | | | | 17| FILTER | | | | | | | | | | | 15| HASH JOIN | | 970 | 333K| 425 | | | | | | | 1| TABLE ACCESS FULL |PA_PROJECTS_ALL | 10 | 540 | 5 | | | | | | | 14| HASH JOIN | | 10K| 2M| 418 | | | | | | | 12| HASH JOIN | | 57 | 14K| 335 | | | | | | | 10| NESTED LOOPS | | 57 | 12K| 331 | | | | | | | 8| HASH JOIN | | 27 | 4K| 250 | | | | | | | 2| TABLE ACCESS FULL |PA_BUDGET_VERSIONS | 142 | 12K| 96 | | | | | | | 7| HASH JOIN | | 1K| 157K| 152 | | | | | | | 3| TABLE ACCESS FULL |GMS_INSTALLMENTS | 124 | 4K| 1 | | | | | | | 6| VIEW | | 1K| 90K| 150 | | | | | | | 5| SORT UNIQUE | | 1K| 559K| 150 | | | | | | | 4| TABLE ACCESS FULL |GMS_PROJECT_FUNDINGS | 22K| 559K| 34 | | | | | | | 9| INDEX RANGE SCAN |PA_RESOURCE_ASSIGNMENTS_U2 | 874K| 43M| 3 | | | | | | | 11| TABLE ACCESS FULL |PA_RESOURCE_LIST_MEMBERS | 219 | 5K| 2 | | | | | | | 13| TABLE ACCESS FULL |PA_TASKS | 18K| 714K| 80 | | | | | | | 16| INDEX RANGE SCAN |GMS_BUDGETARY_CONTROLS_U2 | 513 | 6K| 4 | | | | | | ------------------------------------------------------------------------------------------------------------------------------------------

Thanks in Advance.