讲师介绍
吴海存，10g / 11g / 12c OCM，Oracle Exadata / Golden Gate专家，曾于Amazon和Oracle公司担任全球业务资深DBA，目前供职于中国农业银行，负责数据库前沿技术研究和支持。
本文主要是通过对底层数据块进行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来进行控制，该参数可以取值如下：
standard：表示12c之前的标准长度，即varchar2、nvarchar2最大为4000 bytes，raw最大为2000 bytes；
extended：表示varchar2、nvarchar2、raw的最大长度可以指定为32k bytes。
当使用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 200
SQL> col TABLE_NAME format a25
SQL> col COLUMN_NAME format a25
SQL> col INDEX_NAME format a25
SQL> 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 133
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_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 sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 5368708176 bytes
Fixed Size 8907856 bytes
Variable Size 956301312 bytes
Database Buffers 4395630592 bytes
Redo Buffers 7868416 bytes
Database mounted.
Database opened.
SQL> alter system set max_string_size=extended;
System altered.
SQL> @?/rdbms/admin/utl32k.sql
Session 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" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database does not have compatible >= 12.0.0
DOC>
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.678157000
PL/SQL procedure successfully completed.
No errors.
PL/SQL procedure successfully completed.
Session altered.
Session altered.
0 rows created.
no rows selected
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if we encountered an error while modifying a column to
DOC> account for data type length change as a result of enabling or
DOC> 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 immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 5368708176 bytes
Fixed Size 8907856 bytes
Variable Size 956301312 bytes
Database Buffers 4395630592 bytes
Redo Buffers 7868416 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string EXTENDED
SQL>
如果字段的最大可能长度超过255字节，那么长度值可能…
只能说作者太用心了，优秀
感谢详解
一般干个7-8年（即30岁左右），能做到年入40w-50w；有…
230721