Ranganath I notice that the "cost" for the explain plan is identical. Keep in mind that the explain plan is a good quick look at how Oracle intends to perform the query, but is not an actual execution. It can be fooled, and even make bad choices. When I encounter a query where explain plan isn't giving me good information, I turn trace on and then run the results through tkprof. I entered "tkprof tutorial" in Google and received several good links, this one came out on top: http://info-it.umsystem.edu/oradocs/doc/server/doc/A48506/strace.htm#1018
Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- [mailto:[EMAIL PROTECTED]] Sent: Monday, February 03, 2003 3:59 AM To: Multiple recipients of list ORACLE-L Hi Listers, I have the below query which takes 36 sec. to execute: SELECT /*+ordered*/ S.HOUSEBILL, OH.ORDER_REF_NO, V.CONTAINER_NUM, S.AEI_REFERENCE, CE1.SEQUENCE, TL1.COMPONENT_VALUE, TL2.COMPONENT_VALUE,( 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 XM1.TABLE_NAME = 'SHIPMENT_TIMESTAMP' AND ST1.SHIPMENT_ID = S.SHIPMENT_ID AND ST1.EVENT_ID = XM1.EVENT_ID 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, S.SHIPMENT_ID, OH.ORDER_ID, V.CONTAINER_ID FROM SHIPMENT S, V_SHIP V, SHIPMENT_CUSTOMER SC, ORDER_HEADER OH, SHIPMENT_ORDER SO, TRAFFIC_LANE TL1, TRAFFIC_LANE TL2, CUSTOMER_EVENT CE1, XM_FIELD XM1 WHERE (SC.CUSTOMER_MOT_ID = 1040130842248 AND SC.SHIPMENT_ID = S.SHIPMENT_ID AND trunc(S.MSG_CREATION_DATE) >= trunc(sysdate - 90) AND trunc(S.MSG_CREATION_DATE) <= trunc(sysdate) AND S.SHIPMENT_ID = V.SHIPMENT_ID AND V.SHIPMENT_ID = SO.SHIPMENT_ID(+) AND SO.ORDER_ID = OH.ORDER_ID(+)) AND (TL1.TRAFFIC_LANE_NO = TL2.TRAFFIC_LANE_NO AND TL1.LANE_ID = 10413530577392 and TL2.LANE_ID = 10413530577393 AND TL2.COMPONENT_VALUE = S.ORG_STATION_CODE AND TL1.COMPONENT_VALUE = S.DEST_STATION_CODE) AND CE1.CUSTOMER_MOT_ID = 1040130842248 and XM1.FIELD_ID = CE1.FIELD_ID AND XM1.table_name in ('SHIPMENT_TIMESTAMP') ORDER BY S.SHIPMENT_ID, ORDER_ID, CONTAINER_ID, CE1.SEQUENCE Here is the explain plan for the query: Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop SELECT STATEMENT Hint=CHOOSE 349 23589 SORT ORDER BY 349 103 K 23589 HASH JOIN 349 103 K 23561 TABLE ACCESS BY INDEX ROWID XM_FIELD 6 348 2 INDEX RANGE SCAN XM_FIELD_TABLE_NAME 6 1 MERGE JOIN CARTESIAN 5 K 1 M 23537 NESTED LOOPS 182 41 K 23355 HASH JOIN 364 K 67 M 23355 TABLE ACCESS BY INDEX ROWID TRAFFIC_LANE 5 190 1 INDEX RANGE SCAN TL_LANE_ID_VALUE 5 2 HASH JOIN OUTER 7 M 1G 6630 HASH JOIN OUTER 197 K 27 M 2918 HASH JOIN 197 K 20 M 1096 TABLE ACCESS BY INDEX ROWID SHIPMENT_CUSTOMER 39 1014 2 INDEX RANGE SCAN SHIP_CUS_CMOT_ID 39 1 HASH JOIN 506 K 39 M 450 TABLE ACCESS BY INDEX ROWID SHIPMENT 102 4 K 6 INDEX RANGE SCAN SHIP_MSG_CREATION_DT 102 2 VIEW V_SHIP 496 K 16 M 124 HASH JOIN OUTER 496 K 22 M 124 INDEX FAST FULL SCAN SHP_SHP_ID_DEST_COUNTRY 40 K 514 K 4 TABLE ACCESS FULL SHIPMENT_CONTAINER 1 K 41 K 3 TABLE ACCESS FULL SHIPMENT_ORDER 82 3 K 1 TABLE ACCESS FULL ORDER_HEADER 3 K 36 K 7 INDEX UNIQUE SCAN XPKTRAFFIC_LANE 5 190 SORT JOIN 32 416 23537 TABLE ACCESS BY INDEX ROWID CUSTOMER_EVENT 32 416 1 INDEX RANGE SCAN CUST_EVENT_CMOT_ID 32 But when the query is changed to below query it takes 20 min. to execute: SELECT /*+ordered*/ S.HOUSEBILL, OH.ORDER_REF_NO, V.CONTAINER_NUM, S.AEI_REFERENCE, CE1.SEQUENCE, TL1.COMPONENT_VALUE, TL2.COMPONENT_VALUE,( 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 XM1.TABLE_NAME = 'SHIPMENT_TIMESTAMP' AND ST1.SHIPMENT_ID = S.SHIPMENT_ID AND ST1.EVENT_ID = XM1.EVENT_ID 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, S.SHIPMENT_ID, OH.ORDER_ID, V.CONTAINER_ID FROM SHIPMENT S, V_SHIP V, SHIPMENT_CUSTOMER SC, ORDER_HEADER OH, SHIPMENT_ORDER SO, TRAFFIC_LANE TL1, TRAFFIC_LANE TL2, CUSTOMER_EVENT CE1, XM_FIELD XM1 WHERE (SC.CUSTOMER_MOT_ID = 1040130842248 AND SC.SHIPMENT_ID = S.SHIPMENT_ID AND trunc(S.MSG_CREATION_DATE) >= trunc(sysdate - 90) AND trunc(S.MSG_CREATION_DATE) <= trunc(sysdate) AND S.SHIPMENT_ID = V.SHIPMENT_ID AND V.SHIPMENT_ID = SO.SHIPMENT_ID(+) AND SO.ORDER_ID = OH.ORDER_ID(+)) AND (TL1.TRAFFIC_LANE_NO = TL2.TRAFFIC_LANE_NO AND TL1.LANE_ID = 10413530577392 and TL2.LANE_ID = 10413530577393 AND TL2.COMPONENT_VALUE = S.ORG_STATION_CODE AND TL1.COMPONENT_VALUE = S.DEST_STATION_CODE) AND CE1.CUSTOMER_MOT_ID = 1040130842248 and XM1.FIELD_ID = CE1.FIELD_ID AND XM1.table_name in ('SHIPMENT_TIMESTAMP', 'ORDER_TIMESTAMP, 'SHIPMENT_CONTAINER_TIMESTAMP') ORDER BY S.SHIPMENT_ID, ORDER_ID, CONTAINER_ID, CE1.SEQUENCE Here is the explain plan for the query: Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop SELECT STATEMENT Hint=CHOOSE 349 23589 SORT ORDER BY 349 103 K 23589 HASH JOIN 349 103 K 23561 INLIST ITERATOR TABLE ACCESS BY INDEX ROWID XM_FIELD 6 348 2 INDEX RANGE SCAN XM_FIELD_TABLE_NAME 6 2 MERGE JOIN CARTESIAN 5 K 1 M 23537 NESTED LOOPS 182 41 K 23355 HASH JOIN 364 K 67 M 23355 TABLE ACCESS BY INDEX ROWID TRAFFIC_LANE 5 190 1 INDEX RANGE SCAN TL_LANE_ID_VALUE 5 2 HASH JOIN OUTER 7 M 1G 6630 HASH JOIN OUTER 197 K 27 M 2918 HASH JOIN 197 K 20 M 1096 TABLE ACCESS BY INDEX ROWID SHIPMENT_CUSTOMER 39 1014 2 INDEX RANGE SCAN SHIP_CUS_CMOT_ID 39 1 HASH JOIN 506 K 39 M 450 TABLE ACCESS BY INDEX ROWID SHIPMENT 102 4 K 6 INDEX RANGE SCAN SHIP_MSG_CREATION_DT 102 2 VIEW V_SHIP 496 K 16 M 124 HASH JOIN OUTER 496 K 22 M 124 INDEX FAST FULL SCAN SHP_SHP_ID_DEST_COUNTRY 40 K 514 K 4 TABLE ACCESS FULL SHIPMENT_CONTAINER 1 K 41 K 3 TABLE ACCESS FULL SHIPMENT_ORDER 82 3 K 1 TABLE ACCESS FULL ORDER_HEADER 3 K 36 K 7 INDEX UNIQUE SCAN XPKTRAFFIC_LANE 5 190 SORT JOIN 32 416 23537 TABLE ACCESS BY INDEX ROWID CUSTOMER_EVENT 32 416 1 INDEX RANGE SCAN CUST_EVENT_CMOT_ID 32 Can anybody let me know as to how do I bring down the response time of the query if the IN List is hardcoded with more than 1 value? 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
