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 |
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 #310643 is a reply to message #310642] |
Tue, 01 April 2008 22:41 |
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 #310650 is a reply to message #310641] |
Tue, 01 April 2008 23:28 |
|
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.
|
|
|
Goto Forum:
Current Time: Thu Nov 07 17:29:55 CST 2024
|