转：Oracle中 HWM与数据库性能的探讨

2011-12-30 17:21 Tracy. 阅读(1024) 评论(1) 编辑收藏举报

本文讨论的是oracle中关于table的HWM的内容，主要包括这样几个内容：

1. 什么是HWM？

2. HWM是如何移动的。

3. HWM对于性能的影响

4. 何时应该降低以及如何降低HWM

5. 其他一些影响HWM的操作

除了特殊注明，本章内容所有的实验都基于：win2000,oracle9201,bolcksize 8K的实验环境。

一、什么是HWM

HWM（high water mark）,高水标记,这个概念在segment的存储内容中是比较重要的.简单来说,HWM就是一个segment中已使用和未使用的block的分界线.

在oracle的concept中对于HWM的说明是这样的：在一个segment中，HWM是使用和未使用空间的分界线。当请求新的空闲块，并且现有空闲列表中的块不能满足要求时，HWM指向的块将被标记为已使用，然后HWM将移动指向下一个未使用过的块。

我们知道,在oracle中,存储数据的最小单元是block,对于一个segment(table或index),都是由很多的block组成的,这些block的状态分为已使用和未使用两种,一般来说，在HWM之下的block都是存储过数据的. 如图:

从上面的图，我们就能很清楚的看到，一个segment中的block的分布情况。在HWM左边的block是已使用的，或者说是可以用来存储数据的。而HWM右边的block是不能用来存储数据的。当HWM左边的block空间都使用完之后，还有新的数据需要存储，怎样处理呢？这时oracle会向右移动HWM，即把右边的没有使用的block移到HWM的左边，这时HWM左边的block就增加了，那么就有新的block空间可供使用了。

Oracle从9i开始，推出了新的一种segment的空间管理方式，即ASSM（auto segment space management）。这种segment在空间管理上和以前的FLM（freelist management）是不一样的。这里我们简单地介绍一下。

在FLM模式下，对于一个segment的HWM下的所有block空间的使用，是通过freelist来管理的，freelist位于segment的第一个extent中。一个block何时应该位于freelist之上，取决于PCTUSED和PCTFREE这样两个参数。基于freelist管理模式和位于segment header的情况，如果对一个segment进行高并发的频繁的DML操作，不可避免的出现header争用的情况，虽然我们可以采用增加freelists或freelist group的方式来缓解这种状况。

那么从oracle92开始，推出了ASSM这样一种全新的segmeng空间管理的方式（又称为Bitmap Managed Segments）, freelist被位图所取代，使用位图来管理block的空间使用状况，并且这些位图块分散在segment中。ASSM管理的segment会略掉任何为PCTUSED、NEXT和FREELISTS所指定的值。

使用ASSM也有一定的局限性：

ASSM只能位于Local Manage的tablespace之上；

不能够使用ASSM创建临时的tablespace；

LOB对象不能在一个指定进行自动段空间管理的tablespace中创建。

以上我们简单地介绍了ASSM和FLM的概念和区别，接下来，我们来看看这两种segmeng空间管理模式在HWM的处理上有什么不同。

二、初始创建的table中HWM的不同情况

FLM管理的table：我们先创建名为HWM的tablespace，指定非自动段空间管理，extent大小为40K。并在上面创建table TEST_HWM，PCTFREE 40 PCTUSED 20。

SQL> connect dlinger/dlinger@oracle9i_dl

连接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> CREATE TABLESPACE HWM

2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\HWM.dbf'

3 SIZE 50M uniform size 40K;

表空间已创建。

SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,

2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT

3 from dba_tablespaces where TABLESPACE_NAME = 'HWM';

TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT

--------------- ---------- ----------------- --------------- ------------------------

HWM 8192 LOCAL UNIFORM MANUAL

SQL> alter user dlinger default tablespace hwm;

用户已更改。

SQL> CREATE TABLE TEST_HWM (ID CHAR(2000) , NAME CHAR(2000) )

2 STORAGE ( MINEXTENTS 2) PCTFREE 40 PCTUSED 20;

表已创建。

SQL>select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

from dba_extents

2 where segment_name='TEST_HWM' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 11 11 9 5

1 11 11 14 5

SQL> alter system dump datafile 11 block 9;

系统已更改。

Table TEST_HWM位于datafile 11 ，segment header为block9，我们dump出block9来看看：

*** 2004-06-09 20:31:26.000

*** SESSION ID:(9.5) 2004-06-09 20:31:26.000

Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

buffer tsn: 14 rdba: 0x02c00009 (11/9)

scn: 0x0000.013e974e seq: 0x01 flg: 0x00 tail: 0x974e1001

frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x02c0000a ext#: 0 blk#: 0 ext size: 4

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 2 obj#: 32377 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x02c0000a length: 4

0x02c0000e length: 5

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0

SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

从dump的trace文件，我们可以获得这样的信息：

我们可以看到Highwater:: 0x02c0000a，在FLM的segment下，初始创建的table，HWM是从第一个extent的第二个block开始的。为segment header 保留一个块。我们从这里也可以看出来：

Extent Map

-----------------------------------------------------------------

0x02c0000a length: 4

说明第一个extent可用的block为4。

我们这里看到的结果是在默认freelist 为1的条件下得到的。在FLM下，如果对segment设置了freelist groups N，则HWM指向第N＋2个block，当N+2 > initextent的block数时，会返回ORA-03237的错误信息，这里tablespace HWM的extent为40K，block_size为8K：

SQL> CREATE TABLE TEST_HWM2 (ID CHAR(2000) , NAME CHAR(2000) )

2 STORAGE ( MINEXTENTS 2 freelist groups 4) PCTFREE 40 PCTUSED 20;

CREATE TABLE TEST_HWM2 (ID NUMBER(10) , NAME CHAR(2000) )

ERROR 位于第 1 行:

ORA-03237: 在表空间 (HWM) 无法分配指定大小的初始区

在ASSM下，情况是怎样的呢？

我们创建名为ASSM的tablespace，指定自动段空间管理，extent大小为40K。并在上面创建table TEST_HWM1，注意，这里我们只指定了PCTFREE 40，因为PCTUSED在ASSM下的segment中是无效的。

SQL> CREATE TABLESPACE ASSM

2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\ASSM.dbf'

3 SIZE 50M uniform size 40K segment space management auto;

表空间已创建。

SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,

2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT

3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';

TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT

--------------- ---------- ----------------- --------------- ------------------------

ASSM 8192 LOCAL UNIFORM AUTO

SQL> CREATE TABLE TEST_HWM1 (ID CHAR(2000), NAME CHAR(2000) )

2 Tablespace ASSM

3 STORAGE ( MINEXTENTS 2) PCTFREE 40;

表已创建。

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents

3 where segment_name='TEST_HWM1' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 12 12 9 5

1 12 12 14 5

