Newsgroups: comp.databases.oracle.server
Subject: Re: Poor perfmance after large delete
References: <5jftum$igu$1@ocoee.iac.net>
mwagoner@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@medplus.com (work)
>mwagoner@iac.net (life)
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: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page