今天一客户打电话,说对他们的表删除了很多行,但是查看表所站的块没有减少。这是因为HWM没有移动的原因。HWM如最高水位线一样,默认只有增加。
    HWM对数据库的性能影响是很大的:
    1,比如做全表扫描的时候。
    2,在insert语句中增加了append提示的时候。
    降低HWM有很多的方法。
    1,exp/imp表。
    2,create table  as select ....和alter table .. rename ...
    3, move 表。
    4,shrink表(10G)
 
    下面我们介绍move与shrink两种方法。
    move与shrink他们能移动HWM,还能消除部分行迁移,消除空间碎片。
   
    下面是move与shrink的操作。
   
    创建测试数据:
   
  1. SQL> create user luoping identified by htz; 
  2.  
  3. User created. 
  4.  
  5. SQL> grant connect,resource to luoping; 
  6.  
  7. Grant succeeded. 
  8.  
  9. SQL> conn luoping/htz 
  10. Connected. 
  11.  
  12. SQL> create table htz1 (a int
  13.   2  ; 
  14.  
  15. Table created. 
  16.  
  17. SQL> create table htz2 (a int); 
  18.  
  19. Table created. 
  20.  
  21. SQL> create index htz1_a on htz1(a); 
  22.  
  23. Index created. 
  24.  
  25. SQL> create index htz2_a on htz2(a); 
  26.  
  27. Index created. 
  28.  
  29.  
  30. begin 
  31. for i in 1..1000000 loop 
  32. insert into htz2 values(i); 
  33. insert into htz1 values(i); 
  34. if mod(i,1000)=0 then 
  35. commit
  36. end if; 
  37. end loop; 
  38. end
  39.  
  40. SQL> begin 
  41. for i in 1..1000000 loop 
  42.   2    3  insert into htz2 values(i); 
  43. insert into htz1 values(i); 
  44.   4    5  if mod(i,1000)=0 then 
  45.   6  commit
  46.   7  end if; 
  47.   8  end loop; 
  48.   9  end
  49.  10  / 
  50. PL/SQL procedure successfully completed. 

查看表与索引的消息

  1. oracle@oracle10grac1 ~]$ dbtable orcl1 htz1 
  2.  
  3. OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE) 
  4. ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ---------------- 
  5. LUOPING    HTZ1       USERS                                        N DEFAULT             1 NO  N    12M                10                                     10 
  6.  
  7. OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST 
  8. ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ---------- 
  9. LUOPING    htz1       USERS           HTZ1_A                                          VALID    DEFAULT    NORMAL                A                        1 
  10. [oracle@oracle10grac1 ~]$ dbtable orcl1 htz1 
  11.  
  12. OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE) 
  13. ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ---------------- 
  14. LUOPING    HTZ1       USERS                                        N DEFAULT             1 NO  N    13M                10                                     10 
  15.  
  16. OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST 
  17. ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ---------- 
  18. LUOPING    htz1       USERS           HTZ1_A                                          VALID    DEFAULT    NORMAL                A                        1 
  19. [oracle@oracle10grac1 ~]$ dbtable orcl1 htz2 
  20.  
  21. OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE) 
  22. ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ---------------- 
  23. LUOPING    HTZ2       USERS                                        N DEFAULT             1 NO  N    13M                10                                     10 
  24.  
  25. OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST 
  26. ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ---------- 
  27. LUOPING    htz2       USERS           HTZ2_A                                          VALID    DEFAULT    NORMAL                A                        1 

对表进行分析:

  1. [oracle@oracle10grac1 ~]$ dbmsstats orcl1 luoping htz1 
  2. [oracle@oracle10grac1 ~]$ dbmsstats orcl1 luoping htz2 
  3.  
  4. [oracle@oracle10grac1 ~]$ dbtable orcl1 htz2 
  5.  
  6. OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE) 
  7. ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ---------------- 
  8. LUOPING    HTZ2       USERS      2012-05-30 23:36:00      1630     N DEFAULT             1 NO  N    13M                10               1001889               10 
  9.  
  10. OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST 
  11. ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ---------- 
  12. LUOPING    htz2       USERS           HTZ2_A          2012-05-30 23:36:10        3437 VALID    DEFAULT    NORMAL        1000000 A                        1 
  13. [oracle@oracle10grac1 ~]$ dbtable orcl1 htz1 
  14.  
  15. OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE) 
  16. ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ---------------- 
  17. LUOPING    HTZ1       USERS      2012-05-30 23:35:04      1630     N DEFAULT             1 NO  N    13M                10                999943               10 
  18.  
  19. OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST 
  20. ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ---------- 
  21. LUOPING    htz1       USERS           HTZ1_A          2012-05-30 23:35:25        3437 VALID    DEFAULT    NORMAL        1000000 A                        1 


