Varchar竟然会自动存储成lob类型?

吴海存 2021-01-24 13:32:00

​讲师介绍

吴海存,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.

 

5Dump出数据块结构

 

 

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

 

7Dump出数据块结构进行对比

 

 

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> 

最新评论
访客 2021年09月03日

有没有1000多张表

访客 2021年08月28日

metrics =》 metrix 错误

访客 2021年08月25日

只看到如何避免,如何减少书写慢 sql

访客 2021年08月25日

没看到如何治理呀

访客 2021年07月23日

果然k8s不是神!

活动预告