Home » RDBMS Server » Performance Tuning » help to wrire the procedure (oracle,10g,unix)
help to wrire the procedure [message #496279] Fri, 25 February 2011 13:12 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi All,

my below procedure is updating 5000 records in 15 minutes.RULE_INTERIM_1 having 400000 records. so this procedure will take almost 20 hours. can you suggest some best way to write that code.

DECLARE

    CURSOR C1 IS 
    SELECT DISTINCT INVENTORY_ITEM_ID 
    FROM RULE_INTERIM_1;
    
    TYPE X1 IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
    Z1 X1;

    l_strategy_source NUMBER;
    l_psl_strategy VARCHAR2(20);
BEGIN


    OPEN C1;
    LOOP
       FETCH C1 BULK COLLECT INTO Z1 LIMIT 50001;
       FOR INDX1 IN 1 .. Z1.COUNT LOOP
       
       -- adding the Priority into logic for calculating the strategy source and Psl Strategy flag
       
       
       SELECT max(to_number(temp.STRATEGY_SOURCE)) into l_strategy_source
                      FROM RULE_INTERIM_1 TEMP,STRATEGY_RULES CS 
                      WHERE temp.strategy_rule_id=cs.strategy_rule_id 
                      AND INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id
                      AND NVL(priority,-1) in (SELECT NVL(min(cs.priority),-1)
                      FROM RULE_INTERIM_1 TEMP,STRATEGY_RULES CS 
                      WHERE temp.strategy_rule_id=cs.strategy_rule_id AND 
               INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id);
       
       SELECT DECODE(psl_strategy,
       5, 'Non-PSL',
       4, 'Mixed (P, LD, L)', 
       3, 'Mixed (P Only)',
       2, 'PSL (P, LD, L)',
       1, 'PSL (P Only)', '') into l_psl_strategy
       FROM (SELECT temp.psl_strategy
       FROM RULE_INTERIM_1 TEMP,STRATEGY_RULES CS 
       WHERE temp.strategy_rule_id=cs.strategy_rule_id 
       AND INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id
       AND NVL(priority,-1) in (SELECT NVL(min(cs.priority),-1)
       FROM RULE_INTERIM_1 TEMP,STRATEGY_RULES CS 
       WHERE temp.strategy_rule_id=cs.strategy_rule_id AND 
INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id ) GROUP BY temp.psl_strategy ) WHERE ROWNUM < 2;
       
       
          UPDATE PART EMPA
          SET strategy_source = l_strategy_source,
          psl_strategy =  l_psl_strategy
          WHERE INVENTORY_ITEM_ID = Z1(INDX1).inventory_item_id;

       END LOOP;
    EXIT WHEN C1%NOTFOUND;
    END LOOP;
    CLOSE C1;

       
/*-----------------------------
|                             |
|       FINAL COMMIT          |
|                             |
+-----------------------------*/
       COMMIT;


EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_stack());
       ROLLBACK;
END;
/



Thanks,
Sagar
Re: help to wrire the procedure [message #496280 is a reply to message #496279] Fri, 25 February 2011 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: help to wrire the procedure [message #496283 is a reply to message #496280] Fri, 25 February 2011 15:10 Go to previous message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
Rewrite as a single update statement.
Previous Topic: performance tuning
Next Topic: Wrong REDO log file location
Goto Forum:
  


Current Time: Fri Aug 22 16:55:24 CDT 2014

Total time taken to generate the page: 0.08508 seconds