下面对两张表进行删除5W行记录:

 

  1. SQL> delete from htz1 where rownum <50000; 
  2.  
  3. 49999 rows deleted. 
  4.  
  5. SQL> delete from htz2 where rownum <50000; 
  6.  
  7. 49999 rows deleted. 
  8.  
  9. SQL> commit

查看表站用BLOCK的情况:

 

  1. SQL> exec show_space('HTZ1','LUOPING'); 
  2. Unformatted Blocks .....................              62 
  3. FS1 Blocks (0-25) ......................               0 
  4. FS2 Blocks (25-50) .....................               0 
  5. FS3 Blocks (50-75) .....................               1 
  6. FS4 Blocks (75-100).....................             128 
  7. Full Blocks        .....................           1,439 
  8. Total Blocks............................           1,664 
  9. Total Bytes.............................      13,631,488 
  10. Total MBytes............................              13 
  11. Unused Blocks...........................               0 
  12. Unused Bytes............................               0 
  13. Last Used Ext FileId....................               4 
  14. Last Used Ext BlockId...................           7,561 
  15. Last Used Block.........................             128 
  16.  
  17. PL/SQL procedure successfully completed. 
  18.  
  19. SQL> exec show_space('HTZ2','LUOPING'); 
  20. Unformatted Blocks .....................              62 
  21. FS1 Blocks (0-25) ......................               0 
  22. FS2 Blocks (25-50) .....................               0 
  23. FS3 Blocks (50-75) .....................               1 
  24. FS4 Blocks (75-100).....................             128 
  25. Full Blocks        .....................           1,439 
  26. Total Blocks............................           1,664 
  27. Total Bytes.............................      13,631,488 
  28. Total MBytes............................              13 
  29. Unused Blocks...........................               0 
  30. Unused Bytes............................               0 
  31. Last Used Ext FileId....................               4 
  32. Last Used Ext BlockId...................           7,433 
  33. Last Used Block.........................             128 
  34.  
  35. PL/SQL procedure successfully completed. 

下面对表进行MOVE与SHRINK

 

  1. SQL> alter table luoping.htz2 enable row movement; 
  2.  
  3. Table altered. 
  4.  
  5. SQL> alter table luoping.htz2  shrink space
  6. SQL> alter table luoping.htz1 move
  7.  
  8. Table altered. 

下面查看表LOCK消息:

在SHRINK的时候,产生了行级row-X (SX),在MOVE的时候产生了表级exclusive (X),如果是在生产环境一定得注意这个。

  1. [oracle@oracle10grac1 dbmonitor]$ dblock orcl1 
  2. ACC1 SESSION LOCK STATUS! 
  3.  
  4.     XIDUSN    XIDSLOT     XIDSQN SESSION_ID ORACLE_USE OBJECT_NAME          LOCKED_MODE   OBJECT_TYPE 
  5. ---------- ---------- ---------- ---------- ---------- -------------------- ------------- -------------------- 
  6.          7          1       1079        151 SYS        HTZ2                 row-X (SX)    TABLE 
  7.  
  8. ACC1 SESSION LOCK STATUS! 
  9.  
  10.     XIDUSN    XIDSLOT     XIDSQN SESSION_ID ORACLE_USE OBJECT_NAME          LOCKED_MODE   OBJECT_TYPE 
  11. ---------- ---------- ---------- ---------- ---------- -------------------- ------------- -------------------- 
  12.          5         39       1321        151 SYS        HTZ2                 row-X (SX)    TABLE 
  13.  
  14. ACC1 SESSION LOCK STATUS! 
  15.  
  16. no rows selected 
  17.  
  18. ACC1 SESSION LOCK STATUS! 
  19.  
  20. no rows selected 
  21.  
  22. ACC1 SESSION LOCK STATUS! 
  23.  
  24.     XIDUSN    XIDSLOT     XIDSQN SESSION_ID ORACLE_USE OBJECT_NAME          LOCKED_MODE   OBJECT_TYPE 
  25. ---------- ---------- ---------- ---------- ---------- -------------------- ------------- -------------------- 
  26.          6         19       1303        151 SYS        HTZ1                 exclusive (X) TABLE 

