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: Poor perfmance after large delete

Re: Poor perfmance after large delete

From: Winston Douglas <wdouglas_at_cott.com>
Date: 1997/04/25
Message-ID: <01bc519f$6bde1e20$0e3210ac@winston.cott.com>#1/1

You could also try the "alter table deallocate extent" statement to free unused extents.

g.r.s. deisz <g.r.s.deisz_at_ptt-telecom.unisource.nl> wrote in article <5jq6ja$9ta_at_hdxl16.telecom.ptt.nl>...
>
> In article <5jftum$igu$1_at_ocoee.iac.net>,
> mwagoner_at_iac.net (Mark Wagoner) wrote:
> >We had been testing our database setup with sample data and decided to
 try
> >and go live. The main table had 1.4 million rows, which I deleted by
 doing
> >a series of DELETE FROM WHERE statements (I tried to truncate the table,
> >but Oracle said there were constraints even after I disabled them all,
 but
> >that is another problem). After about 2 hours the main tables were
 empty
> >so I went in and did a SELECT COUNT(*) to make sure. It took almost 3
> >minutes for the result to come back! It took less than 2 seconds when
 the
> >table was full!
>
> A delete doesn't reset the high water mark, so a full table scan will
 still
> read the same number of blocks, even while you delete all rows. Try a
 truncate
> on your empty table.
> Maybe you had an index on your table, allowing Oracle to do a full index
 scan
> instead of a full table scan. This can explain the difference of 3 mins
 to 2
> secs.
>
> >I have rebuilt the primary key and the unique keys using ALTER INDEX ...
> >REBUILD. I have also coalesced the tablespace to make sure any extra
> >extents were freed. I even executed the analyze_schema procedure to
 update
> >the dictionary. Nothing seems to help.
>
> Coalescing only coalescs existing adjacent free extents, it does not free
 any
> empty extents occupied by the table. Truncate will free these. (Except
 the
> initial extent ofcourse.)
>
> >Anybody know what is going on? I called Oracle but have yet to hear
 back
> >from them (it has been 3 days now). I am afraid to start loading
> >production data if the performance is going to be this bad.
> >
> >Version is Workgroup Server 7.3.2.2.1 on NT 4.0 w/service pack 1
 installed.
> >Any help would really be appreciated.
>
> As a last resort, you can try an export/import with the compress=y
 option.
>
> Regards, Stefan.
>
>
> ---------------------------------------------------------------------
> Name :G.R.S. Deisz
> Phone :+31-50-5855954
> E mail :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
> DISCLAIMER:This statement is not an official statement from, nor
> does it represent an official position of, PTT Telecom BV.
>
Received on Fri Apr 25 1997 - 00:00:00 CDT

Original text of this message

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