Re: How to tune this stored proc?

From: <d_a_p_at_my-deja.com>
Date: Wed, 13 Oct 1999 11:36:58 GMT
Message-ID: <7u1qsp$gpu$1@nnrp1.deja.com>

In article <3803F9B4.87E46333_at_pacific.net.hk>,   leemingf_at_pacific.net.hk wrote:
> no, it's not indexed!

Definitely a candidate for indexing then! What is the cardinality of the tran_date field?

> > > PROCEDURE SP_PurgeTxn (p_date IN DATE)
> > > IS
> > > BEGIN
> > > LOOP
> > > DELETE FROM detail_txn dt
> > > WHERE tran_date = p_date
> > > AND rownum <= 4000;
> > > IF SQL%NOTFOUND THEN
> > > EXIT;
> > > ELSE
> > > COMMIT;
> > > END IF;
> > > END LOOP;
> > > COMMIT;
> > > END;
I'm curious - why the check for rownum<=4000? You only want to delete the first 4000 matching rows?

Also, you may improve performance by not committing after every row like you are currently doing. Use a cursor loop and COMMIT periodically, say, every 1000 rows. You may have to tweak either the commit increment or your rollback segments if you blow up the rollback. At any rate, this should help performance. Ex:

PROCEDURE SP_PurgeTxn (p_date IN DATE)
IS

    cursor c1(pdate) is

      select *
      from detail_txn
      where tran_date = p_date
      for update;

    iloop number := 0;
BEGIN
    for r1 in c1(p_date) loop

      delete from detail_txn
        where current of c1;
      iloop := iloop + 1;
      if mod(iloop,1000) = 0 then
        /* Commit every 1000 records */
        commit;
      end if;

    end loop;
    commit; /* Commit last records */
end SP_PurgeTxn;

-Dave Pulaski
 DBD/DBA Consultant

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 13 1999 - 06:36:58 CDT