ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How To Efficiently Drop A Table With Many Extents

How To Efficiently Drop A Table With Many Extents

原创 Linux操作系统 作者:paulyibinyi 时间:2009-01-14 22:51:15 0 删除 编辑

 

 

                                           How To Efficiently Drop A Table With Many Extents

 
文档 ID:
68836.1
类型:
BULLETIN
 
上次修订日期:
12-JUN-2008
状态:
PUBLISHED


               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;

   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 4G

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

以下是自己测试过程:

C:\Documents and Settings\yibin>sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on 星期三 1月 14 21:43:37 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn sys/abcdefg@test as sysdba
已连接。

SQL> create table YIBIN_truncate as select * from dba_objects;

表已创建。

SQL> insert into YIBIN_truncate select * from YIBIN_truncate;

已创建6342行。

SQL> /

已创建12684行。

SQL> /

已创建25368行。

SQL> /

已创建50736行。


SQL>
SQL> insert into YIBIN_truncate select * from YIBIN_truncate;

已创建3247104行。

SQL> commit;

提交完成。

SQL>
SQL> insert into YIBIN_truncate select * from YIBIN_truncate;

已创建6494208行。

SQL> commit;

提交完成。

SQL>
SQL> insert into YIBIN_truncate select * from YIBIN_truncate;

已创建12988416行。

SQL> commit;

提交完成。

SQL> select bytes/1024/1024 from dba_segments where segment_name='YIBIN_TRUNCATE'
;

BYTES/1024/1024
---------------
           2503

SQL> select extents,blocks from dba_segments where segment_name='YIBIN_TRUNCATE';


   EXTENTS     BLOCKS
---------- ----------
       223     320384

SQL> select bytes/1024/1024 from dba_segments where segment_name='YIBIN_TRUNCATE'
;

BYTES/1024/1024
---------------
           2503

SQL> truncate table YIBIN_truncate reuse storage;

表已截掉。

SQL> select extents,blocks from dba_segments where segment_name='YIBIN_TRUNCATE';


   EXTENTS     BLOCKS
---------- ----------
       223     320384

SQL> select bytes/1024/1024 from dba_segments where segment_name='YIBIN_TRUNCATE'
;

BYTES/1024/1024
---------------
           2503

SQL> alter table YIBIN_truncate deallocate unused keep 1000m;

表已更改。

SQL> select extents,blocks from dba_segments where segment_name='YIBIN_TRUNCATE';


   EXTENTS     BLOCKS
---------- ----------
       197     128008

SQL> alter table YIBIN_truncate deallocate unused keep 1000m;

表已更改。

SQL> alter table YIBIN_truncate deallocate unused keep 0m;

表已更改。

SQL> select extents,blocks from dba_segments where segment_name='YIBIN_TRUNCATE';


   EXTENTS     BLOCKS
---------- ----------
         1          8

SQL> select bytes/1024/1024 from dba_segments where segment_name='YIBIN_TRUNCATE'
;

BYTES/1024/1024
---------------
          .0625

通过测试
在truncate大表 时使用reuse storage,暂时不释放extent ,再使用unused keep 分批释放空间,这样能减少truncate时对系

统资源的争用。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-539649/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
学习数据库

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6566963