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

    Unanswered: Query Tuning--Help!

    I am reproducing a query and its explain plan in two databases.
    In database A it runs in 19 secs but in the other database it is going on and on. There is a huge difference in the Explain Plan for the same query in the two databases.
    Tables are analyzed as of yesterday only in both the databases.
    Advice needed.

    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 2002
    Location
    Reading, UK
    Posts
    1,137
    1. Do you have the same data in both databases?
    2. Are the init.ora parameters which affect the optimizer the same?
    3. Same versions of Oracle on both servers?

    Alan

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Pls refer to my other post with the same subject. This post has come accidentally!
    1.Yes, the data returned by the tables in the two queries is same.
    2.Database where performance is good is 8i while the database where it is performing badly is 9i.
    3.I do not think init.ora parameters are impacting since 9i database is performing better than the other adatbase for other batch jobs/report queries etc.
    4.Pls refer to the other sample part of the query and my observation on it in the other post by me.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    >1.Yes, the data returned by the tables in the two queries is same.
    Does that mean the tables themselves have exactly the same data as oppossed to the query treturning the same data


    >3.I do not think init.ora parameters are impacting since 9i database is performing better than the other adatbase for other batch jobs/report queries etc.

    The problem is just because it performs well on other tasks doesnt mean anything. The optimizer will select a different execution plan depending on the values of things like db_file_multiblock_read_count, optimizer*, sort_area_size.

    The fact that you are running different versions of oracle can also explain why its selecting different plans.

    Rather than trying to tune both databases to behave the same (if you want that then atleast run the same version of Oracle on both) try tuning your 9i database so it performs aswell as it can, if necessary use 9i features.

    Alan

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    "I got an idea! Let's join 13 tables together with outer-joins and see what happens!"
    - 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
  •