Message-ID: <F001.00541AE2.20030202210338@fatcity.com>
Date: Sun, 02 Feb 2003 21:03:38 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Krishnaswamy, Ranganath" <Ranganath.Krishnaswamy@blr.hpsglobal.com>
Subject: Slow query tuning
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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,
   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
         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,
   S.SHIPMENT_ID,
   OH.ORDER_ID,
   v.CONTAINER_ID
FROM
   SHIPMENT_CUSTOMER SC,
   V_SHIP v,    
   ORDER_HEADER OH,
   SHIPMENT_ORDER SO,
   SHIPMENT S,
   TRAFFIC_LANE TL1,
   TRAFFIC_LANE TL2,
   CUSTOMER_EVENT CE1,
   XM_FIELD XM1
WHERE
   CE1.CUSTOMER_MOT_ID = 1040130842248
   AND SC.CUSTOMER_MOT_ID = CE1.CUSTOMER_MOT_ID
   AND V.SHIPMENT_ID = S.SHIPMENT_ID
   AND s.SHIPMENT_ID = SO.SHIPMENT_ID(+)
   AND V.SHIPMENT_ID = SC.SHIPMENT_ID
   AND SO.ORDER_ID = OH.ORDER_ID(+)
   and XM1.FIELD_ID = CE1.FIELD_ID
   AND TL1.LANE_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_CODE
ORDER BY
   S.SHIPMENT_ID,
   OH.ORDER_ID,
   v.CONTAINER_ID,
   CE1.SEQUENCE

Execution plan

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@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@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).