PCTFREE和PCTUSED及将LOB存到行外
今天的ADDM报告建议重建存放图片的表并增大PCTFREE，这可能和程序的逻辑有关，这套程序是先INSERT一条记录，但是此时LOB字段是空的，然后UPDATE这条记录，将图片加载到这条记录上。
查找结果 1: 缓冲区忙 - 热对象
受影响的是 .02 个活动会话, 占总活动的 3.46\%。
------------------------------
对数据库块的读写争用消耗了大量数据库时间。
?? 建议案 1: 方案更改
?? 估计的收益为 .02 个活动会话, 占总活动的 3.46\%。
?? -------------------------------
?? 操作
????? 考虑使用更大的 PCTFREE 值重建 LOB "DIGITAL.SYS_LOB0000075645C00004$$" (对象 ID 为 75646)。
????? 相关对象
???????? ID 为 75646 的数据库对象。
?? 原理
????? SQL_ID 为 "akqx47xp7tr8c" 的 UPDATE 语句受到 "缓冲区忙" 等待的严重影响。
????? 相关对象
???????? SQL_ID 为 akqx47xp7tr8c 的 SQL 语句。
???????? update IMAGE set IMAGES=:1 where FILE_PATH=:2
?? 导致查找结果的故障现象:
?? ------------
????? 对数据库块的读写争用消耗了大量数据库时间。
????? 受影响的是 .02 个活动会话, 占总活动的 3.46\%。
???????? 等待类 "并发" 消耗了大量数据库时间。
???????? 受影响的是 .02 个活动会话, 占总活动的 3.46\%。
??? 关于PCTFREE和PCTUSED，很多时候在建表的时候都不会特意指定，使用的都是默认值，PCTFREE默认是10，PCTUSED默认是40。
SQL> create table tttt (id number,name varchar2(10));
表已创建。
SQL> select dbms_metadata.get_ddl('TABLE','TTTT','STREAM') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','TTTT','STREAM')
--------------------------------------
? CREATE TABLE "STREAM"."TTTT"
?? (??? "ID" NUMBER,
??????? "NAME" VARCHAR2(10)
?? ) SEGMENT CREATION DEFERRED
? PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
? TABLESPACE "USERS"
??? 也可以通过查看USER_TABLES视图查看PCTFREE和PCTUSED的值，但是通常PCTUSER都是空。
SQL> select table_name,pct_free,pct_used from user_tables where table_name ='TTTT';
TABLE_NAME??????? ???????????????PCT_FREE?? PCT_USED
------------------------------ ---------- ----------
TTTT?????????????????????????????????? 10
??? 可以在建表的时候指定PCTFREE和PCTUSED的值，也可以通过ALTER TABLE语句修改表的PCTFREE和PCTUSED的值。
SQL> create table tttt (id number,name varchar2(10)) pctfree 20 pctused 50;
表已创建。
SQL> alter table tttt pctfree 30 pctused 40;
表已更改。
??? 为了避免行迁移和行链接和均衡进出freelist的频率，通常会设置PCTFREE和PCTUSED。ORACLE通过牺牲空间来避免行迁移和行链接，也就是PCTFREE，主要是对UPDATE操作影响比较大，比如，一条含有VARCHAR2字段类型的记录，已经将近有了一个BLOCK，此时UPDATE操作将VARCHAR2的字段增大，这条记录可能超出了一个BLOCK，这是就会产生行链接，如果当这条记录的大小并没有超过一个BLOCK，但是这个BLOCK被其他的记录占用了一部分，此时如果UPDATE这条记录使这个BLOCK已经存不下这条记录，就会产生行迁移， PCTFREE被我理解成是ORACLE预留给UPDATE操作的空间，比如，PCTFREE的值是10，那么这个段上的每个数据换在新INSERT进数据的时候，都会预留10%的空间，以最大可能减少由于UPDATE操作产生的行迁移和行链接情况，如果UPDATE经常使记录变大，建议适当增大PCTFREE的值。PCTUSED主要是对INSERT影响较大，比如PCTUSED的值为40，那么只有这个数据块使用率没有超过整个数据块的40%，才会将数据INSERT到这个数据块上，否则INSERT的数据将插入到新的BLOCK，较大的PCTFREE比较合适频繁更新的操作，因为如果更新是行记录变大，也不容易发生行迁移，而且会大大利用PCTFREE的空间不至于浪费，如果更新是行记录变小，还可以便于INSERT操作，较小的PCTFREE一般适合静态表或者只读的表，这样可以减少磁盘空间的浪费。
??? PCTFREE的默认值是10，较大的值一般在20-25，较小的值一般是4-5，PCTUSED的默认值是40，较大值一般是50，PCTFREE和PCTUSED的和一般不要超过90，那样ORACLE会将更多地时间花费在处理空间利用上，如果插入行后，更新操作会增加已有行的长度，建议将PCTFREE设置20，PCTFREE设置40，如果插入行后，更新操作不会增加已有行的长度，建议将PCTFREE设置10，PCTFREE设置50，如果是只读或静态表，建议将PCTFREE设置5，PCTUSED设置40。
??? 但是本案例修改PCTFREE并不能解决问题，这个数据库的BLOCK_SIZE是16K，但是LOB字段中存的图片大小是2MB，要解决这个问题就需要将LOB存放在行外，通常LOB字段都相对较大，建表的时候就需要将LOB字段存放到行外，不和其他字段一起存放，也可以将LOB存到其他的表空间来提高性能。
SQL> create table stream(id number,name varchar2(10),pic blob) tablespace users
? 2? lob (pic) store as securefile (tablespace thams disable storage in row
? 3? pctversion 10);
表已创建。
???????? 查看表存放的表空间信息。
SQL> select table_name,tablespace_name from user_tables where table_name='STREAM';
TABLE_NAME???????????????????? TABLESPACE_NAME
------------------------------ ------------------------------
STREAM???????????????????????? USERS
???????? 查看LOB字段存放的表空间信息。
SQL> SELECT TABLE_NAME,COLUMN_NAME,TABLESPACE_NAME FROM USER_LOBS WHERE TABLE_NAME='STREAM';
TABLE_NAME COLUMN_NAM TABLESPACE_NAME
---------- ---------- ---------------
STREAM???? PIC??????? THAMS
???????? 本案例用到了11g的新特性SECUREFILES，可以参考我之前写过的关于SECUREFILES的文章，（http://www.dbdream.org/?p=22）。
??? 上面的建表语句用到了disable storage in row和pctversion参数，下面解释下这两个参数的含义：
??? disable storage in row：将LOB字段和表的其他字段不放到一起存放，也就是前文说到的将LOB字段存放到行外。
??? enable storage in row：将LOB字段和表的其他字段存放到一起。
??? pctversion：LOB字段不写回滚段，利用pctversion参数来指定在LOB的存储空间中拿百分之多少的空间来存放旧的镜像来提供读一致性，上文指定10%的存储空间。
?
