Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Query tuning help needed

Query tuning help needed

From: Krishnaswamy, Ranganath <Ranganath.Krishnaswamy_at_blr.hpsglobal.com>
Date: Tue, 29 Apr 2003 05:25:40 -0800
Message-ID: <F001.0058BD10.20030429052540@fatcity.com> 





 Hi List,




 The below query is taking almost 32 minutes to execute.  How do I rewrite
the query and/or add hints to make it run faster?  The SERVICE table has
only 5 records and the cartesian product is very much required.  The station
table has 10145 records, the seg_min_times table has 3375 records and the
pry_two_mr_transit has 3046767 records:



   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 
		           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 nvl(sum(OD_TIME_IN_MIN),0)


FROM SEG_MIN_TIMES M, STATION ST3, STATION ST4,

        
PRY_TWO_MR_TRANSIT T, SERVICE



                                                                   WHERE



M.ORIGIN = ST3.STATION_CODE AND M.DESTINATION = ST4.STATION_CODE 

                                                                   AND



ST3.STN_KEY = T.SEG_ORG_STN_KEY AND ST4.STN_KEY = T.SEG_DEST_STN_KEY 

                                                                   AND



M.SERVICE = SERVICE_CODE AND T.TYPE = 'OD'

                                                                   AND



SERVICE_CODE IN ('X','Y','Z','Q'))


                               WHEN T.TYPE ='OT'
                                   THEN (SELECT nvl(sum(OT_TIME_IN_MIN),0)


FROM SEG_MIN_TIMES M, STATION ST3, STATION ST4,

        
PRY_TWO_MR_TRANSIT T, SERVICE



                                                                   WHERE



M.ORIGIN = ST3.STATION_CODE AND M.DESTINATION = ST4.STATION_CODE 

                                                                   AND



ST3.STN_KEY = T.SEG_ORG_STN_KEY AND ST4.STN_KEY = T.SEG_DEST_STN_KEY 

                                                                   AND



M.SERVICE = SERVICE_CODE AND T.TYPE = 'OT'

                                                                   AND



SERVICE_CODE IN ('X','Y','Z','Q'))


                               WHEN T.TYPE ='TT'
                                   THEN (SELECT nvl(sum(TT_TIME_IN_MIN),0)


FROM SEG_MIN_TIMES M, STATION ST3, STATION ST4,

        
PRY_TWO_MR_TRANSIT T, SERVICE 



                                                                   WHERE



M.ORIGIN = ST3.STATION_CODE AND M.DESTINATION = ST4.STATION_CODE 

                                                                   AND



ST3.STN_KEY = T.SEG_ORG_STN_KEY AND ST4.STN_KEY = T.SEG_DEST_STN_KEY 

                                                                   AND



M.SERVICE = SERVICE_CODE AND T.TYPE = 'TT'

                                                                   AND



SERVICE_CODE IN ('X','Y','Z','Q'))


                               WHEN T.TYPE ='TD'
                                   THEN (SELECT nvl(sum(TD_TIME_IN_MIN),0)


FROM SEG_MIN_TIMES M, STATION ST3, STATION ST4,

        
PRY_TWO_MR_TRANSIT T, SERVICE



                                                                   WHERE



M.ORIGIN = ST3.STATION_CODE AND M.DESTINATION = ST4.STATION_CODE 

                                                                   AND



ST3.STN_KEY = T.SEG_ORG_STN_KEY AND ST4.STN_KEY = T.SEG_DEST_STN_KEY 

                                                                   AND



M.SERVICE = SERVICE_CODE AND T.TYPE = 'TD'

                                                                   AND



SERVICE_CODE IN ('X','Y','Z','Q'))


                           END), 0) AS SEG_TIME
            FROM
                 PRY_TWO_MR_TRANSIT T,
                 STATION ST1,
                 STATION ST2,
                 SERVICE
            WHERE
                  ST1.STN_KEY = T.ORG_STN_KEY
            AND   ST2.STN_KEY = T.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;



The explain plan for the query is as below:


Operation	Object Name	Rows	Bytes	Cost	TQ	In/Out
PStart	PStop





INSERT STATEMENT Hint=CHOOSE



  SORT GROUP BY


    VIEW


      SORT GROUP BY


        NESTED LOOPS


          NESTED LOOPS


            NESTED LOOPS


              TABLE ACCESS FULL	STATION


              TABLE ACCESS BY INDEX ROWID	PRY_TWO_MR_TRANSIT



                INDEX RANGE SCAN	I1_PRY_TWO_MR_TRANSIT

            TABLE ACCESS BY INDEX ROWID	STATION

              INDEX UNIQUE SCAN	PK_STATION

          TABLE ACCESS FULL	SERVICE






Can anybody please let me know as to how do I rewrite the query and/or add
additional hints so that the performance is improved (typically it should
take 10 min. to execute)?  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 Tue Apr 29 2003 - 08:25:40 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US