| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Slow query tuning
Hi Listers,
I have the below query which is taking almost 3 min. to execute. Can anybody help me in rewriting the query and/or reducing the query execution time. Please note that V_SHIP is a view created the source for which is as as follows:
View Source
CREATE OR REPLACE VIEW V_SHIP ( SHIPMENT_ID,
HOUSEBILL, AEI_REFERENCE, CONTAINER_ID, CONTAINER_NUM,
CONTAINER_SIZE ) AS select
s.shipment_id,s.housebill,s.aei_reference,sc.container_id,sc.container_num,s
c.container_size
from shipment s,shipment_container sc
where s.shipment_id = sc.shipment_id(+)
and trunc(S.MSG_CREATION_DATE) >= trunc(sysdate - 90)
and trunc(S.MSG_CREATION_DATE) <= trunc(sysdate);
Query:
SELECT /*+ORDERED*/
S.HOUSEBILL,
OH.ORDER_REF_NO, v.CONTAINER_NUM, S.AEI_REFERENCE, CE1.SEQUENCE,
SELECT
to_char(SCT1.TIMESTAMP,
'YYYYMMDDHH24MISS')
FROM
SHIPMENT_CONTAINER_TIMESTAMP SCT1
WHERE
SCT1.EVENT_ID = XM1.EVENT_ID
AND SCT1.SHIPMENT_ID = S.SHIPMENT_ID
AND SCT1.CONTAINER_ID = v.CONTAINER_ID
AND XM1.TABLE_NAME = 'SHIPMENT_CONTAINER_TIMESTAMP'
UNION
SELECT
to_char(ST1.TIMESTAMP,
'YYYYMMDDHH24MISS')
FROM
SHIPMENT_TIMESTAMP ST1
WHERE
ST1.EVENT_ID = XM1.ALT_EVENT_ID
AND ST1.SHIPMENT_ID = S.SHIPMENT_ID
AND XM1.ALT_EVENT_ID IS NOT NULL
AND XM1.TABLE_NAME ='SHIPMENT_CONTAINER_TIMESTAMP'
AND NOT EXISTS (
SELECT
SCT1.TIMESTAMP
FROM
SHIPMENT_CONTAINER_TIMESTAMP SCT1
WHERE
SCT1.EVENT_ID = XM1.EVENT_ID
AND SCT1.SHIPMENT_ID = S.SHIPMENT_ID
AND SCT1.CONTAINER_ID = v.CONTAINER_ID
AND XM1.TABLE_NAME ='SHIPMENT_CONTAINER_TIMESTAMP'
)
UNION
SELECT
to_char(ST1.TIMESTAMP,
'YYYYMMDDHH24MISS')
FROM
SHIPMENT_TIMESTAMP ST1
WHERE
ST1.EVENT_ID = XM1.EVENT_ID
AND ST1.SHIPMENT_ID = S.SHIPMENT_ID
AND XM1.TABLE_NAME = 'SHIPMENT_TIMESTAMP'
UNION
SELECT
to_char(OT1.TIMESTAMP,
'YYYYMMDDHH24MISS')
FROM
ORDER_TIMESTAMP OT1
WHERE
OT1.EVENT_ID = XM1.EVENT_ID
AND OT1.SHIPMENT_ORDER_ID = SO.SHIPMENT_ORDER_ID
AND XM1.TABLE_NAME = 'ORDER_TIMESTAMP'
) AS FROM_DATE,
AND V.SHIPMENT_ID = S.SHIPMENT_ID AND s.SHIPMENT_ID = SO.SHIPMENT_ID(+) AND V.SHIPMENT_ID = SC.SHIPMENT_ID
SELECT
min(LANE_ID)
FROM
LANE
WHERE
CUSTOMER_MOT_ID = 1040130842248
)
AND TL1.TRAFFIC_LANE_NO = TL2.TRAFFIC_LANE_NO AND TL1.LANE_ID < TL2.LANE_ID AND TL2.COMPONENT_VALUE = S.ORG_STATION_CODE AND TL1.COMPONENT_VALUE = S.DEST_STATION_CODEORDER BY
Operation Object Name Rows Bytes Cost TQ In/Out
PStart PStop
SELECT STATEMENT Hint=CHOOSE 275 25352
SORT ORDER BY 275 84 K 25352
HASH JOIN 275 84 K 25337
MERGE JOIN 48 12 K 25332
HASH JOIN 150 35 K 25329
TABLE ACCESS FULL TRAFFIC_LANE 409 15 K 1
HASH JOIN 73 K 14 M 24668
TABLE ACCESS BY INDEX ROWID TRAFFIC_LANE 1 38 1
INDEX RANGE SCAN TL_LANE_ID_VALUE 1 2
SORT AGGREGATE 1 16
FIRST ROW 2 32 1
INDEX RANGE SCAN (MIN/MAX) LANE_CMOT_LANE_ID 2
1
HASH JOIN OUTER 7 M 1G 6564
HASH JOIN OUTER 198 K 30 M 2534
NESTED LOOPS 198 K 22 M 504
HASH JOIN OUTER 490 39 K 14
HASH JOIN 40 1 K 9
TABLE ACCESS BY INDEX ROWID SHIPMENT_CUSTOMER
39 1014 2
INDEX RANGE SCAN SHIP_CUS_CMOT_ID 39
1
TABLE ACCESS BY INDEX ROWID SHIPMENT 102
2 K 6
INDEX RANGE SCAN SHIP_MSG_CREATION_DT 102
2
TABLE ACCESS FULL SHIPMENT_CONTAINER 1 K 41 K
3
TABLE ACCESS BY INDEX ROWID SHIPMENT 40 K 1 M
1
INDEX UNIQUE SCAN XPKSHIPMENT 40 K
TABLE ACCESS FULL SHIPMENT_ORDER 82 3 K 1
TABLE ACCESS FULL ORDER_HEADER 3 K 36 K 7
SORT JOIN 32 416 3
TABLE ACCESS FULL CUSTOMER_EVENT 32 416 1
TABLE ACCESS FULL XM_FIELD 572 32 K 2
Any help in this regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this message
by anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishnaswamy, Ranganath INET: Ranganath.Krishnaswamy_at_blr.hpsglobal.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Sun Feb 02 2003 - 23:03:38 CST
 |
 |