Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jan 2004
    Posts
    84

    Unanswered: Help Query Tuning

    Hi,
    I see one query causing 25% of the CPU utilization.
    Tables are analyzed and sql stats from statspack are as below.
    Any Help?

    SQL Statistics
    ~~~~~~~~~~~~~~
    -> CPU and Elapsed Time are in seconds (s) for Statement Total and in
    milliseconds (ms) for Per Execute
    % Snap
    Statement Total Per Execute Total
    --------------- --------------- ------
    Buffer Gets: 71,171,187 71,171,187.0 10.88
    Disk Reads: 29,881 29,881.0 1.15
    Rows processed: 1,626 1,626.0
    CPU Time(s/ms): 329 329,200.0
    Elapsed Time(s/ms): 343 342,991.6
    Sorts: 0 .0
    Parse Calls: 3 3.0
    Invalidations: 0
    Version count: 1
    Sharable Mem(K): 70
    Executions: 1



    --------------------------------------------------------------------------------
    | Operation | PHV/Object Name | Rows | Bytes| Cost |
    --------------------------------------------------------------------------------
    |SELECT STATEMENT |----- 3810018071 ----| | | 1984 |
    |NESTED LOOPS | | 1 | 114 | 1984 |
    | NESTED LOOPS | | 6 | 528 | 1672 |
    | NESTED LOOPS | | 6 | 426 | 1660 |
    | HASH JOIN | | 290K| 16M| 1660 |
    | HASH JOIN | | 3K| 129K| 1635 |
    | TABLE ACCESS FULL |TIME_CAL_MONTHS | 1K| 13K| 5 |
    | TABLE ACCESS FULL |MIS_EMPLOYEE_HIS | 3K| 87K| 1629 |
    | TABLE ACCESS FULL |FACT_MATC_RATE_BAND_ | 94K| 1M| 17 |
    | INDEX UNIQUE SCAN |PK_DIM_EMPLOYEE | 1 | 12 | |
    | TABLE ACCESS BY INDEX ROWID |DIM_PROJECT | 1 | 17 | 2 |
    | INDEX RANGE SCAN |IDX_DIM_PROJECT_PRJ_ | 1 | | 1 |
    | TABLE ACCESS BY INDEX ROWID |MIS_EMPLOYEE_EFFORT | 1 | 26 | 1984 |
    | AND-EQUAL | | | | |
    | INDEX RANGE SCAN |MIS_EMP_EFFORT_I | | | |
    | INDEX RANGE SCAN |MIS_EMP_EFFORT_I | | | |
    --------------------------------------------------------------------------------

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112
    can you give us the query ?

    Explain shows 3 tables access full

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help Query Tuning

    The fact this this query uses 25% of total CPU time doesn't mean much by itself - after all, if it was the only query you ran it would use 100% of CPU, but that doesn't mean it is necessarily poorly tuned, does it?

    Anyway, your stats indicate that it takes about 6 minutes to run, and I presume that is unacceptable for this query? Without seeing the query and having more information about what indexes exist, stats about the tables, it isn't possible to suggest what you should do.

  4. #4
    Join Date
    Jan 2004
    Posts
    84
    Thank you for such prompt response!
    The query is going on and on without giving any result actually. The statspack snapshot was taken in the middle of it so possibly because of it is showing 6 mins.
    The query follows.




    SELECT FACT_MATC_RATE_BAND_ANALYSIS.RATE, MIS_EMPLOYEE_EFFORT_T.PERIOD,
    MIS_EMPLOYEE_EFFORT_T.EMPLOYEE_NUMBER, DIM_PROJECT.PROJECT_NUMBER,
    MIS_EMPLOYEE_EFFORT_T.ALLOCATED_EFFORT, DIM_PROJECT.PROJECT_LOCATION_ID
    FROM
    FACT_MATC_RATE_BAND_ANALYSIS, MIS_EMPLOYEE_EFFORT_T, DIM_EMPLOYEE, TIME_CAL_MONTHS, DIM_PROJECT
    WHERE
    DIM_EMPLOYEE.EMPLOYEE_ID=FACT_MATC_RATE_BAND_ANALY SIS.EMPLOYEE_ID
    --AND
    --FACT_MATC_RATE_BAND_ANALYSIS.PROJECT_ID=DIM_PROJEC T.PROJECT_ID
    AND
    FACT_MATC_RATE_BAND_ANALYSIS.MONTH_ID=TIME_CAL_MON THS.MONTH_KEY
    AND
    TO_DATE(MIS_EMPLOYEE_EFFORT_T.PERIOD,'MON-YY')=TIME_CAL_MONTHS.CAL_MONTH_START_DT
    AND
    MIS_EMPLOYEE_EFFORT_T.EMPLOYEE_NUMBER=DIM_EMPLOYEE .EMPLOYEE_NUMBER
    AND
    MIS_EMPLOYEE_EFFORT_T.PROJECT_NUMBER=DIM_PROJECT.P ROJECT_NUMBER
    AND
    FACT_MATC_RATE_BAND_ANALYSIS.PROJECT_ID=DIM_PROJEC T.PROJECT_ID

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Are there views involved here? Because the query plan shows a table called MIS_EMPLOYEE_HIS which isn't in the query. If so, what does the view definition look like?

  6. #6
    Join Date
    Jan 2004
    Posts
    84
    The explain Plan has the table name truncated. I was not able to produce it full. As such, all tables in explain plan are included in the query. There are no views invloved.
    Thanks

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There is no table with a name beginning with MIS_EMPLOYEE_HIS% in that query! If not a view, is there a synonym involved? Or is this the wrong query?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The query plan show no cost for the 2 INDEX RANGE SCANs on index MIS_EMP_EFFORT_I - has this index been analyzed?

  9. #9
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    Try dropping the indexes and recreating it
    Thanks and Regards,

    Praveen Pulikunnu

  10. #10
    Join Date
    Jan 2004
    Posts
    84
    Yes the indexes have been analyzed. Can't figure out why they are not being used.

  11. #11
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    Originally posted by preetikate
    Yes the indexes have been analyzed. Can't figure out why they are not being used.
    Please check if Oracle is doing any implicit data conversion, it can slow down the query. You need to check out the datatype of the tables that are used to join and try explicit conversion wherever necessary.
    Thanks and Regards,

    Praveen Pulikunnu

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by preetikate
    Yes the indexes have been analyzed. Can't figure out why they are not being used.
    You still haven't explained why a table named EMP_EMPLOYEE_HIS appears in the query plan but not in the SQL! Also, the query uses something called MIS_EMPLOYEE_EFFORT_T which doesn't appear in the plan. I would guess (assuming this is the right SQL for the plan) that MIS_EMPLOYEE_EFFORT_T is a view over tables MIS_EMPLOYEE_EFFORT and EMP_EMPLOYEE_HIS.

  13. #13
    Join Date
    Jan 2004
    Posts
    84
    Sincere apologies! I happened to get wrong plan from the statspack report. The correct explain plan is below.

    --------------------------------------------------------------------------------
    | Operation | PHV/Object Name | Rows | Bytes| Cost |
    --------------------------------------------------------------------------------
    |SELECT STATEMENT |----- 2687554502 ----| | | 847 |
    |HASH JOIN | | 1 | 87 | 847 |
    | NESTED LOOPS | | 1K| 109K| 789 |
    | HASH JOIN | | 69M| 3G| 789 |
    | HASH JOIN | | 94K| 2M| 24 |
    | TABLE ACCESS FULL |TIME_CAL_MONTHS | 1K| 13K| 5 |
    | TABLE ACCESS FULL |FACT_MATC_RATE_BAND_| 94K| 1M|17 |
    | TABLE ACCESS FULL |MIS_EMPLOYEE_EFF | 797K|19M|342 |
    | INDEX UNIQUE SCAN |PK_DIM_EMPLOYEE | 1 | 12 | |
    | TABLE ACCESS FULL |DIM_PROJECT | 27K| 456K| 55 |
    --------------------------------------------------------------------------------

  14. #14
    Join Date
    Jan 2004
    Posts
    84
    SQL Statistics
    ~~~~~~~~~~~~~~
    -> CPU and Elapsed Time are in seconds (s) for Statement Total and in
    milliseconds (ms) for Per Execute
    % Snap
    Statement Total Per Execute Total
    --------------- --------------- ------
    Buffer Gets: 48,102,724 48,102,724.0 7.35
    Disk Reads: 25 25.0 .00
    Rows processed: 0 0.0
    CPU Time(s/ms): 450 449,710.0
    Elapsed Time(s/ms): 441 440,608.8
    Sorts: 0 .0
    Parse Calls: 1 1.0
    Invalidations: 0
    Version count: 1
    Sharable Mem(K): 59
    Executions: 1

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That's more like it! Now:

    1) What indexes are available on these tables?

    2) How many rows in each table?

    3) How many distinct fact_matc_rate_band_analysis.month_id values?

    4) How many distinct to_date(mis_employee_effort_t.period,'mon-yy') values?

Posting Permissions

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