Home » RDBMS Server » Performance Tuning » performance tuning PROBLEM
| performance tuning PROBLEM [message #520096] |
Thu, 18 August 2011 10:22  |
 |
shashank_kurasrediffmail
Messages: 63
Registered: July 2011
Location: hyderabad
|
Member |
|
|
hi all,
I have problem with below query.
the query some times executing with in 1 minute and some times taking 3 minutes.
SELECT SUBSTR (whse_code, 1, 1) || 'PL' "plant", SUBSTR (A.item_no,8) item_code,
SUM (opnet1) opnet1, SUM (opnet1*CNF) opnet2,
SUM (opqty1 - opnet1) oprej1, SUM ((opqty1 - opnet1)*CNF) oprej2,
SUM (opqty1) opqty1, SUM (opqty1*CNF) opqty2, SUM (prodqty1) prodqty1,
SUM (prodqty1*CNF) prodqty2, SUM (salesqty1 * -1) salesqty1,
SUM ((salesqty1 * -1)*CNF) salesqty2, SUM (lossqty1) lossqty1,
SUM (lossqty1*CNF) lossqty2, SUM (clnet1) clnet1, SUM (clnet1*CNF) clnet2,
SUM (clqty1 - clnet1) clrej1, SUM ((clqty1 - clnet1)*CNF) clrej2,
SUM (clqty1) clqty1, SUM (clqty1*CNF) clqty2, SUM (cl151) cl151,
SUM (cl151*CNF) cl152, SUM (clnet1 - cl151) cm151,
SUM ((clnet1 - cl151)*CNF) cm152
FROM
(
(SELECT whse_code,
item_no,
item_id,
CASE WHEN lot_no NOT LIKE '%IPR%' AND lot_no NOT LIKE '%WH%'
AND (trans_date) < TO_DATE((:p_from_date ||'00:00:00'),'dd-mon-RRRR hh24-mi-ss' ) THEN (trans_qty) ELSE 0
END opnet1 ,
0 opnet2,
CASE WHEN (trans_date) < TO_DATE((:p_from_date ||'00:00:00'),'dd-mon-RRRR hh24-mi-ss' ) THEN (trans_qty) END opqty1,
0 opqty2,
0 prodqty1, 0 prodqty2, 0 salesqty1, 0 salesqty2,
0 lossqty1, 0 lossqty2,
CASE WHEN lot_no NOT LIKE '%IPR%' AND lot_no NOT LIKE '%WH%' THEN (trans_qty) ELSE 0 END clnet1 ,
0 clnet2,
(trans_qty) clqty1,
0 clqty2, 0 cl151, 0 cl152
FROM
( SELECT LOT.lot_no,
lot.item_id,
trx.whse_code,
mst.item_no,
mst.item_desc1,
trx.trans_um,
trx.trans_date,
trx.trans_qty,
trx.trans_um2
FROM
ic_tran_vw1 trx,
ic_item_mst mst,
ic_lots_mst lot
WHERE
mst.item_id = trx.item_id
AND mst.item_id = lot.item_id
AND lot.lot_id = trx.lot_id
AND trx.whse_code BETWEEN NVL (:p_from_whsecode, trx.whse_code)
AND NVL (:p_to_whsecode, trx.whse_code)
AND trx.completed_ind = '1'
AND (SUBSTR (trx.whse_code, 2, 2) = 'FG' OR SUBSTR (trx.whse_code, 2, 2)='WP')
AND trx.whse_code NOT LIKE '5%'
AND trx.doc_type NOT LIKE 'TRNI'
-- AND lot.lot_no LIKE '%' || :p_lot_no || '%'
AND ((SUBSTR( lot.LOT_NO,LENGTH(lot.LOT_NO),1)='S' AND :P_LOT_NO='S') OR (SUBSTR( lot.LOT_NO,LENGTH(lot.LOT_NO),1)='V'
AND :P_LOT_NO='V') OR (lot.LOT_NO=lot.LOT_NO AND :p_LOT_NO IS NULL) )
AND mst.item_no BETWEEN NVL (:p_from_item_no, mst.item_no)
AND NVL (:p_to_item_no, mst.item_no)
---- Change the item code over here
AND mst.item_no LIKE 'A-FG-1%'
AND (trans_date) <= TO_DATE((:p_to_date ||'23:59:59'),'dd-mon-RRRR hh24-mi-ss' )
)
UNION ALL
SELECT whse_code, item_no, item_id, 0 opqty1, 0 opqty2, 0 opnet1, 0 opnet2,
( prodqty1), 0 prodqty2, 0 salesqty1, 0 salesqty2,
(lossqty1), 0 lossqty2, 0 clnet1, 0 clnet2, 0 clqty1,
0 clqty2, 0 cl151,
0 cl152
FROM
(
SELECT
TRX. whse_code,
MST.item_no,
lot.item_id,
CASE WHEN routing_no NOT LIKE '%SALVAGING%' THEN (trans_qty) ELSE 0 END PRODQTY1,
CASE WHEN routing_no LIKE '%SALVAGING%' THEN (trans_qty) ELSE 0 END LOSSQTY1
FROM
ic_tran_pnd trx,
ic_item_mst mst,
ic_lots_mst lot,
gme_batch_header_vw bat
WHERE mst.item_id = trx.item_id
AND mst.item_id = lot.item_id
AND lot.lot_id = trx.lot_id
-- AND lot.lot_no LIKE '%' || :p_lot_no || '%'
AND ((SUBSTR( lot.LOT_NO,LENGTH(lot.LOT_NO),1)='S' AND :P_LOT_NO='S') OR (SUBSTR( lot.LOT_NO,LENGTH(lot.LOT_NO),1)='V'
AND :P_LOT_NO='V') OR (lot.LOT_NO=lot.LOT_NO AND :p_LOT_NO IS NULL) )
AND trx.whse_code BETWEEN NVL (:p_from_whsecode, trx.whse_code)
AND NVL (:p_to_whsecode, trx.whse_code)
AND trx.completed_ind = '1'
AND trx.delete_mark=0
AND trx.whse_code NOT LIKE '5%'
AND trx.doc_type = 'PROD'
AND SUBSTR (trx.whse_code, 2, 2) IN ('FG', 'WP')
AND bat.batch_id = trx.doc_id
AND mst.item_no LIKE 'A-FG-1%'
AND mst.item_no BETWEEN NVL (:p_from_item_no, mst.item_no)
AND NVL (:p_to_item_no, mst.item_no)
AND (trx.trans_date) BETWEEN TO_DATE((:p_from_date ||'00:00:00'),'dd-mon-RRRR hh24-mi-ss' )
AND TO_DATE((:p_to_date ||'23:59:59'),'dd-mon-RRRR hh24-mi-ss' )
-- AND TRUNC(trx.trans_date) BETWEEN :p_from_date AND :p_to_date
)
UNION ALL
SELECT trx.whse_code, mst.item_no,lot.item_id,
0 opqty1, 0 opqty2, 0 opnet1, 0 opnet2,
0 prodqty1, 0 prodqty2, 0 salesqty1,
0 salesqty2,
0 lossqty1, 0 lossqty2, 0 clnet1, 0 clnet2, 0 clqty1,
0 clqty2,(trx.trans_qty) cl151, 0 cl152
FROM
ic_tran_pnd trx,
ic_item_mst mst,
ic_lots_mst lot,
gme_batch_header_vw bat
WHERE mst.item_id = trx.item_id
AND mst.item_id = lot.item_id
AND lot.lot_id = trx.lot_id
-- AND lot.lot_no LIKE '%' || :p_lot_no || '%'
AND ((SUBSTR( lot.LOT_NO,LENGTH(lot.LOT_NO),1)='S' AND :P_LOT_NO='S') OR (SUBSTR( lot.LOT_NO,LENGTH(lot.LOT_NO),1)='V'
AND :P_LOT_NO='V') OR (lot.LOT_NO=lot.LOT_NO AND :p_LOT_NO IS NULL) )
AND trx.whse_code BETWEEN NVL (:p_from_whsecode, trx.whse_code)
AND NVL (:p_to_whsecode, trx.whse_code)
AND trx.completed_ind = '1'
AND trx.delete_mark=0
AND trx.whse_code NOT LIKE '5%'
AND trx.doc_type = 'PROD'
AND SUBSTR (trx.whse_code, 2, 2) IN ('FG', 'WP')
AND bat.batch_id = trx.doc_id
AND mst.item_no LIKE 'A-FG-1%'
AND mst.item_no BETWEEN NVL (:p_from_item_no, mst.item_no)
AND NVL (:p_to_item_no, mst.item_no)
-- AND TRUNC(trx.trans_date) BETWEEN :p_from_date AND :p_to_date
AND lot.lot_no NOT LIKE '%IPR%'
AND lot.lot_no NOT LIKE '%WH%'
AND bat.routing_no NOT LIKE '%SALVAGING%'
AND (trx.trans_date) BETWEEN TO_DATE((:p_to_date||'00:00:00'),'dd-mon-RRRR hh24:mi:ss' )-15
AND TO_DATE((:p_to_date ||'23:59:59'),'dd-mon-RRRR hh24-mi-ss' )
UNION ALL
SELECT trx.whse_code, mst.item_no,lot.item_id,
0 opqty1, 0 opqty2, 0 opnet1, 0 opnet2,
0 prodqty1, 0 prodqty2, (trx.trans_qty) salesqty1,
0 salesqty2,
0 lossqty1, 0 lossqty2, 0 clnet1, 0 clnet2, 0 clqty1,
0 clqty2, 0 cl151, 0 cl152
FROM
ic_tran_pnd trx,
ic_item_mst mst,
ic_lots_mst lot
WHERE
mst.item_id = trx.item_id
AND mst.item_id = lot.item_id
AND lot.lot_id = trx.lot_id
-- AND lot.lot_no LIKE '%' || :p_lot_no || '%'
AND ((SUBSTR( lot.LOT_NO,LENGTH(lot.LOT_NO),1)='S' AND :P_LOT_NO='S') OR (SUBSTR( lot.LOT_NO,LENGTH(lot.LOT_NO),1)='V'
AND :P_LOT_NO='V') OR (lot.LOT_NO=lot.LOT_NO AND :p_LOT_NO IS NULL) )
AND trx.whse_code BETWEEN NVL (:p_from_whsecode, trx.whse_code)
AND NVL (:p_to_whsecode, trx.whse_code)
AND trx.completed_ind = '1'
AND trx.delete_mark=0
AND SUBSTR (trx.whse_code, 2, 2) IN ('FG','WP')
AND trx.whse_code NOT LIKE '5%'
AND trx.doc_type IN ('OMSO', 'PORC')
AND mst.item_no BETWEEN NVL (:p_from_item_no, mst.item_no)
AND NVL (:p_to_item_no, mst.item_no)
AND mst.item_no LIKE 'A-FG-1-%'
AND (trx.trans_date) BETWEEN TO_DATE((:p_from_date ||'00:00:00'),'dd-mon-RRRR hh24-mi-ss' )
AND TO_DATE((:p_to_date ||'23:59:59'),'dd-mon-RRRR hh24-mi-ss' )
)
)A,VIS_ITM_CTG_CNV_MST B
WHERE
A.ITEM_NO=B.ITEM_NO
AND a.item_id=b.item_id
GROUP BY SUBSTR (whse_code, 1, 1) || 'PL', SUBSTR (A.item_no, 8)
--HAVING SUM ( opnet1 + opqty1 + prodqty1 + (salesqty1 * -1) + lossqty1 + clnet1 + clqty1 ) <> 0
ORDER BY 1, 2
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: performance tuning PROBLEM [message #520109 is a reply to message #520108] |
Thu, 18 August 2011 11:17  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
from where I sit, different results imply that SOMETHING changed.
We don't have your table or data so we can't do anything with posted query (other than stare at it)
Any solution must originate with you & you alone.
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 01:40:52 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
