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

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

RE: Query tuning help needed

From: Odland, Brad <Brad.Odland_at_qtiworld.com>
Date: Tue, 29 Apr 2003 07:26:48 -0800
Message-ID: <F001.0058BF09.20030429072648@fatcity.com> 





Two words...



Stored Procedure.



Maybe you should look to recoding this in PL/SQL you may get more efficient
execution from a stored procedure with cursors rather than doing it all in
one SQL statement. When you fetch from a cursor you can do all kinds of row
level processing faster and control how it processes. (not to mention adding
feedback and error handling...)



YOu could start with eliminating the data you don't need from that huge
station table. Then confine processing to that. That is if you can identify
the data you don't need...I would think you should be able to. ASk yourself
how many records are you ulitmately going to insert. If you insert 3 million
then you have a problem. If you only insert a fraction then it should be
easy to write a cursor that gets the data you need for processing then loop
through that cursor fetching the additional data needed to build the row for
the insert....




Good Luck!



Brad O.



-----Original Message-----


[mailto:Ranganath.Krishnaswamy_at_blr.hpsglobal.com]
Sent: Tuesday, April 29, 2003 8:26 AM


To: Multiple recipients of list ORACLE-L




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


-- 



Please see the official ORACLE-L FAQ: http://www.orafaq.net


-- 



Author: Odland, Brad


  INET: Brad.Odland_at_qtiworld.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 - 10:26:48 CDT












Original text of this message













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