Delete / Truncate takes forever

[sjwales]

I've got two tables in one of my databases each currently with 102,650 extents.

At one stage, they had a lot of rows in them, but now they have very few.

I want to reclaim this space drop exporting, dropping, recreating and importing them.

However, it's the truncate and delete that I'm concerned about.

I tried a truncate that ran for 90+ minutes before I killed it. I'm doing deletes on these tables now that have been running almost as long.

Anyone have any idea how long something like this should take?

I've got no messages in my alert log - anyone have any idea how long something like this might take? Am I just not being patient enough?

(HPUX 11i, Oracle 9.2.0.5)

Steve

 

[SantaMufasa]

Steve,

Having the number of extents that you have is not a problem (for SELECT, INSERT, or UPDATE) until you need to either DROP or TRUNCATE the table. Usually (when a table does not have an over abundance of extents), a TRUNCATE will be manifold faster than a DELETE. In this case (due to the proliferation of extents) both commands are molasses slow.

And in any case, a DELETE of all rows will always take longer than a TRUNCATE since 1) all activity is on a row-by-row basis instead of on an extent-by-extent basis and 2) for DELETE, Oracle must write every single byte of every single row into the transaction's rollback segment in case you decide to undo/rollback your decision to delete every row. (TRUNCATE does not write any rollback since it is considered a DDL statement and therefore is not reversible.)

The fact that you have so many extents also tells me that you are not using Oracle's incredibly fast and efficient Locally Managed Tablespaces (LMT). I highly recommend that you migrate everything from your dictionary-managed tablespaces (DMT) to LMT. At that point, you (as the DBA) no longer must worry about extent growth (either by size or by number) and if you every need to either TRUNCATE or DROP a table, it is remarkably fast, no matter what its size.

If you need/want instructions on how to migrate from DMT to LMT, then either post a request for information here, or open a new thread, "How can I migrate from DMT to LMT?".

As for a specific recommendation on how to empty out your table, I believe your fastest solution is to migrate your entire tablespace (or schema or just the table itself) to an LMT, then do a TRUNCATE TABLE. Strangely, (because of its lightening-fast speed and using the PARALLEL NOLOGGING feature) table migration from DMT to LMT (then TRUNCATE) will be your fastest solution (when using the "ALTER TABLE...MOVE" command that migrates your table to a new tablespace.)

Let us know your wishes,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:41 (29Jan05) UTC (aka "GMT" and "Zulu"),
@ 14:41 (29Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sjwales]

Dave,

Thanks for the reply.

Actually, you are right, we are using DMT. I'm in the process of doing a reorg for LMT with uniform extent sizes over several different tablespaces based upon size of the objects.

I ended up going to see a play this afternoon, after I posted the first message in the thread. By the time I got home, eventually the drops had completed.

I've been working on a test copy of my prod database - what I might do is clone the database again, and parallel run a drop against one table and a truncate against the other with timing turned on.

That way, I can get an idea of how much of an outage window I need to request to clean up the space used by these two tables.

Then, I can finally try doing the moves from the system managed extent tablespaces to the uniform extent tablespaces. My initial testing for that had the "MOVE"s failing, which may have been owing to the size of the table in unallocated extents. It seemed to have hung, but maybe it hadn't.

In any event, once I've done with that, I may very well post a followup here just for informational purposes.

Steve

 

[SantaMufasa]

Steve,

Here is a script that migrates DMT objects to LMT: See thread186-888289, my post timestamped 15:33 (27Jul04) UTC (aka "GMT" and "Zulu").

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 02:35 (30Jan05) UTC (aka "GMT" and "Zulu"),
@ 19:35 (29Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.