tuning the procedures - taking much time

hi , plz suggest how to tune this procedure.

i observed that at the statement it hangs. can u tell why?
should i replace this query?

' SELECT COUNT(*) INTO BCOUNT FROM VAM_LEDGER WHERE PARTY=TPARTY AND VCHTYPE IN ('BA','CA') AND VCHNO=c2.vchno'



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 bill_dt1 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 bill_dt1 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_DT1,'/ 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_dt1,'/ 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;
/

Some statistical info would help...
Number of rows per table?
Indexes?
Are stats up-to-date?
Why do you say it hangs in that specific query?
Have you looked at explain plans?

hi, here vam_ledger is a view with total no of rows above one lack.
the underlying table is led_temp.

using Toad tool i observed in the option viewing locks.
the statement

'select count(*) into btotal from vam_ledger ....................
...........

is shown continuesly for long time after running the procedure.

if u get any idea now plz give solution.

Doing row-by-row processing when you can do it in a single SQL is not a good idea to start with. The reason why your SQL shows up as the longest running query is because it executes against a large table as many times as you have rows in the same table (it is inside the for loop created on the same table)! Trying to optimize it is no different from chasing the dog's tail.

The real problem is lack of SQL (and Pl/SQL) programming knowledge on the developer side. The entire for loop routine can be implemented as a single statement. There are only two things going on inside the loop: 1) if/elsif/else and 2) some math is implemented using about a dozen of unnecessary intermediate variables.

Here is an example of what I am talking about:
This:
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_DT1,'/ 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_dt1,'/ OPENING BALANCE -----> ',ABS(OP_BAL1),abs(OP_BAL1),'DR');
end if;

... can be replaced in this:
insert into ledger_temp (sno,party,NAME,bill_dt,narration,cramt,BAL_AMT,STAT)
select sn,TPARTY,name,BILL_DT1,'/ OPENING BALANCE -----> ',abs(OP_BAL1),abs(OP_BAL1),CRDR
from acc_mast where ac_code=tparty;

... by only eliminating one unnecessary variable pname, taking crdr variable into the list as oppose to using it in the IF/ELSIF and combining select with insert statements.

Send the developer to SQL 101 class or hire a better one.

PS: believe it or not but abs(0-op_bal1) = op_bal1 !!! )