SQL> alter system dump datafile 12 block min 9 block max 11;

系统已更改。

在FMT下，segment的第一个block是存储segment header的，在本例中，ASSM下，oracle使用segment的至少前3个block来存储segment header。这里，我们dump 9－11的block信息。我们来看一下dump的结果和FMT下有什么不同：

Start dump data blocks tsn: 15 file#: 12 minblk 9 maxblk 11

buffer tsn: 15 rdba: 0x03000009 (12/9)

scn: 0x0000.01ca6d7f seq: 0x02 flg: 0x00 tail: 0x6d7f2002

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

Dump of First Level Bitmap Block

--------------------------------

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

unformatted: 7 total: 10 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 3

Locker xid: : 0x0004.008.0000713c

Highwater:: 0x0300000c ext#: 0 blk#: 3 ext size: 5

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

HWM Flag: HWM Set

--------------------------------------------------------

DBA Ranges :

--------------------------------------------------------

0x03000009 Length: 5 Offset: 0

0x0300000e Length: 5 Offset: 5

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

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

8:unformatted 9:unformatted

--------------------------------------------------------

buffer tsn: 15 rdba: 0x0300000a (12/10)

scn: 0x0000.01ca6d7e seq: 0x02 flg: 0x00 tail: 0x6d7e2102

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

Dump of Second Level Bitmap Block

number: 1 nfree: 1 ffree: 0 pdba: 0x0300000b

opcode:0

xid:

L1 Ranges :

--------------------------------------------------------

0x03000009 Free: 5 Inst: 1

--------------------------------------------------------

buffer tsn: 15 rdba: 0x0300000b (12/11)

scn: 0x0000.01ca6d80 seq: 0x01 flg: 0x00 tail: 0x6d802301

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

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 10

last map 0x00000000 #maps: 0 offset: 2716

Highwater:: 0x0300000c ext#: 0 blk#: 3 ext size: 5

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Unlocked

--------------------------------------------------------

Low HighWater Mark :

Highwater:: 0x0300000c ext#: 0 blk#: 3 ext size: 5

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Level 1 BMB for High HWM block: 0x03000009

Level 1 BMB for Low HWM block: 0x03000009

--------------------------------------------------------

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

L2 Array start offset: 0x00001434

First Level 3 BMB: 0x00000000

L2 Hint for inserts: 0x0300000a

Last Level 1 BMB: 0x03000009

Last Level II BMB: 0x0300000a

Last Level III BMB: 0x00000000

Map Header:: next 0x00000000 #extents: 2 obj#: 32499 flag: 0x20000000

Extent Map

-----------------------------------------------------------------

0x03000009 length: 5

0x0300000e length: 5

Auxillary Map

--------------------------------------------------------

Extent 0 : L1 dba: 0x03000009 Data dba: 0x0300000c

Extent 1 : L1 dba: 0x03000009 Data dba: 0x0300000e

--------------------------------------------------------

Second Level Bitmap block DBAs

--------------------------------------------------------

DBA 1: 0x0300000a

这里可以看到Highwater:: 0x0300000c ，HWM指向的第一个extent的第四个block，也就是说，segment head保留了3个block。

为什么前面我们说oracle在ASSM的segment中至少用前3个block来存储segment header的信息呢？我们可以创建一个extent为256K的tablespace来，然后在上面创建table，来看看结果：

SQL> create tablespace assm

2 datafile '/data1/oracle/oradata/assm01.dbf'

3 size 10M

4 extent management local uniform size 256K

5 segment space management auto

6 /

Tablespace created.

SQL> CREATE TABLE TEST_HWM1 (ID CHAR(2000), NAME CHAR(2000) )

2 Tablespace ASSM

3 STORAGE ( MINEXTENTS 2) PCTFREE 40

4 /

Table created.

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents

3 where segment_name='TEST_HWM1'

4 /

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 7 7 9 32

1 7 7 41 32

SQL> alter system dump datafile 7 block min 9 block max 11;

System altered.

我们看其中一部分的trace文件的内容：

Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11

buffer tsn: 16 rdba: 0x01c00009 (7/9)

scn: 0x0000.01444ea9 seq: 0x02 flg: 0x00 tail: 0x4ea92002

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

Dump of First Level Bitmap Block

--------------------------------

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

unformatted: 12 total: 16 first useful block: 4

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 : 4

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Highwater:: 0x01c0000d ext#: 0 blk#: 4 ext size: 32

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

HWM Flag: HWM Set

--------------------------------------------------------

DBA Ranges :

--------------------------------------------------------

0x01c00009 Length: 16 Offset: 0

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

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

8:unformatted 9:unformatted 10:unformatted 11:unformatted

12:unformatted 13:unformatted 14:unformatted 15:unformatted

--------------------------------------------------------

我们发现，这里使用了前4个block来存储segment header的内容。

三、 insert数据时HWM的移动

LMT下:

SQL> insert into test_hwm values('1','dlinger');

已创建 1 行。

SQL> alter system dump datafile 11 block 9;

系统已更改。

SQL> insert into test_hwm values('2','dlinger');

已创建 1 行。

SQL> alter system dump datafile 11 block 9;

系统已更改。

SQL> insert into test_hwm values('3','dlinger');

已创建 1 行。

SQL> alter system dump datafile 11 block 9;

系统已更改。

SQL> insert into test_hwm values('4','dlinger');

已创建 1 行。

SQL> alter system dump datafile 11 block 9;

系统已更改。

SQL> insert into test_hwm values('5','dlinger');

已创建 1 行。

SQL> alter system dump datafile 11 block 9;

系统已更改。

查看_bump_highwater_mark_count参数:

select x.ksppinm name, y.ksppstvl value,

from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm like '\_%' escape '\' and x.ksppinm like '%bump_highwater_mark_count%' order by translate(x.ksppinm, ' _', ' ');

NAME VALUE

----------------------------- --------

_bump_highwater_mark_count 0

看看dump的结果:

*** 2004-06-14 10:46:56.000

Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

buffer tsn: 14 rdba: 0x02c00009 (11/9)

scn: 0x0000.015032ef seq: 0x01 flg: 0x00 tail: 0x32ef1001

frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x02c0000b ext#: 0 blk#: 1 ext size: 4

#blocks in seg. hdr's freelists: 1

#blocks below: 1

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x02c0000a length: 4

0x02c0000e length: 5

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 1

SEG LST:: flg: USED lhd: 0x02c0000a ltl: 0x02c0000a

End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

*** 2004-06-14 10:47:25.000

Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

buffer tsn: 14 rdba: 0x02c00009 (11/9)

scn: 0x0000.01503349 seq: 0x02 flg: 0x00 tail: 0x33491002

frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x02c0000c ext#: 0 blk#: 2 ext size: 4

#blocks in seg. hdr's freelists: 1

#blocks below: 2

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x02c0000a length: 4

