Analysis of TRUNCATE TABLE principle

As we all know, TRUNCATE TABLE is a way to quickly empty the data in the table. Different from delete, truncate only produces very few redo and undo, which realizes the function of clearing the table data and reducing the HWM of the table. This paper mainly focuses on the implementation principle of TRUNCATE TABLE and the recovery of TRUNCATE TABLE.

Firstly, the test environment is constructed, and the whole operation process of truncate is analyzed through 10046 and redo dump. 10046 is used to observe the operation of truncate on the dictionary base table; Redo dump is used to observe truncate's operation on segment header and L1 and L2 bitmap blocks.

  • OS: redhat 6.5
  • db: 11.2.0.4
  • Based on assm
segment&extent info:
SYS@:>select owner,segment_name,header_file,header_block from dba_segments where segment_name='TRUNCATE_TABLE' and owner='TEST';
 
OWNER                SEGMENT_NAME         HEADER_FILE HEADER_BLOCK
-------------------- -------------------- ----------- ------------
TEST                 TRUNCATE_TABLE                 5         1898
 
SYS@:>select extent_id,file_id,block_id,blocks from dba_extents where segment_name='TRUNCATE_TABLE' and owner='TEST' order by 1;
 
EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          5       1896          8
         1          5      12104          8
         2          5      12112          8
         3          5      12120          8
         4          5      12128          8
         5          5      12136          8
         6          5      12144          8
         7          5      12152          8
         8          5      11904          8
         9          5      11912          8
        10          5      11920          8
        11          5      11928          8
        12          5      11936          8
        13          5      11944          8
        14          5      11952          8
        15          5      11960          8
        16          5      16256        128
        17          5      16384        128
        18          5      16512        128
        19          5      16768        128
        20          5      22528        128
        21          5      22656        128
        22          5      22784        128
        23          5      22912        128
        24          5      23040        128
        25          5      23168        128
        26          5      23296        128
        27          5      23424        128

Observe the truncate operation through 10046 and redo dump:

SYS@TEST(test):1>select count(*) from test.truncate_table;
 
  COUNT(*)
----------
    113426
 
SYS@:>alter system flush SHARED_POOL;
 
System altered.
 
SYS@:>alter system flush BUFFER_CACHE;
 
System altered.
 
SYS@:>alter system switch logfile;
 
System altered.
 
SYS@:>select * from v$log;
 
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME         NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
         1          1         85   52428800        512          1 NO  CURRENT                4116465 21-APR-18            2.8147E+14
         2          1         83   52428800        512          1 NO  INACTIVE               4092314 20-APR-18               4116301 21-APR-18
         3          1         84   52428800        512          1 NO  INACTIVE               4116301 21-APR-18               4116465 21-APR-18
 
SYS@:>oradebug setmypid;
Statement processed.
SYS@:>oradebug tracefile_name
/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_7091.trc
SYS@:>oradebug event 10046 trace name context forever,level 12;
Statement processed.
SYS@:>truncate table test.truncate_table;
Table truncated.
SYS@:>oradebug event 10046 trace name context off;
Statement processed.
SYS@TEST(test):1>alter system dump logfile '/u01/app/oracle/oradata/test/redo01.log';
System altered.

Find the dml operation on the base table from 10046 trace:

update: 
[root@prim1-11g ~]# grep -i "^update"  /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_7091.trc
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35 where obj#=:1
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
delete: 
[root@prim1-11g ~]# grep -i "^delete"  /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_7091.trc      
delete from superobj$ where subobj# = :1
delete from tab_stats$ where obj#=:1

The main modifications to the base table are:

  • Modify the data obj of obj and tab#
  • Modify the corresponding information of seg $(extensions, blocks, hwmincr, etc.)
  • Delete tab_stats $statistics of the corresponding object

The operations of segment header and L1 and L2 bitmap blocks can only be observed through redo dump, because only the changes of data blocks will be recorded in logmine, while for the operations of segment header and L1 and L2 bitmap blocks, only the operation type of internal or unsupported will be recorded in logmine, which has no valuable information.

Through the analysis of redo dump, it is found that the truncate operation only modifies the segment header, L2 bitmap block, the first L1 bitmap block and the L1 bitmap block of HWM block.

For segment header:

  • Modify the dataobj of the block#
  • Modify LHWM and HHWM
  • Modify the number of extent map s, aux map s, and extents

For L2 bitmap blocks:

  • Delete L1 ranges
  • Modify dataobj of L2 block#

For the first L1 bitmap block:

  • Modify the dataobj of the first L1 block#
  • set hwm is the 3rd + 1st block with ext# 0 (i.e. segment header block + 1)

For the L1 bitmap block to which the HWM block belongs:

  • clear HWM flag

The essence of truncate is to empty the table by modifying the data_object_id, hwm, extension map, aux map and other information of the segment header without modifying the data block. It also involves the modification of the base table of the data dictionary and L1 and L2 bitmap blocks. Therefore, the truncate operation only stores the data block without generating any redo and undo, but the segment header and bitmap Block, data dictionary and base table will still generate redo and undo.

About the author Li Xiangyu, delivery technical consultant of Yunhe enmo West District, has long served customers in the mobile operator industry, and is familiar with Oracle Performance Optimization, fault diagnosis and special recovery.

Posted by lunarul on Sun, 28 Nov 2021 20:28:33 -0800

Hot Keywords

©2024 Programmer Group