表碎片起因及解决办法.doc

1、表碎片起因及解决办法跟表碎片有关的基础知识：什么是水线(High Water Mark)? - 所有的 oracle段(segments，在此，为了理解方便，建议把 segment作为表的一个同义词) 都有一个在段内容纳数据的上限，我们把这个上限称为“high water mark“或 HWM。这个 HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个 segment。HWM 通常增长的幅度为一次 5个数据块，原则上 HWM只会增大，不会缩小，即使将表中的数据全部删除，HWM 还是为原值，由于这个特点，使 HWM很象一个水库的历史最高水位，这也就是 HWM的原始含义，当然不能说一个水

2、库没水了，就说该水库的历史最高水位为 0。但是如果我们在表上使用了 truncate命令，则该表的 HWM会被重新置为 0。HWM数据库的操作有如下影响：a) 全表扫描通常要读出直到 HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。b) 即使 HWM以下有空闲的数据库块，键入在插入数据时使用了 append关键字，则在插入时使用 HWM以上的数据块，此时 HWM会自动增大。如何知道一个表的 HWM？a) 首先对表进行分析: ANALYZE TABLE ESTIMATE/COMPUTE STATISTICS;b) SELECT blocks, empty_blocks, num_ro

3、ws FROM user_tables WHERE table_name = ; BLOCKS 列代表该表中曾经使用过得数据库块的数目，即水线。 EMPTY_BLOCKS 代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块。让我们以一个有 28672行的 BIG_EMP1表为例进行说明：1) SQL SELECT segment_name,segment_type,blocks FROM dba_segments WHERE segment_name=BIG_EMP1; SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS - - - - BIG_EM

4、P1 TABLE 1024 2 1 row selected.2) SQL ANALYZE TABLE big_emp1 ESTIMATE STATISTICS; Statement processed. 3) SQL SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name=BIG_EMP1; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS - - - - BIG_EMP1 28672 700 323 1 row selected. 注意：BLOCKS +

5、EMPTY_BLOCKS (700+323=1023)比 DBA_SEGMENTS.BLOCKS少个数据库块，这是因为有一个数据库块被保留用作 segment header。DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。USER_TABLES.BLOCKS表示已经使用过的数据库块的数目。4) SQL SELECT COUNT (DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)| DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) “Used“ FROM big_emp1; Used - 700 1

6、 row selected. 5) SQL DELETE from big_emp1; 28672 rows processed. 6) SQL commit; Statement processed. 7) SQL ANALYZE TABLE big_emp1 ESTIMATE STATISTICS; Statement processed. 8) SQL SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE table_name=BIG_EMP1; TABLE_NAME NUM_ROWS BLOCKS E

7、MPTY_BLOCKS - - - - BIG_EMP1 0 700 323 1 row selected. 9) SQL SELECT COUNT (DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)| DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) “Used“ FROM big_emp1; Used - 0 - 这表名没有任何数据库块容纳数据，即表中无数据1 row selected. 10) SQL TRUNCATE TABLE big_emp1; Statement processed. 11) SQL ANALYZE

8、 TABLE big_emp1 ESTIMATE STATISTICS; Statement processed. 12) SQL SELECT table_name,num_rows,blocks,empty_blocks 2 FROM user_tables 3 WHERE table_name=BIG_EMP1; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS - - - - BIG_EMP1 0 0 511 1 row selected. 13) SQL SELECT segment_name,segment_type,blocks FROM dba_s

9、egments WHERE segment_name=BIG_EMP1; SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS - - - - BIG_EMP1 TABLE 512 1 1 row selected. 注意: TRUNCATE命令回收了由 delete命令产生的空闲空间，注意该表分配的空间由原先的 1024块降为 512块。为了保留由 delete命令产生的空闲空间，可以使用 TRUNCATE TABLE big_emp1 REUSE STORAGE 用此命令后，该表还会是原先的 1024块。行链接(Row chaining) 与行迁移(Row Mi

10、gration)当一行的数据过长而不能插入一个单个数据块中时，可能发生两种事情：行链接(row chaining)或行迁移(row migration)。 行链接当第一次插入行时，由于行太长而不能容纳在一个数据块中时，就会发生行链接。在这种情况下，oracle 会使用与该块链接的一块或多块数据块来容纳该行的数据。行连接经常在插入比较大的行时才会发生，如包含 long, long row, lob等类型的数据。在这些情况下行链接是不可避免的。行迁移 当修改不是行链接的行时，当修改后的行长度大于修改前的行长度，并且该数据块中的空闲空间已经比较小而不能完全容纳该行的数据时，就会发生行迁移。在这种情况

