Dropping Very Large Table In Oracle
这是一张550G的大表，表上还包括了CLOB和BLOB对象；我们来观察下Oracle drop这样一个大表时的具体表现:
SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - ProductionSQL> show parameter recyclebinNAME TYPE VALUE------------------------------------ ----------- ------------------------------recyclebin string OFF/* 为了避免被flashback table骚扰，关闭了recyclebin回收站功能 */SQL> conn maclean/macleanConnected.SQL> col segment_name for a20SQL> select segment_name,bytes/1024/1024/1024 "size in Gbytes" from user_segments where segment_name='TV';SEGMENT_NAME size in Gbytes-------------------- --------------TV 547.25SQL> select NUM_ROWS,BLOCKS from user_tables where table_name='TV';NUM_ROWS BLOCKS---------- ----------859150100 65649786SQL> desc tv;Name Null? Type----------------------------------------- -------- ----------------------------OWNER VARCHAR2(30)OBJECT_NAME VARCHAR2(128)SUBOBJECT_NAME VARCHAR2(30)OBJECT_ID NUMBERDATA_OBJECT_ID NUMBEROBJECT_TYPE VARCHAR2(19)CREATED DATELAST_DDL_TIME DATETIMESTAMP VARCHAR2(19)STATUS VARCHAR2(7)TEMPORARY VARCHAR2(1)GENERATED VARCHAR2(1)SECONDARY VARCHAR2(1)SPARE1 CLOBSPARE2 CLOBSPARE3 CLOBSPARE4 BLOB/* 该大表包含CLOB、BLOB2种大对象，共859150100行数据，占用65649786个块其所在是一个大文件表空间(bigfile tablespace)，本地区间管理方式，区间大小统一为128MB*/SQL> col tablespace_name for a2SQL> select relative_fno,header_block,owner,tablespace_name from dba_segments where segment_name='TV';RELATIVE_FNO HEADER_BLOCK OWNER TA------------ ------------ ------------------------------ --1024 82 MACLEAN BF/* 因为是用bigfile tablespace技术，故数据段所在相对数据文件号为1024 */SQL> col segment_name for a30SQL> col owner for a10SQL> select owner,segment_name,segment_type,header_block from dba_segments where relative_fno=1024;OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_BLOCK---------- ------------------------------ ------------------ ------------MACLEAN TV TABLE 82MACLEAN SYS_IL0000057409C00014$$ LOBINDEX 32850MACLEAN SYS_IL0000057409C00015$$ LOBINDEX 65618MACLEAN SYS_IL0000057409C00016$$ LOBINDEX 98386MACLEAN SYS_IL0000057409C00017$$ LOBINDEX 131154MACLEAN SYS_LOB0000057409C00014$$ LOBSEGMENT 16466MACLEAN SYS_LOB0000057409C00015$$ LOBSEGMENT 49234MACLEAN SYS_LOB0000057409C00016$$ LOBSEGMENT 82002MACLEAN SYS_LOB0000057409C00017$$ LOBSEGMENT 1147709 rows selected./* 该bigfile数据文件1024上的数据段如上包括TV表本身以及LOB对象和LOB索引 */SQL> select current_scn from v$database;CURRENT_SCN-----------44989856/* 获取当前scn以便闪回数据库 */SQL> alter session set events '10046 trace name context forever,level 12';Session altered.SQL> set timing on;SQL> drop table tv;Table dropped.Elapsed: 00:00:01.21/* 虽然是550G的大表，但drop也仅耗时1.21s再次证明了drop仅仅是修改数据字典 */通过tkprof分析的trace文件信息:drop table tvcall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.12 0.29 28 9 30163 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.12 0.29 28 9 30163 0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 64Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited---------------------------------------- Waited ---------- ------------db file sequential read 28 0.00 0.00rdbms ipc reply 10 0.01 0.06reliable message 8 0.00 0.00enq: RO - fast object reuse 8 0.00 0.00write complete waits 9 0.04 0.10log file sync 1 0.00 0.00SQL*Net message to client 1 0.00 0.00SQL*Net message from client 1 7.22 7.22
以上可以看到少量的等待事件，drop要求所被drop对象先做object对象级别的checkpoint检查点，以便将脏块写出；故而会出现Write complete waits(A user wants to modify a block that is part of DBWR
￡神魔★判官ぃ
爱被打了一巴掌
古城微笑少年丶
骑猪看日落
傷城~
深藏阁楼爱情的钟
朱雀
还没有评论，来说两句吧...