Oracle 10g小Blob或Clob是否不能内联存储? [英] Oracle 10g small Blob or Clob not being stored inline?

问题描述

根据我阅读的文档，CLOB或BLOB的默认存储为内联，这意味着如果大小小于大约4k，则它将存储在表中.

但是，当我在Oracle(10.2.0.1.0)的虚拟表中对此进行测试时，来自Oracle Monitor(由Allround Automations提供)的性能和响应表明它与表无关.

这是我的测试场景...

create table clobtest ( x int primary key, y clob, z varchar(100) )  
;
insert into clobtest 
   select object_id, object_name, object_name  
   from all_objects where rownum < 10001  
;
select COLUMN_NAME, IN_ROW 
from user_lobs 
where table_name = 'CLOBTEST'  
;

这显示:是(建议Oracle将Clob存储在行中)

select x, y from CLOBTEST where ROWNUM < 1001 -- 8.49 seconds  
select x, z from CLOBTEST where ROWNUM < 1001 -- 0.298 seconds  

因此，在这种情况下，CLOB值的最大长度为30个字符，因此应始终为内联.如果我运行Oracle Monitor，对于返回的每一行，它将显示一个LOB.Length以及一个LOB.Read()，再次表明该Clob值与表无关.

我也尝试过创建这样的表

create table clobtest 
    ( x int primary key, y clob, z varchar(100) ) 
    LOB (y) STORE AS     (ENABLE STORAGE IN ROW)  

，但得到的结果完全相同.

有人对我如何强制(说服，鼓励)Oracle在表中直接存储Clob值有任何建议吗? (我希望获得与读取varchar2列z相似的响应时间)

更新:如果我运行此SQL

select COLUMN_NAME, IN_ROW, l.SEGMENT_NAME, SEGMENT_TYPE, BYTES, BLOCKS, EXTENTS 
from user_lobs l 
      JOIN USER_SEGMENTS s
       on (l.Segment_Name = s. segment_name )
where table_name = 'CLOBTEST'  

然后我得到以下结果...

Y   YES SYS_LOB0000398621C00002$$   LOBSEGMENT  65536   8   1  

解决方案

Oracle LOB的行为如下.

在以下情况下，将LOB内联存储:

(
  The size is lower or equal than 3964
  AND
  ENABLE STORAGE IN ROW has been defined in the LOB storage clause
) OR (
  The value is NULL
)

在以下情况下，LOB存储在行外:

(
  The value is not NULL
) AND (
  Its size is higher than 3964
  OR
  DISABLE STORAGE IN ROW has been defined in the LOB storage clause
)

现在，这不是唯一可能影响性能的问题.

如果最终未将LOB最终以内联方式存储，则Oracle的默认行为是避免对其进行缓存(只有内联LOB与该行的其他字段一起缓存在缓冲区高速缓存中).为了告诉Oracle也缓存非内联LOB，在定义LOB时应使用CACHE选项.

默认行为是行中启用存储"和"NOCACHE"，这意味着将内联小型LOB，大型LOB将不会(也不会被缓存).

最后，在通信协议级别还存在性能问题.典型的Oracle客户端将为每个LOB执行两次额外的往返操作以获取它们: -检索LOB的大小并相应分配内存的工具 -一个用来获取数据本身的文件(前提是LOB很小)

即使使用数组接口检索结果，也会执行这些额外的往返.如果检索1000行并且数组大小足够大，则需要支付1次往返来检索行，并支付2000次往返来检索LOB的内容.

请注意，它不取决于LOB是否以内联方式存储.它们是完全不同的问题.

为了在协议级别进行优化，Oracle提供了一个新的OCI动词，可以在一次往返中获取多个LOB(OCILobArrayRead).我不知道JDBC是否存在类似的东西.

另一种选择是将LOB绑定在客户端，就好像它是一个大RAW/VARCHAR2.这仅在可以定义LOB的最大大小的情况下有效(因为必须在绑定时提供最大大小).这个技巧避免了额外的漫游:LOB像RAW或VARCHAR2一样被处理.我们在LOB密集型应用程序中经常使用它.

一旦优化了往返次数，就可以在网络配置中调整数据包大小(SDU)的大小，以更好地适应这种情况(即，有限的大型往返次数).这样可以减少"SQL *从客户端获取更多数据净额"和"SQL *从客户端获取更多数据净额"的等待事件.

