Hi All,
I am sitting on 10.2.0.4.0 on linux box. I've got one complain from the application folks regarding the query the slow query , it's running quite long time. I extracted out the explain plan and commulative wait events for sessions and system and found db file sequenciial read caused us the most waits. the same query was running fine a day before. not sure , where to focus ??
++ Query ++
SELECT A.ITEM, A.LOC, MAX(CO1.AFFBILLPR) AFFBILLPR, MAX(CO1.UNIT_COST) UNIT_COST FROM INVOPT_STG.TMP_COST_OCS CO1,
(SELECT SS.LOC, SS.MANUF_LOC, SS.ITEM, MAX(CO.ITEM_10D) ITEM_10D, COUNTRY_CD FROM INVOPT_STG.STG_LOC SL, TMP_COST_OCS CO, STG_SKU SS WHERE
CASE WHEN SL.COUNTRY_CD_COST = CO.COUNTRY_CD THEN SL.COUNTRY_CD_COST
WHEN SL.COUNTRY_CD_COST != NVL((SELECT DISTINCT CO3.COUNTRY_CD FROM INVOPT_STG.TMP_COST_OCS
CO3 WHERE CO3.ITEM_10D = CO.ITEM_10D AND CO3.FYEAR = CO.FYEAR AND CO3.COUNTRY_CD = SL.COUNTRY_CD_COST),' ')
AND SL.COUNTRY_CD_COST= 'US' THEN 'CAN' WHEN SL.COUNTRY_CD_COST != NVL((SELECT DISTINCT CO3.COUNTRY_CD
FROM INVOPT_STG.TMP_COST_OCS CO3 WHERE CO3.ITEM_10D = CO.
ITEM_10D AND CO3.FYEAR = CO.FYEAR AND CO3.COUNTRY_CD = SL.COUNTRY_CD_COST),' ')
AND SL.COUNTRY_CD_COST = 'CAN' THEN 'US' ELSE SL.COUNTRY_CD_COST END = CO.COUNTRY_CD AND CO.COST_TYPE IN ('F') AND CO.ITEM_10D !=
SS.ITEM_10D AND CO.ITEM_10D < SS.ITEM_10D AND
SUBSTR(CO.ITEM_10D,1,9) = SUBSTR(SS.ITEM_10D,1,9)
AND SUBSTR(SL.LOC,1,3) = SS.MANUF_LOC GROUP BY SS.LOC, SS.MANUF_LOC, SS.ITEM,
CO.COUNTRY_CD) A WHERE CO1.ITEM_10D = A.ITEM_10D AND CO1.FYEAR =
(SELECT MAX(CO2.FYEAR) FROM INVOPT_STG.TMP_COST_OCS CO2 WHERE CO2.ITEM_10D
= A.ITEM_10D AND CO2.COST_TYPE = 'F' AND CO2.COUNTRY_CD = A.COUNTRY_CD)
AND CO1.COST_TYPE = 'F' AND CO1.COUNTRY_CD = A.COUNTRY_CD GROUP BY A.ITEM, A.LOC
------ Eplain plan generated from shared pool +++++++++++
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 11M| 2139K| 3095K (1)| 8192 |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | TMP_COST_OCS | 1 | | | | |
| 4 | NESTED LOOPS | | 1 | | | | |
| 5 | VIEW | | 1 | | | | |
| 6 | HASH GROUP BY | | 1 | 22M| 4334K| 3898K (1)| 17408 |
|* 7 | FILTER | | | | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | TMP_COST_OCS | 1 | | | | |
| 9 | NESTED LOOPS | | 1 | | | | |
|* 10 | HASH JOIN | | 1 | 1179K| 1179K| 1210K (0)| |
| 11 | TABLE ACCESS FULL | STG_LOC | 155 | | | | |
| 12 | TABLE ACCESS FULL | STG_SKU | 1738K| | | | |
|* 13 | INDEX RANGE SCAN | TMP_COST_OCS_01 | 1 | | | | |
| 14 | SORT UNIQUE NOSORT | | 1 | | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | TMP_COST_OCS | 1 | | | | |
|* 16 | INDEX RANGE SCAN | TMP_COST_OCS_01 | 3 | | | | |
| 17 | SORT UNIQUE NOSORT | | 1 | | | | |
|* 18 | TABLE ACCESS BY INDEX ROWID| TMP_COST_OCS | 1 | | | | |
|* 19 | INDEX RANGE SCAN | TMP_COST_OCS_01 | 3 | | | | |
|* 20 | INDEX RANGE SCAN | TMP_COST_OCS_01 | 3 | | | | |
| 21 | SORT AGGREGATE | | 1 | | | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | TMP_COST_OCS | 1 | | | | |
|* 23 | INDEX RANGE SCAN | TMP_COST_OCS_01 | 3 | | | | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CO1"."FYEAR"=)
3 - filter(("CO1"."COST_TYPE"='F' AND "CO1"."COUNTRY_CD"="A"."COUNTRY_CD"))
7 - filter("CO"."COUNTRY_CD"=CASE WHEN ("SL"."COUNTRY_CD_COST"="CO"."COUNTRY_CD") THEN
"SL"."COUNTRY_CD_COST" WHEN (("SL"."COUNTRY_CD_COST"<>NVL(,' ')) AND ("SL"."COUNTRY_CD_COST"='US'))
THEN 'CAN' WHEN (("SL"."COUNTRY_CD_COST"<>NVL(,' ')) AND ("SL"."COUNTRY_CD_COST"='CAN')) THEN 'US'
ELSE "SL"."COUNTRY_CD_COST" END )
8 - filter("CO"."COST_TYPE"='F')
10 - access("SS"."MANUF_LOC"=SUBSTR("SL"."LOC",1,3))
13 - access("CO"."ITEM_10D"<"SS"."ITEM_10D")
filter(("CO"."ITEM_10D"<>"SS"."ITEM_10D" AND
SUBSTR("CO"."ITEM_10D",1,9)=SUBSTR("SS"."ITEM_10D",1,9)))
15 - filter(("CO3"."COUNTRY_CD"=:B1 AND "CO3"."FYEAR"=:B2))
16 - access("CO3"."ITEM_10D"=:B1)
18 - filter(("CO3"."COUNTRY_CD"=:B1 AND "CO3"."FYEAR"=:B2))
19 - access("CO3"."ITEM_10D"=:B1)
20 - access("CO1"."ITEM_10D"="A"."ITEM_10D")
22 - filter(("CO2"."COUNTRY_CD"=:B1 AND "CO2"."COST_TYPE"='F'))
23 - access("CO2"."ITEM_10D"=:B1)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
+++++ System Waits ++++++++
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
db file sequential read 11887758 3643542 .31
jobq slave wait 4434 1297399 292.6
db file scattered read 3821415 615568 .16
log file parallel write 778118 476860 .61
db file parallel write 652969 456530 .7
SQL*Net more data to client 93400714 364435 0
PX Idle Wait 1078 208509 193.42
control file parallel write 141212 62082 .44
log file switch (checkpoint incomplete) 323 22567 69.87
log buffer space 786 10051 12.79
log file sync 17816 7988 .45
db file single write 42869 5994 .14
read by other session 13713 5051 .37
log file switch completion 1125 4963 4.41
db file parallel read 756 3951 5.23
Data file init write 18444 3569 .19
cursor: pin S wait on X 3280 3409 1.04
os thread startup 452 2250 4.98
direct path read 4438445 1629 0
SQL*Net more data from client 73118 1613 .02
control file sequential read 291106 1523 .01
local write wait 4707 1169 .25
latch: shared pool 166 954 5.75
direct path read temp 4657234 921 0
library cache load lock 313 753 2.41
enq: KO - fast object checkpoint 5286 734 .14
PL/SQL lock timer 7 682 97.43
latch: library cache 501 614 1.23
+++ commulative events for session +++
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
db file sequential read 6224 1978 .32
SQL*Net message from client 20 2 .09
SQL*Net message to client 20 0 0
log file sync 1 0 .17
Edited by: user11983993 on May 18, 2012 11:12 AM