eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle在Solaris的VXFS上的异步I/O问题 | Blog首页 | 关于Oracle数据库中行迁移/行链接的问题 »

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

本文讨论的是oracle中关于tableHWM的内容,主要包括这样几个内容:

1.    什么是HWM

2.    HWM是如何移动的。

3.    HWM对于性能的影响

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

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

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

 

一、什么是HWM

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

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

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

 

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

 

Oracle9i开始,推出了新的一种segment的空间管理方式,即ASSMauto segment space management)。这种segment在空间管理上和以前的FLMfreelist management)是不一样的。这里我们简单地介绍一下。

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

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

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

ASSM只能位于Local Managetablespace之上;

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

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

 

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

 

二、初始创建的tableHWM的不同情况

 

FLM管理的table:我们先创建名为HWMtablespace,指定非自动段空间管理,extent大小为40K。并在上面创建table TEST_HWMPCTFREE 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 headerblock9,我们dumpblock9来看看:

 

*** 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

 

dumptrace文件,我们可以获得这样的信息:

我们可以看到Highwater::  0x02c0000aFLMsegment下,初始创建的tableHWM是从第一个extent的第二个block开始的。为segment header 保留一个块。我们从这里也可以看出来:

Extent Map

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

   0x02c0000a  length: 4    

说明第一个extent可用的block4

我们这里看到的结果是在默认freelist 1的条件下得到的。在FLM下,如果对segment设置了freelist groups N,则HWM指向第 N2block,当N+2 > initextentblock数时,会返回ORA-03237的错误信息,这里tablespace HWMextent40Kblock_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下,情况是怎样的呢?

我们创建名为ASSMtablespace,指定自动段空间管理,extent大小为40K。并在上面创建table TEST_HWM1,注意,这里我们只指定了PCTFREE 40,因为PCTUSEDASSM下的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的至少前3block来存储segment header。这里,我们dump 911block信息。我们来看一下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保留了3block

为什么前面我们说oracleASSMsegment中至少用前3block来存储segment header的信息呢?我们可以创建一个extent256K 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

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

我们发现,这里使用了前4block来存储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移动遵循:

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

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

 

四、HWM对性能的影响

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

这里我们要先引入一个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 )

as

    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 )

    is

    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显示的结果,我们可以得到一个segmentHWM的位置。在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_OBJECTSHWM,这里

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 scandelete前后访问的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

。。。

 

 

这里,我们通过oracleautotrace来观察sql的执行情况。

看看这个sql访问的block422  physical reads

我们通过Statistics的内容,可以看到,在table MY_OBJECTS31007行数据的情况下,对table MY_OBJECTS 进行一次full table scanoracle需要访问了422block

这里,我们发现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

 

在这里,我们把oracleshutdown,然后在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仍然访问了422block

 

当我没有delete14999行数据时,全表扫描需要访问31007行数据;而当delete14999行数据之后,全表扫描实际需要访问的数据行减少了,但是oracle访问的block数量并没有减少。这说明进行table full scan时,实际上是对HWM下所有的block进行访问。我们知道,访问的block数量越多,代表需要消耗的资源越多。那么,当一个table在进行了大量的delete操作后,或者说,当一个tableHWM之下的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进行分析,来观察现在这个tableHWM之下的数据分布状况。

 

对于LMTFLM

我们可以用这个方法来一个tableHWM有多少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_OBJECTSHWM下有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%。注意,这里我的环境下oracleblock_size8k,那么在不同的block_size下,我们应该修改上面的sql中的8192的数值。这里计算时已经除去的PCTFREE的部分,MY_OBJECTSPCTFREE10,那么block的平均数据充满度实际上是72%×90%= 64.8%

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

 

对于ASSM:

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

create or replace procedure show_space_assm(

p_segname in varchar2,

p_owner in varchar2 default user,

p_type in varchar2 default 'TABLE' )  

as

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 )

is

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 HWM1fullblock417个,free space 75-100% Block8个,free space 25-50% Block1个。当table HWM下的blocks的状态大多为free space

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

 

六、如何降低HWM

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

(1) Move

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

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

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');

 

我们来看看这个tablerowidblockID和信息:

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行数据分布在AAAAAUAAAAAVAAAAAWAAAAAX这四个block中。

