"Translated from MoS article" How effective drop or truncate have a large number of extents table?

How effective drop or truncate have a large number of extents table?

From:
How to efficiently Drop (or Truncate) A Table with many extents (document ID 68836.1)

Objective:
This article describes why a user process consumes a lot of CPU resources after the drop of a table containing a large number of extents. And give a potential workaround to prevent this problem from happening. Essentially, the CPU is used to manage the extents (manipulate), such as moving used extents (uet$) to the free extents (fet$). In some cases, it may is possible to Regulate this CPU activity.

Scope of application:
This article assists the DBA in processing tables that drop many extents.

Related articles:
note:61997.1 smon-temporary Segment Cleanup and free Space coalescing

Cleanup of permanent objects

If a persistent object (table) is made up of many extents, and the object is drop,drop the user process of that object will consume a lot of CPU resources,
This is the unavoidable fact (an inescapable fact). But, based on some foresight (with some forethought),
can mitigate (mitigate) CPU usage (and hence the knock-on effect on other users of system resources)

1. Identify, but does not drop the table
2. Use the REUSE storage clause to truncate the table. This will be quick, because the associated extents will not be released. The high watermark (Highwater mark) is simply adjusted to the segment header block

3. Use the Keep clause to release unused extents in the table. This is the key to this step-you can control how many extents are freed by specifying how many extent in the table are not released

Example:
O. Table Bigtab is 2Gb in size and consists of 262144 8Kb extents
O. There is little CPU power available, and (from past experience) It's known that dropping a object of this number of E Xtents can take days
O. The system is quiet at night times (no other users or batch jobs)
In the example above, table can be divided into several stages that are drop in several nights

1. Truncate the table, specifying the Reuse STORAGE clause:

      sql> TRUNCATE TABLE bigtab reuse STORAGE;

2. If It takes 3 days (hours) to drop the table, spread the
6 nights i.e. drop 1/3 Gb per night. This can is achieved in 6 (nightly)
Steps as follows:
Note: If the table only needed truncating, no drop statement are needed here.

Night 1:         sql> ALTER TABLE bigtab deallocate UNUSED KEEP 1707M; (2GB*5/6)      Night 2:         sql> ALTER TABLE bigtab deallocate UNUSED KEEP 1365M; (2GB*4/6)      Night 3:         sql> ALTER TABLE bigtab deallocate UNUSED KEEP 1024M; (2GB*3/6)      Night 4:         sql> ALTER TABLE bigtab deallocate UNUSED KEEP 683M; (2GB*2/6)      Night 5:         sql> ALTER TABLE bigtab deallocate UNUSED KEEP 341M; (2GB*1/6)      Night 6:         sql> DROP TABLE bigtab;

The same approach also applies to LOB segments and index segment

        sql> ALTER TABLE  MODIFY LOB ()             deallocate UNUSED KEEP M;         sql> ALTER INDEX  deallocate UNUSED KEEP M;

Warning (caveats):
O. If you accidentally use the drop command, this method does not apply. This is because the drop table will first convert segment to a temporary segment,and only then start cleaning up the now temporary segment ' s extents. Thus, if the drop is interrupted, the temporary segment would now being cleaned up by Smon.
O.this method would only work for table, LOB and index segment types.
O.this method won't work for segments bigger than 4GB in size due to unpublished bug:
1190939--ORA-3277 when issuing a ALTER TABLE deallocate UNUSED > 4G (fixed in 10g and higher)

"Translated from MoS article" How effective drop or truncate have a large number of extents table?