3月18日 理解表的存储结构 （2）PCTFREE和行迁移
昨天我们了解了表的基本结构，从表的存储结构上我们讨论了几个建表的参数。实际上在绝大多数项目中，开发团队并没有对表进行合理的设计，从而导致了系统上线后出现大量的性能问题。
一个最常见的问题就是行链和行迁移。行链的出现在绝大多数系统中是由于我们的设计不合理，比如说某张表的行长度超过了一个数据块的大小，那么这个表的部分行就会出现行链。这种行链的出现实际上是设计者选择了不合理的数据块大小导致的。从Oracle 9i开始，不同的表空间可以使用不同的块大小，因此我们完全可以设计块大小较大的表空间来存放这些表。
和行链不同，有些行链是不可避免的，绝大多数的行迁移是可以避免的。行迁移是怎么产生的呢？Oracle数据库支持varchar字段，varchar字段使用极为灵活，但是正是灵活的varchar字段导致了行迁移的产生。在创建一张表的时候，我们一般不会太关注PCTFREE这个参数。这个参数的缺省值是10%的含义是，当某个数据块的使用率小于（100%-PCTFREE）这个百分比的时候，这个数据块是可以被插入数据的，一旦块使用率达到了这个指标，这个数���块就不能再插入数据了。Oracle预留这部分空间的目的就是为VARCAHR字段的扩展提供空间。大家是不是还记得，Oracle的数据块是从块的底部开始使用的，空闲空间在块头和数据之间。
下面我们通过一个例子来说明表数据的存储，我们首先创建一张测试表，并且插入3条记录：
create table test1 ( a integer,b varchar2(100),c varchar2(100));
insert into test1 values (1,null,'aaaa');
insert into test1 values (2,null,'bbbb');
insert into test1 values (1,'11111',null);
commit;
然后我们查找一下这个EXTENT所在的位置：
SQL> select extent_id,file_id,block_id from dba_extents where segment_name='TEST1' AND OWNER='SCOTT';
EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
0 10 497
我们先查找一下10号文件是什么：
SQL>select file_name from dba_data_files where file_id=10;
FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/orcl/users02.dbf
下面通过dd命令将499这个数据块dump出来：
dd if=/opt/oracle/oradata/orcl/users02.dbf of=a.dmp bs=8192 skip=501 count=1
上述命令从文件中拷贝出了第501号块，这个块就是存储这三条记录的数据块，我们来看这个数据块的尾部：
其中2c 01 03 02 c1 02 FF 04 61 61 61 61就是我们插入的第一条记录（1,null,’aaaa’），2c是行头，这是一个标准的数据行，01表示该行使用了1号ITL槽,03表示这一条记录共有多少个字段（本例子有3个字段），02 C1 02是第一个字段的值，这个字段是数值类型，02表示该字段的长度。C1 02就是10进制的1。后面的FF表示第二个字段是空值，后面的04 61 61 61 61是最后一个字段aaaa。从第3条记录来看（最上面的那条记录，地址是00001fd0h开始的那条2C 01 02开头的），我们知道每一行的第三个字节表示这一行的字段数量，什么这个一行只有2个字段呢？我们可以看看前面的INSERT语句，这一条记录的最后一个字段是null，如果某一行的最后几个字段都市NULL，那么Oracle在存储的时候，直接省略了这些NULL的字段，以节约存储空间。
如果在这种情况下，如果我们执行“UPDATE TEST1 SET B=’ABC’ WHERE A=1”，会出现什么情况呢？这个时候数据块会重组，在第一条记录的原本只有一个字节“FF”的地方插入三个字节，同时这个数据块的数据占用的顶部也上升了。
从上面的例子我们可以看出，一旦VARCHAR字段发生改变，需要在数据块中额外分配空间，因此如果我们要UPDATE某条记录的时候，发现这个数据块已经满了，在原有的数据块中就无法存储这条记录了。那么这条记录就被迫迁移到别的数据块中，而在这条记录原本存放的位置，放入一个指针。在这种情况下，如果我们要访问这条记录，就需要读取了这个指针后，再访问另外一个数据块。行迁移的存在降低了数据访问的性能。也许有朋友要问，为什么把原来的行迁移走了还要留个指针干什么？不留指针不是不会存在这个性能隐患吗？我们可能忘记了一点，可能这张表存在几个索引，如果我们将这一行直接迁移而不留下指针，那么所有索引中和这一行相关的数据都需要进行重组，这个重组的代价远高于行迁移访问的开销。下面我们做一个实验：
首先我们将这张表插入一定的数据，为了证明在某些情况下，我们使用缺省的10%的PCTFREE是多么危险，我们将表的PCTFREE设置为10%，然后TRUNCATE原有的表，重新插入数据：
drop sequence seqt;
create sequence seqt;
alter table test1 pctfree 10;
truncate table test1;
begin
for i in 1..1000 loop
insert into test1 values (seqt.nextval,'abc',null);
end loop;
end;
/
commit;
这个时候我们执行查询语句
SQL> select a,b from test1 where a=1;
A B
---------- --------------------------------------------------------------------------------
1 abc
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 12 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST1 | 2 | 12 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
459 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由于我们没建索引，所以这个查询走了全表扫描，产生了8个CR GET。下面我们通过UPDATE字段使数据块产生行迁移：
update test1 set c='123456789012345678901234567890abcdefghg';
commit;
这个时候我们再来看数据块中发生了什么：
我们看到了部分原来是数据行的地方变成了一些类似20 02 00 02 80 01 f7 00 04这样的数据，这就是发生了行迁移，这一行已经被迁移到RDBA=0x028001f7的数据块中的0x04这一行中了。这个时候如果访问这条数据，就需要再到0x028001f7做一次查询才能完成。我们再来执行刚才的SELECT语句，看看发生了什么变化：
SQL> set autotrace on;
SQL> col b format a50 trunc
SQL> set line 132
SQL> select a,b from test1 where a=1;
A B
---------- --------------------------------------------------
1 abc
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 12 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST1 | 2 | 12 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
459 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们看到由于行迁移，原本8个CR GET变成了17个CR GET，看样子行迁移的负面作用还是挺明显的。当然这个例子是个特例，做了全表扫描，但是行迁移会产生负面影响是肯定的。
了解了行迁移的产生原因，以及危害性后，我们再回过头来认真考虑一下PCTFREE这个参数就会发现，这样一个小小的参数，里面包含了数据库优化的大道理。如果一张表中的数据插入后经常需要进行UPDATE，那么我们必须要把PCTFREE参数设置的大一些，以避免行迁移的出现。如果一张表插入数据后不做修改和删除，那么我们是不是可以把PCTFREE设置的小一些，比如5，甚至更小，这样的话这张表的每一个数据块中可以包含更多的记录，从而减少访问这张表带来的开销。而如果有一张表，数据块的热块冲突��严重，我们也是否可以通过加大PCTFREE来减少每个数据块中的记录数，从而缓解热块冲突呢？实际上，减少热块冲突的更好的办法是将这张表放在B LOCK_SIZE较小的表空间里，不过在实际生产环境中我们往往难以这么幸运，当发现热块冲突存在的时候，我们只能通过权宜之计来解决问题了。
在调整PCTFREE的时候，我们要注意的是，这个参数是可以动态调整的，但是我们调整PCTFREE参数只能对新的数据插入起作用，对于已经填充过满的老数据块，是无法起作用的，要想彻底解决行迁移的问题，必须调整参数后，对表进行重组，才能对表中的所有数据块都起作用。对表重组的办法有很多，比如ALTER TABLE ... MOVE 或者EXP/IMP。
 m1.png (2010-03-20 08:25, 0.005 M)
该附件被下载的次数 0
|
m2.png (2010-03-20 08:27, 0.011 M)
该附件被下载的次数 0
|
给他（她）留言 | 相册 | 回到专栏 | 管理 |
登录
博客首页
