Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to tune this stored proc?

Re: How to tune this stored proc?

From: <michael_bialik_at_my-deja.com>
Date: Wed, 13 Oct 1999 21:28:32 GMT
Message-ID: <7u2thf$c5o$1@nnrp1.deja.com>


Hi.

 Create an index on that field ASAP!

 You are deleting 4000 record each time and it means  that you are making a FULL scan of 4.5M records  145 ( 580 / 4 ) times.
 Assuming homogeneous dispersion of your data you  are reading 145 * 4.5M / 2 rows!!!

 Increase the number of records from 4000 to 10000 at least.  I think you set it to 4000 because of long execution time, but  after index creation it will decrease.  Even better - pass it as parameter to your procedure.

 Another possibility is to create an index before the stored proc is  executed and drop it afterward.

 HTH. Michael.

In article <3803F9B4.87E46333_at_pacific.net.hk>,   leemingf_at_pacific.net.hk wrote:
> no, it's not indexed!
>
> Sybrand Bakker wrote:
>
> > Is trandate indexed? You are purging about 10 percent of the table.
This
> > would urge for forcing a full table scan to be used in the delete.
> >
> > Hth
> > --
> > Sybrand Bakker, Oracle DBA
> > Lee Ming Fai <leemingf_at_pacific.net.hk> wrote in message
> > news:3803846C.AE7D0296_at_pacific.net.hk...
> > > We have a table which contain about 4.5M records and need to
purge about
> > > 580000 records each day by transaction date.
> > > The problem is that we found it takes too long, more than 4 hours
to
> > > delete these records. The following is the stored proc we use, is
there
> > > any better method or how to tune this stored proc?
> > >
> > >
> > > 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;
> > >
> > >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 13 1999 - 16:28:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US