sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle性能优化 >

【学习笔记】Oracle truncate表操作 前后位图块信息变化情况分析

时间:2016-10-22 22:26   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 分享一篇关于Oracle数据库truncate表操作时对数据库块的影响变化,通过案例分析。
下面是一个dump表truncate操作前后的位图块的信息,原因是同事在问,truncate表,到底在对象所在的块更改了那些内容

其实truncate表,主要是更改对象所在第一个区中的L1,L2,L3 3个块的值。对象的其它的块的信息是不会修改的

我们可以通过块的scn的值来判断

1,测试表创建与基本信息收集

SQL> create table scott.htz1 tablespace htz as select * from dba_objects where rownum<10000;

Table created.

SQL> @segment.sql

Enter value for owner: scott

Enter value for segment_name: htz1

Enter value for tablespace_name:

HEADER

OWNER:SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_BLOCK SIZE(M) BLOCKS EXTENTS

—————————— ——————– ————— ——————– ——————– ———- ———- ——-

SCOTT.HTZ1 TABLE HTZ 5.146 2 256 17

****************************** ———-

Total: 2

1 row selected.

SQL> @extent.sql

Enter value for owner: scott

Enter value for segment_name: htz1

Enter value for tablespace_name:

FILE BLOCK

OWNER:SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME ID FNO EXTENT_ID BEGIN_END BLOCKS BYTES(KB)

—————————— ——————– ————— ——————– —– —– ———- ————————- ——— ———

SCOTT.HTZ1 TABLE HTZ 5 5 0 144~151 8 64

TABLE HTZ 5 5 1 152~159 8 64

TABLE HTZ 5 5 2 160~167 8 64

TABLE HTZ 5 5 3 168~175 8 64

TABLE HTZ 5 5 4 176~183 8 64

TABLE HTZ 5 5 5 184~191 8 64

TABLE HTZ 5 5 6 192~199 8 64

TABLE HTZ 5 5 7 200~207 8 64

TABLE HTZ 5 5 8 208~215 8 64

TABLE HTZ 5 5 9 216~223 8 64

TABLE HTZ 5 5 10 224~231 8 64

TABLE HTZ 5 5 11 232~239 8 64

TABLE HTZ 5 5 12 240~247 8 64

TABLE HTZ 5 5 13 248~255 8 64

TABLE HTZ 5 5 14 256~263 8 64

TABLE HTZ 5 5 15 264~271 8 64

TABLE HTZ 5 5 16 384~511 128 1024

****************************** ——— ———

Total: 256 2048

SQL> alter system flush buffer_cache;

System altered.

这里可以看到htz1这个对象的块头在146这个块,一共占用17个区的信息

2,dump位图块与第一个数据块,第二个L1位图块

SQL> alter system dump datafile 5 block min 144 block max 147;

System altered.

SQL> alter system dump datafile 5 block 160;

System altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name;

/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_29424.trc

3,truncate表

SQL> truncate table scott.htz1;

Table truncated.

SQL> alter system flush buffer_cache;

System altered.

SQL> @extent.sql

Enter value for owner: scott

Enter value for segment_name: htz1

Enter value for tablespace_name:

FILE BLOCK

OWNER:SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME ID FNO EXTENT_ID BEGIN_END BLOCKS BYTES(KB)

—————————— ——————– ————— ——————– —– —– ———- ————————- ——— ———

SCOTT.HTZ1 TABLE HTZ 5 5 0 144~151 8 64

****************************** ——— ———

Total: 8 64

这里可以看到只占用一个区的信息

4,再次dump位图块,第一个数据块,truncate前的第二个L1位图块

SQL> alter system dump datafile 5 block min 144 block max 147;

System altered.

SQL> alter system dump datafile 5 block 160;

System altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name;

/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_29455.trc

SQL> !cp /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_29455.trc /soft/test

5,L1 位图块

truncate前

buffer tsn: 6 rdba: 0x01400090 (5/144)

scn: 0x0000.000ec1e9 seq: 0x04 flg: 0x04 tail: 0xc1e92004

frmt: 0x02 chkval: 0x6f7d type: 0x20=FIRST LEVEL BITMAP BLOCK

Hex dump of block: st=0, typ_found=1

Dump of First Level Bitmap Block

——————————–

nbits : 4 nranges: 2 parent dba: 0x01400091 poffset: 0

unformatted: 0 total: 16 first useful block: 3

owning instance : 1

instance ownership changed at

Last successful Search

Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 16

Bitmap block lock opcode 2

Locker xid: : 0x0004.013.000002ca

Dealloc scn: 966333.0

Flag: 0x00000000 (-/-/-/-/-/-)

Inc #: 0 Objd: 87355

——————————————————–

DBA Ranges :

——————————————————–

0x01400090 Length: 8 Offset: 0

0x01400098 Length: 8 Offset: 8

0:Metadata 1:Metadata 2:Metadata 3:FULL

4:FULL 5:FULL 6:FULL 7:FULL

8:FULL 9:FULL 10:FULL 11:FULL

