tuning stored procedure - help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: tuning stored procedure - help

  1. #1
    Join Date
    Jul 2006
    Posts
    96

    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

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    you ask for suggestions already, you need to post details of your problem 1st.
    ---------------

  3. #3
    Join Date
    Jul 2006
    Posts
    96

    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;

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use profiler to know which line of code is running longer.

    See the link:

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

  5. #5
    Join Date
    Feb 2005
    Posts
    158
    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]

  6. #6
    Join Date
    Sep 2005
    Posts
    278
    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.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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?

  8. #8
    Join Date
    Jul 2006
    Posts
    96

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width