Re: Some questions on truncating a partition in Oracle

On 21 Jul 2003 05:46:20 -0700, Johnny Yeung <johnny.yeung_at_unforgettable.com> wrote:

> Hi experts,
>
> I have some questions on Oracle partition and truncate. I would be
> grateful of any of your experience here.
>
> 1. Truncate - Concept
> Here, I truncate a partition. It takes around 10 seconds. Is it
> fast or slow? Is it tunable? For your information, there are 2
> partitions: one partition is empty (0 records), while the other
> partition has a total of 120,000 records. When I truncate the empty
> partition, the statement returns immediately. On the other hand, when
> I truncate the other partition, it takes around 10 seconds.
> The problem is that I don't know whether the 10 sec is a good
> enough figure or not. For Oracle, what are the steps that will be
> performed in a "TRUNCATE" statement? How to measure the performance
> of each sub-steps? Or, if it is really too slow, any way I can
> improve the situation?

Truncate is a DDL statement, so it involves hacking around in the data dictionary tables. The segment header block is also adjusted to indicate that the High Water Mark is back at the very first extent.

The second function there is minimal. Unless your segment has acquired hundreds of thousands of extents, there's only one or two blocks containing the extent map, so the I/O is trivial.

The first function is where is gets interesting. I'll lay odds you are using Dictionary Managed Tablespaces, and that means each extent of the segment is recorded as a row in UET$ in the data dictionary. A truncate therefore means 'delete all these rows from UET$'. If you've only a few hundred extents, then that will be quite quick. But if you've thousands of rows, then it obviously takes a lot longer to do the deleting (which also requires the generation of undo/rollback and redo). If there is a lot of activity of this type going on simultaneously, then UET$ (of which there is only one) can be a source of contention, and hence you end up waiting to do the deletes in the first place.

So, possible 'tuning' of a truncate steps are: migrate to Locally Managed Tablespaces or (if you really have to stick with DMTs) re-create the entire table using larger extents. Then there should be fewer extents, and the delete from UET$ should be quicker. By using LMTs, however, you also eliminate the possible source of contention for UET$... provided you don't store everything in the one tablespace, there will be many LMT bitmaps to be modified simultaneously to record the removal of extents.

>
>
> 2. Select a partition while truncating another partition of the same
> table
> While the statement "ALTER TABLE TEST_PART TRUNCATE PARTITION
> part_a" is being issued, I issue a "select" statement concurrently in
> another session. In this case, will the select be blocked until the
> truncate operation end? If so, any ideas how to prevent such blocking
> from happening? Here, I know that my result must not be inside the
> partition which is undergoing the truncate statement.
>

I can't remember, to be honest. Ordinarily, a piece of DDL requires exclusive locks on a table to work, which means there would be blocking... but partition DDL is a different beast entirely, and I strongly suspect that it was designed so that the exclusive locking is done at the partition level, not the table level... which means you'd be alright. The only way to be sure is to test it, as ever! On the other hand, DDL is supposed to be lightening quick, so even if there was blocking, it should last no more than a second or two, tops.

~QM Received on Mon Jul 21 2003 - 15:16:13 CDT