Home » RDBMS Server » Performance Tuning » How to re-write th eprocedure...? (oracle 9.0.1)
How to re-write th eprocedure...? [message #310641] Tue, 01 April 2008 22:21 Go to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
How to re-write the procedure...?, becase it is taking long time to run the procedre...

Logic: taking two cursor, one by one, if data match with cursor then updating else inserting into table, same logic for both the cursor.

CREATE OR REPLACE PROCEDURE UPDATE_POPS
AS
CURSOR cur_trans_1 IS
select ent,
ctr_or_hier,
post_acct,
sum(avg_mtd_01) plan_01,
sum(avg_mtd_02) plan_02,
sum(avg_ytd_01) plan_03,
sum(avg_ytd_02) plan_04
from mon_act_cypy
where rec_type = 'AAA' and sum_flag = 'DDD' and yr = '2008' and
substr(ctr_or_hier, 1, 2) = 'll' and
ent || sub_ent in
(select ent || sub_ent
from ent_ref
where roll_ent || roll_sub_ent = '999999')
group by post_acct
having sum(avg_mtd_01) <> 0 or sum(avg_mtd_02) <> 0 or sum(avg_ytd_01) <> 0 or sum(avg_ytd_02) <> 0;

CURSOR cur_trans_2 IS
select b.plan_ent b_plan_ent,
b.plan_ctr b_plan_ctr,
a.post_acct a_post_acct,
sum(a.avg_mtd_01) plan_01,
sum(a.avg_mtd_02) plan_02,
sum(a.avg_ytd_01) plan_03,
sum(a.avg_ytd_02) plan_04
from mon_act_cypy a, plan_unit_tbl b
where a.ent || a.ctr_or_hier = b.ent || b.ctr_or_hier and
a.rec_type = 'AAA' and a.sum_flag = 'DDD' and a.yr = '2008' and
b.hier_tbl_num = '991' and
a.ent || a.sub_ent in
(select ent || sub_ent
from ent_ref
where roll_ent || roll_sub_ent = '999999')
group by b.plan_ent, b.plan_ctr, a.post_acct
having sum(a.avg_mtd_01) <> 0 or sum(a.avg_mtd_02) <> 0 or sum(a.avg_ytd_01) <> 0 or sum(a.avg_ytd_02) <> 0;

BEGIN
UPDATE MON_PLAN
SET PLAN_01 = 0, PLAN_02 = 0, PLAN_03 = 0, PLAN_04 = 0
WHERE plan_type = 'update_pops' AND yr = '2008';

FOR rec_trans_1 IN cur_trans_1 LOOP
UPDATE mon_plan
SET PLAN_01 = rec_trans_1.plan_01,
PLAN_02 = rec_trans_1.plan_02,
PLAN_03 = rec_trans_1.plan_03,
PLAN_04 = rec_trans_1.plan_04
WHERE EXISTS (Select 1
from mon_plan
where ent = rec_trans_1.ent AND
CTR_OR_HIER = rec_trans_1.ctr_or_hier AND
post_acct = rec_trans_1.post_acct);
IF SQL%NOTFOUND THEN
INSERT INTO mon_plan
VALUES
(rec_trans_1.ent,
rec_trans_1.post_acct,
rec_trans_1.ctr_or_hier,
rec_trans_1.plan_01,
rec_trans_1.plan_02,
rec_trans_1.plan_03,
rec_trans_1.plan_04);
END IF;
END LOOP;

FOR rec_trans_2 IN cur_trans_2 LOOP
UPDATE mon_plan
SET PLAN_01 = rec_trans_2.plan_01,
PLAN_02 = rec_trans_2.plan_02,
PLAN_03 = rec_trans_2.plan_03,
PLAN_04 = rec_trans_2.plan_04
WHERE EXISTS (Select 1
from mon_plan
WHERE ent = rec_trans_2.b_plan_ent AND
CTR_OR_HIER = rec_trans_2.b_plan_ctr AND
post_acct = rec_trans_2.a_post_acct);
IF SQL%NOTFOUND THEN
INSERT INTO mon_plan
VALUES
(rec_trans_2.b_plan_ent,
rec_trans_2.a_post_acct,
rec_trans_2.b_plan_ctr,
rec_trans_2.plan_01,
rec_trans_2.plan_02,
rec_trans_2.plan_03,
rec_trans_2.plan_04);
END IF;
END LOOP;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
error_logs(SYSDATE, SQLCODE, SQLERRM);
END;
Re: How to re-write th eprocedure...? [message #310642 is a reply to message #310641] Tue, 01 April 2008 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
FORMAT your code using <code tags> as explained in http://www.orafaq.com/forum/t/88153/0/

post results from following guidelines from URL below
http://www.orafaq.com/forum/t/84315/74940/

You might benefit from using MERGE command & avoiding PL/SQL.

[Updated on: Tue, 01 April 2008 22:32] by Moderator

Report message to a moderator

Re: How to re-write th eprocedure...? [message #310643 is a reply to message #310642] Tue, 01 April 2008 22:41 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
Please find the formated code for the above..
CREATE OR REPLACE PROCEDURE UPDATE_POPS AS
  CURSOR cur_trans_1 IS
    select ent,
           ctr_or_hier,
           post_acct,
           sum(avg_mtd_01) plan_01,
           sum(avg_mtd_02) plan_02,
           sum(avg_ytd_01) plan_03,
           sum(avg_ytd_02) plan_04
      from mon_act_cypy
     where rec_type = 'AAA' and sum_flag = 'DDD' and yr = '2008' and
           substr(ctr_or_hier, 1, 2) = 'll' and
           ent || sub_ent in
           (select ent || sub_ent
              from ent_ref
             where roll_ent || roll_sub_ent = '999999')
     group by post_acct
    having sum(avg_mtd_01) <> 0 or sum(avg_mtd_02) <> 0 or sum(avg_ytd_01) <> 0 or sum(avg_ytd_02) <> 0;

  CURSOR cur_trans_2 IS
    select b.plan_ent b_plan_ent,
           b.plan_ctr b_plan_ctr,
           a.post_acct a_post_acct,
           sum(a.avg_mtd_01) plan_01,
           sum(a.avg_mtd_02) plan_02,
           sum(a.avg_ytd_01) plan_03,
           sum(a.avg_ytd_02) plan_04
      from mon_act_cypy a, plan_unit_tbl b
     where a.ent || a.ctr_or_hier = b.ent || b.ctr_or_hier and
           a.rec_type = 'AAA' and a.sum_flag = 'DDD' and a.yr = '2008' and
           b.hier_tbl_num = '991' and
           a.ent || a.sub_ent in
           (select ent || sub_ent
              from ent_ref
             where roll_ent || roll_sub_ent = '999999')
     group by b.plan_ent, b.plan_ctr, a.post_acct
    having sum(a.avg_mtd_01) <> 0 or sum(a.avg_mtd_02) <> 0 or sum(a.avg_ytd_01) <> 0 or sum(a.avg_ytd_02) <> 0;

BEGIN
  UPDATE MON_PLAN
     SET PLAN_01 = 0, PLAN_02 = 0, PLAN_03 = 0, PLAN_04 = 0
   WHERE plan_type = 'update_pops' AND yr = '2008';

  FOR rec_trans_1 IN cur_trans_1 LOOP
    UPDATE mon_plan
       SET PLAN_01 = rec_trans_1.plan_01,
           PLAN_02 = rec_trans_1.plan_02,
           PLAN_03 = rec_trans_1.plan_03,
           PLAN_04 = rec_trans_1.plan_04
     WHERE EXISTS (Select 1
              from mon_plan
             where ent = rec_trans_1.ent AND
                   CTR_OR_HIER = rec_trans_1.ctr_or_hier AND
                   post_acct = rec_trans_1.post_acct);
    IF SQL%NOTFOUND THEN
      INSERT INTO mon_plan
      VALUES
        (rec_trans_1.ent,
         rec_trans_1.post_acct,
         rec_trans_1.ctr_or_hier,
         rec_trans_1.plan_01,
         rec_trans_1.plan_02,
         rec_trans_1.plan_03,
         rec_trans_1.plan_04);
    END IF;
  END LOOP;

  FOR rec_trans_2 IN cur_trans_2 LOOP
    UPDATE mon_plan
       SET PLAN_01 = rec_trans_2.plan_01,
           PLAN_02 = rec_trans_2.plan_02,
           PLAN_03 = rec_trans_2.plan_03,
           PLAN_04 = rec_trans_2.plan_04
     WHERE EXISTS (Select 1
              from mon_plan
             WHERE ent = rec_trans_2.b_plan_ent AND
                   CTR_OR_HIER = rec_trans_2.b_plan_ctr AND
                   post_acct = rec_trans_2.a_post_acct);
    IF SQL%NOTFOUND THEN
      INSERT INTO mon_plan
      VALUES
        (rec_trans_2.b_plan_ent,
         rec_trans_2.a_post_acct,
         rec_trans_2.b_plan_ctr,
         rec_trans_2.plan_01,
         rec_trans_2.plan_02,
         rec_trans_2.plan_03,
         rec_trans_2.plan_04);
    END IF;
  END LOOP;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    error_logs(SYSDATE, SQLCODE, SQLERRM);
END;
Re: How to re-write th eprocedure...? [message #310644 is a reply to message #310641] Tue, 01 April 2008 22:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gksenthilkumar,
Thanks for posting formatted code which is much, much easier to read.
Please proceed with the other suggestions & feedback.
Re: How to re-write th eprocedure...? [message #310647 is a reply to message #310644] Tue, 01 April 2008 23:19 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
I am sure, what u are expecting...?
Re: How to re-write th eprocedure...? [message #310650 is a reply to message #310641] Tue, 01 April 2008 23:28 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You have code & data and can test different ways to write SQL.
We have no way to test different coding possibilities.
In some/many/most cases SQL performance is data dependent.

Have you looked at SQL only solution using MERGE?

Have you generated, looked at, posted EXPLAIN_PLAN?
If you do not know where time is being spent, you have no idea where to look for improvements.

Previous Topic: optimzier RULE
Next Topic: Usage of Materialized view to improve performance
Goto Forum:
  


Current Time: Thu Nov 07 17:29:55 CST 2024