0x02c0000e length: 5

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 2

SEG LST:: flg: USED lhd: 0x02c0000b ltl: 0x02c0000b

End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

*** 2004-06-14 10:47:50.000

Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

buffer tsn: 14 rdba: 0x02c00009 (11/9)

scn: 0x0000.01503350 seq: 0x02 flg: 0x00 tail: 0x33501002

frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x02c0000d ext#: 0 blk#: 3 ext size: 4

#blocks in seg. hdr's freelists: 1

#blocks below: 3

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x02c0000a length: 4

0x02c0000e length: 5

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 3

SEG LST:: flg: USED lhd: 0x02c0000c ltl: 0x02c0000c

End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

*** 2004-06-14 10:48:04.000

Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

buffer tsn: 14 rdba: 0x02c00009 (11/9)

scn: 0x0000.015033a4 seq: 0x02 flg: 0x00 tail: 0x33a41002

frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x02c0000e ext#: 0 blk#: 4 ext size: 4

#blocks in seg. hdr's freelists: 1

#blocks below: 4

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x02c0000a length: 4

0x02c0000e length: 5

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 4

SEG LST:: flg: USED lhd: 0x02c0000d ltl: 0x02c0000d

End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

*** 2004-06-14 10:50:20.000

Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

buffer tsn: 14 rdba: 0x02c00009 (11/9)

scn: 0x0000.0150350e seq: 0x03 flg: 0x00 tail: 0x350e1003

frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x02c00013 ext#: 1 blk#: 5 ext size: 5

#blocks in seg. hdr's freelists: 5

#blocks below: 9

mapblk 0x00000000 offset: 1

Unlocked

Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x02c0000a length: 4

0x02c0000e length: 5

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 5

SEG LST:: flg: USED lhd: 0x02c0000e ltl: 0x02c00012

End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9

分析一下这个结果:

Highwater:0x02c0000b à Highwater: 0x02c0000c à Highwater: 0x02c0000d

à Highwater: 0x02c0000e à Highwater: 0x02c00013

当我们没有设置_bump_highwater_mark_count时,在前五个数据块，HWM是以1为步长移动的；在五块以后，HWM是以5为步长移动的。

对于ASSM来说,情况又是不一样的。

对于 extents <= 16 blocks的情况，HWM 移动遵循:

第一次移动----à extent blocks - metadata

第二次移动----à extent blocks

对于 extents > 16 blocks的情况，HWM移动遵循:

每次移动32个blocks，但是HWM包含未格式化的block，每次格式化16个block或者16 -metadata blocks。

我们在这里只是提出这样的问题让大家注意，不对ASSM的问题进行专门的讨论。

四、HWM对性能的影响

我们对一个table进行DML操作，主要是insert，update，delete这三种。当一个table进行了多次的insert数据时，前面我们已经讨论了，table的HWM会不停地提升。现在我们来这样一种情况：如果在这期间我们对这个table进行了大量的delete操作，这是table的HWM会不会随着数据量的减少而下降呢？我们将通过一个实现来说明这个问题：

这里我们要先引入一个procedure(转自tom的《oracle高级专家编程》)：

create or replace procedure show_space

( p_segname in varchar2,

p_owner in varchar2 default user,

p_type in varchar2 default 'TABLE',

p_partition in varchar2 default NULL )

l_total_blocks number;

l_total_bytes number;

l_unused_blocks number;

l_unused_bytes number;

l_LastUsedExtFileId number;

l_LastUsedExtBlockId number;

l_last_used_block number;

procedure p( p_label in varchar2, p_num in number )

begin

dbms_output.put_line( rpad(p_label,40,'.') ||

p_num );

end;

begin

dbms_space.unused_space

( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

partition_name => p_partition,

total_blocks => l_total_blocks,

total_bytes => l_total_bytes,

unused_blocks => l_unused_blocks,

unused_bytes => l_unused_bytes,

last_used_extent_file_id => l_LastUsedExtFileId,

last_used_extent_block_id => l_LastUsedExtBlockId,

last_used_block => l_last_used_block );

p( 'Total Blocks', l_total_blocks );

p( 'Total Bytes', l_total_bytes );

p( 'Unused Blocks', l_unused_blocks );

p( 'Unused Bytes', l_unused_bytes );

p( 'Last Used Ext FileId', l_LastUsedExtFileId );

p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );

p( 'Last Used Block', l_last_used_block );

end;

通过这个procedure显示的结果，我们可以得到一个segment的HWM的位置。在sqlplus中，我们要看到这个procedure显示的结果，需要设置: set serveroutput on

这里，HWM = total_blocks - Unused Blocks +1

我们来看这样一个实验：

使用系统视图all_objects来创建测试table MY_OBJECTS，然后insert 31007行数据：

SQL> create table MY_OBJECTS as

2 select * from all_objects;

Table created

SQL> select count(*) from MY_OBJECTS;

COUNT(*)

----------

31007

SQL> exec show_space(p_segname=>'MY_OBJECTS',p_owner =>'DLINGER',p_type => 'TABLE');

Total Blocks............................425

Total Bytes.............................3481600

Unused Blocks...........................3

Unused Bytes............................24576

Last Used Ext FileId....................11

Last Used Ext BlockId...................439

Last Used Block.........................2

这时，我们使用show_space来计算table MY_OBJECTS的HWM，这里

HWM=425 - 3 + 1 = 423 ;

我们现在对table MY_OBJECTS 进行delete操作，删除前15000行数据：

SQL> delete from MY_OBJECTS where rownum <15000;

已删除14999行。

