Re: Poor perfmance after large delete

(Mark Wagoner's article follows my comments - Ari) What has happened is that Oracle set the highwater mark for your table at the largest size it has been (1.4 million rows). Even selecting count(*) from the table will take a while even after you deleted from the table.

The truly best method to improve performance is to TRUNCATE the table. This will reset the highwater mark. You will have to disable and/or drop all constraints that reference the table. It is insufficient to disable the constraints on the table. To find these constraints, try:

select * from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name = 'TABLE_NAME'); Then disable the constraints on the tables that are reported by the above statement. Truncate should work at that point.

Using the REBUILD option (7.3 only) for the indexes was a good idea as well.

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

<-> For 50+ technical tips, visit my Web Page:                    <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->

mwagoner_at_iac.net (Mark Wagoner) writes:

>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!
>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.
>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.
>--
>Mark Wagoner
>mwagoner_at_medplus.com (work)
>mwagoner_at_iac.net     (life)