Oracle-L: Query running very slow

From: Krishnaswamy, Ranganath <Ranganath.Krishnaswamy_at_blr.hpsglobal.com>
Date: Fri, 25 Apr 2003 07:11:56 -0800
Message-ID: <F001.005894B1.20030425071156@fatcity.com>

Hi List,

I have the below query which is taking 32 min. to execute:

INSERT INTO OD_PAIR_ADD_VALUE_TEMP(ORIGIN, DESTINATION, SERVICE, ADD_ON_MIN, LAST_UPDATED, UPDATED_BY)
SELECT

            ORIGIN,
            DESTINATION,
            SERVICE_CODE,
            CASE
                WHEN SERVICE_CODE = 'X'
                    THEN AVG(SEG_TIME) + 1
                WHEN SERVICE_CODE = 'Y'
                    THEN AVG(SEG_TIME) + 2
                WHEN SERVICE_CODE = 'Z'
                    THEN AVG(SEG_TIME) + 3
                WHEN SERVICE_CODE = 'Q'
                    THEN AVG(SEG_TIME) + 4
            END AS ADD_ON_VALUE,
            SYSDATE,
            'SYSTEM'
    FROM
           (SELECT /*+ index(T I1_PRY_TWO_MR_TRANSIT) index(ST1 PK_STATION)

index(ST2 PK_STATION) index(ST3 PK_STATION) index(ST4 PK_STATION) index(SERVICE UK_SERVICE) */

                   META_ROUTING_KEY     AS ROUTE_KEY,
                   ST1.STATION_CODE     AS ORIGIN,
                   ST2.STATION_CODE     AS DESTINATION,
                   SERVICE_CODE,
                   NVL(SUM(CASE
                               WHEN T.TYPE ='OD'
                                   THEN (SELECT OD_TIME_IN_MIN FROM

SEG_MIN_TIMES M WHERE M.ORIGIN = ST3.STATION_CODE AND M.DESTINATION = ST4.STATION_CODE AND M.SERVICE = SERVICE_CODE)

                               WHEN T.TYPE ='OT'
                                   THEN (SELECT OT_TIME_IN_MIN FROM

SEG_MIN_TIMES M WHERE M.ORIGIN = ST3.STATION_CODE AND M.DESTINATION = ST4.STATION_CODE AND M.SERVICE = SERVICE_CODE)

                               WHEN T.TYPE ='TT'
                                   THEN (SELECT TT_TIME_IN_MIN FROM

SEG_MIN_TIMES M WHERE M.ORIGIN = ST3.STATION_CODE AND M.DESTINATION = ST4.STATION_CODE AND M.SERVICE = SERVICE_CODE)

                               WHEN T.TYPE ='TD'
                                   THEN (SELECT TD_TIME_IN_MIN FROM

SEG_MIN_TIMES M WHERE M.ORIGIN = ST3.STATION_CODE AND M.DESTINATION = ST4.STATION_CODE AND M.SERVICE = SERVICE_CODE)

                           END), 0) AS SEG_TIME
            FROM
                 PRY_TWO_MR_TRANSIT T,
                 STATION ST1,
                 STATION ST2,
                 STATION ST3,
                 STATION ST4,
                 SERVICE
            WHERE			      
                  ST1.STN_KEY = T.ORG_STN_KEY
            AND   ST2.STN_KEY = T.DEST_STN_KEY
            AND   ST3.STN_KEY = T.SEG_ORG_STN_KEY
            AND   ST4.STN_KEY = T.SEG_DEST_STN_KEY
			AND   SERVICE_CODE IN ('X','Y','Z','Q')           
            GROUP BY META_ROUTING_KEY, ST1.STATION_CODE, ST2.STATION_CODE,

SERVICE_CODE)
GROUP BY ORIGIN, DESTINATION, SERVICE_CODE; Here is the execution plan for the query:

INSERT STATEMENT Hint=CHOOSE

  SORT GROUP BY		 	      	             	 
    VIEW		 	      	             	 
      SORT GROUP BY		 	      	             	 
        MERGE JOIN CARTESIAN

          HASH JOIN		  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	STATION

              INDEX FULL SCAN	PK_STATION

            HASH JOIN			 	      	             	 
              TABLE ACCESS BY INDEX ROWID	STATION

                INDEX FULL SCAN	PK_STATION

              HASH JOIN		  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	STATION

                  INDEX FULL SCAN	PK_STATION

                NESTED LOOPS

                  TABLE ACCESS BY INDEX ROWID	PRY_TWO_MR_TRANSIT

                    INDEX FULL SCAN	I1_PRY_TWO_MR_TRANSIT

                  TABLE ACCESS BY INDEX ROWID	STATION

                    INDEX UNIQUE SCAN	PK_STATION

          BUFFER SORT			 	      	             	 
            INLIST ITERATOR

              BITMAP CONVERSION TO ROWIDS

                BITMAP INDEX SINGLE VALUE	UK_SERVICE

How do I reduce the execution time for the query? Is there any way I can rewrite the query and/or add few more hints? 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 Fri Apr 25 2003 - 10:11:56 CDT

Query running very slow - Please help