Home » SQL & PL/SQL » SQL & PL/SQL » Procedure -Performance Issue.. (10.2.0.3.0)
Procedure -Performance Issue.. [message #404336] Thu, 21 May 2009 07:17 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have written the following procedure. But It's taking more than 5 hours for execution .
I have created Index as follows.
Create Index to speed up the research of the first code with sdn 'null' :
CREATE INDEX TMP.IDX_RED_COAT_2 ON TMP.RED_COAT
(CODE_YEAR, SUBSTR("CODE",1,2), NVL("SDN",'0'))
NOLOGGING
TABLESPACE TPOCLIENT_INDEX_5M_01
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          237M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )	
NOPARALLEL;


and the procedure is
CREATE OR REPLACE PROCEDURE TMP.pr_delight
AS
   v_promo       red_coat.code%TYPE;
   v_date         DATE;

   CURSOR c1 (v_date DATE)
   IS
      SELECT DISTINCT ref_v.sdn sdn, 
                      ref_v.recharge_type recharge_type,
                      ref_v.REFERENCE REFERENCE, 
                      icat.langcode lang_code
                 FROM tmp.DEF_view ref_v, 
                      inf_tmp icat
                WHERE ref_v.sdn = icat.cardnum
                  AND ref_v.currency IN (
                               SELECT emp_id_d
                                 FROM tmp.emp
                                WHERE emp_id_h = 'CURRENCY'
                                      AND emp_txt = 'EURO')
                  AND ref_v.recharge_amount >= 100
                  AND ref_v.date_exec > v_date
                  AND ref_v.sdn NOT IN (
                         SELECT gprs_sdn
                           FROM stage.ppa_gprs
                          WHERE ppa_gprs.prom_idct =
                                   (SELECT emp_txt
                                      FROM tmp.emp
                                     WHERE emp_id_h = 'LOT'
                                       AND emp_id= 'LOT_ID'))
                  AND icat.profiled NOT IN ('KADOR', 'DINHG');

          rec   c1%ROWTYPE;
BEGIN
   v_promo := NULL;
   v_date := NULL;

   SELECT TO_DATE (emp_txt, 'DD-MON-YYYY')
     INTO v_date
     FROM tmp.emp
    WHERE emp_id_h = 'LOT' 
     AND emp_id = 'LAST_DATE' 
     AND emp_id_t = 'D';

    OPEN c1 (v_date);
   LOOP
      FETCH c1 INTO rec;
      EXIT WHEN c1%NOTFOUND;
      SELECT code
        INTO v_promo
        FROM tmp.red_coat
       WHERE SUBSTR (code, 1, 2) = TO_CHAR (SYSDATE, 'MM')
         AND code_year = TO_CHAR (SYSDATE, 'YYYY')
         AND nvl(sdn,0) =0
         AND ROWNUM = 1;

      UPDATE red_coat
         SET sdn = SUBSTR (rec.sdn, 3),
             REFERENCE = rec.REFERENCE,
             recharge_type = rec.recharge_type,
             assign_date = TRUNC (SYSDATE),
             lang_code = rec.lang_code
       WHERE code = v_promo;

      COMMIT;
   END LOOP;
   
   UPDATE tmp.emp
      SET emp_txt = TO_CHAR (SYSDATE, 'DD-MON-YYYY')
    WHERE emp_id_h = 'LOT' 
     AND emp_id = 'LAST_DATE' 
     AND emp_id_t = 'D';

   COMMIT;

   CLOSE c1;
EXCEPTION
   WHEN OTHERS
   THEN
      UPDATE tmp.emp
         SET emp_txt = TO_CHAR (SYSDATE, 'DD-MON-YYYY')
       WHERE emp_id_h = 'LOT' 
        AND emp_id = 'LAST_DATE' 
        AND emp_id_t = 'D';
      COMMIT;

      CLOSE c1;
END pr_delight;


can any one please look into this and let me know the way to improve the performance of this procedure.

