本文主要是通过对底层数据块进行 DUMP 分析的方式，介绍了 varchar 如何被自动存储成了 lob 类型，该情况在某些业务场景下会产生大量的 lob 读写操作，从而造成数据库性能问题。本文不会对 varchar 以及 lob 的使用和存储机制做过多赘述。
我们知道，在 Oracle 数据库中常使用 varchar2 来存储字符串，而使用 lob 来存储一些较大的数据对象，比如二进制或十六进制的信息文件、图像、音频等，都可以使用 lob 类型进行存储。
varchar2 是以字节 byte 为单位存储在数据块中，而 lob 在存储的时候，若 lob 列数据小于 4000 bytes 时，则 Oracle 会将其和本行的其他列一起，存储在本行的数据块中；若 lob 列数据大于 4000 bytes,则 Oracle 会将其保存在 logsegment 中，在数据行该 lob 列的位置上会存储一个指针信息，记录实际存储该 lob 列数据的 lobsegment 位置，此时 lob 的存储是以 chunk 为单位，chunk 的大小为标准数据块(db_block_size)大小的整数倍，最大为 32K，一个 chunk 只能保存一行 lob 对象的数据，如果出现填不满的情况，空闲空间是被置空的。
对于存储在 lobsegment 中的 lob 数据，是否会经过 buffer cache 进行缓存呢？
这个是由 lob 的存储参数 cache/nocache 进行控制，若设置不合理，可能会造成较严重的数据库性能问题，比如设置为 nocache 时，Oracle 对该 lob 列读写时会绕开 buffer cache，直接使用 direct read/write 的方式，可能会造成较高的物理 IO 负载；而设置为 cache 时，则在对该 Lob 列进行读写时会使用 buffer cache 进行 Lob chunk 的缓存，在该情况下有可能造成大量的 buffer 被从 LRU chain 上 age out 出内存，从而影响数据库性能。
文中如有疏漏之处，望指正！
环境版本信息
Oracle 版本：12.1.0.2
OS 版本：CentOS 7.5
验证测试
在 Oracle 12c 之前的版本中，varchar2 数据类型的最大长度为 4000 bytes。从 12c 的版本开始，引入了 extended data type 的字段类型，简单地讲，就是可以直接定义 varchar2、nvarchar2 和 raw 数据类型的长度为最大 32767 bytes(即 32k) 了，方便用户对较长的字符串进行存储。该功能是通过参数 max_string_size 来进行控制，该参数可以取值如下：
当使用 extended data type 的字段时，在数据库的底层又是如何存储的呢？我们通过如下案例来进行验证。
1、确认一下相应的用户下没有 lob 信息
SQL> select table_name,COLUMN_NAME,INDEX_NAME,CHUNK,CACHE from user_lobs;no rows selected
复制代码
2、创建相应的表
SQL> create table test_extend_type (id number, name varchar2(4000), detail_info varchar2(32767)); --此处定义为扩展的数据类型
复制代码
3、查看 lob 信息
SQL> set linesize 200 pagesize 200SQL> col TABLE_NAME format a25SQL> col COLUMN_NAME format a25SQL> col INDEX_NAME format a25SQL> select table_name,COLUMN_NAME,INDEX_NAME,CHUNK,CACHE from user_lobs;TABLE_NAME COLUMN_NAME INDEX_NAME CHUNK CACHE------------------------- ------------------------- ------------------------- ---------- ----------TEST_EXTEND_TYPE DETAIL_INFO SYS_IL0000111114C00003$$ 8192 YES
复制代码
可以发现，数据库自动为扩展类型的 detail_info 列创建了一个 lob segment。
4、对 detail_info 列插入不超过 4000 bytes 的数据
SQL> insert into TEST_EXTEND_TYPE values(1111,'aaaa','bbbbbb');1 row created.SQL> commit;Commit complete.
复制代码
5、Dump 出数据块结构
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) 2 from TEST_EXTEND_TYPE where id=1111;DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------ ------------------------------------ 2 133SQL> alter system dump datafile 2 block 133; System altered.SQL> oradebug setmypid;Statement processed.SQL> oradebug tracefile_name;/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_24291.trc
复制代码
如下图所示，列 detail_info 存储为 SecureFile 的 Blob 类型，由于长度并不超过 4000 bytes，所以保存为 DataInRow：
备注：Oracle 在存储 Lob 时，分为 BasicFiles 和 SecureFiles 两种存储结构。后者是 11g 引入的，支持加密、压缩和去重等功能，12c 开始默认创建为 SecureFiles 格式。
6、对 detail_info 插入超过 4000 bytes 数据
SQL> insert into TEST_EXTEND_TYPE values(202020,'Sam_testing',lpad('TEST',500000,'SAM')); 1 row created.SQL> commit;Commit complete.SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from TEST_EXTEND_TYPE where id=202020;DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------ ------------------------------------ 2 133
复制代码
7、Dump 出数据块结构进行对比
SQL> alter system dump datafile 2 block 133; System altered.SQL> oradebug setmypid;Statement processed.SQL> oradebug tracefile_name;/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30873.trc
复制代码
如下图所示，由于数据长度超过了 4000 bytes，直接被存储到了 SecureFile 的 Blob Segment 中：
通过如上分析，我们可以得出如下结论：
当定义列为 extended data type 时，Oracle 会自动创建一个 SecureFile 的 Lob segment；
当该字段存储不超过 4000 bytes 的数据时，数据是和数据行其他的列一起保存在 data block 中的，以数据块为存储单位；
当改字段存储超过 4000 bytes 的数据时，数据是存放 Lob segment 中的，以 chunk 为单位，在数据块上会存储一个 locator 指针，指向实际存储该列数据的 chunk 位置；
当没有创建 lob 字段而数据库中有大量的 direct path read/write(lob)等待事件时，需要检查一下是否启用了 extended data type 并且发生了自动存储转换，若是，则需要对相应的 Lob segment 进行调优。
附：如下是启用 extended data type 的步骤：
SQL> conn / as sysdbaConnected.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> SQL> startup upgradeORACLE instance started.Total System Global Area 5368708176 bytesFixed Size 8907856 bytesVariable Size 956301312 bytesDatabase Buffers 4395630592 bytesRedo Buffers 7868416 bytesDatabase mounted.Database opened.SQL> alter system set max_string_size=extended;System altered.SQL> @?/rdbms/admin/utl32k.sqlSession altered.Session altered.DOC>#######################################################################DOC>#######################################################################DOC> The following statement will cause an "ORA-01722: invalid number"DOC> error if the database has not been opened for UPGRADE.DOC>DOC> Perform a "SHUTDOWN ABORT" andDOC> restart using UPGRADE.DOC>#######################################################################DOC>#######################################################################DOC>#no rows selectedDOC>#######################################################################DOC>#######################################################################DOC> The following statement will cause an "ORA-01722: invalid number"DOC> error if the database does not have compatible >= 12.0.0DOC>DOC> Set compatible >= 12.0.0 and retry.DOC>#######################################################################DOC>#######################################################################DOC>#PL/SQL procedure successfully completed.Session altered.2 rows updated.Commit complete.System altered.PL/SQL procedure successfully completed.Commit complete.System altered.Session altered.Session altered.Table created.Table created.Table created.Table truncated.0 rows created.PL/SQL procedure successfully completed.STARTTIME--------------------------------------------------------------------------------09/25/2020 15:00:33.678157000PL/SQL procedure successfully completed.No errors.PL/SQL procedure successfully completed.Session altered.Session altered.0 rows created.no rows selectedno rows selectedDOC>#######################################################################DOC>#######################################################################DOC> The following statement will cause an "ORA-01722: invalid number"DOC> error if we encountered an error while modifying a column toDOC> account for data type length change as a result of enabling orDOC> disabling 32k types.DOC>DOC> Contact Oracle support for assistance.DOC>#######################################################################DOC>#######################################################################DOC>#PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.Commit complete.Package altered.Package altered.Session altered.SQL> SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> SQL> startupORACLE instance started.Total System Global Area 5368708176 bytesFixed Size 8907856 bytesVariable Size 956301312 bytesDatabase Buffers 4395630592 bytesRedo Buffers 7868416 bytesDatabase mounted.Database opened.SQL> SQL> show parameter max_string_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------max_string_size string EXTENDEDSQL>
复制代码
讲师介绍
吴海存，10g / 11g / 12c OCM，Oracle Exadata / Golden Gate 专家，曾于 Amazon 和 Oracle 公司担任全球业务资深 DBA，目前供职于中国农业银行，负责数据库前沿技术研究和支持。
原文链接：Varchar竟然会自动存储成lob类型？
评论