下面查看表与索引的消息:

MOVE的时候表上的索引会自动的不可能用,因为表中的数据是基于ROWID,而在做MOVE的时候ROWID发生了变化。而SHRINK表的时候,会自己更新索引的消息。

  1. [oracle@oracle10grac1 dbmonitor]$ dbtable orcl1 htz1 
  2.  
  3. OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE) 
  4. ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ---------------- 
  5. LUOPING    HTZ1       USERS      2012-05-30 23:54:42      1478     N DEFAULT             1 NO  N    12M                10                953347               10 
  6.  
  7. OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST 
  8. ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ---------- 
  9. LUOPING    htz1       USERS           HTZ1_A          2012-05-30 23:48:22        3328 UNUSABLE DEFAULT    NORMAL         950001 A                        1 
  10. [oracle@oracle10grac1 dbmonitor]$ dbtable orcl1 htz2 
  11.  
  12. OWNER      TABLENAME  TABLESPACE ANALYZED               BLOCKS CACHE BUFFER_POO  INI_TRANS PAR TEMP BYTES        PCT_FREE   PCT_USED   NUM_ROWS LENGTH(A.DEGREE) 
  13. ---------- ---------- ---------- -------------------- -------- ----- ---------- ---------- --- ---- ---------- ---------- ---------- ---------- ---------------- 
  14. LUOPING    HTZ2       USERS      2012-05-30 23:48:00      1630     N DEFAULT             1 NO  N    11.5625M           10                945834               10 
  15.  
  16. OWNER      TABLENAME  TABLESPACENAME  INDEXNAME       ANALYZED               C_FACTOR STATUS   BUFFER_POO INDEX_TYPE   NUM_ROWS COLUMNNAME      COLUMNPOST 
  17. ---------- ---------- --------------- --------------- -------------------- ---------- -------- ---------- ---------- ---------- --------------- ---------- 
  18. LUOPING    htz2       USERS           HTZ2_A          2012-05-30 23:48:10        3328 VALID    DEFAULT    NORMAL         950001 A   

手动对相关索引rebuild online一次

下面查看表占用块的消息:

 

  1. SQL> exec show_space('HTZ1','LUOPING'); 
  2. Unformatted Blocks .....................               0 
  3. FS1 Blocks (0-25) ......................               0 
  4. FS2 Blocks (25-50) .....................               0 
  5. FS3 Blocks (50-75) .....................               0 
  6. FS4 Blocks (75-100).....................               0 
  7. Full Blocks        .....................           1,446 
  8. Total Blocks............................           1,536 
  9. Total Bytes.............................      12,582,912 
  10. Total MBytes............................              12 
  11. Unused Blocks...........................              58 
  12. Unused Bytes............................         475,136 
  13. Last Used Ext FileId....................               4 
  14. Last Used Ext BlockId...................           9,097 
  15. Last Used Block.........................              70 
  16.  
  17. PL/SQL procedure successfully completed. 
  18.  
  19. SQL> exec show_space('HTZ2','LUOPING'); 
  20. Unformatted Blocks .....................               0 
  21. FS1 Blocks (0-25) ......................               0 
  22. FS2 Blocks (25-50) .....................               1 
  23. FS3 Blocks (50-75) .....................               0 
  24. FS4 Blocks (75-100).....................               0 
  25. Full Blocks        .....................           1,441 
  26. Total Blocks............................           1,480 
  27. Total Bytes.............................      12,124,160 
  28. Total MBytes............................              11 
  29. Unused Blocks...........................               6 
  30. Unused Bytes............................          49,152 
  31. Last Used Ext FileId....................               4 
  32. Last Used Ext BlockId...................           6,921 
  33. Last Used Block.........................              66 

 

相关的值已经明显下降。