DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Query Optimization

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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

  2. #2
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Angry

    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
  •  


Click Here to Expand Forum to Full Width