-
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.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Hi,
Can someone take out some time for this thread please...
Thanks in Adv.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|