Home » SQL & PL/SQL » SQL & PL/SQL » Performance of the Procedure (10.2.0.3.0)
Performance of the Procedure [message #402497] Mon, 11 May 2009 02:38 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have written the following procedure . But it is taking more than 5 hrs. Can any one please look into this procedure and let me know the way 2 improve the performance of this.

Create or replace Procedure PROC1
v_code   Raise_code.code%TYPE;
   v_date         DATE;
 
   CURSOR cur1 (v_date DATE)
   IS
      SELECT DISTINCT ref_v.msn mscn, ref_v.recharge_type 
                      recharge_type,
                      ref_v.REFERENCE REFERENCE, 
                      icat.langcode lang_code
                 FROM tmp.refills_view ref_v, 
                      i_card_ale_tmp icat
                WHERE ref_v.msn = icat.cardnbr
                  AND ref_v.currency IN (
                               SELECT prm_id_d
                                 FROM tmp.prm
                                WHERE prm_id_h = 'CURRENCY'
                                      AND prm_txt = 'EUR')
                  AND ref_v.recharge_amount >= 10
                  AND ref_v.date_exec > v_date
                  AND ref_v.msn NOT IN (
                         SELECT gl_msn
                           FROM s_write.ppa_gmlist
                          WHERE ppa_gmlist.prom_idct =
                                   (SELECT prm_txt
                                      FROM tmp.prm
                                     WHERE prm_id_h 
                                                 = 'INVENTORY'
                                       AND prm_id_d 
                                             = 'INVENTORY_ID'))
                  AND icat.productprofil NOT IN 
                           ('BPL', 'SONATA');
 
   rec            cur1%ROWTYPE;
BEGIN
   v_code := NULL;
   v_date := NULL;
 
   SELECT TO_DATE (prm_txt, 'DD-MON-YYYY')
     INTO v_date
     FROM tmp.prm
    WHERE prm_id_h = 'INVENTORY' AND prm_id_d = 'LAST_DATE' AND prm_id_t = 'SD';
 
   OPEN cur1 (v_date);
 
   LOOP
      FETCH cur1
       INTO rec;
 
      EXIT WHEN cur1%NOTFOUND;
 
      SELECT code
        INTO v_code
        FROM tmp.Raise_code
       WHERE SUBSTR (code, 1, 2) = TO_CHAR (SYSDATE, 'MM')
         AND code_year = TO_CHAR (SYSDATE, 'YYYY')
         AND nvl(msn,0) =0
         AND ROWNUM = 1;
 
      UPDATE Raise_code
         SET msn = SUBSTR (rec.msn, 3),
             REFERENCE = rec.REFERENCE,
             recharge_type = rec.recharge_type,
             assign_date = TRUNC (SYSDATE),
             lang_code = rec.lang_code
       WHERE code = v_code;
 
      COMMIT;
   END LOOP;
   
   UPDATE tmp.prm
      SET prm_txt = TO_CHAR (SYSDATE, 'DD-MON-YYYY')
    WHERE prm_id_h = 'INVENTORY' AND prm_id_d = 'LAST_DATE' AND prm_id_t = 'SD';
 
   COMMIT;
 
   CLOSE cur1;
EXCEPTION
   WHEN OTHERS
   THEN
      UPDATE tmp.prm
         SET prm_txt = TO_CHAR (SYSDATE, 'DD-MON-YYYY')
       WHERE prm_id_h = 'INVENTORY' AND prm_id_d = 'LAST_DATE' AND prm_id_t = 'SD';
 
      COMMIT;
 
      CLOSE cur1;
END PROC1;

and I have created Index as
CREATE INDEX TMP.IDX_CODE_2 ON TMP.Raise_CODE
(CODE_YEAR, SUBSTR("CODE",1,2), NVL("MSN",'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;
 



Pleaase help me out in this.

Thank you
Re: Performance of the Procedure [message #402498 is a reply to message #402497] Mon, 11 May 2009 02:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Still not able to read the performance tuning guide and post the required information?
Re: Performance of the Procedure [message #402500 is a reply to message #402498] Mon, 11 May 2009 03:00 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Thomas,

The Cursor is returning more than 100000 records and let me know if more information required.

Thank you
Re: Performance of the Procedure [message #402501 is a reply to message #402500] Mon, 11 May 2009 03:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The first thing that is needed would be a session trace analysed with tkprof.

And of course, we would need to know what the procedure actually tries to accomplish.

[Updated on: Mon, 11 May 2009 03:07]

Report message to a moderator

Re: Performance of the Procedure [message #402502 is a reply to message #402497] Mon, 11 May 2009 03:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Congratulations - that's pretty much a textbook example of the worst possible way to write a piece of code.
You've got just about everything - commits inside a loop, row by row cursor processing, DML inside a loop, and repeated execution of the same piece of SQL.

The best thing to do would be to rewrite the whole thing as a single update statement.

The second best thing, (which will be slower than the above) is:
1) Get rid of the commit inside the loop.
2) Get the Select from Tmp.Raise_Code outside of the loop - nothing in it seems to depend on data from the cursor, so it'll return the same value every time you execute it.
3) Do a Bulk Collect from the cursor, and turn the Update in the loop into a Forall update
4) Tune the query in the cursor
Re: Performance of the Procedure [message #402503 is a reply to message #402502] Mon, 11 May 2009 03:12 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

I have written as follows. But I could find that much change in perfromance.

update raise_code
set msn = (cursor select with a correlated sub-query join)
where code = SELECT code
        INTO v_code
        FROM tmp.Raise_code
       WHERE SUBSTR (code, 1, 2) = TO_CHAR (SYSDATE, 'MM')
         AND code_year = TO_CHAR (SYSDATE, 'YYYY')
         AND nvl(msn,0) =0
         AND ROWNUM = 1;


Thank you

Re: Performance of the Procedure [message #402510 is a reply to message #402503] Mon, 11 May 2009 03:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You have somewhat misunderstood me.

The best approach would be to combine the SELECT statement from the cursor CUR1 and the update statement into a single piece of SQL.

If you are unable or unwilling to do that, then steps 1-4 that I outlined should still give you a significant improvement in performance.
Re: Performance of the Procedure [message #402512 is a reply to message #402510] Mon, 11 May 2009 04:26 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member


UPDATE Raise_code
      SET (msn, reference, recharge_type,assign_date,lang_code) = 
      (SELECT SUBSTR (msn, 3),
              reference,
              trunc(sysdate),
              lang_code
        FROM (cursor_query))
  WHERE code = v_code;



Please look into this...
Re: Performance of the Procedure [message #402514 is a reply to message #402501] Mon, 11 May 2009 04:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Please look into it yourself first, post the required information already and stop wasting everybodies time.

ThomasG wrote on Mon, 11 May 2009 10:04
The first thing that is needed would be a session trace analysed with tkprof.

And of course, we would need to know what the procedure actually tries to accomplish.

Re: Performance of the Procedure [message #402520 is a reply to message #402514] Mon, 11 May 2009 04:46 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

I am new to these tuning concepts. can you please guide me to find the Trace file and TKPROF of the procedure...

Thank you
Re: Performance of the Procedure [message #402523 is a reply to message #402520] Mon, 11 May 2009 04:55 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
See the blue underlined thing in my post?

That is something called a "hyperlink".

Which links to the documentation and a step-by-step guide on how to trace a session and run tkprof.

Previous Topic: regular expression with owa pattern
Next Topic: Filtering data from SQL Resultset output [merged]
Goto Forum:
  


Current Time: Fri Dec 09 21:35:26 CST 2016

Total time taken to generate the page: 0.06813 seconds