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%的存储空间。