12:FULL 13:FULL 14:FULL 15:FULL

——————————————————–

truncate后

buffer tsn: 6 rdba: 0x01400090 (5/144)

scn: 0x0000.000ec24b seq: 0x02 flg: 0x04 tail: 0xc24b2002

frmt: 0x02 chkval: 0x03c7 type: 0x20=FIRST LEVEL BITMAP BLOCK

Hex dump of block: st=0, typ_found=1

Dump of First Level Bitmap Block

——————————–

nbits : 4 nranges: 1 parent dba: 0x01400091 poffset: 0

unformatted: 5 total: 8 first useful block: 3

owning instance : 1

instance ownership changed at

Last successful Search

Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 3

Bitmap block lock opcode 9

Locker xid: : 0x0005.01c.00000361

Dealloc scn: 967243.0

Flag: 0x00000021 (OBJD/-/-/-/-/HWM)

Inc #: 0 Objd: 87356

HWM Flag: HWM Set

Highwater:: 0x01400093 ext#: 0 blk#: 3 ext size: 8

#blocks in seg. hdr’s freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

——————————————————–

DBA Ranges :

——————————————————–

0x01400090 Length: 8 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:unformatted

4:unformatted 5:unformatted 6:unformatted 7:unformatted

——————————————————–

这里我们可以看到l1 bmb变化的很多信息,主要是块空间的使用情况

6,L2 BMB块

truncate前

buffer tsn: 6 rdba: 0x01400091 (5/145)

scn: 0x0000.000ec1e9 seq: 0x09 flg: 0x04 tail: 0xc1e92109

frmt: 0x02 chkval: 0xd396 type: 0x21=SECOND LEVEL BITMAP BLOCK

Hex dump of block: st=0, typ_found=1

Dump of Second Level Bitmap Block

number: 10 nfree: 2 ffree: 8 pdba: 0x01400092

Inc #: 0 Objd: 87355

opcode:0

xid:

L1 Ranges :

——————————————————–

0x01400090 Free: 1 Inst: 1

0x014000a0 Free: 1 Inst: 1

0x014000b0 Free: 1 Inst: 1

0x014000c0 Free: 1 Inst: 1

0x014000d0 Free: 1 Inst: 1

0x014000e0 Free: 1 Inst: 1

0x014000f0 Free: 1 Inst: 1

0x01400100 Free: 1 Inst: 1

0x01400180 Free: 5 Inst: 1

0x01400181 Free: 5 Inst: 1

——————————————————–

truncate 后

Dump of Second Level Bitmap Block

number: 1 nfree: 1 ffree: 0 pdba: 0x01400092

Inc #: 0 Objd: 87356

opcode:4

xid:

L1 Ranges :

——————————————————–

0x01400090 Free: 5 Inst: 1

——————————————————–

这里可以看到,truncate后,其实的l1 bmb全部已经释放了,只剩下一个L1 BMB信息

7,段头,L3 BMB的信息

truncate前

buffer tsn: 6 rdba: 0x01400092 (5/146)

scn: 0x0000.000ec1eb seq: 0x01 flg: 0x04 tail: 0xc1eb2301

frmt: 0x02 chkval: 0xff3d type: 0x23=PAGETABLE SEGMENT HEADER

Hex dump of block: st=0, typ_found=1

Extent Control Header

—————————————————————–

Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 256

last map 0x00000000 #maps: 0 offset: 2716

Highwater:: 0x0140018e ext#: 16 blk#: 14 ext size: 128

#blocks in seg. hdr’s freelists: 0

#blocks below: 142

mapblk 0x00000000 offset: 16

Unlocked

——————————————————–

Low HighWater Mark :

Highwater:: 0x0140018e ext#: 16 blk#: 14 ext size: 128

#blocks in seg. hdr’s freelists: 0

#blocks below: 142

mapblk 0x00000000 offset: 16

Level 1 BMB for High HWM block: 0x01400180

Level 1 BMB for Low HWM block: 0x01400180

——————————————————–

Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0

L2 Array start offset: 0x00001434

First Level 3 BMB: 0x00000000

L2 Hint for inserts: 0x01400091

Last Level 1 BMB: 0x01400181

Last Level II BMB: 0x01400091

Last Level III BMB: 0x00000000

Map Header:: next 0x00000000 #extents: 17 obj#: 87355 flag: 0x10000000

Inc # 0

Extent Map

—————————————————————–

0x01400090 length: 8

0x01400098 length: 8

0x014000a0 length: 8

0x014000a8 length: 8

0x014000b0 length: 8

0x014000b8 length: 8

0x014000c0 length: 8

0x014000c8 length: 8

0x014000d0 length: 8

0x014000d8 length: 8

0x014000e0 length: 8

0x014000e8 length: 8

0x014000f0 length: 8

0x014000f8 length: 8

0x01400100 length: 8

0x01400108 length: 8

0x01400180 length: 128

Auxillary Map

——————————————————–

Extent 0 : L1 dba: 0x01400090 Data dba: 0x01400093

