当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

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 lob (pic) store as securefile
(tablespace thams disable storage in row 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.com.cn/2011/09/08/%E6%88%91%E6%89%80%E4%BA%86%E8%A7%A3%E7%9A%84oracle11g%E6%96%B0%E7%89%B9%E6%80%A7securefiles/)。
上面的建表语句用到了disable storage in row和pctversion参数,下面解释下这两个参数的含义:
disable storage in row:将LOB字段和表的其他字段不放到一起存放,也就是前文说到的将LOB字段存放到行外。
enable storage in row:将LOB字段和表的其他字段存放到一起。
pctversion:LOB字段不写回滚段,利用pctversion参数来指定在LOB的存储空间中拿百分之多少的空间来存放旧的镜像来提供读一致性,上文指定10%的存储空间。

本文固定链接: http://www.dbdream.com.cn/2011/12/pctfree%e5%92%8cpctused%e5%ad%a6%e4%b9%a0/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2011年12月20日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: PCTFREE和PCTUSED及将LOB字段存放到行外 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , , ,

PCTFREE和PCTUSED及将LOB字段存放到行外:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter