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

    Unanswered: Help : Query Tuning

    Pls help tuning the following.
    It was running fine until few days back but suddenly seems to be hanging or possibly taking too long.

    I am attaching the tkprof output for the tracing set ON and the Statapack Report for that particular session.

    SELECT
    project.project_id,
    timesheet.EXPENDITURE_ITEM_DATE,
    timesheet.EXPENDITURETYPE,
    sum(timesheet.TIMESHEET_HOURS * TCS_MIS_GET_STD_COST_RATE(employee.person_id,
    timesheet.EXPENDITURE_ITEM_DATE))
    FROM
    tcs_timesheet_details timesheet,
    pa_projects_all project,
    per_all_people_f employee,
    per_person_type_usages_f pid
    WHERE
    timesheet.employee_number=employee.employee_number
    and decode(timesheet.project_number,'9999999','1003442 ',timesheet.project_number)=project.segment1
    and pid.person_id=employee.person_id
    and pid.person_type_usage_id=(select max(person_type_usage_id) from per_person_type_usages_f where person_id=employee.person_id)
    and pid.person_type_id in (83,84,97,98)
    and trunc(EXPENDITURE_ITEM_DATE) between '01-MAR-2004' and '31-MAR-2004'
    and trunc(EXPENDITURE_ITEM_DATE) between employee.effective_start_date and employee.effective_end_date
    and trunc(EXPENDITURE_ITEM_DATE) between pid.effective_start_date and pid.effective_end_date
    group by project.project_id,
    timesheet.EXPENDITURE_ITEM_DATE,
    timesheet.EXPENDITURETYPE

  2. #2
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    TKPROF output file
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    STATSPACK Report for that session
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by MePreeti
    TKPROF output file
    This appears to be for a totally different SELECT:

    SELECT b.name
    from PER_ALL_ASSIGNMENTS_F a, PER_GRADES b
    where a.grade_id=b.grade_id(+)
    and a.person_id=:b2
    and a.primary_flag='Y'
    and trunc(:b1) between a.effective_start_date and a.effective_end_date

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    No, it is a part of execution of the function.
    The function refers to those tables as mentioned in the query by you.
    The uploaded reports are for the correct session.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    So which query do you want to tune, the one in the TKPROF report (which I cut and pasted above), which has this in TKPROF:
    PHP Code:
    SELECT  b.name
                   from  PER_ALL_ASSIGNMENTS_F a
    PER_GRADES b
                      where  a
    .grade_id=b.grade_id(+)
                            and  
    a.person_id=:b2
                            
    and  a.primary_flag='Y'
                            
    and  trunc(:b1between a.effective_start_date and a.effective_end_date

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute  26510      5.00       5.68          0          0          0           0
    Fetch    26510   3872.04    3783.61        972    7100030          0       26509
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    53020   3877.04    3789.30        972    7100030          0       26509

    Misses in library cache during parse
    0
    Misses in library cache during execute
    23
    Optimizer goal
    CHOOSE
    Parsing user id
    43     (recursive depth1)

    Rows     Row Source Operation
    -------  ---------------------------------------------------
       
    6222  NESTED LOOPS OUTER (cr=1671312 r=268 w=0 time=1641867358 us)
       
    6222   TABLE ACCESS BY INDEX ROWID PER_ALL_ASSIGNMENTS_F (cr=1658868 r=268 w=0 time=1641695750 us)
      
    33457    BITMAP CONVERSION TO ROWIDS (cr=1625899 r=2 w=0 time=1639192918 us)
       
    6222     BITMAP AND  (cr=1625899 r=2 w=0 time=1639153189 us)
       
    6223      BITMAP CONVERSION FROM ROWIDS (cr=12492 r=2 w=0 time=204001 us)
      
    53126       INDEX RANGE SCAN PER_ASSIGNMENTS_F_N12 (cr=12492 r=2 w=0 time=150736 us)(object id 111648)
      
    13784      BITMAP CONVERSION FROM ROWIDS (cr=1613409 r=0 w=0 time=1636892970 us)
    1991883286       INDEX RANGE SCAN TCS_PA_PER_ASSIGNMENTS_IDX1 (cr=1613514 r=0 w=0 time=995002034 us)(object id 111656)
       
    6222   TABLE ACCESS BY INDEX ROWID PER_GRADES (cr=12444 r=0 w=0 time=109756 us)
       
    6222    INDEX UNIQUE SCAN PER_GRADES_PK (cr=6222 r=0 w=0 time=58548 us)(object id 112165
    ... or the query you posted at the top, for which we have no TKPROF output to go on?!!!

    There seems to be something amiss here!

  7. #7
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    The query posted by me at the top is the master query which calls query posted by you. The one posted by you seems to be most resource consuming since it appeared in TOP Sessions as well.
    The one posted by you if tuned could also help.
    Thanks.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, it is executed 26510 times at ~0.14 seconds per execution. Can you not incorporate this into the main query rather than execute it separately for each row, since it seems to bring back 0 or 1 record each time? Doing something that is quite fast thousands of times isn't so fast any more. Perhaps something like:
    PHP Code:
    SELECT
    project
    .project_id,
    timesheet.EXPENDITURE_ITEM_DATE,
    timesheet.EXPENDITURETYPE,
    b.name,
    sum(timesheet.TIMESHEET_HOURS TCS_MIS_GET_STD_COST_RATE(employee.person_id,
    timesheet.EXPENDITURE_ITEM_DATE))
    FROM
    tcs_timesheet_details timesheet
    ,
    pa_projects_all project,
    per_all_people_f employee,
    per_person_type_usages_f pid
    PER_ALL_ASSIGNMENTS_F a
    PER_GRADES b
    WHERE
    timesheet
    .employee_number=employee.employee_number
    and decode(timesheet.project_number,'9999999','1003442',timesheet.project_number)=project.segment1
    and pid.person_id=employee.person_id
    and pid.person_type_usage_id=(select max(person_type_usage_idfrom per_person_type_usages_f where person_id=employee.person_id)
    and 
    pid.person_type_id in (83,84,97,98)
    and 
    trunc(EXPENDITURE_ITEM_DATEbetween '01-MAR-2004' and '31-MAR-2004'
    and trunc(EXPENDITURE_ITEM_DATEbetween employee.effective_start_date and employee.effective_end_date
    and trunc(EXPENDITURE_ITEM_DATEbetween pid.effective_start_date and pid.effective_end_date
    and a.grade_id=b.grade_id(+)
    and 
    a.person_id=employee.person_id
    and a.primary_flag='Y'
    and trunc(timesheet.EXPENDITURE_ITEM_DATEbetween a.effective_start_date and a.effective_end_date
    group by project
    .project_id,
    timesheet.EXPENDITURE_ITEM_DATE,
    timesheet.EXPENDITURETYPE 
    On the other hand, if you must keep the separate cursor, why do the outer join to PER_GRADES? Why not just join and then if no row returned, use NULL as the name?

  9. #9
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Thanks Andrews for your valuable suggestions. Unfortunately I have no control over the application code . I can only pass the suggestion.
    Incidentally, how does one come to know it was 0.14 sec/exec.
    Thanks again!

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by MePreeti
    Thanks Andrews for your valuable suggestions. Unfortunately I have no control over the application code . I can only pass the suggestion.
    Incidentally, how does one come to know it was 0.14 sec/exec.
    Thanks again!
    PHP Code:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute  26510      5.00       5.68          0          0          0           0
    Fetch    26510   3872.04    3783.61        972    7100030          0       26509
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    53020   3877.04    3789.30        972    7100030          0       26509 
    Executed 26510 times, taking a total of 3877.04 CPU seconds:

    3877.04 / 26510 = 0.1462... seconds per execution.

  11. #11
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    This is CPU seconds!!
    I thought you talked about hour seconds!!!

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by MePreeti
    This is CPU seconds!!
    I thought you talked about hour seconds!!!
    Not sure what you mean. The CPU seconds and elapsed seconds were pretty similar anyway, both around 3600 which is 1 hour. Divide either figure by 26510 and you get ~0.14 seconds per execution.

  13. #13
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Yes, realized it...
    Thanks!

Posting Permissions

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