然后我们从table TEST_HWMdelete一些数据:

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;

 

我们在来看看这个tablerowidblockID和信息:

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行数据依然分布在AAAAAUAAAAAVAAAAAWAAAAAX这四个block中。

接下来我们对table TEST_HWM进行move的操作,然后再来观察rowidblockid的信息:

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 的数据分布在AAAANrAAAANs两个block中了。但是这四行数据的rowid的顺序来看,这四行数据在table中的存储顺序并没有发生改变。move是在block之间对于数据的物理copy

 

我们再来看看move操作对于tableHWM的位置有什么变化,我们同样使用系统视图all_objects来创建测试table my_objects,然后delete9999行数据:

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_OBJECTSHWM423移动到290tableHWM降低了!

 

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

 

上面我们讨论了move的执行机制和如何使用move降低tableHWM,这里,我们补充说明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是通过rowidfetch数据行的,所以,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的操作。反过来说,当我们的一个sessiontable进行DML操作且没有commit时,在另一个session中是不能对这个table进行move操作的,否则oracle会返回这样的错误信息:ORA-00054: 资源正忙,要求指定 NOWAIT

 

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

当我们使用alter table move来降低tableHWM时,有一点是需要注意的,这时,当前的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_REDEFINITIONtable进行在线重组和重定义,关于这个package具体的使用方法和使用上的限制,可以查阅oracle的官方文档:

http://tahiti.oracle.com/

 

(3). Shrink

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

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

   同样,我们用系统视图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_spaceshow_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_objectsHWM下有679block,其中,free space25-50%block205个,free space50-75%block180个,free space75-100%block229个,full spaceblock只有45个,这种情况下,我们需要对这个table的现有数据行进行重组。

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

alter table my_objects enable row movement;

现在,就可以来降低my_objectsHWM,回收空间了,使用命令:

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_objectsHWM现在降到了264的位置,而且HWM下的block的空间使用状况,full spaceblock249个,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');

 

我们来看看这个tablerowidblockID和信息:

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_hwmrowidblockid的信息:

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中,剩下的数据是分布在AAAAFHAAAAFIAAAAFJAAAAFK这样四个连续的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的空间使用状况,AAAAFHAAAAFIAAAAFJ上各有一行数据,我们猜测free space50-75%3block是这三个block,那么free space25-50%1block就是AAAAFK了,剩下free space 75-100% 3block,是HWM下已格式化的尚未使用的block。(关于assmhwm的移动我们前面已经详细地讨论过了,在extent不大于于16block时,是以一个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的区域也发生了变化,但是所有行物理存储的顺序都没有发生变化,所以我们得到的结论是,oracleblock为单位,进行了block间的数据copy。那么shrink后,我们发现,部分行数据的rowid发生了变化,同时,部分行数据的物理存储的顺序也发生了变化,而table所位于的block的区域却没有变化,这就说明,shrink只移动了table其中一部分的行数据,来完成释放空间,而且,这个过程是在table当前所使用的block中完成的。

 

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

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

 

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

alter table my_objects shrink space cascade;

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

   

 

Shrink的几点问题:

a.  shrinkindex是否需要rebuild

因为shrink的操作也会改变行数据的rowid,那么,如果table上有index时,shrink tableindex会不会变为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时对tablelock

在对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 appendtruncate

还有一些方法也可以用来降低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

 

下面,我们来比较一下insertinsert 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 appendHWM = 440 - 3 + 1 = 438 HWM423移动到了438

 

我们再来比较以下insertinsert 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;

 

124040rows 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 13HWM上加上了exclusiveTM锁,这时session 13 blockingsession 10

这里我们是在LMT下的segment中做的测试。在ASSMappend锁表的情况同样存在(直到oracle10gASSM中依然如此)。

 

(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 过程已成功完成。

--执行truncateHWM = 5 - 4 + 1 = 2

 

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




历史上的今天...
    >> 2010-11-28文章:
    >> 2008-11-28文章:
    >> 2007-11-28文章:
    >> 2006-11-28文章:
    >> 2005-11-28文章:

无觅

By eygle on 2011-11-28 08:28 | Comments (0) | FAQ | 2910 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com