How To Efficiently Drop (or Truncate) A Table With Many Extents (文件 ID 68836.1)

 How to efficiently drop a table with many extents

PURPOSE
~~~~~~~

    This note describes why a user process can consume large amounts of CPU 
    after dropping a table consisting of many extents, and a potential
    workaround to stop the problem occurring. Essentially the CPU is being
    used to manipulate the extents i.e. moving used extents (uet$) to free
    extents (fet$). In certain circumstances it may be possible to regulate
    this CPU activity.



SCOPE & APPLICATION
~~~~~~~~~~~~~~~~~~~
This article is intended to assist DBAs who may need to drop a table
consisting of many extents.

RELATED DOCUMENTS
~~~~~~~~~~~~~~~~~
Note:61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing

Permanent object cleanup
~~~~~~~~~~~~~~~~~~~~~~~~

   If a permanent object (table) is made up of many extents, and the object is 
   to be dropped, the user process dropping the object will consume large 
   amounts of CPU - this is an inescapable fact. However, with some forethought
   it is possible to mitigate the effects of CPU usage (and hence the knock-on
   effect on other users of system resources) thus:

   1. Identify, but do NOT drop the table

   2. Truncate the table, specifying the REUSE STORAGE clause. This will be 
      quick as extents are not deallocated; the highwater mark is simply 
      adjusted to the segment header block.

   3. Deallocate unused extents from the table, SPECIFYING THE KEEP CLAUSE.
      This is the crux - you can control how many extents are to be deallocated
      by specifying how much (in terms of Kb or Mb) of the table is NOT
      to be deallocated.

   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 is
      known that dropping an object of this number of extents can take days
   o. The system is quiet at night times (no other users or batch jobs)
   

   In the above example the table could be dropped in 'phases' over the period
   of a few nights as follows:

   1. Truncate the table, specifying the REUSE STORAGE clause:
      SQL> TRUNCATE TABLE BIGTAB REUSE STORAGE;

   2. If it takes 3 days (72 hours) to drop the table, spread this out over
      6 nights i.e. drop 1/3 Gb per night. This can be achieved in 6 (nightly)
      steps as follows:
      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;

  NOTE:
  If the table only needed truncating, no drop statement is needed here. 


   The same method can be applied if LOB segments or indexes are involved.

        SQL> ALTER TABLE  MODIFY LOB ()
             DEALLOCATE UNUSED KEEP M;
 
        SQL> ALTER INDEX  DEALLOCATE UNUSED KEEP M;
 

Caveats
~~~~~~~

   o. If you have inadvertently tried to drop the table, this method will
      not work. This is because the drop will first convert the 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 will now be cleaned up by SMON.
   
   o. This method will only work for table, lob and index segment types.

   o. This method will not work for segments bigger than 4gb in size due to
      unpublished bug:
      1190939 -- ORA-3277 WHEN ISSUING AN ALTER TABLE DEALLOCATE UNUSED > 4G (fixed in 10g and higher)


-------------------------------------------------------------------------------
                                                        Oracle Support Services

REFERENCES

怎麼有效的drop 或者truncate 有大量extents的table？

來源於：
How To Efficiently Drop (or Truncate) A Table With Many Extents (文件 ID 68836.1)

目的：
本文描述了為什麼一個使用者程式在drop 了一個含有大量extents的table之後消耗大量的cpu資源。並給出一個潛在的workaround以阻止該問題的發生。本質來說，CPU被用於對extents的管理（manipulate）,比如moving used extents（uet$）到 free extents（fet$）.在某種情況中，it may be possible to regulate this CPU activity.

適用範圍：
本文協助DBA處理 drop 掉很多extents的表。

相關的文章：
Note:61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing

永久物件的清理

如果一個永久物件（table）是由 很多extents組成的，並且該object被drop，drop 該object的使用者程式將會消耗很多的CPU資源，
這是不可避免的事實（an inescapable fact）。但是，基於一些遠見卓識（with some forethought），
可以減輕（mitigate）CPU的使用(and hence the knock-on effect on other users of system resources)

1. Identify, but do NOT drop the table
2.使用 REUSE STORAGE子句來truncate 該表。這樣會很快，因為相關的extents不會被釋放。高水位線（highwater mark）被簡單的調整到segment header block

3. 使用KEEP子句來釋放該table中未使用的extents。這是本步驟的關鍵所在--通過指定table中有多少extent不會被釋放，你可以控制有多少extents被釋放

舉例：
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 is known that dropping an object of this number of extents can take days
o. The system is quiet at night times (no other users or batch jobs)
在上面的例子中，table可以分為幾個階段在幾個晚上被drop

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


SQL> TRUNCATE TABLE BIGTAB REUSE STORAGE;


2. If it takes 3 days (72 hours) to drop the table, spread this out over 6 nights i.e. drop 1/3 Gb per night. This can be achieved in 6 (nightly) steps as follows:


注意：If the table only needed truncating, no drop statement is 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;
相同的方法也適用於LOB segments和index segment


SQL> ALTER TABLE  MODIFY LOB ()
     DEALLOCATE UNUSED KEEP M;
 
SQL> ALTER INDEX  DEALLOCATE UNUSED KEEP M;

警告（caveats）:
o.如果你無意之間使用了drop 命令，本方法就不適用了。這是因為，drop table 會首先convert segment到一個臨時segment，and only then start cleaning up the now temporary segment's extents.Thus, if the drop is interrupted, the temporary segment will now be cleaned up by SMON.
o.This method will only work for table, lob and index segment types.
o.This method will not work for segments bigger than 4gb in size due to unpublished bug:
1190939 -- ORA-3277 WHEN ISSUING AN ALTER TABLE DEALLOCATE UNUSED > 4G (fixed in 10g and higher)

Truncate Slow in 11.2.0.3 and Higher (文件 ID 1667223.1)

This issue occurs on 11.2.0.3 and higher, and its caused by a regression introduced by the fix for un-published Bug 11856377, which was 
included in the 11.2.0.3 patchset.

Another symptom of this bug is the much more frequent access to dc_segments.