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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Performance issue in a function

Re: Performance issue in a function

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 13 Jul 2006 21:03:52 GMT
Message-ID: <J2D16L.7Dr@igsrsparc2.er.usgs.gov> 





Sujatha wrote:


> Hi Brian,

> 

> As you said, i tried tuning my cursors in the function. here is my

> function.

> CREATE OR REPLACE FUNCTION GET_DM_AMOUNT (P_DM_NUMBER NUMBER


>                           ,P_MFR_ID NUMBER DEFAULT NULL


>                           ) RETURN NUMBER


>    IS

>      vn_amount Number;

>      vr_eb EXP_BATCH%ROWTYPE;

>      vr_dm EXP_DEBIT_MEMO%ROWTYPE;

>      CURSOR mfr_phy_returns_cur IS

>        SELECT SUM(extended_price) price

>          FROM exp_order_lines

>         WHERE order_line_id IN

>         (SELECT order_line_id FROM exp_contents_of_bag WHERE

> container_id IN

>           (SELECT container_id FROM exp_containers

>             WHERE manufacturer_id    = vr_dm.tc_id

>               AND batch_id           = vr_dm.batch_id

>               AND exp_dea_group      = vr_dm.EXP_DEA_GROUP

>               AND container_type_id  = 1

>               AND parent_id IS NOT NULL

>               AND NVL(ship_via, 'F') = NVL(vr_dm.RETURN_VIA,'F')))

>   ;

> 

>        CURSOR mfr_risw_returns_cur IS

>          SELECT SUM(ol.extended_price) price

>            FROM exp_order_lines ol, exp_order_headers oh

>           WHERE oh.batch_id                   = vr_dm.batch_id

>             AND oh.order_header_id            = ol.order_header_id

>             AND ol.return_waste_indate_flag   = 'R'

>             AND ol.cap_line                   = 'Y'

>             AND ol.return_in_system_indicator = 'Y'

>             AND NVL(ol.cla_rx_code_drug_code, 'RX') IN

>                 (SELECT dea_class_code FROM exp_dea_class WHERE

> exp_dea_group = vr_dm.EXP_DEA_GROUP)

>             AND ol.manufacturer_name IN

>                 (SELECT manufacturer_name FROM mfrs_in_debit_memo WHERE

> exp_dm_number = vr_dm.exp_dm_number)             ;

> 

>        CURSOR retDepot_phy_returns_cur IS

>      	 SELECT SUM(extended_price) price

>        	 FROM exp_order_lines ol

>       	 WHERE order_line_id IN

>      		 (SELECT order_line_id FROM exp_contents_of_bag WHERE

> container_id IN

>      		   (SELECT container_id FROM exp_containers

>      		     WHERE manufacturer_id    = vr_dm.tc_id

>      		       AND batch_id           = vr_dm.batch_id

>      		       AND exp_dea_group      = vr_dm.exp_dea_group

>      		       AND container_type_id  = 1

>      		       AND parent_id IS NOT NULL

>      		       AND NVL(ship_via, 'F') = NVL(vr_dm.RETURN_VIA, 'F')))

> 

>          AND manufacturer_name IN (SELECT manufacturer_name FROM

> mfrs_in_debit_memo mfrs --, exp_trading_community tc

>                                    WHERE mfrs.exp_dm_number =

> vr_dm.exp_dm_number

>                                    AND parent_mfr_id = P_MFR_ID);

> 

>        CURSOR retDepot_risw_returns_cur IS

>   	SELECT SUM(ol.extended_price) price

>       	FROM exp_order_lines ol, exp_order_headers oh

>      	WHERE oh.batch_id                   = vr_dm.batch_id

>           AND oh.order_header_id            = ol.order_header_id

>           AND ol.return_waste_indate_flag   = 'R'

>           AND ol.cap_line                   = 'Y'

>           AND ol.return_in_system_indicator = 'Y'

>           AND ol.wholesaler_fax_unipak_flag = (SELECT tc_initial FROM

> exp_trading_community

>                                              WHERE tc_id = vr_dm.tc_id)

>           AND NVL(ol.cla_rx_code_drug_code, 'RX') IN

>                 (SELECT dea_class_code FROM exp_dea_class WHERE

> exp_dea_group = vr_dm.exp_dea_group)

