Query Optimization

[samdba]

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

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.

[samdba]

Hi,

Can someone take out some time for this thread please...

Thanks in Adv.