How To Efficiently Drop A Table With Many Extents
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
DEALLOCATE UNUSED KEEP
SQL> ALTER INDEX
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
-------------------------------------------------------------------------------
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 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [doc]How To Efficiently Drop A Table With Many Extents
- How to drop Oracle RAC database manually?OracleDatabase
- drop table和truncate table的區別
- Oracle ASM How many allocation units per fileOracleASM
- flashback drop/query/table/database/archiveDatabaseHive
- oracle 誤刪表 drop tableOracle
- HDU 1213 How Many Tables(並查集)並查集
- drop apply INSTANTIATION for one tableAPP
- audit drop table為什麼不行
- Drop table cascade constraintsAI
- HDU 2157 How many ways?? (矩陣快速冪)矩陣
- 動態規劃 hdu 1978 How many ways動態規劃
- How to monitor data transaction on one table
- How to partition a non-partitioned table
- Oracle10g 回收站及徹底刪除table : drop table xx purge 以及drop flashOracle
- 深入解析 oracle drop table內部原理Oracle
- DROP TABLE ** CASCADE CONSTRAINTS PURGEAI
- One more way regarding germany niubian how to remain difficult forREMAI
- HDU 3038 How Many Answers Are Wrong (帶權並查集)並查集
- MySQL資料災難挽救之drop tableMySql
- Drop Table Fails With ORA-600 [15264]AI
- Get detailed table(many other objects) structure with dbms_metadataAIObjectStruct
- Leetcode 1365. How Many Numbers Are Smaller Than the Current Number (cpp)LeetCode
- MySQL 5.6 drop database時,table metadata lock等待MySqlDatabase
- How To Drop, Create And Recreate DB Control In A 10g DatabaseDatabase
- codeforces 9D How many trees? (組合二叉樹)二叉樹
- Truncate table 詳解及與delete,drop 的區別delete
- Truncate table詳解及與delete,drop的區別delete
- 資料庫審計(create/alter/drop table、user、tablespace)資料庫
- v$lock之alter table drop column與alter table set unused column區別系列五
- How to Add/Drop/Resize Redo Log with Physical Standby in place. [ID 473442.1]
- How many nodes can be had in an HP-UX/Solaris/AIX/Windows/Linux cluster?AIWindowsLinux
- Overview of Extents(11)View
- MSSQL---extentsSQL
- Extents in Indexes (19)Index
- How to Quickly Create a Copy of a Table using Transact-SQLUISQL
- How To Know If An Object (Table / Procedure / View /…) Is Currently BeingObjectView
- MySQL DROP TABLE刪除表報錯'ERROR 1051 (42S02): Unknown table'MySqlError