>           AND ol.manufacturer_name IN (SELECT manufacturer_name FROM

> mfrs_in_debit_memo

>                                      WHERE exp_dm_number =

> vr_dm.exp_dm_number

>                                        AND parent_mfr_id = P_MFR_ID);

>    BEGIN


>      SELECT * INTO vr_dm FROM EXP_DEBIT_MEMO WHERE

> EXP_DM_NUMBER=P_DM_NUMBER;


>      SELECT * INTO vr_eb FROM EXP_BATCH WHERE BATCH_ID=vr_dm.BATCH_ID;

>      IF vr_dm.BATCH_ID IS NOT NULL THEN -- CAP

>        IF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'N' AND vr_dm.TC_TYPE_ID =

> 1 THEN


>          FOR mfr_phy_returns_rec IN mfr_phy_returns_cur LOOP

>            vn_amount:=mfr_phy_returns_rec.price;

>          END LOOP;


>        ELSIF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'Y' AND vr_dm.TC_TYPE_ID

> = 1 THEN


>          FOR mfr_risw_returns_rec IN mfr_risw_returns_cur LOOP

>            vn_amount:=mfr_risw_returns_rec.price;

>          END LOOP;


>        ELSIF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'N' AND vr_dm.TC_TYPE_ID

> = 6 THEN


>          FOR retDepot_phy_returns_rec IN retDepot_phy_returns_cur LOOP

>            vn_amount:=retDepot_phy_returns_rec.price;

>          END LOOP;


>        ELSIF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'Y' AND vr_dm.TC_TYPE_ID

> = 6 THEN


>          FOR retDepot_risw_returns_rec IN retDepot_risw_returns_cur

> LOOP


>            vn_amount:=retDepot_risw_returns_rec.price;

>          END LOOP;


>        END IF;


>      ELSE  -- Non-CAP

>        null;

>      END IF;


>      return NVL(vn_amount,0);

>    END GET_DM_AMOUNT;


> 

> The function is really big and hence for about 2500 records if it has

> to go through the function each time, it will take time. But can

> somebody suggests another method of doing this function.




Again...I ask...."Have you taken those SQL statements and tuned them to 
ensure that they are optimal?"



Your first cursor is defined as:


       CURSOR mfr_phy_returns_cur IS
         SELECT SUM(extended_price) price
           FROM exp_order_lines
          WHERE order_line_id IN
          (SELECT order_line_id FROM exp_contents_of_bag WHERE
  container_id IN
            (SELECT container_id FROM exp_containers
              WHERE manufacturer_id    = vr_dm.tc_id
                AND batch_id           = vr_dm.batch_id
                AND exp_dea_group      = vr_dm.EXP_DEA_GROUP
                AND container_type_id  = 1
                AND parent_id IS NOT NULL
                AND NVL(ship_via, 'F') = NVL(vr_dm.RETURN_VIA,'F')));





Have you tuned this SELECT query? It seems to me that one approach would 
be to write this as a join instead of using subqueries:



SELECT SUM(o.extended_price) price


FROM exp_order_lines o, exp_contents_of_bag b, exp_containers c
WHERE o.order_line_id = b.order_line_id

   AND b.container_id = c.container_id
   AND c.manufacturer_id    = vr_dm.tc_id
   AND c.batch_id           = vr_dm.batch_id
   AND c.exp_dea_group      = vr_dm.EXP_DEA_GROUP
   AND c.container_type_id  = 1
   AND c.parent_id IS NOT NULL




   AND NVL(c.ship_via, 'F') = NVL(vr_dm.RETURN_VIA,'F')));



Does that change the runtime of the cursor? The truth is that I do not 
know...only you will know that. But you'll have to take each cursor's 
query and tune that (as I have already said twice before).



And looking at your function's code, you have other SELECT statements 
that are not inside a cursor. For instance, you have:



SELECT * INTO vr_dm FROM EXP_DEBIT_MEMO WHERE EXP_DM_NUMBER=P_DM_NUMBER;



Have you tuned that query?



Trying to be as polite as possible...I have already stated this three 
times...I cannot wave a magic wand and tune your queries for you. 
Querying tuning is a process sufficient to warrant an entire volume on 
the subject. So I cannot state here in this forum an easy way to do that.



Cheers,


Brian




-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown


Received on Thu Jul 13 2006 - 16:03:52 CDT












Original text of this message













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