ORACLE]关于delete释放表存储空间的中度研究
(2010-11-17 16:47:10)
转载▼
| 标签： | 分类： |
在给QA部门做Oracle基础原理分享时，有位同学提问，他说他在测试的时候造了一批数据，例如有1000万，然后按照一定的条件删掉了100万，接着又新插入了100万，结果却报表空间不足的错误。他的疑问是新插入的100万难道不能重用之前删除的100万的存储空间吗？
我这里由浅入深地按照问答的形式来进行研究解答。 1.insert操作能否重用delete操作释放的表存储空间？答案是肯定的，如果Oracle连这一点都做不到那现在肯定早已关门大吉了。简单实验如下： ETL@RACTEST> create table ttt1 (a char(2000)); Table created. ETL@RACTEST> Select owner,segment_name,Sum(bytes)/1024/1024||'MB' as sizes 2 From dba_extents 3 WHERE 4 segment_name='TTT1' 5 Group By owner,segment_name 6 ORDER BY 3 DESC; OWNER SEGME SIZES ----- ----- ------------ ETL TTT1 .0625MB我创建了一个空表，这个空表初始占了0 .0625MB。 ETL@RACTEST> insert into ttt1 select 'a' from dual connect by level<=100; 100 rows created. Elapsed: 00:00:00.00 ETL@RACTEST> commit; Commit complete. Elapsed: 00:00:00.00 ETL@RACTEST> Select owner,segment_name,Sum(bytes)/1024/1024||'MB' as sizes 2 From dba_extents 3 WHERE 4 segment_name='TTT1' 5 Group By owner,segment_name 6 ORDER BY 3 DESC; OWNER SEGME SIZES ----- ----- ------------------------------------------ ETL TTT1 .3125MB然后插入100行数据，插入以后表的存储大小是0.3125MB。 ETL@RACTEST> delete from ttt1; 100 rows deleted. Elapsed: 00:00:00.01 ETL@RACTEST> commit; Commit complete. Elapsed: 00:00:00.00 ETL@RACTEST> Select owner,segment_name,Sum(bytes)/1024/1024||'MB' as sizes 2 From dba_extents 3 WHERE 4 segment_name='TTT1' 5 Group By owner,segment_name 6 ORDER BY 3 DESC; OWNER SEGME SIZES ----- ----- ------------------------------------------ ETL TTT1 .3125MB我将表里的数据全部清空，用的是delete，这时查表还是占用了0.3125MB，因为delete不会改变表的高水标记，因此虽然表里面没有数据，但是还占用着数据块。这里稍微说明一下，高水标记除了truncate操作以外，只会增大，不会减小。虽然高水标记里的块可能有空块，这个块也要被放到freelist中，但是这个块只能被重用，也就是说它只能被这个表使用，而不能彻底释放给别的数据对象使用，这点要清楚。 ETL@RACTEST> insert into ttt1 select 'a' from dual connect by level<=100; 100 rows created. Elapsed: 00:00:00.01 ETL@RACTEST> commit; Commit complete. Elapsed: 00:00:00.00 ETL@RACTEST> Select owner,segment_name,Sum(bytes)/1024/1024||'MB' as sizes 2 From dba_extents 3 WHERE 4 segment_name='TTT1' 5 Group By owner,segment_name 6 ORDER BY 3 DESC; OWNER SEGME SIZES ----- ----- ------------------------------------------ ETL TTT1 .3125MB我重新插入100条数据，可以看到表占用的空间还是0.3125MB，说明delete释放掉的表存储空间是可以被重用的。根据我上面的说明，高水标记只能增大不能减小，可以判定新插入的100条数据用的是之前的块，而没有申请新块，因为如果申请了新块，高水标记会增长，那么表占用的存储空间也会增长。 2.Jack提的问题，为什么删除100万，再插入100万会报表空间不足。有几个原因，我这里先讲两个简单的，下面再讲主要原因。第一个原因：退一万步讲，新插入的100万数据通常和之前删除的100万数据不完全一样，有可能新插的100万行数据比之前删除的100万行数据占用的存储空间多，这是正常的。例如一个字段定义为varchar2(4000)，那么存'a'和存'abcdefg'所占用的存储空间就不同。第二个原因：表的一个数据块有可利用的存储空间后会被放入freelist链表中，通常会被放到链尾。而申请新块时可能从freelist的链头申请，因此可能不用之前释放的块而申请新块，因为高水标记只能增长不能减小，因此表的存储占用也只会增长不会减小，这样报表空间不足也是可能的。 3.是不是delete释放的存储一定会被重用？答案是否定的，先举个例子，后面再说明原因。 ETL@RACTEST> create table ttt5(a char(800),b CHAR(2)); Table created. ETL@RACTEST> insert into ttt5 select 'a',level from dual connect by level<=20; 20 rows created. Elapsed: 00:00:00.00 ETL@RACTEST> commit; Commit complete. ETL@RACTEST> select b,dbms_rowid.rowid_block_number(rowid) rn from ttt5 order by to_number(b); B RN -- ---------- 1 47016 2 47016 3 47016 4 47016 5 47016 6 47016 7 47016 8 47016 9 47012 10 47012 11 47012 12 47012 13 47012 14 47012 15 47012 16 47012 17 47013 18 47013 19 47013 20 47013 20 rows selected.我创建了一个表，插入20条数据，RN是块的序号，可以看到一个块可以存储8条数据，47013块没有被占满。 ETL@RACTEST> ETL@RACTEST> delete from ttt5 where b<=2; 2 rows deleted. Elapsed: 00:00:00.00 ETL@RACTEST> commit; Commit complete.之后我删除掉2条数据。 ETL@RACTEST> select b,dbms_rowid.rowid_block_number(rowid) rn from ttt5 order by to_number(b); B RN -- ---------- 3 47016 4 47016 5 47016 6 47016 7 47016 8 47016 9 47012 10 47012 11 47012 12 47012 13 47012 14 47012 15 47012 16 47012 17 47013 18 47013 19 47013 20 47013 18 rows selected.再查可以看到47016块的两条数据已经被释放掉了。 ETL@RACTEST> insert into ttt5 select 'a','b' from dual connect by level<=10000; 10000 rows created. Elapsed: 00:00:00.08 ETL@RACTEST> commit; Commit complete.然后我向表插入大量数据。 ETL@RACTEST> select b,dbms_rowid.rowid_block_number(rowid) rn from ttt5 where dbms_rowid.rowid_block_number(rowid)=47016; B RN -- ---------- 3 47016 4 47016 5 47016 6 47016 7 47016 8 47016 6 rows selected.可以看到47016块并没有被重用。 4.那delete释放的存储空间什么时候会被重用？再举个例子，建表操作都一样： ETL@RACTEST> create table ttt4(a char(800),b CHAR(2)); Table created. Elapsed: 00:00:00.00 ETL@RACTEST> ETL@RACTEST> insert into ttt4 select 'a',level from dual connect by level<=20; 20 rows created. Elapsed: 00:00:00.00 ETL@RACTEST> ETL@RACTEST> commit; Commit complete. Elapsed: 00:00:00.00 ETL@RACTEST> select b,dbms_rowid.rowid_block_number(rowid) rn from ttt4 order by to_number(b); B RN -- ---------- 1 47000 2 47000 3 47000 4 47000 5 47000 6 47000 7 47000 8 47000 9 46996 10 46996 11 46996 12 46996 13 46996 14 46996 15 46996 16 46996 17 46997 18 46997 19 46997 20 46997 20 rows selected.这时候我删除3行而不是2行 ETL@RACTEST> delete from ttt4 where b<=3; 3 rows deleted. Elapsed: 00:00:00.00 ETL@RACTEST> commit; Commit complete.然后还是插入大量数据 ETL@RACTEST> insert into ttt4 select 'a','b' from dual connect by level<=10000; 10000 rows created. Elapsed: 00:00:00.49 ETL@RACTEST> commit; Commit complete.再查一下： ETL@RACTEST> select b,dbms_rowid.rowid_block_number(rowid) rn from ttt4 where dbms_rowid.rowid_block_number(rowid)=47000; B RN -- ---------- b 47000 b 47000 3 47000 4 47000 5 47000 6 47000 7 47000 8 47000可以看到这时47000块被重用了！好，我解释一下原因。原来一个表有两个参数：PCT_FREE、PCT_USED。PCT_FREE大致为一个块剩余空间占总块的比重，PCT_USED大致为一个块使用的空间占总块的比重。我们来看一下： ETL@RACTEST> select dbms_metadata.get_ddl('TABLE','TTT4','ETL') from dual; DBMS_METADATA.GET_DDL('TABLE','TTT4','ETL') -------------------------------------------------------------------------------- CREATE TABLE "ETL"."TTT4" ( "A" CHAR(800), "B" CHAR(2) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TBS_DW_ADMIN_00"可以看到TTT4表在建表时没有指定PCTFREE 和PCTUSED ，默认的值为PCTFREE = 10 ，PCTUSED = 40 。这两个参数有什么用呢？ITPUB上有人这样解释： INSERT、UPDATE的时候如果BLOCK剩余空间小于PCT_FREE相对应的大小，就从FREELIST中去掉，如果DELETE的时候小于PCT_USED相对应的大小，就加入FREELIST中。这样就应该很明确了吧，上面的实验删除TTT5的2行时，块使用的空间占总空间的比重仍然大于40%，所以块没有被放到freelist中，而TTT4的表删了3行，块使用的空间占总空间的比重小于40%了，所以被放到freelist中，就可以被重用了。好了，真相大白了！最后给个建议，Oracle的这两个默认参数并不一定在任何情况下都是最理想的。例如如果我们的表从来不做update操作，那么在建表时PCT_FREE可以设置为0，如果我们想充分利用delete释放的空间，那么PCTUSED可以设置得高一点，70,80,甚至90都可以！