tuning stored procedure

tuning stored procedure - help

hi, my stored procedure is taking aournd 15 min. for
execution some times.(even to process just thousands of records)

how to tune it to reduce the time, any suggestion.

thanks

you ask for suggestions already, you need to post details of your problem 1st.

stored procedure details to tune

this is my stored procedure.
if i execute it using E.G
exec vam_proc1('AC00007','01-APR-06','28-FEB-07')
it takes so much to display or hangs.
what changes should i make or any techniques to tune it??

CREATE OR REPLACE PROCEDURE VAM_PROC1
(tPARTY IN varchar,BILL_DT1 DATE, BILL_DATE2 DATE) AS
begin
DELETE FROM LED_TEMP WHERE (DRCODE=TPARTY OR CRCODE=TPARTY);
DELETE FROM LEDGER_TEMP WHERE PARTY=TPARTY;
COMMIT;
INSERT INTO LED_TEMP
SELECT *FROM TRAN WHERE VDATE BETWEEN '01-APR-06' AND BILL_DATE2 AND
(DRCODE=TPARTY OR CRCODE=TPARTY );
DELETE FROM LED_TEMP WHERE VCHTYPE='JV' AND ADJ_FLAG IS NOT NULL;
COMMIT;
DECLARE
CURSOR C1 IS
SELECT PARTY,VCHNO,VCHTYPE,BILL_NO,BILL_DT,NARRATION,DRAMT,CRAMT,ADJ_FLAG FROM VAM_LEDGER
WHERE PARTY=TPARTY AND BILL_DT BETWEEN '01-APR-06' AND BILL_DATE2 Order by bill_dt;
C2 C1%ROWTYPE;
BAL_AMT1 NUMBER(16,2):=0;
STAT1 VARCHAR2(2);
op_bal1 number(10,2);
pname varchar2(50);
stat number:=0;
SN NUMBER:=1;
BCOUNT NUMBER:=0;
crdr varchar2(2);
tdramt number:=0;
tcramt number:=0;
tsno number:=0;
tbalamt number:=0;
BILL_DATE1 DATE:='01-APR-06';
stat2 varchar2(2);
BEGIN
select opbal,STAT into op_bal1,CRDR from acc_mast where ac_code=TPARTY;
IF OP_BAL1>0 THEN
IF CRDR='CR' then
op_bal1:=0-op_bal1;
select name into pname from acc_mast where ac_code=tparty;
insert into ledger_temp (sno,party,NAME,bill_dt,narration,cramt,BAL_AMT,STAT)
values (sn,TPARTY,pname,BILL_DATE1,'/ OPENING BALANCE -----> ',abs(OP_BAL1),abs(OP_BAL1),'CR');
elsif crdr='DR' then
select name into pname from acc_mast where ac_code=tparty;
insert into ledger_temp (sno,party,NAME,bill_dt,narration,dramt,bal_amt,STAT)
values (sn,TPARTY,pname,bill_date1,'/ OPENING BALANCE -----> ',ABS(OP_BAL1),abs(OP_BAL1),'DR');
end if;
sn:=2;
end if;
select name into pname from acc_mast where ac_code=tparty;
FOR C2 IN C1 LOOP
IF C2.VCHTYPE='BD' or c2.vchtype='CR' then
if SUBSTR(C2.BILL_NO,1,5)='CRBAL' then
if stat=0 then
BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0)-nvl(OP_BAL1,0);
stat:=1;
else
BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0);
end if;
IF BAL_AMT1<0 THEN
STAT1:='DR';
elsif bal_amt1>0 then
STAT1:='CR';
else
STAT1:=NULL;
end if;
insert into ledger_temp (SNO,PARTY,NAME,BILL_NO,BILL_DT,NARRATION,DRAMT,CRAMT,BAL_AMT,STAT)
VALUES (SN,C2.PARTY,PNAME,C2.BILL_NO,C2.BILL_DT,C2.NARRATION,C2.DRAMT,C2.CRAMT,ABS(BAL_AMT1),STAT1);
ELSE
SELECT COUNT(*) INTO BCOUNT FROM VAM_LEDGER WHERE PARTY=TPARTY AND VCHTYPE IN ('BA','CA') AND VCHNO=c2.vchno;
if bcount=0 then
if stat=0 then
BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0)-nvl(OP_BAL1,0);
stat:=1;
else
BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0);
end if;
IF BAL_AMT1<0 THEN
STAT1:='DR';
elsif bal_amt1>0 then
STAT1:='CR';
else
STAT1:=NULL;
end if;
insert into ledger_temp (SNO,PARTY,NAME,BILL_NO,BILL_DT,NARRATION,DRAMT,CRAMT,BAL_AMT,STAT)
VALUES (SN,C2.PARTY,PNAME,C2.BILL_NO,C2.BILL_DT,C2.NARRATION,C2.DRAMT,C2.CRAMT,ABS(BAL_AMT1),STAT1);
elsif bcount>0 then
null;
end if;
END IF;
else
if stat=0 then
BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0)-nvl(OP_BAL1,0);
stat:=1;
else
BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0);
end if;
IF BAL_AMT1<0 THEN
STAT1:='DR';
elsif bal_amt1>0 then
STAT1:='CR';
else
STAT1:=NULL;
end if;
insert into ledger_temp (SNO,PARTY,NAME,BILL_NO,BILL_DT,NARRATION,DRAMT,CRAMT,BAL_AMT,STAT)
VALUES (SN,C2.PARTY,PNAME,C2.BILL_NO,C2.BILL_DT,C2.NARRATION,C2.DRAMT,C2.CRAMT,ABS(BAL_AMT1),STAT1);
end if;
sn:=sn+1;
BCOUNT:=0;
END LOOP;
COMMIT;
if BILL_DT1>'01-APR-06' then
tdramt:=0;
tcramt:=0;
STAT2:=NULL;
DELETE FROM LEDGER_TEMP WHERE PARTY=TPARTY AND BILL_DT SELECT MIN(SNO) INTO TSNO FROM LEDGER_TEMP WHERE PARTY=TPARTY;
select bal_amt,dramt,cramt,stat into tbalamt,tdramt,tcramt,stat2 from ledger_temp where sno=tsno and party=tparty;
TSNO:=TSNO-1;
if nvl(tdramt,0)>0 then
tbalamt:=tbalamt-tdramt;
elsif nvl(tcramt,0)>0 then
tbalamt:=tbalamt+tcramt;
end if;
if stat2='DR' then
insert into ledger_temp (sno,party,NAME,bill_dt,narration,dramt,bal_amt,STAT)
values (tsno,TPARTY,pname,bill_dt1,'/ OPENING BALANCE -----> ',ABS(TBALAMT),ABS(TBALAMT),STAT2);
ELSIF stat2='CR' then
insert into ledger_temp (sno,party,NAME,bill_dt,narration,cramt,bal_amt,STAT)
values (tsno,TPARTY,pname,bill_dt1,'/ OPENING BALANCE -----> ',ABS(TBALAMT),ABS(TBALAMT),STAT2);
ELSIF STAT2=NULL THEN
NULL;
end if;
end if;
END;
COMMIT;
END VAM_PROC1;

