Home » RDBMS Server » Performance Tuning » procedure tuning (oracle 10g)
procedure tuning [message #350208] Wed, 24 September 2008 06:55 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

I have a procedure which is taking more time. Could you suggest a way to tune it.
CREATE OR REPLACE PROCEDURE prc_mine IS

     appidNUMBER(10);
     appname VARCHAR2(250);
     v_id NUMBER(10);
     v_new NUMBER(10);
     v_new_name VARCHAR2(250);
     v_old DATE;
     v_dt DATE;
     cnt NUMBER;

     CURSOR c_state_compare IS
     SELECT current_status.app_id,
            current_status.app_name,
            updateable.app_state_id old_state_id,
            current_status.app_state_id new_state_id,
            current_status.app_state_name new_state_name,
            updateable.app_state_begin begin_date,
            current_status.app_state_dt state_date
     FROM (SELECT h.app_id, h.app_state_id, h.app_state_begin
           FROM (SELECT app_id, MAX(app_state_begin) app_state_begin
                 FROM gt_temp h
                 WHERE h.app_state_end IS NULL
                 GROUP BY app_id) 
	   open_status, gt_temp h
           WHERE h.app_id = open_status.app_id
             AND h.app_state_begin = open_status.app_state_begin) 
     updateable,
          (SELECT a.app_id, a.app_name, sc.app_state_id, sn.app_state_name, sc.app_state_dt
           FROM t_app_state_current sc,
                t_application a,
                t_application_state sn
           WHERE a.app_id = sc.app_id
             AND sc.app_state_id = sn.app_state_id) 
     current_status
     WHERE updateable.app_id = current_status.app_id;

BEGIN

     INSERT INTO gt_temp
     SELECT *
     FROM table_mine;

     cnt := 0;
     OPEN c_state_compare;
     LOOP
          FETCH c_state_compare INTO v_app_id,
                                     appname,
                                     v_id,
                                     v_new,
                                     v_new_name,
                                     v_old,
                                     v_dt;

          EXIT WHEN c_state_compare%NOTFOUND;

          IF v_id != v_new  and v_old != v_dt THEN
               UPDATE gt_temp
               SET app_state_end = v_dt
               WHERE app_id = v_app_id
                 AND app_state_begin = v_old
                 AND app_state_id = v_id;

               INSERT INTO gt_temp
               (app_id, app_name, app_state_id, app_state_name, app_state_begin, app_state_end)
               VALUES
               (v_app_id, appname, v_new, v_new_name, v_dt, NULL);

               cnt := cnt + 1;
          END IF;

     END LOOP;
     CLOSE c_state_compare;

     EXECUTE IMMEDIATE
     'TRUNCATE TABLE table_mine';

     INSERT INTO table_mine
     SELECT *
     FROM gt_temp;

     COMMIT;

     dbms_output.put_line(cnt || ' RECORDS UPDATED');
END prc_mine;

any inputs are appreciated.

Regards,
Pointers.
Re: procedure tuning [message #350218 is a reply to message #350208] Wed, 24 September 2008 07:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If your procedure is processing a lot of rows, then it is falling into a classic trap of row-by-row PL/SQL programs. The solution is described in this article.

Ross Leishman

[Updated on: Wed, 24 September 2008 07:45]

Report message to a moderator

Re: procedure tuning [message #350282 is a reply to message #350208] Wed, 24 September 2008 10:26 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
rleishman right,
at first look, you can use merge statement to to the same job, but faster.

Re: procedure tuning [message #350295 is a reply to message #350282] Wed, 24 September 2008 11:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's a bit trickier than that- they're creating a series of records with contiguous start and end dates. Each time they go through the loop they update one recrod with an end date, and create a new record with the same date as a start date.

I think the solution is to change the cursor and add a LAG/LEAD on APP_STATE_DATE, and then simply insert a single record with both start and end date.

Once you've made that change, converting the whole thing to a singel SQL statement should be relatively easy.
Previous Topic: Query performance
Next Topic: Stroring Hierarchical Data(XML) into relational Table
Goto Forum:
  


Current Time: Sun Dec 04 10:47:26 CST 2016

Total time taken to generate the page: 0.06910 seconds