| low cost and high cpu cost [message #467324] |
Fri, 23 July 2010 06:00  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi,
I am facing one performance issue, in which the query cost is very low compare to cpu cost and as a result the cpu always show the high graph.
I am also attaching the gv$sql and gv$sql_plan data of this query.
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
|
|
|
|
| Re: low cost and high cpu cost [message #467328 is a reply to message #467324] |
Fri, 23 July 2010 06:14  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Please read the orafaq forum guide and post accordingly, use code tags.
2) When posting an explain plan do the following in sqlplus:
EXPLAIN PLAN FOR <your query here>;
SELECT * FROM table(dbms_xplan.display);
Copy and paste the output here, using code tags to preserve the format.
Most people won't download attachments.
|
|
|
|
|
|
| Re: low cost and high cpu cost [message #467370 is a reply to message #467328] |
Fri, 23 July 2010 11:23 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
one thing i am getting is the %cpu which i got in explain plan and the value is coming 14 in %cpu while in the v$sql_plan the cpu cost is very much high.I am attaching the data of v$explain_plan.
|
|
|
|
|
|
|
|
|
|
| Re: low cost and high cpu cost [message #467400 is a reply to message #467364] |
Fri, 23 July 2010 14:04 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
This is the info i got from SELECT * FROM table(dbms_xplan.display) query,but the cost is showing here is 14%cpu while the cpu cost in v$sql_plan is very much high, that info I attached as a gif file.
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 | 14 (15)|
| 1 | HASH GROUP BY | | 1 | 90 | 14 (15)|
| 2 | NESTED LOOPS | | 1 | 90 | 13 (8)|
| 3 | NESTED LOOPS | | 1 | 79 | 11 (10)|
| 4 | NESTED LOOPS | | 1 | 60 | 8 (13)|
| 5 | SORT UNIQUE | | 1 | 22 | 4 (0)|
|* 6 | INDEX RANGE SCAN | PK_PRJ_TIMELOG_STAGE | 1 | 22 | 4 (0)|
|* 7 | TABLE ACCESS BY INDEX ROWID| PROJECTTIMELOGSSTAGE | 1 | 38 | 4 (0)|
|* 8 | INDEX RANGE SCAN | TEMP003 | 1 | | 3 (0)|
|* 9 | TABLE ACCESS BY INDEX ROWID | PROJECTTIMESHEETITEM | 1 | 19 | 3 (0)|
|* 10 | INDEX RANGE SCAN | PROJTSI_IDX3 | 1 | | 2 (0)|
|* 11 | INDEX RANGE SCAN | TIMESHEET_IDX2 | 1 | 11 | 2 (0)|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("PTLS1"."TIMESHEETID"=TO_NUMBER(:B3) AND
"PTLS1"."PROJECTID"=TO_NUMBER(:B2))
filter("ITEMTYPE"<>'OtherTsk' AND "ITEMTYPE"<>'NewTsk' AND "ITEMTYPE"<>'Loc'
AND "ITEMTYPE"<>'Glb')
7 - filter("PTLS"."PROJECTID"=TO_NUMBER(:B2))
8 - access("PTLS"."ITEMTYPE"="ITEMTYPE" AND "PTLS"."ITEMID"="ITEMID")
filter("PTLS"."ITEMTYPE"<>'OtherTsk' AND "PTLS"."ITEMTYPE"<>'NewTsk' AND
"PTLS"."ITEMTYPE"<>'Loc' AND "PTLS"."ITEMTYPE"<>'Glb')
9 - filter(("PTSI"."ISPWFITEM"='N' OR "PTSI"."ISPWFITEM" IS NULL) AND
"PTSI"."TIMESHEETID"="PTLS"."TIMESHEETID")
10 - access("PTSI"."ITEMTYPE"="PTLS"."ITEMTYPE" AND
"PTSI"."ITEMID"="PTLS"."ITEMID")
filter("PTSI"."ITEMTYPE"<>'OtherTsk' AND "PTSI"."ITEMTYPE"<>'NewTsk' AND
"PTSI"."ITEMTYPE"<>'Loc' AND "PTSI"."ITEMTYPE"<>'Glb')
11 - access("TS"."USERID"=TO_NUMBER(:B1) AND
"TS"."TIMESHEETID"="PTSI"."TIMESHEETID")
|
|
|
|
|
|
| Re: low cost and high cpu cost [message #467476 is a reply to message #467470] |
Sat, 24 July 2010 12:55 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 64bi
PL/SQL Release 10.2.0.4.0 Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 Production
NLSRTL Version 10.2.0.4.0 Production
|
|
|
|
| Re: low cost and high cpu cost [message #467494 is a reply to message #467324] |
Sat, 24 July 2010 20:31 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Here's your SQL formatted - you're welcome.
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
That plan actually looks pretty good to me - it is joining on appropriate looking indexes, and doing things in the right order.
How long does it take?
How long do you want it to take?
I suspect one of two things is happening here:
1 - You have badly skewed data, and there is one or more combinations of bind variables that causes thousands of rows to be selected instead of just a few.
2 - The SQL is actually very efficient, but you are executing it thousands of times in a loop. It is appearling in your "bad SQL" list because of the aggregate number of executions, but in reality it is very efficient.
Ross Leishman
|
|
|
|
| Re: low cost and high cpu cost [message #467501 is a reply to message #467494] |
Sat, 24 July 2010 23:18 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Thanks for the suggestion.
Actually this query took hardly 1 second in each iteration, and in one transaction we call it 50 to 60 times, so totally it will take 60 seconds, but this query we always get in OEM with highest cpu.So my concern how can i reduce the cpu cost.
|
|
|
|
|
|
|
|
| Re: low cost and high cpu cost [message #467513 is a reply to message #467509] |
Sun, 25 July 2010 02:09 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
One thing i want to share that when I quried on the v$sql for this query i got some data like
buffer gets:1194558229
executions:624
fetch:621
CLUSTER_WAIT_TIME:12891689
USER_IO_WAIT_TIME:168651342
Can we reach on some conclusion with this data.
For Trace file first I have to get the bind variable data, because this query call from one procedure and from inside the application.
|
|
|
|
| Re: low cost and high cpu cost [message #467532 is a reply to message #467513] |
Sun, 25 July 2010 08:34 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
One billion buffer gets for 624 executions - sounds pretty high unless you can explain whey this query should be processing around 300K to 1M rows per execution.
Think I see the problem. You have an index on projecttimelogsstage(itemtype, itemid). Add projectid as a third column on this index, that way the filter at Step 7 wil be added to the Access at Step 8, which I think is probably a non-selective or skewed access.
Ross Leishman
|
|
|
|
| Re: low cost and high cpu cost [message #467539 is a reply to message #467532] |
Sun, 25 July 2010 09:58 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Thanks,
i will create an index and will let u know. One thing I am not clear that how i can we decide that for this query how much buffer gets is required and also whatever the buffer get we get from v$sql is right or should be reduced or not? Please advice.
|
|
|
|
| Re: low cost and high cpu cost [message #467554 is a reply to message #467539] |
Sun, 25 July 2010 22:43 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Well, whenever Oracle needs a row - either from a table or an index - it must go and get it. It can get it from disk or from the buffer cache.
The number of blocks you read will depend on the type of scan you are performing, but as a general rule if you average more than 3 or 4 buffer gets per Table/Index entry in your Explain Plan per source row, then there is some redundancy that could be eliminated.
In your plan, you have 6 steps that perform Table or Index reads. I am guessing based on the column names in your query - and my own imagination - that each execution should process no more than a hundred or so rows. 6 steps x 100 rows x (say) 4 blocks per step = 2400 blocks (buffers). You have nearly 2M buffer gets per execution, so that's why I think it is a bit high.
Note that this is not a hard-and-fast rule. Your plan might have included a Full Table Scan, which might read and discard a lot of redundant blocks very efficiently.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