11、下，Oracle 会将整行的数据迁移到一个新的数据块上，而将该行原先的空间只放一个指针，指向该行的新的位置，并且该行原先空间的剩余空间不再被数据库使用，这些剩余的空间我们将其称之为空洞，这就是产生表碎片的主要原因，表碎片基本上也是不可避免的，但是我们可以将其降到一个我们可以接受的程度。注意，即使发生了行迁移，发生了行迁移的行的 rowid 还是不会变化，这也是行迁移会引起数据库 I/O性能降低的原因。其实行迁移是行链接的一种特殊形式，但是它的起因与行为跟行链接有很大不同，所以一般把它从行链接中独立出来，单独进行处理。行链接和行迁移引起数据库性能下降的原因：引起性能下降的原因主要是由于引起多余的

12、 I/O造成的。当通过索引访问已有行迁移现象的行时，数据库必须扫描一个以上的数据块才能检索到改行的数据。这主要有一下两种表现形式：1) 导致 row migration 或 row chaining INSERT 或 UPDATE 语句的性能比较差，因为它们需要执行额外的处理2) 利用索引查询已经链接或迁移的行的 select语句性能比较差，因为它们要执行额外的 I/O如何才能检测到行迁移与行链接：在表中被迁移或被链接的行可以通过带 list chained rows选项的 analyze语句识别出来。这个命令收集每个被迁移或链接的行的信息，并将这些信息放到指定的输出表中。为了创建这个输出表，

13、运行脚本 UTLCHAIN.SQL。SQL ANALYZE TABLE scott.emp LIST CHAINED ROWS; SQL SELECT * FROM chained_rows; 当然你也可以通过检查 v$sysstat视图中的table fetch continued row来检查被迁移或被链接的行。SQL SELECT name, value FROM v$sysstat WHERE name = table fetch continued row; NAME VALUE - - table fetch continued row 308 尽管行迁移与行链接是两个不同的事情，

14、但是在 oracle内部，它们被当作一回事。所以当你检测行迁移与行链接时，你应该仔细的分析当前你正在处理的是行迁移还是行链接。解决办法o 在大多数情况下，行链接是无法克服的，特别是在一个表包含象 LONGS, LOBs 等这样的列时。当在不同的表中有大量的链接行，并且哪些表的行的长度不是很长时，你可以通过用更大的 block size重建数据库的方法来解决它。 例如：当前你的数据库的数据块的大小为 4K，但是你的行的平均长度为 6k，那么你可以通过用 8k大小的数据块来重建数据库的办法解决行链接现象。o 行迁移主要是由于设置的 PCTFREE参数过小，导致没有给 update操作留下足够的空闲

15、空间引起。为了避免行迁移，所有被修改的表应该设置合适的PCTFREE 值，以便在每个数据块内为数据修改保留足够的空间。可以通过增加PCTFREE值的办法来避免行迁移，但这种解决办法是以牺牲更多的空间为代价的，这也就是我们通常所说的以空间换效率。 而且通过增加 PCTFREE值的办法只能缓解行迁移现象，而不能完全解决行迁移，所以较好的办法是在设置了合适的 PCTFREE值的后，在发现行迁移现象比较严重时，对表的数据进行重组。下面是对行迁移数据进行重组的步骤(这种方法也被成为 CTAS):- Get the name of the table with migrated rows: ACCEPT

16、table_name PROMPT Enter the name of the table with migrated rows: - Clean up from last execution set echo off DROP TABLE migrated_rows; DROP TABLE chained_rows; - Create the CHAINED_ROWS table ./rdbms/admin/utlchain.sql set echo on spool fix_mig - List the chained and migrated rows ANALYZE TABLE - C

17、opy the chained/migrated rows to another table create table migrated_rows as SELECT orig.* FROM - Delete the chained/migrated rows from the original table DELETE FROM - Copy the chained/migrated rows back into the original table INSERT INTO spool off 当对一个表进行全表扫描时，我们实际上忽略行迁移中各个指向其它行的指针，因为我们知道，全表扫描会遍历

18、全表，最终会读到发生行迁移的行的行数据，在此时才会处理这些行数据。因此，在全表扫描中，行迁移不会引发其它额外的工作。当通过索引读一个表的数据时，被迁移的行会引起额外的 I/O操作。这是因为从所引中我们会读到数据行的 rowid，它告诉数据库到指定文件的指定数据块的指定 slot上可以找到需要的数据，但是因为发生了行迁移，此处只存放一个指向数据的指针，而不是真正的数据，所以数据库又需要根据该指针(类似rowid)到指定文件的指定数据块的指定 slot上去找真正的数据，重复上面的过程，知道找到真正的数据。我们可以看出，这会引入额外的 I/O操作。发现有严重表碎片的表的步骤：表需要整理原因有 2:a