Extent 1 : L1 dba: 0x01400090 Data dba: 0x01400098

Extent 2 : L1 dba: 0x014000a0 Data dba: 0x014000a1

Extent 3 : L1 dba: 0x014000a0 Data dba: 0x014000a8

Extent 4 : L1 dba: 0x014000b0 Data dba: 0x014000b1

Extent 5 : L1 dba: 0x014000b0 Data dba: 0x014000b8

Extent 6 : L1 dba: 0x014000c0 Data dba: 0x014000c1

Extent 7 : L1 dba: 0x014000c0 Data dba: 0x014000c8

Extent 8 : L1 dba: 0x014000d0 Data dba: 0x014000d1

Extent 9 : L1 dba: 0x014000d0 Data dba: 0x014000d8

Extent 10 : L1 dba: 0x014000e0 Data dba: 0x014000e1

Extent 11 : L1 dba: 0x014000e0 Data dba: 0x014000e8

Extent 12 : L1 dba: 0x014000f0 Data dba: 0x014000f1

Extent 13 : L1 dba: 0x014000f0 Data dba: 0x014000f8

Extent 14 : L1 dba: 0x01400100 Data dba: 0x01400101

Extent 15 : L1 dba: 0x01400100 Data dba: 0x01400108

Extent 16 : L1 dba: 0x01400180 Data dba: 0x01400182

——————————————————–

Second Level Bitmap block DBAs

——————————————————–

DBA 1: 0x01400091

truncate 后

buffer tsn: 6 rdba: 0x01400092 (5/146)

scn: 0x0000.000ec270 seq: 0x03 flg: 0x04 tail: 0xc2702303

frmt: 0x02 chkval: 0xfc29 type: 0x23=PAGETABLE SEGMENT HEADER

Hex dump of block: st=0, typ_found=1

Extent Control Header

—————————————————————–

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8

last map 0x00000000 #maps: 0 offset: 2716

Highwater:: 0x01400093 ext#: 0 blk#: 3 ext size: 8

#blocks in seg. hdr’s freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Disk Lock:: Locked by xid: 0x0001.01c.0000028d

——————————————————–

Low HighWater Mark :

Highwater:: 0x01400093 ext#: 0 blk#: 3 ext size: 8

#blocks in seg. hdr’s freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Level 1 BMB for High HWM block: 0x01400090

Level 1 BMB for Low HWM block: 0x01400090

——————————————————–

Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0

L2 Array start offset: 0x00001434

First Level 3 BMB: 0x00000000

L2 Hint for inserts: 0x01400091

Last Level 1 BMB: 0x01400090

Last Level II BMB: 0x01400091

Last Level III BMB: 0x00000000

Map Header:: next 0x00000000 #extents: 1 obj#: 87356 flag: 0x10000000

Inc # 0

Extent Map

—————————————————————–

0x01400090 length: 8

Auxillary Map

——————————————————–

Extent 0 : L1 dba: 0x01400090 Data dba: 0x01400093

——————————————————–

Second Level Bitmap block DBAs

——————————————————–

DBA 1: 0x01400091

这里主要提一个HHWM,LHWM的值都发生了变化,所以说truncate释放了空间

8,第一个数据块

truncate前

Block header dump: 0x01400093

Object id on Block Y

seg/obj: 0x1553b csc: 0x00.ec1c4 itc: 3 flg: E typ: 1 – DATA

brn: 0 bdba: 0x1400090 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.000ec1c4

0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.0000http://www.oracleplus.net0000

0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000

bdba: 0x01400093

data_block_dump,data header at 0x2a97ed627c

===============

tsiz: 0x1f80

hsiz: 0xc2

pbl: 0x2a97ed627c

76543210

flag=——–

ntab=1

nrow=88

frre=-1

fsbo=0xc2

fseo=0x432

avsp=0x370

tosp=0x370

truncate后

Block header dump: 0x01400093

Object id on Block Y

seg/obj: 0x1553b csc: 0x00.ec1c4 itc: 3 flg: E typ: 1 – DATA

brn: 0 bdba: 0x1400090 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.000ec1c4

0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000

bdba: 0x01400093

data_block_dump,data header at 0x2a97f6227c

===============

tsiz: 0x1f80

hsiz: 0xc2

pbl: 0x2a97f6227c

76543210

flag=——–

ntab=1

nrow=88

frre=-1

fsbo=0xc2

fseo=0x432

avsp=0x370

tosp=0x370

看到第1个数据块的scn值没有变化,说明truncate表并没有对数据进行修改。

本文固定链接: http://www.htz.pw/2014/04/22/truncate%e8%a1%a8%e6%93%8d%e4%bd%9c%ef%bc%8c%e5%89%8d%e5%90%8e%e4%bd%8d%e5%9b%be%e5%9d%97%e4%bf%a1%e6%81%af%e5%8f%98%e5%8c%96.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle truncate表操作 前后位图块信息变化情况分析

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1031.html

Oracle研究中心

关键词:

Oracle truncate表操作内部运行原理

Oracle truncate表时对数据的影响