Query takes more time when number of IN List value increases
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,
'MMDDHH24MISS')
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,
'MMDDHH24MISS')
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,
'MMDDHH24MISS')
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,
'MMDDHH24MISS')
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 RowsBytes CostTQ In/Out
PStart PStop
SELECT STATEMENT Hint=CHOOSE349 23589
SORT ORDER BY 349 103 K 23589
HASH JOIN 349 103 K 23561
TABLE ACCESS BY INDEX ROWID XM_FIELD6 348 2
INDEX RANGE SCANXM_FIELD_TABLE_NAME 6 1
MERGE JOIN CARTESIAN 5 K 1 M 23537
NESTED LOOPS182 41 K23355
HASH JOIN 364 K 67 M23355
TABLE ACCESS BY INDEX ROWID TRAFFIC_LANE5 190 1
INDEX RANGE SCAN TL_LANE_ID_VALUE5 2
HASH JOIN OUTER 7 M 1G 6630
HASH JOIN OUTER 197 K 27 M2918
HASH JOIN 197 K 20 M1096
TABLE ACCESS BY INDEX ROWID SHIPMENT_CUSTOMER 39
10142
INDEX RANGE SCANSHIP_CUS_CMOT_ID39
1
HASH JOIN 506 K 39 M450
TABLE ACCESS BY INDEX ROWID SHIPMENT102 4 K
6
INDEX RANGE SCAN SHIP_MSG_CREATION_DT102
2
VIEWV_SHIP 496 K 16 M124
HASH JOIN OUTER 496 K 22 M124
INDEX FAST FULL SCANSHP_SHP_ID_DEST_COUNTRY 40 K
514 K 4
TABLE ACCESS FULL SHIPMENT_CONTAINER 1 K
41 K3
TABLE ACCESS FULL SHIPMENT_ORDER 82 3 K 1
TABLE ACCESS FULL ORDER_HEADER3 K 36 K7
INDEX UNIQUE SCAN XPKTRAFFIC_LANE 5 190
SORT JOIN 32 416 23537
TABLE ACCESS BY INDEX ROWID CUSTOMER_EVENT 32 416 1
RE: Query takes more time when number of IN List value increases
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,
'MMDDHH24MISS')
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,
'MMDDHH24MISS')
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,
'MMDDHH24MISS')
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,
'MMDDHH24MISS')
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 RowsBytes CostTQ In/Out
PStart PStop
SELECT STATEMENT Hint=CHOOSE349 23589
SORT ORDER BY 349 103 K 23589
HASH JOIN 349 103 K 23561
TABLE ACCESS BY INDEX ROWID XM_FIELD6 348 2
INDEX RANGE SCANXM_FIELD_TABLE_NAME 6 1
MERGE JOIN CARTESIAN 5 K 1 M 23537
NESTED LOOPS182 41 K23355
HASH JOIN 364 K 67 M23355
TABLE ACCESS BY INDEX ROWID TRAFFIC_LANE5 190 1
INDEX RANGE SCAN TL_LANE_ID_VALUE5 2
HASH JOIN OUTER 7 M 1G 6630
HASH JOIN OUTER 197 K 27 M2918
HASH JOIN 197 K 20 M1096
TABLE ACCESS BY INDEX ROWID SHIPMENT_CUSTOMER 39
10142
INDEX RANGE SCANSHIP_CUS_CMOT_ID39
1
HASH JOIN 506 K 39 M450
TABLE ACCESS BY INDEX ROWID SHIPMENT102 4 K
6
INDEX RANGE SCAN SHIP_MSG_CREATION_DT102
2
RE: Query takes more time when number of IN List value increases
Raj - If someone hasn't previously used tkprof, in your opinion should they start with this utility, or with tkprof and work their way to this? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 03, 2003 9:40 AM To: Multiple recipients of list ORACLE-L Also see http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_ database_id=NOT http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p _database_id=NOTp_id=224270.1 p_id=224270.1 this makes it easy ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- 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).
RE: Query takes more time when number of IN List value increases
Title: RE: Query takes more time when number of IN List value increases Also see http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=NOT_id=224270.1 this makes it easy ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Query takes more time when number of IN List value increases
Title: RE: Query takes more time when number of IN List value increases Dennis, I am not an expert, but I find this utility gives a (relatively) readable output compared to tkprof. I use both, but for starters I'd say, look at both and see what you like. This utility doesn't use tkprof ... I think ... g As usual YMMV Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Monday, February 03, 2003 11:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: Query takes more time when number of IN List value increases Raj - If someone hasn't previously used tkprof, in your opinion should they start with this utility, or with tkprof and work their way to this? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 03, 2003 9:40 AM To: Multiple recipients of list ORACLE-L Also see http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_ database_id=NOT http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p _database_id=NOTp_id=224270.1 p_id=224270.1 this makes it easy ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- 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). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