19、) 有太多的 migration rowsb) 表经过删除数据后有大量的空块, 而全表扫描时，仍需要读这些空块发现需要 reorganization的表，需要从表的实际使用的空间与表的 hwm入手首先分析表：Alter table emp compute statistics.然后可以查询出有数据的数据块的个数:For ORACLE 7: SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)| SUBSTR(rowid,1,8) “Used“ FROM schema.table; For ORACLE 8+: SELECT COUNT (DISTINCT DBMS_

20、ROWID.ROWID_BLOCK_NUMBER(rowid)| DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) “Used“ FROM schema.table; or SELECT COUNT (DISTINCT SUBSTR(rowid,1,15) “Used“ FROM schema.table; 查询出 HWM以下的数据块的个数(可能由于 delete, 数据块中并不包含数据):This will update the table statistics. After generating the statistics, to determine the hi

21、gh water mark: SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = ; 下面给出一个综合的 sql语句，它可以查询出浪费空间的表(浪费超过 25%)，而且还计算出其它信息(使用时根据具体情况修改 where子句中的blocks，owner 限制条件)：SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE, GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HW

22、M,1),1) ), 2), 0) WASTE_PER,ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,N,Y) CAN_EXTEND_SPACE, NEXT_EXTENT, MAX_FREE_SPACE,O_TABLESPACE_NAME TAB

23、LESPACE_NAMEFROM (SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,DECODE( ROUND(B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)/C.BLOCKSIZE, 0),0, 1,ROUND(B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FRE

24、E/100)/C.BLOCKSIZE, 0) + 2 AVG_USED_BLOCKS,ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1), 2) CHAIN_PER,ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS,A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAMEFROM SYS.DBA_

25、SEGMENTS A,SYS.DBA_TABLES B,SYS.TS$ CWHERE A.OWNER =B.OWNER and SEGMENT_NAME = TABLE_NAME andSEGMENT_TYPE = TABLE ANDB.TABLESPACE_NAME = C.NAMEUNION ALLSELECT A.OWNER OWNER, SEGMENT_NAME | . | B.PARTITION_NAME, SEGMENT_TYPE, BYTES,B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS -

26、B.EMPTY_BLOCKS - 1 HWM,DECODE( ROUND(B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)/C.BLOCKSIZE, 0),0, 1,ROUND(B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)/C.BLOCKSIZE, 0) + 2 AVG_USED_BLOCKS,ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1), 2) CHAIN_PER,ROUND(100 * (A.EXTEN

27、TS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS, A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,B.TABLESPACE_NAME O_TABLESPACE_NAMEFROM SYS.DBA_SEGMENTS A,SYS.DBA_TAB_PARTITIONS B,SYS.TS$ C,SYS.DBA_TABLES DWHERE A.OWNER = B.TABLE_OWNER and SEGMENT_NAME = B.TABLE_NAME andSEGMENT_TYPE = TABLE PARTI

28、TION ANDB.TABLESPACE_NAME = C.NAME ANDD.OWNER = B.TABLE_OWNER ANDD.TABLE_NAME = B.TABLE_NAME ANDA.PARTITION_NAME = B.PARTITION_NAME),(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES)MAX_FREE_SPACEFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME)WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME ANDGREATE

29、ST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) 25AND OWNER = ? AND BLOCKS 128ORDER BY 10 DESC, 1 ASC, 2 ASC;各列的说明：WASTE_PER：已分配空间中水线以下的空闲空间(即浪费空间)的百分比。TABLE_KB：该表目前已经分配的所有空间的大小，以 k为单位。NUM_ROWS：在在表中数据的行数BLOCKS：该表目前已经分配的数据块的块数，包含水线以上的部分EMPTY_BLOCKS：已分配空间中水线以上的空闲空间HIGHWATER_MARK：目前的水线AVG_USED_BLOCKS：理想情况下(没有行迁移)，该表数据应该占用的数据块的个数CHAIN_PER：发生行迁移现象的行占总行的比率EXTENTS：该表目前已经分配的 extent数MAX_EXTENTS：该表可以分配的最大 extent的个数ALLO_EXTENT_PER：目前已分配的 extent的个数占可以分配最大 extent的比率CAN_EXTEND_SPACE：是否可以分配下一个 extentNEXT_EXTENT：下一个 extent的大小MAX_FREE_SPACE：表的已分配空间中最大的空闲空间