2013-04-08 lob pre-fetching

http://yumianfeilong.com/html/2013/03/24/601.html

LobPrefetch: Faster Lob fetching in a single roundtrip

March 24th, 2013 | Categories: Boring | Tags: JDBC, Lob, Oracle

setLobPrefetchSize是JDBC driver 11.2中引入的新方法,它设置可以一次读取lob内容(包括lob metadata和lob data)的大小。默认是4k,通常默认值有些小,可以根据LOB数据的实际大小来调整,会对LOB fetch有较大的性能提高,包括降低CPU消耗和减少不必要的网络传输。

Overrides the LOB prefetch size for this statement. With LOB prefetch, meta-data such as the lob length and the chunk size as well as the beginning of the LOB data are sent along with the locator during the regular fetch operation. This has a significant performance impact especially for small LOBs which can potentially be entirely prefetched. The data is then available to the user without having to go through the LOB protocol. Note that this is available only with the Oracle database starting in 11.1.

For Oracle Database 11g Release 2 (11.2) JDBC drivers, the number of round trips is reduced by prefetching the metadata such as the LOB length and the chunk size as well as the beginning of the LOB data along with the locator during regular fetch operations.

If you select LOB columns into a result set, the new capability in the server and JDBC drivers allow some or all of the data to be prefetched to the client, when the locator is fetched. Subsequent read API calls will get the data from the prefetch buffers without any need to make database round trips.

LOB prefetch is enabled by default (see the oracle.jdbc.defaultLobPrefetchSize connection property which default value is 4k bytes for BLOBs and 4k chars for CLOBs). The LOB prefetch size can be set at the connection level through the property or at the statement level through this method. The statement level setting overrides the setting at the connection level. This setting can also be overriden at the column level through the defineColumnType method where the size represents the number of bytes (or chars for CLOB) to prefetch.

如下为简单测试,CLOB平均长度为6927bytes,执行10,000次unique index scan,客户端和数据库服务器延迟为18ms. 测试前将所有结果load到SGA中,CLOB为cache store in row存储。

Lobprefetchsize (bytes)

Response time (s)

CPU used by this session(10 milliseconds)

User calls

Avg_clob_length(bytes)

4000

315

530

18940

6927

8000

255

378

12407


16000

207

339

10219


32000

204

387

10096


64000

212

426

10040


如上测试,当CLOB数据不能够在默认的LobPrefetchSize下一次返回给客户端时,增加LobPrefetchSize既能够降低响应时间(减少network round trip),而且可以减少DB CPU的时候。

由于该功能不支持10g,所以如果在10g中有频繁读取LOB的应用,最好升级到11G.

可参考 http://nzoug.org/files/conf2011/Advanced-Java-Prog.pdf







http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1769036900346343041

because you are looking at sqlplus - sqlplus (a very simple command line program designed to print out ad-hoc query results) parsed your query and discovered "hey, there is a REALLY big field here we need to print out, lets go slow by slow so we can do that easily"

In short, it was a design decision made by the sqlplus developer, they don't array fetch longs/lobs - by their decision.

that doesn't mean you cannot array fetch them - just that they decided "no, we will not, don't want to"

ops$tkyte%ORA10GR2> CREATE TABLE test
  2  (  x number,
  3      val CLOB
  4  ) ;

Table created.

ops$tkyte%ORA10GR2> insert into test select rownum, rpad('X',1000,'X') from dual connect by 
level<=100;

100 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA10GR2> begin
  2          for x in ( select * from test )
  3          loop
  4                  null;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.


SELECT * FROM TEST

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         23          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0         24          0         100




plsql (as of 10g and above) implicitly array fetches 100 rows at a time with implicit cursors. It only called fetch twice (once to get rows 1-100 and once more to discover "no more data")

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24383181/viewspace-757968/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/24383181/viewspace-757968/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值