SQL> exec show_space(p_segname => 'MY_OBJECTS',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................425

Total Bytes.............................3481600

Unused Blocks...........................3

Unused Bytes............................24576

Last Used Ext FileId....................11

Last Used Ext BlockId...................439

Last Used Block.........................2

PL/SQL 过程已成功完成。

现在我们再来观察HWM的结果，可以看到：这里HWM=425 - 3 + 1 = 423 。

HWM的位置并没有发生变化。这说明对table MY_OBJECTS 删除了14999行数据后，并不会改变HWM的位置。

那么，HWM过高会对数据库的性能有什么样的影响呢？

这里我们以全表扫描为例，来讨论HWM过高的不良影响。

同样，我们也通过一个实验来看full table scan在delete前后访问的block数量的情况：

SQL> set autotrace traceonly

SQL> select count(*) from MY_OBJECTS;

COUNT(*)

----------

31007

Statistics

----------------------------------------------------------

。。。

422 physical reads

0 redo size

378 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

。。。

这里，我们通过oracle的autotrace来观察sql的执行情况。

看看这个sql访问的block：422 physical reads

我们通过Statistics的内容，可以看到，在table MY_OBJECTS有31007行数据的情况下，对table MY_OBJECTS 进行一次full table scan，oracle需要访问了422个block。

这里，我们发现full table scan时访问的block数和HWM之下的block数量是一致的。

如果我们删除table MY_OBJECTS 的一部分数据后，那我们对table MY_OBJECTS进行一次full table scan需要访问的block会不会随着数据行数的减少而降低呢？

我们delete 14999行数据，这是只剩16008行数据了：

SQL> delete from MY_OBJECTS where rownum<15000;

14999 rows deleted

SQL> commit;

Commit complete

在这里，我们把oracle先shutdown，然后在startup，以便清空cache中的数据。

SQL> set autotrace traceonly

SQL> select count(*) from MY_OBJECTS;

COUNT(*)

----------

16008

Statistics

----------------------------------------------------------

。。。

422 physical reads

0 redo size

378 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

。。。

通过上面的Statistics的内容，可以看到，table full scan仍然访问了422个block。

当我没有delete前14999行数据时，全表扫描需要访问31007行数据；而当delete了14999行数据之后，全表扫描实际需要访问的数据行减少了，但是oracle访问的block数量并没有减少。这说明进行table full scan时，实际上是对HWM下所有的block进行访问。我们知道，访问的block数量越多，代表需要消耗的资源越多。那么，当一个table在进行了大量的delete操作后，或者说，当一个table在HWM之下的block上的数据不饱和时，我们应该考虑采用一些方法来降低该表的HWM，以减小table full scan时需要访问的block数量。

五、何时该降低HWM

Table包含两种空闲的block：

在HWM之上的空闲block。我们运行analyze table后，这些空闲的blocks会在user_tables的 EMPTY_BLOCKS中被统计。这些空闲的blocks实际上是从来没有存储过数据的，我们可以用以下命令来释放这些空间：

Alter table table_name deallocate unused;

在HWM之下的空闲block。当数据插入到一个block后，那么HWM就移动到这个block之上了。然后后续的操作又将这个block中的数据删除了，那么，这个block实际上是空闲的。但是这些blocks位于HWM之下，所以是不会出现在EMPTY_BLOCKS中的。那么，这样的block过多，是会影响性能的，就像前面我们讨论过table full scan 中看到的那样。

我们同样用系统视图all_objects来创建测试table MY_OBJECTS,然后随意delete其中的一部分数据，然后我们在对table MY_OBJECTS进行分析，来观察现在这个table的HWM之下的数据分布状况。

对于LMT下FLM：

我们可以用这个方法来一个table在HWM有多少blocks是不包含数据的。：

SQL> analyze table MY_OBJECTS compute statistics;

Table analyzed

SQL> select (1- a.num/ b.num_total)*100 as percent from

2 (select count(distinct substr(rowid,1,15)) num from MY_OBJECTS)a ,

3 (select BLOCKS - EMPTY_BLOCKS num_total from user_tables where table_name= 'MY_OBJECTS') b;

PERCENT

----------

24.8606346

从上面的结果，我们可以看到，table MY_OBJECTS中HWM下有24.86%的blocks是不包含数据的。当这个值比较高的时时候，我们可以考虑用一些方法来释放HWM下的空闲blocks了。注意，这里一定要先对table进行分析。

我们还可以考察这样一个指标：

SQL>select NUM_ROWS*AVG_ROW_LEN/

((BLOCKS-EMPTY_BLOCKS)*((100-PCT_FREE)/100)*8192)*100 percnt

2 from dba_tables where table_name = 'MY_OBJECTS';

PERCNT

----------

72.1461836

这里，我们可以看到table MY_OBJECTS的平均blocks的数据充满度为72%。注意，这里我的环境下oracle的block_size为8k,那么在不同的block_size下，我们应该修改上面的sql中的8192的数值。这里计算时已经除去的PCTFREE的部分，MY_OBJECTS的PCTFREE为10，那么block的平均数据充满度实际上是72%×90%= 64.8%。

如果table经常进行全表扫描，或范围扫描，那么当这个值比较低的时候，也应该考虑来合并HWM下的blocks，将空闲的block释放。

对于ASSM:

对于ASSM的segment来说，考察HWM下的blocks的空间使用状况相对要简单一些。在这里，我们可以使用这样一个procedure来得到table的blocks使用情况：

create or replace procedure show_space_assm(

p_segname in varchar2,

p_owner in varchar2 default user,

p_type in varchar2 default 'TABLE' )

l_fs1_bytes number;

l_fs2_bytes number;

l_fs3_bytes number;

l_fs4_bytes number;

l_fs1_blocks number;

l_fs2_blocks number;

l_fs3_blocks number;

l_fs4_blocks number;

l_full_bytes number;

l_full_blocks number;

l_unformatted_bytes number;

l_unformatted_blocks number;

procedure p( p_label in varchar2, p_num in number )

begin

dbms_output.put_line( rpad(p_label,40,'.') ||p_num );

end;

begin

dbms_space.space_usage(

segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

fs1_bytes => l_fs1_bytes,

fs1_blocks => l_fs1_blocks,

fs2_bytes => l_fs2_bytes,

fs2_blocks => l_fs2_blocks,

fs3_bytes => l_fs3_bytes,

fs3_blocks => l_fs3_blocks,

fs4_bytes => l_fs4_bytes,

fs4_blocks => l_fs4_blocks,

full_bytes => l_full_bytes,

full_blocks => l_full_blocks,

unformatted_blocks => l_unformatted_blocks,

unformatted_bytes => l_unformatted_bytes);

p('free space 0-25% Blocks:',l_fs1_blocks);

p('free space 25-50% Blocks:',l_fs2_blocks);

p('free space 50-75% Blocks:',l_fs3_blocks);

p('free space 75-100% Blocks:',l_fs4_blocks);

p('Full Blocks:',l_full_blocks);

p('Unformatted blocks:',l_unformatted_blocks);

end;

我们知道，在ASSM下，block的空间使用分为free space： 0-25%，25-50%，50-75%，70-100%，full 这样5中情况，show_space_assm会对需要统计的table汇总这5中类型的block的数量。

我们来看table HWM1的空间使用情况：

SQL> exec show_space_assm('HWM1','DLINGER');

free space 0-25% Blocks:.................0

free space 25-50% Blocks:...............1

free space 50-75% Blocks:...............0

free space 75-100% Blocks:..............8

Full Blocks:.....................................417

Unformatted blocks:.........................0

这个结果显示，table HWM1，full的block有417个，free space 为75-100% Block有8个，free space 25-50% Block有1个。当table HWM下的blocks的状态大多为free space

较高的值时，我们考虑来合并HWM下的blocks，将空闲的block释放，降低table的HWM。

六、如何降低HWM

在oracle8i以前的版本，如果我们需要降低segment 的HWM，可以采用两种方法：EXP/IMP 和CTAS，对这两种方法大家都很熟悉，我们在这里就不做讨论了。

(1) Move

从8i开始，oracle开始提供Move的命令。我们通常使用这个命令，将一个table segment从一个tablespace移动到另一个tablespace。

Move实际上是在block之间物理的copy数据，那么，我们可以通过这种方式来降低table的HWM。我们先通过一个实验来看看move是如何移动数据的。创建table TEST_HWM，insert一些数据：

SQL> create table TEST_HWM (id int ,name char(2000)) tablespace hwm;

Table created

我们往table TEST_HWM 中insert如下数据：

insert into TEST_HWM values (1,'aa');

insert into TEST_HWM values (2,'bb');

insert into TEST_HWM values (2,'cc');

insert into TEST_HWM values (3,'dd');

insert into TEST_HWM values (4,'ds');

insert into TEST_HWM values (5,'dss');

insert into TEST_HWM values (6,'dss');

insert into TEST_HWM values (7,'ess');

insert into TEST_HWM values (8,'es');

insert into TEST_HWM values (9,'es');

insert into TEST_HWM values (10,'es');

我们来看看这个table的rowid和block的ID和信息：

SQL> select rowid , id,name from TEST_HWM;

ROWID ID NAME

------------------------- ----------- ------

AAAH7JAALAAAAAUAAA 1 aa

AAAH7JAALAAAAAUAAB 2 bb

AAAH7JAALAAAAAUAAC 2 cc

AAAH7JAALAAAAAVAAA 3 dd

AAAH7JAALAAAAAVAAB 4 ds

AAAH7JAALAAAAAVAAC 5 dss

AAAH7JAALAAAAAWAAA 6 dss

AAAH7JAALAAAAAWAAB 7 ess

AAAH7JAALAAAAAWAAC 8 es

AAAH7JAALAAAAAXAAA 9 es

AAAH7JAALAAAAAXAAB 10 es

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 11 11 19 5

这里，简单地介绍一下rowid的相关知识：

ROWID 在磁盘上需要10 个字节的存储空间并使用18 个字符来显示它包含下列组件：

数据对象编号：每个数据对象如表或索引在创建时分配，并且此编号在数据库中是唯一的；

相关文件编号：此编号对于一个表空间中的每个文件是唯一的；

块编号：表示包含此行的块在文件中的位置；

行编号：标识块头中行目录位置的位置；

在内部数据对象编号需要32 位，相关文件编号需要10 位，块编号需要22，位行编号需要16 位，加起来总共是80 位或10 个字节，ROWID使用以64 为基数的编码方案来显示该方案将六个位置用于数据对象，编号三个位置用于相关文件编号六个位置用于块编号三个位置用于行编号以64 为基数的编码方案使用字符A-Z a-z 0-9 + 和/共64 个字符，如下例所示：

AAAH7J AAL AAAAAU AAA

在本例中

AAAH7J 是数据对象编号

AAL 是相关文件编号

AAAAAU 是块编号

AAA 是行编号

那么，我们根据数据的rowid，可以看出这11行数据分布在AAAAAU，AAAAAV，AAAAAW，AAAAAX这四个block中。

然后我们从table TEST_HWM中delete一些数据：

delete from TEST_HWM where id = 2;

delete from TEST_HWM where id = 4;

delete from TEST_HWM where id = 3;

delete from TEST_HWM where id = 7;

delete from TEST_HWM where id = 8;

delete from TEST_HWM where id = 9;

我们在来看看这个table的rowid和block的ID和信息：

SQL> select rowid , id,name from TEST_HWM;

ROWID ID NAME

------------------ ---------- --------- --------------

AAAH7JAALAAAAAUAAA 1 aa

AAAH7JAALAAAAAVAAC 5 dss

AAAH7JAALAAAAAWAAA 6 dss

AAAH7JAALAAAAAXAAB 10 es

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 11 11 19 5

在这里，我们可以看到，数据的rowid没有发生改变，我们根据数据的rowid，可以看出这4行数据依然分布在AAAAAU，AAAAAV，AAAAAW，AAAAAX这四个block中。

接下来我们对table TEST_HWM进行move的操作，然后再来观察rowid，blockid的信息：

SQL> alter table TEST_HWM move;

Table altered

SQL> select rowid,id,name from HWM;

ROWID ID NAME

------------------ ---------- -------- ---------------

AAAH7NAALAAAANrAAA 1 aa

AAAH7NAALAAAANrAAB 5 dss

AAAH7NAALAAAANrAAC 6 dss

AAAH7NAALAAAANsAAA 10 es

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name=' TEST_HWM ' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 11 11 874 5

我们可以看到，对table TEST_HWM进行move后，该table所在blockid发生了改变，那么数据的rowid自然也发生了改变。从上面的结果，我们可以看到，现在table TEST_HWM 的数据分布在AAAANr，AAAANs两个block中了。但是这四行数据的rowid的顺序来看，这四行数据在table中的存储顺序并没有发生改变。move是在block之间对于数据的物理copy。

我们再来看看move操作对于table的HWM的位置有什么变化，我们同样使用系统视图all_objects来创建测试table my_objects，然后delete前9999行数据：

SQL> create table my_objects tablespace HWM

2 as select * from all_objects;

SQL> delete from my_objects where rownum<10000;

9999 rows deleted

SQL> select count(*) from my_objects;

COUNT(*)

----------

21015

SQL> exec show_space(p_segname => 'MY_OBJECTS',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................425

Total Bytes.............................3481600

Unused Blocks...........................3

Unused Bytes............................24576

Last Used Ext FileId....................11

Last Used Ext BlockId...................1294

Last Used Block.........................2

这里HWM=425 - 3 + 1 = 423

然后对table MY_OBJECTS进行move操作：

SQL> alter table MY_OBJECTS move;

表已更改。

SQL> exec show_space(p_segname => 'MY_OBJECTS',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................290

Total Bytes.............................2375680

Unused Blocks...........................1

Unused Bytes............................8192

Last Used Ext FileId....................11

Last Used Ext BlockId...................1584

Last Used Block.........................4

我们可以看到，table MY_OBJECTS的HWM从423移动到290，table的HWM降低了！

我们还可以使用别的方法来降低table的HWM，比如CTAS，insert into 等，那么move操作对redo logo的写和其他的方式比较起来是相对较少的，我们在这里就不列出把具体的实验结果了，大家有兴趣的可以自己动手来证实一下。

上面我们讨论了move的执行机制和如何使用move降低table的HWM，这里，我们补充说明move的另外一些用法，以及使用move时的一些要注意的问题。

Move的一些用法

以下是alter table 中move子句的完整语法，我们介绍其中的几点：

MOVE [ONLINE]

  [segment_attributes_clause]

  [data_segment_compression]

  [index_org_table_clause]

  [ { LOB_storage_clause | varray_col_properties }

    [ { LOB_storage_clause | varray_col_properties } ]...

  [parallel_clause]

a. 我们可以使用move将一个table从当前的tablespace上移动到另一个tablespace上，如：

alter table t move tablespace tablespace_name;

b. 我们还可以用move来改变table已有的block的存储参数,如：

alter table t move storage (initial 30k next 50k);

另外，move操作也可以用来解决table中的行迁移的问题。

使用move的一些注意事项

a. table上的index需要rebuild：

在前面我们讨论过，move操作后，数据的rowid发生了改变，我们知道，index是通过rowid来fetch数据行的，所以，table上的index是必须要rebuild的。

SQL> create index i_my_objects on my_objects (object_id);

Index created

SQL> alter table my_objects move;

Table altered

SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';

INDEX_NAME STATUS

------------------------------ --------

I_MY_OBJECTS UNUSABLE

从这里可以看到，当table MY_OBJECTS进行move操作后，该table 上的inedx的状态为UNUSABLE，这时，我们可以使用alter index I_MY_OBJECTS rebuild online的命令，对index I_MY_OBJECTS进行在线rebuild。

b. move时对table的锁定

当我们对table MY_OBJECTS进行move操作时，查询v$locked_objects视图可以发现，table MY_OBJECTS上加了exclusive lock：

SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;

OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE

---------- ---------- ------------------ -----------

32471 9 DLINGER 6

SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';

OBJECT_ID

----------

32471

这就意味着，table在进行move操作时，我们只能对它进行select的操作。反过来说，当我们的一个session对table进行DML操作且没有commit时，在另一个session中是不能对这个table进行move操作的，否则oracle会返回这样的错误信息：ORA-00054: 资源正忙，要求指定 NOWAIT。

c. 关于move时空间使用的问题：

当我们使用alter table move来降低table的HWM时，有一点是需要注意的，这时，当前的tablespace中需要有1倍于table的空闲空间以供使用：

SQL> CREATE TABLESPACE TEST1

2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\TEST1.dbf' SIZE 5M

3 UNIFORM SIZE 128K ;

SQL> create table my_objects tablespace test1 as select * from all_objects;

表已创建。

SQL> select bytes/1024/1024 from user_segments where segment_name='MY_OBJECTS';

BYTES/1024/1024

---------------

3.125

SQL> alter table MY_OBJECTS move;

alter table MY_OBJECTS move

ERROR 位于第 1 行:

ORA-01652: 无法通过16（在表空间TEST1中）扩展 temp 段

SQL> ALTER DATABASE

2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\TEST1.DBF' RESIZE 7M;

数据库已更改。

SQL> alter table MY_OBJECTS move;

表已更改。

(2) DBMS_REDEFINITION

这个包是从oracle 9i开始引入的，用来作table的联机重组和重定义。我们可以通过这种方法在线地重组table，来移动table中的数据，降低HWM，修改table的存储参数，分区等等。

这个操作要求table上有一个主键，并要求预先创建一个带有要求修改的存储参数的table，以便保存重新组织后的数据。保存重新组织的数据的tble叫临时表，它只在重新组织期间被使用，在操作完成后可以被删除。

使用DBMS_REDEFINITION Package需要如下权限：

Create any table；

alter any table；

drop any table；

lock any table；

select any table；

在DBMS_REDEFINITION上执行操作

使用DBMS_REDEFINITION重组table一般是这样几个步骤：

a. 使用DBMS_REDEFINITION.CAN_REDEF_TABLE()验证所选择的table能够被重建；

b. 创建空的临时表，确保这个临时表定义了主键；

c. 使用DBMS_REDEFINITION.START_REDEF_TABLE()进行table的重组；

d. 在临时表上创建触发器，索引和约束，一般来说，这些对象于源有表中的是一致的，但是名称必须不同。同时要确保所创建的所有外键约束不可用。在重组结束时，所有这些对象将替换定义在源表上的对象。

e. 使用DBMS_REDEFINITION.FINISH_REDEF_TABLE()完成重组的过程。在这期间，源表将会lock较短的时间。

f．删除临时表。

在这里，我们只是简单第介绍如何使用DBMS_REDEFINITION对table进行在线重组和重定义，关于这个package具体的使用方法和使用上的限制，可以查阅oracle的官方文档：

http://tahiti.oracle.com/

(3). Shrink

从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。

在第4部分，我们已经讨论过，如何考察在ASSM下table是否需要回收浪费的空间，这里，我们来讨论如和对一个ASSM的segment回收浪费的空间。

同样，我们用系统视图all_objects来在tablespace ASSM上创建测试表my_objects，这一小节的内容，实验环境为oracle10.1.0.2：

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

PL/SQL Release 10.1.0.2.0 - Production

CORE 10.1.0.2.0 Production

TNS for 32-bit Windows: Version 10.1.0.2.0 - Production

NLSRTL Version 10.1.0.2.0 - Production

SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,

2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT

3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';

TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT

---------------- ---------- ----------------- --------------- ------------------------

ASSM 8192 LOCAL UNIFORM AUTO

SQL> create table my_objects tablespace assm

2 as select * from all_objects;

Table created

然后我们随机地从table MY_OBJECTS中删除一部分数据：

SQL> select count(*) from my_objects;

COUNT(*)

----------

47828

SQL> delete from my_objects where object_name like '%C%';

16950 rows deleted

SQL> delete from my_objects where object_name like '%U%';

4503 rows deleted

SQL> delete from my_objects where object_name like '%A%';

6739 rows deleted

现在我们使用show_space和show_space_assm来看看my_objects的数据存储状况：

SQL> exec show_space('MY_OBJECTS','DLINGER');

Total Blocks............................680

Total Bytes.............................5570560

Unused Blocks...........................1

Unused Bytes............................8192

Last Used Ext FileId....................6

Last Used Ext BlockId...................793

Last Used Block.........................4

PL/SQL 过程已成功完成。

SQL> exec show_space_assm('MY_OBJECTS','DLINGER');

free space 0-25% Blocks:................0

free space 25-50% Blocks:...............205

free space 50-75% Blocks:...............180

free space 75-100% Blocks:..............229

Full Blocks:............................45

Unformatted blocks:.....................0

PL/SQL 过程已成功完成。

这里，table my_objects的HWM下有679个block，其中，free space为25-50%的block有205个，free space为50-75%的block有180个，free space为75-100%的block有229个，full space的block只有45个，这种情况下，我们需要对这个table的现有数据行进行重组。

要使用assm上的shink，首先我们需要使该表支持行移动，可以用这样的命令来完成：

alter table my_objects enable row movement;

现在，就可以来降低my_objects的HWM，回收空间了，使用命令：

alter table bookings shrink space;

我们具体的看一下实验的结果：

SQL> alter table my_objects enable row movement;

表已更改。

SQL> alter table my_objects shrink space;

表已更改。

SQL> exec show_space('MY_OBJECTS','DLINGER');

Total Blocks............................265

Total Bytes.............................2170880

Unused Blocks...........................2

Unused Bytes............................16384

Last Used Ext FileId....................6

Last Used Ext BlockId...................308

Last Used Block.........................3

PL/SQL 过程已成功完成。

SQL> exec show_space_assm('MY_OBJECTS','DLINGER');

free space 0-25% Blocks:................0

free space 25-50% Blocks:...............1

free space 50-75% Blocks:...............0

free space 75-100% Blocks:..............0

Full Blocks:............................249

Unformatted blocks:.....................0

PL/SQL 过程已成功完成。

在执行玩shrink命令后，我们可以看到，table my_objects的HWM现在降到了264的位置，而且HWM下的block的空间使用状况，full space的block有249个，free space 为25-50% Block只有1个。

我们接下来讨论一下shrink的实现机制，我们同样使用讨论move机制的那个实验来观察。

SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;

Table created

往table test_hwm中插入如下的数据：

insert into TEST_HWM values (1,'aa');

insert into TEST_HWM values (2,'bb');

insert into TEST_HWM values (2,'cc');

insert into TEST_HWM values (3,'dd');

insert into TEST_HWM values (4,'ds');

insert into TEST_HWM values (5,'dss');

insert into TEST_HWM values (6,'dss');

insert into TEST_HWM values (7,'ess');

insert into TEST_HWM values (8,'es');

insert into TEST_HWM values (9,'es');

insert into TEST_HWM values (10,'es');

我们来看看这个table的rowid和block的ID和信息：

SQL> select rowid , id,name from TEST_HWM;

ROWID ID NAME

------------------ ---------- ----- ---------

AAANhqAAGAAAAFHAAA 1 aa

AAANhqAAGAAAAFHAAB 2 bb

AAANhqAAGAAAAFHAAC 2 cc

AAANhqAAGAAAAFIAAA 3 dd

AAANhqAAGAAAAFIAAB 4 ds

AAANhqAAGAAAAFIAAC 5 dss

AAANhqAAGAAAAFJAAA 6 dss

AAANhqAAGAAAAFJAAB 7 ess

AAANhqAAGAAAAFJAAC 8 es

AAANhqAAGAAAAFKAAA 9 es

AAANhqAAGAAAAFKAAB 10 es

11 rows selected

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 6 6 324 5

1 6 6 329 5

然后从table test_hwm中删除一些数据：

delete from TEST_HWM where id = 2; delete from TEST_HWM where id = 4; delete from TEST_HWM where id = 3; delete from TEST_HWM where id = 7; delete from TEST_HWM where id = 8;

观察table test_hwm的rowid和blockid的信息：

SQL> select rowid , id,name from TEST_HWM;

ROWID ID NAME

------------------ ---------- ----- --------

AAANhqAAGAAAAFHAAA 1 aa

AAANhqAAGAAAAFIAAC 5 dss

AAANhqAAGAAAAFJAAA 6 dss

AAANhqAAGAAAAFKAAA 9 es

AAANhqAAGAAAAFKAAB 10 es

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 6 6 324 5

1 6 6 329 5

从以上的信息，我们可以看到，在table test_hwm中，剩下的数据是分布在AAAAFH，AAAAFI，AAAAFJ，AAAAFK这样四个连续的block中。

SQL> exec show_space_assm('TEST_HWM','DLINGER');

free space 0-25% Blocks:................0

free space 25-50% Blocks:...............1

free space 50-75% Blocks:...............3

free space 75-100% Blocks:..............3

Full Blocks:............................0

Unformatted blocks:.....................0

通过show_space_assm我们可以看到目前这四个block的空间使用状况，AAAAFH，AAAAFI，AAAAFJ上各有一行数据，我们猜测free space为50-75%的3个block是这三个block，那么free space为25-50%的1个block就是AAAAFK了，剩下free space为 75-100%的3个block，是HWM下已格式化的尚未使用的block。（关于assm下hwm的移动我们前面已经详细地讨论过了，在extent不大于于16个block时，是以一个extent为单位来移动的）

然后，我们对table my_objects执行shtink的操作：

SQL> alter table test_hwm enable row movement;

Table altered

SQL> alter table test_hwm shrink space;

Table altered

SQL> select rowid ,id,name from TEST_HWM;

ROWID ID NAME

------------------ ---------- ------ -----------

AAANhqAAGAAAAFHAAA 1 aa

AAANhqAAGAAAAFHAAB 10 es

AAANhqAAGAAAAFHAAD 9 es

AAANhqAAGAAAAFIAAC 5 dss

AAANhqAAGAAAAFJAAA 6 dss

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 6 6 324 5

1 6 6 329 5

当执行了shrink操作后，有意思的现象出现了。我们来看看oracle是如何移动行数据的，这里的情况和move已经不太一样了。我们知道，在move操作的时候，所有行的rowid都发生了变化，table所位于的block的区域也发生了变化，但是所有行物理存储的顺序都没有发生变化，所以我们得到的结论是，oracle以block为单位，进行了block间的数据copy。那么shrink后，我们发现，部分行数据的rowid发生了变化，同时，部分行数据的物理存储的顺序也发生了变化，而table所位于的block的区域却没有变化，这就说明，shrink只移动了table其中一部分的行数据，来完成释放空间，而且，这个过程是在table当前所使用的block中完成的。

那么oracle具体移动行数据的过程是怎样的呢？我们根据这样的实验结果，可以来猜测一下：

Oracle是以行为单位来移动数据的。Oracle从当前table存储的最后一行数据开始移动，从当前table最先使用的block开始搜索空间，所以，shrink之前，rownum＝10的那行数据（10,es），被移动到block AAAAFH上，写到（1,aa）这行数据的后面，所以（10,es）的rownum和rowid同时发生改变。然后是（9,es）这行数据，重复上述过程。这是oracle从后向前移动行数据的大致遵循的规则，那么具体移动行数据的的算法是比较复杂的，包括向ASSM的table中insert数据使用block的顺序的算法也是比较复杂的，大家有兴趣的可以自己来研究，在这里我们不多做讨论。

我们还可以在shrink table的同时shrink这个table上的index：

alter table my_objects shrink space cascade;

同样地，这个操作只有当table上的index也是ASSM时，才能使用。

Shrink的几点问题：

a. shrink后index是否需要rebuild：

因为shrink的操作也会改变行数据的rowid，那么，如果table上有index时，shrink table后index会不会变为UNUSABLE呢？我们来看这样的实验，同样构建my_objects的测试表：

create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;

create index i_my_objects on my_objects (object_id);

delete from my_objects where object_name like '%C%';

delete from my_objects where object_name like '%U%';

现在我们来shrink table my_objects：

SQL> alter table my_objects enable row movement;

Table altered

SQL> alter table my_objects shrink space;

Table altered

SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';

INDEX_NAME STATUS

------------------------------ --------

I_MY_OBJECTS VALID

我们发现，table my_objects上的index的状态为VALID，估计shrink在移动行数据时，也一起维护了index上相应行的数据rowid的信息。我们认为，这是对于move操作后需要rebuild index的改进。但是如果一个table上的index数量较多，我们知道，维护index的成本是比较高的，shrink过程中用来维护index的成本也会比较高。

b. shrink时对table的lock

在对table进行shrink时，会对table进行怎样的锁定呢？当我们对table MY_OBJECTS进行shrink操作时，查询v$locked_objects视图可以发现，table MY_OBJECTS上加了row-X (SX) 的lock：

SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;

OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE

---------- ---------- ------------------ -----------

55422 153 DLINGER 3

SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';

OBJECT_ID

----------

55422

那么，当table在进行shrink时，我们对table是可以进行DML操作的。

c. shrink对空间的要求

我们在前面讨论了shrink的数据的移动机制，既然oracle是从后向前移动行数据，那么，shrink的操作就不会像move一样，shrink不需要使用额外的空闲空间。

小结：我们在这一部分介绍了三种降低table HWM的方法，那么实际的环境中，我们选择move还是shrink，可以针对这几项的特性，考虑你的系统的情况，做出选择。

七、其余几种会移动HWM的操作

还有几种操作是会改变HWM的：insert append，truncate。

还有一些方法也可以用来降低HWM，比如：exp/imp等，我们在这里不做讨论。

(1).insert append

当我们使用insert /*+ append */ into向一个table中插入数据时，oracle不会在HWM以下寻找空间，而是直接移动HWM，从EMPTY_BLOCKS中获得要使用的block空间，来满足这一操作的blocks的需要。

我们来看一个实验：

SQL> create table hwm as select * from all_objects;

Table created

SQL> select count(*) from hwm;

COUNT(*)

----------

31009

SQL> delete from hwm;

31009 rows deleted

SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................425

Total Bytes.............................3481600

Unused Blocks...........................3

Unused Bytes............................24576

Last Used Ext FileId....................11

Last Used Ext BlockId...................439

Last Used Block.........................2

我们往表hwm中先插入31009条数据，然后在delete掉所有的数据。前面我们讨论过，delete操作不会降低HWM，所以这时的HWM = 425 - 3 + 1 = 423。

下面，我们来比较一下insert和insert append的不同结果：

我们先使用insert into向表HWM中插入1000行数据，结果HWM没有移动。

SQL> insert into hwm select * from all_objects where rownum<1000;

999 rows inserted

SQL> commit;

Commit complete

SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................425

Total Bytes.............................3481600

Unused Blocks...........................3

Unused Bytes............................24576

Last Used Ext FileId....................11

Last Used Ext BlockId...................439

Last Used Block.........................2

然后我们delete掉所有的数据，再用insert append来作同样的操作。可以看到，使用append提示后，结果就不一样了。

SQL> delete from hwm;

999 rows deleted

SQL> commit;

Commit complete

SQL> insert /*+ append */ into hwm select * from all_objects where rownum<1000;

999 rows inserted

SQL> commit;

Commit complete

SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................440

Total Bytes.............................3604480

Unused Blocks...........................3

Unused Bytes............................24576

Last Used Ext FileId....................11

Last Used Ext BlockId...................459

Last Used Block.........................2

我们发现，往hwm中插入同样的999行数据，使用insert append，HWM = 440 - 3 + 1 = 438 ，HWM从423移动到了438！

我们再来比较以下insert和insert append的性能，对HWM插入同样的10000条数据。

构建表T：

SQL> create table t as select * from all_objects;

Table created

SQL> insert /*+ append */ into t select * from t;

31010 rows inserted

SQL> commit;

Commit complete

SQL> insert /*+ append */ into t select * from t;

62020 rows inserted

SQL> commit;

Commit complete

SQL> select count(*) from t;

COUNT(*)

----------

124040

对HWM插入数据：

SQL> set timing on

SQL> insert into hwm select * from t;

124040 rows inserted

已用时间: 00: 00: 02.93

SQL> commit;

Commit complete

已用时间: 00: 00: 00.20

SQL> insert /*+ append */ into hwm select * from t;

124040行rows inserted

已用时间: 00: 00: 01.02

SQL> commit;

Commit complete

已用时间: 00: 00: 00.30

当使用insert来插入124040行数据时，使用了2.93sec；而使用insert append插入124040行数据时，只使用了1.02sec。

在这里，提一下使用append的一个需要注意的问题：

当我们使用insert append时，oracle会生成表级的独占锁：

SQL> select * from v$mystat where rownum <2;

SID STATISTIC# VALUE

---------- ---------- ----------

13 0 1

SQL> insert /*+ append */ into hwm select * from all_objects where rownum<1000;

999 rows inserted ――我们在这里不作commit

――在另一个session中执行：

QL> select * from v$mystat where rownum <2;

SID STATISTIC# VALUE

---------- ---------- ----------

10 0 1

SQL> insert into hwm select * from all_objects where rownum<10;

――这个session出现等待

现在我们观察v$lock：

SQL> select SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK from v$lock;

SID TYPE ID1 ID2 LMODE REQUEST BLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

......

10 TM 32398 0 0 3 0

13 TX 65579 22477 6 0 0

13 TM 32398 0 6 0 1

--13阻塞了一个process

SQL> select object_name from user_objects where object_id = '32398';

OBJECT_NAME

--------------------------------------------------------------------------------

HWM

Session 13在HWM上加上了exclusive的TM锁，这时session 13 blocking了session 10。

这里我们是在LMT下的segment中做的测试。在ASSM中append锁表的情况同样存在（直到oracle10g的ASSM中依然如此）。

(2).Truncate

我们讨论truncate table，一般是和delete from table做比较。

前面，我们已经讨论过delete不会降低HWM的问题，这里我们再来看一下truncate的情况：

SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................3380

Total Bytes.............................27688960

Unused Blocks...........................18

Unused Bytes............................147456

Last Used Ext FileId....................11

Last Used Ext BlockId...................5069

Last Used Block.........................2

PL/SQL 过程已成功完成。

--这里HWM = 3380 - 18 + 1= 3363

SQL> truncate table HWM;

表已截掉。

SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................5

Total Bytes.............................40960

Unused Blocks...........................4

Unused Bytes............................32768

Last Used Ext FileId....................11

Last Used Ext BlockId...................19

Last Used Block.........................1

PL/SQL 过程已成功完成。

--执行truncate后HWM = 5 - 4 + 1 = 2

我们发现，truncate table之后，HWM又回到了1中我们看到的segment初始化状态下HWM的位置。

刷新页面返回顶部

☜ Tracy ☞ ☜天佑老爸☞

转：Oracle中 HWM与数据库性能的探讨

About