Dropping Very Large Table In Oracle

朱雀 2021-04-24 02:16 575阅读 0赞

这是一张550G的大表，表上还包括了CLOB和BLOB对象；我们来观察下Oracle drop这样一个大表时的具体表现:

SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for Linux: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    
    SQL> show parameter recyclebin
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    recyclebin                           string      OFF
    
    /* 为了避免被flashback table骚扰，关闭了recyclebin回收站功能 */
    
    SQL> conn maclean/maclean
    Connected.
    
    SQL> col segment_name for a20
    SQL> select segment_name,bytes/1024/1024/1024 "size in Gbytes"  from user_segments where segment_name='TV';
    
    SEGMENT_NAME         size in Gbytes
    -------------------- --------------
    TV                           547.25
    
    SQL> select NUM_ROWS,BLOCKS from user_tables where table_name='TV';
    
      NUM_ROWS     BLOCKS
    ---------- ----------
     859150100   65649786
    
    SQL> desc tv;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     OWNER                                              VARCHAR2(30)
     OBJECT_NAME                                        VARCHAR2(128)
     SUBOBJECT_NAME                                     VARCHAR2(30)
     OBJECT_ID                                          NUMBER
     DATA_OBJECT_ID                                     NUMBER
     OBJECT_TYPE                                        VARCHAR2(19)
     CREATED                                            DATE
     LAST_DDL_TIME                                      DATE
     TIMESTAMP                                          VARCHAR2(19)
     STATUS                                             VARCHAR2(7)
     TEMPORARY                                          VARCHAR2(1)
     GENERATED                                          VARCHAR2(1)
     SECONDARY                                          VARCHAR2(1)
     SPARE1                                             CLOB
     SPARE2                                             CLOB
     SPARE3                                             CLOB
     SPARE4                                             BLOB
    
    /* 该大表包含CLOB、BLOB2种大对象，共859150100行数据，占用65649786个块
        其所在是一个大文件表空间(bigfile tablespace)，本地区间管理方式，区间大小统一为128MB
    */
    
    SQL> col tablespace_name for a2
    SQL> 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 a30
    SQL> col owner for a10
    SQL> 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                        82
    MACLEAN    SYS_IL0000057409C00014$$       LOBINDEX                  32850
    MACLEAN    SYS_IL0000057409C00015$$       LOBINDEX                  65618
    MACLEAN    SYS_IL0000057409C00016$$       LOBINDEX                  98386
    MACLEAN    SYS_IL0000057409C00017$$       LOBINDEX                 131154
    MACLEAN    SYS_LOB0000057409C00014$$      LOBSEGMENT                16466
    MACLEAN    SYS_LOB0000057409C00015$$      LOBSEGMENT                49234
    MACLEAN    SYS_LOB0000057409C00016$$      LOBSEGMENT                82002
    MACLEAN    SYS_LOB0000057409C00017$$      LOBSEGMENT               114770
    
    9 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 tv
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.12       0.29         28          9      30163           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.12       0.29         28          9      30163           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 64
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      db file sequential read                        28        0.00          0.00
      rdbms ipc reply                                10        0.01          0.06
      reliable message                                8        0.00          0.00
      enq: RO - fast object reuse                     8        0.00          0.00
      write complete waits                            9        0.04          0.10
      log file sync                                   1        0.00          0.00
      SQL*Net message to client                       1        0.00          0.00
      SQL*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