Thank you,
Re: Procedure -Performance Issue.. [message #404340 is a reply to message #404336] Thu, 21 May 2009 07:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
For a quite a while now,
We have been yelling at each other NOT to commit inside the loop.
If you are using a Function Index, did you enable the required parameters and collect stats?
I do not understand why you are calling this inside the loop and what rownum =1 signifies here.
 SELECT code
        INTO v_promo
        FROM tmp.red_coat
       WHERE SUBSTR (code, 1, 2) = TO_CHAR (SYSDATE, 'MM')
         AND code_year = TO_CHAR (SYSDATE, 'YYYY')
         AND nvl(sdn,0) =0
         AND ROWNUM = 1
Re: Procedure -Performance Issue.. [message #404344 is a reply to message #404336] Thu, 21 May 2009 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
And the result of that select is the where clause for the update.
Either the rownum is unecessary or you're updating random rows.

Actually you're presumably updating the same row(s) in red_coat in every iteration of the loop.
Re: Procedure -Performance Issue.. [message #404345 is a reply to message #404336] Thu, 21 May 2009 07:52 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
In fact I don't think you have a performance problem.
I think you have a big fat BUG - I very much doubt this code does what you think it does.
Re: Procedure -Performance Issue.. [message #404347 is a reply to message #404345] Thu, 21 May 2009 08:06 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
cookiemonster wrote on Thu, 21 May 2009 13:52
In fact I don't think you have a performance problem.
I think you have a big fat BUG - I very much doubt this code does what you think it does.

+1

EXCEPTION
   WHEN OTHERS
   THEN
      UPDATE tmp.emp
         SET emp_txt = TO_CHAR (SYSDATE, 'DD-MON-YYYY')
       WHERE emp_id_h = 'LOT' 
        AND emp_id = 'LAST_DATE' 
        AND emp_id_t = 'D';

He [i]does have a performance problem, but it's not in his code...His code is the end result of his performance problem
Re: Procedure -Performance Issue.. [message #404414 is a reply to message #404347] Thu, 21 May 2009 13:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pablolee wrote on Thu, 21 May 2009 15:06

He [i]does have a performance problem, but it's not in his code...His code is the end result of his performance problem

Thanks.
This was a really big laugh!
Re: Procedure -Performance Issue.. [message #404474 is a reply to message #404347] Thu, 21 May 2009 22:17 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

I do accept it... That's why I told please correct my code...
Please correct it..

Thank you
Re: Procedure -Performance Issue.. [message #404492 is a reply to message #404344] Fri, 22 May 2009 00:23 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

can you please help me to write a single Update statement.

Thank you
Re: Procedure -Performance Issue.. [message #404493 is a reply to message #404336] Fri, 22 May 2009 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Procedure -Performance Issue.. [message #404513 is a reply to message #404474] Fri, 22 May 2009 01:16 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
user71408 wrote on Fri, 22 May 2009 04:17
Hi,

I do accept it... That's why I told please correct my code...
Please correct it..

Thank you

and, as usual, you miss the point.
Re: Procedure -Performance Issue.. [message #404548 is a reply to message #404513] Fri, 22 May 2009 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This thread has been closed for further discussion as per OP's request. I can't make it "invisible" (unless it is deleted).
Quote:
Reported By: user71408 On: Fri, 22 May 2009 10:01 In: SQL & PL/SQL » SQL & PL/SQL » Procedure -Performance Issue..
Reason: Please close this thread and make not to visible to any one. Thank you
Re: Procedure -Performance Issue.. [message #404554 is a reply to message #404548] Fri, 22 May 2009 04:39 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The OP has made several requests like this about threads they have started.

I'm quite curious about why they are so keen to stop the discussions.
Previous Topic: Getting max value without using group by (merged 3)
Next Topic: suggest me to which one is best way to write query and why? (merged 4)
Goto Forum:
  


Current Time: Wed Dec 07 18:30:34 CST 2016

Total time taken to generate the page: 0.15271 seconds