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

    Unanswered: Query Tuning --Help!!

    Following query when run in two databases has different explain plan . In database A it runs in 19 secs while in database B it goes on and on without giving any output. Tables in both databases are analyzed as of yesterday.

    select count(*)
    from per_all_people_f papf,
    per_person_types ppt,
    per_person_type_usages_f pptuf,
    per_all_assignments_f paaf,
    hr_all_organization_units hou,
    hr_all_organization_units hou1,
    per_grades pg,
    per_grade_definitions pgd,
    per_periods_of_service ppos,
    hr_locations_all hla,
    hr_locations_all hla1,
    per_assignment_status_types past,
    fnd_user fu
    where papf.person_id = pptuf.person_id
    and pptuf.person_type_id = ppt.person_type_id
    and ppt.person_type_id in (83,97,98,99,103,104,105,150)
    and papf.employee_number is not null
    and paaf.person_id = papf.person_id
    and paaf.primary_flag = 'Y'
    and paaf.assignment_status_type_id in (1,94)
    and hou.organization_id = paaf.organization_id
    and pg.grade_id(+) = paaf.grade_id
    and pgd.grade_definition_id(+) = pg.grade_definition_id
    and ppos.person_id = papf.person_id
    and (ppos.actual_termination_date is null or trunc(sysdate) <= ppos.actual_termination_date )
    and hou1.organization_id = decode(ppt.person_type_id,103,hou.organization_id,
    104,decode(hou.type,'IDC_DTA', nvl(to_number(hou.attribute12),hou.organization_id ) ,'IDC_SEZ',nvl(to_number(hou.attribute12),hou.orga nization_id) ,'IDC_STP',nvl(to_number(hou.attribute12),hou.orga nization_id),hou.organization_id)
    ,105,hou.organization_id, nvl(to_number(hou.attribute12),hou.organization_id ))
    and hou1.location_id = hla.location_id
    and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
    and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
    and trunc(sysdate) between pptuf.effective_start_date and pptuf.effective_end_date
    and paaf.assignment_status_type_id = past.assignment_status_type_id
    and paaf.location_id = hla1.location_id(+)
    and papf.employee_number = fu.user_name (+)

  2. #2
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Explain Plans in Databases A and B attached in the excel as two sheets

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    ..
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    EXPLAIN PLAN in the adtabase where it runs fast
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    I broke up the query just to check a very small part of it
    select COUNT(*) from per_all_people_f papf,per_person_type_usages_f pptuf where papf.person_id = pptuf.person_id
    And I see the explain plan as below

    1.Fast (3 sec)

    SELECT STATEMENT, GOAL = CHOOSE 2134 1 10
    SORT AGGREGATE 1 10
    MERGE JOIN 2134 261463 2614630
    INDEX FULL SCAN HR PER_PEOPLE_F_PK 1290 211316 1056580
    SORT JOIN 844 243580 1217900
    TABLE ACCESS FULL HR PER_PERSON_TYPE_USAGES_F321 243580 1217900

    2.Slow in the other database (16 sec)

    SELECT STATEMENT, GOAL = CHOOSE 1 1 26 1
    SORT AGGREGATE 1 26
    NESTED LOOPS 1 1 26 1
    INDEX FULL SCAN APPS PER_PEOPLE_F_PK 1 13
    INDEX RANGE SCAN APPS PER_PERSON_TYPE_USAGES_F_FK 1 1 13

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Could it be that the tables were ~empty when they were analyzed yesterday, and have since been loaded with data? The optimizer seems to think it will only return 1 row.

  7. #7
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    No, tables were not empty when analyzed.
    SQL> select table_name,num_rows from all_tables where table_name in
    2 ('PER_ALL_PEOPLE_F' ,
    3 'PER_PERSON_TYPES',
    4 'PER_PERSON_TYPE_USAGES_F',
    5 'PER_ALL_ASSIGNMENTS_F',
    6 'HR_ALL_ORGANIZATION_UNITS',
    7 'PER_GRADES',
    8 'PER_GRADE_DEFINITIONS',
    9 'PER_PERIODS_OF_SERVICE',
    10 'HR_LOCATIONS_ALL',
    11 'PER_ASSIGNMENT_STATUS_TYPES',
    12 'FND_USER')
    13 /

    TABLE_NAME NUM_ROWS
    ------------------------------ --------
    FND_USER 41764
    PER_ALL_ASSIGNMENTS_F 536679
    PER_ALL_PEOPLE_F 210618
    PER_ASSIGNMENT_STATUS_TYPES 49
    PER_GRADE_DEFINITIONS 954
    PER_PERSON_TYPES 205
    PER_PERSON_TYPE_USAGES_F 241545
    PER_PERIODS_OF_SERVICE 59615
    PER_GRADES 913
    HR_ALL_ORGANIZATION_UNITS 897
    HR_LOCATIONS_ALL 1082

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What about the stats on this index?

    INDEX FULL SCAN APPS INDX_EMPLOYEE_NUMBER 1

    It seems to think there will be only 1 row?

  9. #9
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Statistics on indexes are also updated with teh analyze since CASCADE=>TRUE option is give,
    SQL> select num_rows from all_indexes where index_name='INDX_EMPLOYEE_NUMBER' and owner='APPS';

    NUM_ROWS
    --------
    95548

Posting Permissions

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