2013-04-08 lob pre-fetching

最新推荐文章于 2021-04-06 01:45:40 发布

cnmqy9945

最新推荐文章于 2021-04-06 01:45:40 发布

阅读量190

点赞数

文章标签：数据库

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

Leave a comment | Trackback

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/