Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010
    Posts
    1

    Unanswered: CPU cost is high

    Hi,

    I have one query whose IO cost is very low and cpu cost is very high and as a result the cpu always show the high graph. What can i do.
    I am also attaching the plan from gv$sql_plan table.
    This is the query:
    SELECT PTLS.ITEMTYPE , PTLS.ITEMID , PTLS.STAGEID, TS.USERID, SUM(PREVIOUSHOURS) AS PREVIOUSHOURS, MIN(STARTDATE) AS STARTDATE, MAX(STARTDATE) AS ENDDATE FROM PROJECTTIMELOGSSTAGE PTLS, PROJECTTIMESHEETITEM PTSI, TIMESHEET TS WHERE PTLS.PROJECTID = :B2 AND TS.TIMESHEETID = PTSI.TIMESHEETID AND TS.USERID = :B1 AND PTSI.TIMESHEETID = PTLS.TIMESHEETID AND PTSI.ITEMTYPE = PTLS.ITEMTYPE AND PTSI.ITEMID = PTLS.ITEMID AND (PTSI.ISPWFITEM = 'N' OR PTSI.ISPWFITEM IS NULL) AND PTLS.ITEMTYPE NOT IN ('OtherTsk','NewTsk','Loc','Glb') AND (PTLS.ITEMTYPE, PTLS.ITEMID ) IN (SELECT ITEMTYPE, ITEMID FROM PROJECTTIMELOGSSTAGE PTLS1 WHERE PTLS1.PROJECTID = :B2 AND PTLS1.TIMESHEETID = :B3 ) GROUP BY PTLS.ITEMTYPE, PTLS.ITEMID, PTLS.STAGEID, TS.USERID
    Attached Thumbnails Attached Thumbnails explainplan.GIF  

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    First off, you should use code tags, [ CODE ][ / CODE ] (just remove the spaces) around your SQL or any code to keep them formatted, so that we trying to help you can easily see what you are doing. Your query looks rather simple and straight-forward, except for the subquery, which seems rather pointless. Normally, a subquery being against a table you are already joining to would be to get the min/max/summation of something within the table to join to. You don't do any of that however, so makes it rather pointless from what I can see. Try without the subquery and see how it runs:
    Code:
    SELECT PTLS.ITEMTYPE 
         , PTLS.ITEMID
         , PTLS.STAGEID
         , TS.USERID
         , SUM(PREVIOUSHOURS) AS PREVIOUSHOURS
         , MIN(STARTDATE) AS STARTDATE
         , MAX(STARTDATE) AS ENDDATE
       FROM PROJECTTIMELOGSSTAGE PTLS
          , PROJECTTIMESHEETITEM PTSI
          , TIMESHEET TS 
    WHERE PTLS.PROJECTID = :B2
      AND TS.TIMESHEETID = PTSI.TIMESHEETID
      AND TS.USERID = :B1
      AND PTSI.TIMESHEETID = PTLS.TIMESHEETID
      AND PTSI.ITEMTYPE = PTLS.ITEMTYPE
      AND PTSI.ITEMID = PTLS.ITEMID
      AND (PTSI.ISPWFITEM = 'N'
        OR PTSI.ISPWFITEM IS NULL)
      AND PTLS.ITEMTYPE NOT IN ('OtherTsk','NewTsk','Loc','Glb')
      AND PTLS.TIMESHEETID = :B3 
    GROUP BY PTLS.ITEMTYPE
           , PTLS.ITEMID
           , PTLS.STAGEID
           , TS.USERID
    Dave

Posting Permissions

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