Re: How to tune this stored proc?

<d_a_p_at_my-deja.com> wrote in message news:7u1qsp$gpu$1_at_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?

No. As a consultant, you should know that the indexes are not helpful here. In fact, indexes hurt performance when executing bulk INSERT, DELETE, and UPDATE the indexed columns.

As what Sybrand said, to force a full table scan to do bulk deleting is better.
In some sites, they drop all indexes before executing bulk INSERT/DELETE, and then rebuind the indexes. Doing this may reduce hours into minutes.

> > > > 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?

No. Lee's procedure deletes 4000 rows in one time, and iterates until no row founds.

> 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

Candidly, your program is worse than Lee's one.

Since you execute one SQL for each row, and there is one potential "ORA-01555 snapshot too old" issue when executing your program.

You can benchmark two programs, and then you will find your program take twice or more time than Lee's one. Received on Thu Oct 14 1999 - 11:51:24 CDT