According to the documents I've read, the default storage for a CLOB or BLOB is inline, which means that if it is less than approx 4k in size then it will be held in the table.

But when I test this on a dummy table in Oracle (10.2.0.1.0) the performance and response from Oracle Monitor (by Allround Automations) suggest that it is being held outwith the table.

Here's my test scenario ...

create table clobtest ( x int primary key, y clob, z varchar(100) )  
;
insert into clobtest 
   select object_id, object_name, object_name  
   from all_objects where rownum < 10001  
;
select COLUMN_NAME, IN_ROW 
from user_lobs 
where table_name = 'CLOBTEST'  
;

This shows: Y YES (suggesting that Oracle will store the clob in the row)

select x, y from CLOBTEST where ROWNUM < 1001 -- 8.49 seconds  
select x, z from CLOBTEST where ROWNUM < 1001 -- 0.298 seconds  

So in this case, the CLOB values will have a maximum length of 30 characters, so should always be inline. If I run Oracle Monitor, it shows a LOB.Length followed by a LOB.Read() for each row returned, again suggesting that the clob values are held outwith the table.

I also tried creating the table like this

create table clobtest 
    ( x int primary key, y clob, z varchar(100) ) 
    LOB (y) STORE AS     (ENABLE STORAGE IN ROW)  

but got exactly the same results.

Does anyone have any suggestions how I can force (persuade, encourage) Oracle to store the clob value in-line in the table? (I'm hoping to achieve similar response times to reading the varchar2 column z)

UPDATE: If I run this SQL

select COLUMN_NAME, IN_ROW, l.SEGMENT_NAME, SEGMENT_TYPE, BYTES, BLOCKS, EXTENTS 
from user_lobs l 
      JOIN USER_SEGMENTS s
       on (l.Segment_Name = s. segment_name )
where table_name = 'CLOBTEST'  

then I get the following results ...

Y   YES SYS_LOB0000398621C00002$$   LOBSEGMENT  65536   8   1  

解决方案

The behavior of Oracle LOBs is the following.

A LOB is stored inline when:

(
  The size is lower or equal than 3964
  AND
  ENABLE STORAGE IN ROW has been defined in the LOB storage clause
) OR (
  The value is NULL
)

A LOB is stored out-of-row when:

(
  The value is not NULL
) AND (
  Its size is higher than 3964
  OR
  DISABLE STORAGE IN ROW has been defined in the LOB storage clause
)

Now this is not the only issue which may impact performance.

If the LOBs are finally not stored inline, the default behavior of Oracle is to avoid caching them (only inline LOBs are cached in the buffer cache with the other fields of the row). To tell Oracle to also cache non inlined LOBs, the CACHE option should be used when the LOB is defined.

The default behavior is ENABLE STORAGE IN ROW, and NOCACHE, which means small LOBs will be inlined, large LOBs will not (and will not be cached).

Finally, there is also a performance issue at the communication protocol level. Typical Oracle clients will perform 2 additional roundtrips per LOBs to fetch them: - one to retrieve the size of the LOB and allocate memory accordingly - one to fetch the data itself (provided the LOB is small)

These extra roundtrips are performed even if an array interface is used to retrieve the results. If you retrieve 1000 rows and your array size is large enough, you will pay for 1 roundtrip to retrieve the rows, and 2000 roundtrips to retrieve the content of the LOBs.

Please note it does not depend on the fact the LOB is stored inline or not. They are complete different problems.

To optimize at the protocol level, Oracle has provided a new OCI verb to fetch several LOBs in one roundtrips (OCILobArrayRead). I don't know if something similar exists with JDBC.

Another option is to bind the LOB on client side as if it was a big RAW/VARCHAR2. This only works if a maximum size of the LOB can be defined (since the maximum size must be provided at bind time). This trick avoids the extra rountrips: the LOBs are just processed like RAW or VARCHAR2. We use it a lot in our LOB intensive applications.

Once the number of roundtrips have been optimized, the packet size (SDU) can be resized in the net configuration to better fit the situation (i.e. a limited number of large roundtrips). It tends to reduce the "SQL*Net more data to client" and "SQL*Net more data from client" wait events.

这篇关于Oracle 10g小Blob或Clob是否不能内联存储?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！