Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Poor perfmance after large delete
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