Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unanswered: Query Tuning Required

    I need to tune the query given below which I find to be extremely I/O Intensive on my database.

    Query :
    ---------
    SELECT project_number, end_client
    FROM tcs_mis_project_customers_v b

    View tcs_mis_project_customers_v Definition
    -----------------------------------------------------
    CREATE OR REPLACE VIEW TCS_MIS_PROJECT_CUSTOMERS_V
    (project_id, project_number, project_name, end_client_id, end_client, local_company_id, local_company, prime_contractor_id, prime_contractor, group_client, local_company_flag, customer_active_date)
    AS
    SELECT ppa.project_id, ppa.segment1 "Project_Number",
    ppa.name "Project_name",
    NVL(end_client.customer_id, 0) "End_client_id",
    NVL(end_client.party_name, 'N/A') "End_Client",
    NVL(lc.customer_id, 0) "local_company_id",
    NVL(lc.party_name, 'N/A') "Local_company",
    NVL(pc.customer_id, 0) "prime_contractor_id",
    NVL(pc.party_name, 'N/A') "Prime_contractor",
    NVL(end_client.customer_name_phonetic, 'N/A') "Group_Client",
    NVL(ppc.class_code, 'N/A') "Local_company_flag",
    end_client.creation_date "Customer_Active_Date"
    FROM pa_projects_all ppa,
    (SELECT ppc.project_id, rc.customer_id, hp.party_name,
    rc.customer_name_phonetic,
    TRUNC (rc.creation_date) creation_date
    FROM pa_project_customers ppc, ra_customers rc, hz_parties hp
    WHERE ppc.customer_id = rc.customer_id
    AND rc.party_id = hp.party_id
    AND hp.party_type = 'ORGANIZATION'
    AND ppc.project_relationship_code = 'END_CLIENT') end_client,
    (SELECT ppc.project_id, rc.customer_id, hp.party_name
    FROM pa_project_customers ppc, ra_customers rc, hz_parties hp
    WHERE ppc.customer_id = rc.customer_id
    AND rc.party_id = hp.party_id
    AND hp.party_type = 'ORGANIZATION'
    AND ppc.project_relationship_code = 'LOCAL_COMPANY') lc,
    (SELECT ppc.project_id, rc.customer_id, hp.party_name
    FROM pa_project_customers ppc, ra_customers rc, hz_parties hp
    WHERE ppc.customer_id = rc.customer_id
    AND rc.party_id = hp.party_id
    AND hp.party_type = 'ORGANIZATION'
    AND ppc.project_relationship_code = 'PRIME_CONTRACTOR') pc,
    pa_project_classes ppc
    WHERE ppa.template_flag = 'N'
    AND ppa.project_id = end_client.project_id(+)
    AND ppa.project_id = lc.project_id(+)
    AND ppa.project_id = pc.project_id(+)
    AND ppa.project_id = ppc.project_id(+)
    AND ppc.class_category = 'Local Company Involved'

    Explain Plan:
    ---------------
    --------------------------------------------------------------------------------
    | Operation | PHV/Object Name | Rows | Bytes| Cost |
    --------------------------------------------------------------------------------
    |SELECT STATEMENT |----- 2012689046 ----| | | 71512 |
    |NESTED LOOPS OUTER | | 50K| 3M| 71512 |
    | NESTED LOOPS OUTER | | 50K| 3M| 71512 |
    | NESTED LOOPS OUTER | | 23K| 1M| 23906 |
    | HASH JOIN | | 23K| 976K| 103 |
    | TABLE ACCESS FULL |PA_PROJECTS_ALL | 24K| 377K| 41 |
    | INDEX FAST FULL SCAN |PA_PROJECT_CLASSES_U | 23K| 604K| 49 |
    | VIEW PUSHED PREDICATE | | 1 | 5 | 1 |
    | NESTED LOOPS | | 77 | 3K| 80 |
    | NESTED LOOPS | | 77 | 2K| 3 |
    | TABLE ACCESS BY INDEX ROWI|PA_PROJECT_CUSTOMERS | 1 | 22 | 2 |
    | INDEX RANGE SCAN |PA_PROJECT_CUSTOMERS | 1 | | 1 |
    | TABLE ACCESS BY INDEX ROWI|RA_CUSTOMERS | 3K| 36K| 1 |
    | INDEX UNIQUE SCAN |RA_CUSTOMERS_U1 | 1 | | |
    | TABLE ACCESS BY INDEX ROWID|HZ_PARTIES | 1 | 14 | 1 |
    | INDEX UNIQUE SCAN |HZ_PARTIES_PK | 3 | | |
    | VIEW PUSHED PREDICATE | | 2 | 44 | 2 |
    | HASH JOIN | | 2K| 123K| 175 |
    | NESTED LOOPS | | 2K| 62K| 3 |
    | TABLE ACCESS BY INDEX ROWID|PA_PROJECT_CUSTOMERS | 1 | 22 | 2 |
    | INDEX RANGE SCAN |PA_PROJECT_CUSTOMERS | 1 | | 1 |
    | TABLE ACCESS BY INDEX ROWID|RA_CUSTOMERS | 1K| 18K| 1 |
    | INDEX UNIQUE SCAN |RA_CUSTOMERS_U1 | 1 | | |
    | TABLE ACCESS FULL |HZ_PARTIES | 52K| 1M| 168 |
    | VIEW PUSHED PREDICATE | | 1 | 5 | |
    | HASH JOIN | | 1K| 72K| 173 |
    | NESTED LOOPS | | 1K| 50K| 3 |
    | TABLE ACCESS BY INDEX ROWID |PA_PROJECT_CUSTOMERS | 1 | 22 | 2 |
    | INDEX RANGE SCAN |PA_PROJECT_CUSTOMERS | 1 | | 1 |
    | TABLE ACCESS BY INDEX ROWID |RA_CUSTOMERS | 3K| 36K| 1 |
    | INDEX UNIQUE SCAN |RA_CUSTOMERS_U1 | 1 | | |
    | TABLE ACCESS FULL |HZ_PARTIES | 52K| 714K| 168 |
    --------------------------------------------------------------------------------

  2. #2
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Statspack Report for the query attached.
    Attached Files Attached Files

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    tell me if this works.
    I still don't like the outer joins however ...

    PHP Code:
    SELECT 
      ppa
    .project_id,
      
    ppa.segment1 "Project_Number",
      
    ppa.name "Project_name",
      
    MAX(DECODE,project.project_relationship_code'END_CLIENT'NVL(project.customer_id,0)) "End_client_id",
      
    MAX(DECODE,project.project_relationship_code'END_CLIENT'NVL(project.party_name'N/A')) "End_Client",
      
    MAX(DECODE,project.project_relationship_code'LOCAL_COMPANY'NVL(project.customer_id0)) "local_company_id",
      
    MAX(DECODE,project.project_relationship_code'LOCAL_COMPANY'NVL(project.party_name'N/A')) "Local_company",
      
    MAX(DECODE,project.project_relationship_code'PRIME_CONTRACTOR'NVL(project.customer_id0)) "prime_contractor_id",
      
    MAX(DECODE,project.project_relationship_code'PRIME_CONTRACTOR'NVL(project.party_name'N/A')) "Prime_contractor",
      
    MAX(DECODE,project.project_relationship_code'END_CLIENT'NVL(project.customer_name_phonetic'N/A')) "Group_Client",
      
    NVL(ppc.class_code'N/A'"Local_company_flag",
      
    MAX(DECODE,project.project_relationship_code'END_CLIENT'end_client.creation_dateNULL)) "Customer_Active_Date"
    FROM 
      pa_projects_all ppa
    ,
      
    pa_project_classes ppc,
      (
    SELECT 
        ppc
    .project_id
        
    rc.customer_id
        
    hp.party_name
        
    rc.customer_name_phonetic,
        
    TRUNC (rc.creation_datecreation_date,
        
    ppc.project_relationship_code 
       FROM 
        pa_project_customers ppc
    ,
        
    ra_customers rc,
        
    hz_parties hp
       WHERE 
        ppc
    .customer_id rc.customer_id  AND
        
    rc.party_id hp.party_id  AND
        
    hp.party_type 'ORGANIZATION'  AND
        
    ppc.project_relationship_code IN ('END_CLIENT','LOCAL_COMPANY','PRIME_CONTRACTOR')) project
    WHERE
      ppa
    .template_flag 'N'  AND
      
    ppa.project_id ppc.project_id(+)  AND
      
    ppa.project_id project.project_id(+) AND
      
    ppc.class_category 'Local Company Involved'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •