Re: Poor perfmance after large delete

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!
>
> 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)

I think your problem is related to "highwatermak" of a table. Highwatermark is kept in each table header and it marks the last block data was ever written to. It is used mainly for SQL*Loader direct load (as a starting point of load) and when you perform full scan on the table. When executing full table scans, Oracle reads blocks up to the highwatermark, regardless of the number of the records in a table and regardless of space ocupied by tables extents. The highwatermark is reset only when you TRUNCATE or drop and recreate the table! Moreover, even if you dealocate space from table in Oracle 7.3 with "ALTER TABLE .... DEALlOCATE UNUSED ..." it will dealocate only space down to the highwatermark, even if there is unused space below it.

Typical problem with highwatermarks is that when you perform large deletes on a table (the highwatermark will not be reset) and then execute "SELECT * FROM THAT_TABLE" (full table scann) the query will take unexpectedly long to execute!

Mechanism of highwatermark and problems related to it are described in "Advanced Oracle tuning and administration" by K. Aronof, M. Lonely and N. Sonawalla (Oracle Press, Mc Graw Hill).

However, this still does not explain why query on deleted table took much longer than querry on pre-deleted table. In any case, in your position I would still TRUNCATE (there must be a way, regardless of constraints!) or recreate the table. If you don't, it can hit you later if you do direct load (SQL*Loader) to that table!

Hope this helps a bit.

Regards,

-- 
 ===============================================================
 ! Jurij Modic                            Republic of Slovenia !
 !  tel: +386 61 178 55 14                Ministry of Finance  !
 !  fax: +386 61  21 45 84                Zupanciceva 3        !
 !  e-mail: jurij.modic_at_mf.sigov.mail.si  Ljubljana 1000       !
 ===============================================================