Use profiler to know which line of code is running longer.

See the link:

http://www.oracleact.com/papers/plsql_profiler.html

If I was a betting man, I'd say the culprit is
SELECT COUNT(*)
INTO bcount
FROM vam_ledger
WHERE party = tparty
AND vchtype IN('BA', 'CA')
AND vchno = c2.vchno;

Since you are only interested in whether there are zero rows or not, do a simple
SELECT count(*) INTO bcount
FROM DUAL WHERE EXISTS (SELECT 1 FROM vam_ledger
WHERE party = tparty
AND vchtype IN('BA', 'CA')
AND vchno = c2.vchno);
[There are other ways of writing that, but this has the smallest impact]

You can replace the following code with Single Query:

Code:

select opbal,STAT into op_bal1,CRDR from acc_mast where ac_code=TPARTY IF OP_BAL1>0 THEN IF CRDR='CR' then op_bal1:=0-op_bal1; select name into pname from acc_mast where ac_code=tparty; insert into ledger_temp (sno,party,NAME,bill_dt,narration,cramt,BAL_AMT,STAT) values (sn,TPARTY,pname,BILL_DATE1,'/ OPENING BALANCE -----> ',abs(OP_BAL1),abs(OP_BAL1),'CR'); elsif crdr='DR' then select name into pname from acc_mast where ac_code=tparty; insert into ledger_temp (sno,party,NAME,bill_dt,narration,dramt,bal_amt,STAT) values (sn,TPARTY,pname,bill_date1,'/ OPENING BALANCE -----> ',ABS(OP_BAL1),abs(OP_BAL1),'DR'); end if; sn:=2; end if;

something like below

Code:

insert into ledger_temp (sno,party,NAME,bill_dt,narration,cramt,BAL_AMT,STAT) select name, opbal, STAT from acc_mast where ac_code=TPARTY

Need more information to help furthur, table structures and the requirement. As the procedure is too long to digest.

exec vam_proc1('AC00007','01-APR-06','28-FEB-07')

You should not pass date as varchar2 data type. I think in 9i/8i, the procedure simply hangs because of mismatch data type.

What's your oracle release?

stored procedure tuning plz

hi in the above stored procedure i found that
AT folllwing statement it is hanging.
can tell why it hangs here?
should i replace this query?

SELECT COUNT(*) INTO BCOUNT from vam_ledger WHERE party = tparty and vchtype in('BA','CA') AND
VCHNO = C2.VCHNO